Bug #31197 federated table that can't connect to remote table crashes system
Submitted: 26 Sep 2007 1:13 Modified: 4 Oct 2007 0:17
Reporter: b brown Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.0.45 OS:Any
Assigned to: CPU Architecture:Any
Tags: Connections, crash, federated

[26 Sep 2007 1:13] b brown
Description:
Local federated table was unable to connect to remote table on a remote server due to communications failure at the remote end (which continued for several hours).

On our production machine serving the internet connections piled up to the point that the system was no longer able to operate due to "too many connections".  (This killed a working web sales system on a different domain on the same server.)

MySQL would respond but attempting to "use" a database would hang.  Same problem on both Red Hat Apache and WinXP.

Speculation:  The local federated table KEEPS trying to connect to the remote; there seems to be no failure mode to stop this behavior (said behavior leading to the inability of anything else to connect).  

How to repeat:
Pull the power plug on the remote server with the remote table (or otherwise disable communications).

Suggested fix:
Provide the federated table with some sort of failure mode when it is unable to connect such that no longer continues to try to connect (or perhaps does so on some limited basis?)

As a sidelight, I was asked if there could be a failover to another IP address... This might be useful but right now apparently there can be only one IP address in the federated connect statement.  But even with such a failover, if my speculation is correct and a non-connect failure is going to gum up ALL the works, the federated table can't be used on a production machine.
[26 Sep 2007 6:13] Sveta Smirnova
Thank you for the report.

I can not repeat described behaviour using next sequence of actions:

1. Create table on remote server.
2. Create federated table.
3. Shutting down remote server.
4. Issuing different commands on local server. If I use federated table I get correct error after timeout.

Please provide your actions how to repeat the bug. May be do you use many parallel connections which try to access to federated table? Also please check if your case is duplicate of bug #29801
[26 Sep 2007 15:24] b brown
(Typically) Today things are occurring differently.  At least one system is now issuing (federated table) does not exist.  Yesterday it was causing the system to hang at the "use DB" command.  I do not believe it to be the same as the other bug.  We are investigating the possibility that the communications connection to the remote server that contains the remote table was "feeble".  This has been noted before where a connection is made but is incapable of returning data.
[27 Sep 2007 16:41] MySQL Verification Team
I can't repeat the behavior reported, same as Sveta commented.
[4 Oct 2007 0:17] b brown
Having started this, I have also tried to repeat it and cannot do so.  We have tried everything and it has not reacted as it did initially.