I have one database in server A but my application is running in server B
So, in order to separate the information, I used Federated in server B to access the tables from server A. I tried two things:
CREATE SERVER s
FOREIGN DATA WRAPPER mysql
OPTIONS (DATABASE 'XPTO', USER 'aaa', PASSWORD 'aaa', HOST 'serverA');
CREATE TABLE IF NOT EXISTS `test` (
`col1` int(11) NOT NULL AUTO_INCREMENT,
`col2` int(11) NOT NULL,
PRIMARY KEY (`col1`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='s';
And I tried this:
CREATE TABLE IF NOT EXISTS `test` (
`col1` int(11) NOT NULL AUTO_INCREMENT,
`col2` int(11) NOT NULL,
PRIMARY KEY (`col1`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://aaa:aaa@IP_ADDRESS:3306/XPTO/test'
The problem is that I have a lot of connections. It is one connection foreach table and every time I execute the query again, it creates a new connection. If I have 11 tables, it creates 11 connections, after executing again, I get 11+11 connections = 22 connections, and so on...
After creating this, the problem was still there. So I tried to create a view in server B from the tables that I created with the last code and I did the querys at the views only. But the problem still there again!
How can I execute the query and close the connections that are "sleep"?