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 17:50	Modified:	23 Sep 2014 12:42
Reporter:	Rob Blick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5.0.15/BK source 5.0.17	OS:	Linux (CentOS 4.1/Suse 9.3)
Assigned to:		CPU Architecture:	Any

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)

I can repeat this behavior if the federated table goes through MySQL Proxy to connect to the source server. After wait_timeout elapses, a query against the federated table will report the lost connection. The query after that will recreate the connection and work properly (even if it is longer than wait_timeout since the failed query).

Bug #67861 was marked as duplicate of this one.

this bug still happens with 5.6.19!

Related to Federated Table :

Whenever a query is issued against a federated table, a single/multiple [ depends on Number = table_open_cache_instances] will be created from the DB instance hosting the federated table to the DB instance hosting the main table. If those single/mutiple connection on the DB instance hosting main table expires because of [wait_timeout,connect_timeout] or if those gets terminated manually , any new queries on federated table will result in "ERROR 1158 (08S01) at line 1: Got an error reading communication packets" on the DB instance hosting the federated table.

Reason : Flushing of the federated table does not happen properly from the table_open_cache_instances ,

Solution : To avoid this, the temporary solution is to manually/forcefully flush only those federated tables once before every [wait_timeout,connect_timeout] seconds. In our case we put the flush command in event_scheduler since the number of federated tables were very less and we knew exactly which federated tables were rarely getting queried with gap of more than [wait_timeout,connect_timeout] seconds. This used to affect even our MEB backup when it used execute "flush tables with READ lock".

flush table <federated_table_name>; -- executed every 4 hours

Key : The frequency with which it errors depends on how many table_open_cache_instances is configured on the Instance.

Since then, after putting the manual flush of federated tables with a frequence less than [wait_timeout,connect_timeout] , this issue has not showed up. Hope this helps.

Thanks
Prasad

Noted in 5.6.20, 5.7.5 changelogs.

If a FEDERATED table was accessed after wait_timeout expired, a Lost
connection to MySQL server error occurred without an attempt to
re-establish the connection.