MySQL Bugs: #9926: Federated table throws error 1219 (HY000) if remote server is reset

Bug #9926	Federated table throws error 1219 (HY000) if remote server is reset
Submitted:	15 Apr 2005 10:49	Modified:	22 Jun 2005 19:44
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.3-beta	OS:	Any (All)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
Upon sucessful creation of a federated table, if the remote server process is ever restarted, the federated table will respond back with:

 select count(*) from client where location_id=63 && client_id=88888;                                   ERROR 1219 (HY000): Error running query on master: Lost connection to MySQL server during query

mysql> select count(*) from client where location_id=63 && client_id=88888;
ERROR 1219 (HY000): Error running query on master: MySQL server has gone away

mysql> select count(*) from client where location_id=63 && client_id=88888;
ERROR 1219 (HY000): Error running query on master: MySQL server has gone away

and never return unless the federated table is dropped and re-created

How to repeat:
1.) Create federated table

2.)  on remote server, shutdown and restart mysql

3.) try query against federated table - should return back error 1219

Suggested fix:
use 'mysql has gone away' logic for handling step #4 ( Remote database -> MySQL client API)

Verified w/5.0.4

Tried adding mysql->reconnect, which still doesn't solve this problem. Still investigating.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25600

Needed to also add "mysql->reconnect" to ha_federated::open, as well as already tried ha_federated::create. Tested successfully:

Federated server window:

mysql> select * from client where location_id = 100 LIMIT 1;
+-------------+-----------+-------------------+---------------------+------------+
| location_id | client_id | client_name       | client_title        | client_age |
+-------------+-----------+-------------------+---------------------+------------+
|         100 |        66 | fGNsxvHFXcAFCy6XN | marketing assistant |         43 |
+-------------+-----------+-------------------+---------------------+------------+
1 row in set (0.62 sec)

mysql> select * from client where location_id = 100 LIMIT 1;
ERROR 1219 (HY000): Error running query on master: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
mysql> select * from client where location_id = 100 LIMIT 1;
+-------------+-----------+-------------------+---------------------+------------+
| location_id | client_id | client_name       | client_title        | client_age |
+-------------+-----------+-------------------+---------------------+------------+
|         100 |        66 | fGNsxvHFXcAFCy6XN | marketing assistant |         43 |
+-------------+-----------+-------------------+---------------------+------------+
1 row in set (0.56 sec)

mysql> select * from client where location_id = 100 LIMIT 1;
ERROR 1219 (HY000): Error running query on master: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
mysql> select * from client where location_id = 100 LIMIT 1;
+-------------+-----------+-------------------+---------------------+------------+
| location_id | client_id | client_name       | client_title        | client_age |
+-------------+-----------+-------------------+---------------------+------------+
|         100 |        66 | fGNsxvHFXcAFCy6XN | marketing assistant |         43 |
+-------------+-----------+-------------------+---------------------+------------+
1 row in set (0.82 sec)

Terminal window, foreign server:

radha:/usr/local/mysql root# /usr/local/mysql/bin/mysqld_safe &
[1] 22420
radha:/usr/local/mysql root# Starting mysqld daemon with databases from /usr/local/mysql/data

radha:/usr/local/mysql root# /usr/local/mysql/bin/mysqladmin -uroot shutdown
050604 02:24:04  mysqld ended

[1]+  Done                    /usr/local/mysql/bin/mysqld_safe
radha:/usr/local/mysql root# /usr/local/mysql/bin/mysqld_safe &
[1] 22453
radha:/usr/local/mysql root# Starting mysqld daemon with databases from /usr/local/mysql/data

radha:/usr/local/mysql root# /usr/local/mysql/bin/mysqladmin -uroot shutdown
radha:/usr/local/mysql root# 050604 02:24:32  mysqld ended

[1]+  Done                    /usr/local/mysql/bin/mysqld_safe
radha:/usr/local/mysql root# /usr/local/mysql/bin/mysqld_safe &
[1] 22481
radha:/usr/local/mysql root# Starting mysqld daemon with databases from /usr/local/mysql/data

recreating patch - original approved patch was in a bk tree/clone that since has been abandoned. Also including locking changes per Mikael Ronstrom, and will ask for his review.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26328

Pushed fix