1

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.

Gruber
  • 151
  • 3
  • 9
  • 1
    Check the following bug reports to see if these are the cases with you http://bugs.mysql.com/bug.php?id=27180 (querying a big table) http://bugs.mysql.com/bug.php?id=28269 (use of a reserve word in table definition) – Wasif Feb 01 '13 at 06:46
  • @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 Feb 01 '13 at 07:44

1 Answers1

2

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.

Michael - sqlbot
  • 21,988
  • 1
  • 57
  • 81
  • 1
    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? – Gruber Feb 08 '13 at 08:16
  • 2
    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. – Michael - sqlbot Feb 08 '13 at 17:23