MySQL error 1429, federation

Question

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.

Solution

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.

Comments

[Wasif]

Check the following bug reports to see if these are the cases with you bugs.mysql.com/bug.php?id=27180 (querying a big table) bugs.mysql.com/bug.php?id=28269 (use of a reserve word in table definition)

[Gruber]

@mwasif: Thanks a lot. I've updated the question to include the versions of MySQL I use. Apparently those old versions may have some bugs. One (tedious and risky) option could be to upgrade my servers to modern versions. Perhaps I'll try using ALTER TABLE as described in your linked article.

[Gruber]

Thanks for a superb answer. Indeed, max_connections is 100, and Max_used_connections is 101 on my remote server. Intriguingly, Aborted_connects is 26515. Seems to indicate a significant problem. Currently my setup is working again but I will try FLUSH TABLES when it goes down. Exactly how do I check if the remote server shows a lot of idle inbound federated connections?

[Michael - sqlbot]

That isn't something you should necessarily expect to see, but I thought I would mention it since it's a possibility. Since the federated engine makes an ordinary MySQL client connection to the target server, you would see these along with other client connections in SHOW FULL PROCESSLIST. You need to be logged in as a user with the SUPER privilege to see processes other than your own.