MySQL Bugs: #14874: Error 2013: Lost connection to MySQL server with Federated table

Bug #14874	Error 2013: Lost connection to MySQL server with Federated table
Submitted:	11 Nov 2005 18:50	Modified:	23 Nov 17:47
Reporter:	Rob Blick
Status:	Can't repeat
Category:	Server: Federated	Severity:	S2 (Serious)
Version:	5.0.15/BK source 5.0.17	OS:	Linux (CentOS 4.1/Suse 9.3)
Assigned to:		Target Version:
Triage:	Triaged: D5 (Feature request)

Description:
If a Federated table is accessed after wait_timeout has expired, an Error 2013: Lost
connection to MySQL server occurs.  The server accessing the remote table should first
try to re-establish the connection transparently before reporting the error.

How to repeat:
On one server:
use test;
create table T(a int);
grant all on test.* to 'test'@'%' identified by 'bogus';
flush privileges;
insert into T values (1);

On another server:
use test;
create table F(a int) ENGINE=FEDERATED CONNECTION='mysql://test:bogus@n.d.d.e/test/T';
select * FROM F\G
*************************** 1. row ***************************
a: 1
1 row in set (0.00 sec)

now, wait for the connection to expire (wait_timeout), and run the query again:
mysql> select * FROM F\G
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source.
Data source error: ': 2013 : Lost connection to MySQL server during query'
mysql> select * FROM F\G
*************************** 1. row ***************************
a: 1
*************************** 2. row ***************************
a: 2
2 rows in set (0.02 sec)

mysql> 

Suggested fix:
Have the local server re-establish the federated connection transparently.  If this fails
(after a few attempts), only then report an error.

I was unable to repeat with a server built from current source. I got
the same behavior as MyISAM engine:

[miguel@hegel 5.0]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.17-debug

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

mysql> create table tb2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb2 set id=1;
Query OK, 1 row affected (0.00 sec)

mysql> set wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: test

+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.03 sec)

mysql> set wait_timeout=28000;
Query OK, 0 rows affected (0.00 sec)

mysql> create table F(a int) ENGINE=FEDERATED
    -> CONNECTION='mysql://test:bogus@192.168.0.33/test/T';
Query OK, 0 rows affected (4.78 sec)

mysql> select * FROM F\G
*************************** 1. row ***************************
a: 1
1 row in set (0.09 sec)

mysql> set wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)

mysql> select * FROM F\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: test

*************************** 1. row ***************************
a: 1
1 row in set (0.03 sec)

mysql>

Need to do set GLOBAL wait_timeout=5 on the remote server before the federated connection
is established.  Once the federated connection disappears from the show processlist
output, you'll see the error: 

Call the two servers R and L.  R is the remote server where the table physically resides.
 L is the local server whether the CREATE TABLE...ENGINE=FEDERATED is executed.

On server R:

mysql> create table T(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into T values (1);
Query OK, 1 row affected (0.00 sec)

mysql> set GLOBAL wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  5 | root | localhost | test | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
--Verify that a connection from the other server (the one where the CREATE
TABLE...ENGINE=FEDERATED is executed) does not exist.

On server L:

mysql> create table F(a int) ENGINE=FEDERATED
CONNECTION='mysql://test:bogus@n.d.d.e/test/T';
Query OK, 0 rows affected (0.01 sec)

mysql> select * FROM F;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Now, back over on server R:

mysql> show processlist;
+----+------+---------------+------+---------+------+-------+------------------+
| Id | User | Host          | db   | Command | Time | State | Info             |
+----+------+---------------+------+---------+------+-------+------------------+
|  5 | root | localhost     | test | Query   |    0 | NULL  | show processlist |
|  7 | test | f.d.d.e:42489 | test | Sleep   |    3 |       | NULL             |
+----+------+---------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

--Wait 10 seconds
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  5 | root | localhost | test | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
--Verify that the connection between L and R has expired and does not appear in the
processlist

Now, back over on server L:
mysql> select * FROM F;
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source.
Data source error: ': 2013 : Lost connection to MySQL server during query'
mysql> select * FROM F;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

Thank you for the bug report and feedback.

mysql> select * FROM F;
ERROR 1430 (HY000): There was a problem processing the query on the foreign data source.
Data source error: ': 2013 : Lost connection to MySQL server during query'
mysql> select * FROM F;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.04 sec)

mysql>

Note that the implicit reconnect can be "dangerous" if it cannot restore settings like
"set names utf8;" in which case subsequent statements may cause the the connection to use
the wrong characters set potentially corrupting data.  (Note that I have these issues in
Debian/Sarge packages which are based on 4.1).  IMHO I think the failure is better wrt
any state that may be stored with the connection (not that I fond of the 'set names
utf8;' mechanism in the first place, but that's just the way it is).

Hi, this is obviously still a problem in 5.0.77.  Is there no fix for this yet?  And only
6.x is targetted for the resolution?  This makes the federated engine very much less
useful.

I couldn't repeat anymore with current source server 5.0.

Tested against the below server:

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.0.89-debug        | 
| version_comment         | Source distribution | 
| version_compile_machine | x86_64              | 
| version_compile_os      | unknown-linux-gnu   | 
+-------------------------+---------------------+
5 rows in set (0.00 sec)