MySQL error 1429, federation

federatedMySQL

I use MySQL federation to let one MySQL database access another's data tables. This has worked fine for more than a year, but today (from out of the blue) a stored routine reported this MySQL error:

Error Code: 1429. Unable to connect to foreign data source: Too many connections

If I try to access the federated table with a SELECT, I get 

Error Code: 1030 Got error 1 from storage engine

Moving on to the MySQL server which hosts the data, I can actually SELECT the desired data, so it seems to be working.

The server accessing the data is version 5.0.51a24 and the host server is 5.0.96-0. Old stuff, that is.

How do I solve the problem? Couldn't find much help in the MySQL documention.

Best Answer

Check the number of active connections on the remote/destination server.

Looking at the source code (of newer versions) I think it's a good bet that the "Too many connections" message is actually being returned from the remote end -- as opposed to originating on the local server (where the FEDERATED engine is being used). 

SHOW STATUS LIKE '%connect%';

Threads_connected is the current count, Max_used_connections is the highest value seen since the last time FLUSH STATUS was issued.

If that max value is the same as (or 1 more than) the value returned by SHOW VARIABLES LIKE 'max_connections'; then at some point that server was maxed out and could have returned this message.

If the remote server shows a lot of idle inbound federated connections, you may find that issuing FLUSH TABLES on the local server will release them.

Related Topic