Bug #17049 FEDERATED storage engine does not transparently reconnect
Submitted: 2 Feb 2006 15:47 Modified: 2 Oct 2006 17:18
Reporter: Kristian Koehntopp Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.0.18-max OS:any Linux
Assigned to: Bugs System CPU Architecture:Any

[2 Feb 2006 15:47] Kristian Koehntopp
Description:
When the data source for a FEDERATED table restarts, the connection is lost. The first query to the FEDERATED table after that bails out with an error instead of reconnecting transparently (and reporting a warning at best).

How to repeat:
On FEDERATED machine

root@localhost [kris]> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `d` varchar(4000) NOT NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://root:mysql2006@127.0.0.1:3041/kris/t'
1 row in set (0.00 sec)

root@localhost [kris]> select * from t;
+------+
| d    |
+------+
|      |
| abcd |
+------+
2 rows in set (0.01 sec)

On data source:
[mysql@ldvdbwm1 ~]$ mysqlstop-3041
Waiting for server shutdown: ...gone.
[mysql@ldvdbwm1 ~]$ mysqlstart-3041
Starting mysqld daemon with databases from /usr/local/mysql/instances/marina_s/data

On FEDERATED thereafter:

root@localhost [kris]> select * from t;
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: %The foreign data source you are trying to reference does not exist. Data source error:  : 2013 : Lost connection to MySQL server during query
root@localhost [kris]> select * from t;
+------+
| d    |
+------+
|      |
| abcd |
+------+
2 rows in set (0.01 sec)

Suggested fix:
Try a reconnect. If this works, create a warning, not an error and do the query. If the reconnect fails, report an error as above.
[2 Feb 2006 16:37] Kristian Koehntopp
Define "not ready".

On FEDERATED:

root@localhost [kris]> select * from t;
+------+
| d    |
+------+
|      |
| abcd |
+------+
2 rows in set (0.00 sec)

On Source:

[mysql@ldvdbwm1 ~]$ mysqlstop-3041
Waiting for server shutdown: ...gone.
[mysql@ldvdbwm1 ~]$ date; mysqlstart-3041; sleep 10; date
Thu Feb  2 17:36:29 CET 2006
Starting mysqld daemon with databases from /usr/local/mysql/instances/marina_s/data
Thu Feb  2 17:36:39 CET 2006
[mysql@ldvdbwm1 ~]$

On FEDERATED:

root@localhost [kris]> select * from t;
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: %The foreign data source you are trying to reference does not exist. Data source error:  : 2013 : Lost connection to MySQL server during query
root@localhost [kris]> select * from t;
+------+
| d    |
+------+
|      |
| abcd |
+------+
2 rows in set (0.00 sec)

This was a rapid-fire succession (cursor-up, return, cursor-up, return).
[2 Feb 2006 16:40] Kristian Koehntopp
Even more:

[mysql@ldvdbwm1 ~]$ mysqlstop-3041
Waiting for server shutdown: ....STOPPING server from pid file /usr/local/mysql/
instances/marina_s/data/ldvdbwm1.pid
060202 17:38:45  mysqld ended

gone.
[mysql@ldvdbwm1 ~]$ mysqlstart-3041
Starting mysqld daemon with databases from /usr/local/mysql/
instances/marina_s/data
[mysql@ldvdbwm1 ~]$ mysql-3041 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                            | Info             |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   13 | Waiting for master to send event | NULL             |
|  3 | root        | localhost | NULL | Query   |    0 | NULL                             | show processlist |
+----+-------------+-----------+------+---------+------+----------------------------------+------------------+
2 rows in set (0.00 sec)

Now on FEDERATED:

root@localhost [kris]> select * from t;
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source. Data source error: %The foreign data source you are trying to reference does not exist. Data source error:  : 2013 : Lost connection to MySQL server during query

So the source is verifyably up and responding to commands.
[12 May 2006 1:54] Patrick Galbraith
This reconnect is not handled by the engine, it's done by setting the connection to reconnect, and therefore is handled by the client API. This is how the client API works and I don't have a way to handle it.
[12 May 2006 21:09] Patrick Galbraith
I need info, help, on how to reproduce this easily. I don't have any problem with this in how it works. If the foreign server is up, it connects, if not, it fails as listed, and if I restart, it silently reconnects. Now, perhaps there's an instant of time where the federated connection can't connect, but I can't stumble upon that instant to get the problem.
[12 Jun 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[2 Oct 2006 17:18] Chris Calender
This bug is a duplicate of bug #14874.