MySQL Bugs: #18287: create federated table always times out, error 1159 ' '

Bug #18287	create federated table always times out, error 1159 ' '
Submitted:	16 Mar 2006 19:48	Modified:	1 Aug 2007 9:51
Reporter:	Jacek Becla	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.20-BK, 5.0.19-max, 5.1.19-BK	OS:	Linux (RedHat Enterprise 4)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	federated, sr5_1

Description:
Creation of a federated table consistently fails after 30 sec.
I even tried to create federated table that points to another
table that is managed by the same server. The 'source'
table is empty, it is hard to believe 30 sec timeout is not sufficient.

show processlist shows two queries during these 30 sec:
State: creating table
Info: create table f (x int) ENGINE=FEDERATED ....

State: Opening tables
Info: "SELECT *  FROM `test` WHERE  1=0"

The exact error is:
ERROR 1434 (HY000): Can't create federated table. Foreign data src error:  error: 1159  ''

How to repeat:
use testDB;
create table test (x int);
create table f (x int) ENGINE=FEDERATED CONNECTION='mysql://<userNameHere>@<hostNameHere>:3306/testDB/test';

Thank you for a bug report. Verified just as described with 5.0.20-BK (ChangeSet@1.2091.1.1, 2006-03-21 14:35:49+01:00, mats@mysql.com):

mysql> show engines\G
*************************** 1. row ***************************
 Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables

...

*************************** 10. row ***************************
 Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
 Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
 Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.01 sec)

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

mysql> create table test_ff (x int) engine=federated
    -> connection='mysql://root@suse:3306/test/test_f';
ERROR 1434 (HY000): Can't create federated table. Foreign data src error:  error: 1159  ''
mysql> create table test_ff (x int) engine=federated connection='mysql://root@s
use:3306/test/test_f';
ERROR 1434 (HY000): Can't create federated table. Foreign data src error:  error: 1159  ''
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.20    |
+-----------+
1 row in set (0.00 sec)

And yes, processlist is the same you described:

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 7
  State: creating table
   Info: create table test_ff (x int) engine=federated connection='mysql://root@
suse:3306/test/test_f'
*************************** 2. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 3. row ***************************
     Id: 4
   User: root
   Host: localhost:3714
     db: test
Command: Query
   Time: 7
  State: Opening tables
   Info: SELECT *  FROM `test_f` WHERE  1=0
3 rows in set (0.00 sec)

It is a bug, I think.

I can repeat it with 5.0.22 release.
I'd set Severity as Critical, as this bug makes FEDERATED table totaly unsuable.

looking into this.

From testing, it has to do with a federated table connecting to a table on the same mysql instance:

patg@govinda:~/mysql-build/mysql-5.0-bug18764.2> mysql -u root -P 5555 -h govinda test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24-debug-log

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

mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 5555  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>
mysql> create table test_f (x int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table test_ff (x int) engine=federated connection='mysql://root@govinda:5555/test/test_f';
ERROR 1434 (HY000): Can't create federated table. Foreign data src error:  error: 1159  ''
mysql> \q
Bye

patg@govinda:~/mysql-build/mysql-5.0-bug18764.2> mysql -u root -P 5555 -h govinda 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 4 to server version: 5.0.24-debug-log

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

mysql> create table test_ff (x int) engine=federated connection='mysql://root:foo@govinda:3306/test/test_f';
Query OK, 0 rows affected (0.04 sec)

I'm going to scrutinise 'check_foreign_data_src' which I assume where the problem is.

Having looked into this, the problem seems to be in the query in "check_foreign_data_src". mysql_real_connect succeeds, and then the sql query "select * from t1 where 1=0" (this checks if the table exists on the foreign end) hangs for 30 seconds, then 1159 is returned. This _only_ happens if the connection is to the same mysqld instance that the federated table is trying to be created from. This used to work just fine, but something seems to have changed  in the past 6 months to where this no longer works. It works fine if the mysqld instance is a different instance. I've checked all my logic in both parse_url and check_foreign_data_source, and it seems fine since it connects. I can even see the query that fails in the sql log.

Kostja is looking into this as it seems it might be something in vio as he states. As soon as I get some input from him, I canhave a better idea on time estimate to get this bug fixed.

I'm still waiting for help from Kostja (or Guilhem if he's available). I've asked the dev list, done all that I can do to get info on this. The problem is not in Federated, but in the client API with the server unable to connect to a local instance of MySQL, and I need help.

Just to set the record straight:

[patg@buffy mysql-5.0-engines]$ mysql -u root -S /tmp/fed.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug-log

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

mysql> connect test;
Connection id:    2
Current database: test

mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 5555  |
+---------------+-------+
1 row in set (0.01 sec)

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

mysql> create table t2 (a int) ENGINE=FEDERATED CONNECTION="mysql://root@localhost:5555/test/t1";
ERROR 1434 (HY000): Can't create federated table. Foreign data src error:  error: 1159  ''

[patg@buffy mysql-5.0-engines]$ mysql -u root -S /tmp/fed2.sock 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 3 to server version: 5.0.25-debug-log

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

mysql> show variables like 'port%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 5556  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> create table t2 (a int) ENGINE=FEDERATED CONNECTION="mysql://root@localhost:5555/test/t1";
Query OK, 0 rows affected (0.01 sec)

From the trace:

T@10745388: | | | | | | | | >mysql_real_query
T@10745388: | | | | | | | | | enter: handle: de0478
T@10745388: | | | | | | | | | query: Query = 'SELECT *  FROM `t1` WHERE  1=0'
T@10745388: | | | | | | | | | >mysql_send_query
T@10745388: | | | | | | | | | | enter: rpl_parse: 0  rpl_pivot: 1
T@10745388: | | | | | | | | | <mysql_send_query
T@10745388: | | | | | | | | | >cli_advanced_command
T@10745388: | | | | | | | | | | >net_clear
T@10745388: | | | | | | | | | | <net_clear
T@10745388: | | | | | | | | | | >net_write_command
T@10745388: | | | | | | | | | | | enter: length: 30
T@10745388: | | | | | | | | | | <net_write_command
T@10745388: | | | | | | | | | | >net_flush
T@10745388: | | | | | | | | | | | >vio_is_blocking
T@10745388: | | | | | | | | | | | | exit: 0
T@10745388: | | | | | | | | | | | <vio_is_blocking
T@10745388: | | | | | | | | | | | >net_real_write
T@10745388: | | | | | | | | | | | | >vio_write
T@10745388: | | | | | | | | | | | | | enter: sd: 22, buf: 0x0xdf3bb8, size: 35
T@10745388: | | | | | | | | | | | | | exit: 35
T@10745388: | | | | | | | | | | | | <vio_write
T@10745388: | | | | | | | | | | | <net_real_write
T@10745388: | | | | | | | | | | <net_flush
T@10745388: | | | | | | | | | | exit: result: 0
T@10745388: | | | | | | | | | <cli_advanced_command
T@10745388: | | | | | | | | <mysql_real_query

...

@10745388: | | | | | | | | | | | vio_error: Got error 4 during read
T@10745388: | | | | | | | | | | | exit: -1
T@10745388: | | | | | | | | | | <vio_read
T@10745388: | | | | | | | | | <vio_read_buff
T@10745388: | | | | | | | | | info: vio_read returned -1,  errno: 4
T@10745388: | | | | | | | | | error: Couldn't read packet: remain: 4  errno: 4  length: -1
T@10745388: | | | | | | | | | >thr_end_alarm
T@10745388: | | | | | | | | | <thr_end_alarm
T@10745388: | | | | | | | | | >vio_blocking
T@10745388: | | | | | | | | | | enter: set_blocking_mode: 0  old_mode: 1
T@10745388: | | | | | | | | | | exit: 0
T@10745388: | | | | | | | | | <vio_blocking
T@10745388: | | | | | | | | | error: Wrong connection or packet. fd: TCP/IP (22)  len: -1
T@10745388: | | | | | | | | <cli_read_query_result
T@10745388: | | | | | | | | info: error code 1074527232
T@10745388: | | | | | | | | >my_error
T@10745388: | | | | | | | | | my: nr: 1434  MyFlags: 0  errno: 4
T@10745388: | | | | | | | | <my_error
T@10745388: | | | | | | | | >my_message_sql
T@10745388: | | | | | | | | | error: error: 1434  message: 'Can't create federated table. Foreign data src error:  error: 1159  '''

...

More detail can be provided with the trace.

Has this ever been fixed?  I'm having the same problem on : mysql  Ver 14.12 Distrib 5.0.22, for apple-darwin8.8.1 (i686)

Also fails in 5.0.26.  Has this ever been fixed?

If will be fixed when the bug will have closed status. So, it is NOT fixed in 5.0.26. Still work in progress, sorry.

Please see bug #25679 for a similar issue

I have been having the same problem and the same error.

However, I noticed that the error happens in a pattern:

1. Create Federated Table 1 = Success
2. Create Federated Table 2 = Error 1159
3. Try again on second table = success
* repeat 2-3 until all tables created *

This is using the MySQL 5.1.x beta

Howcome that this has not been fixed for over a year.
While marked as show stopper.

Seems still present in 5.1.16 I`ll check 5.1.19 asap.

Sadly also still in 5.1.19 

I verified being able to create fedrated tables to remote databases.
Trying to create them to localhost fails.

Same issue is present in 5.0.41 ..

Can't repeat the bug any more. This is a duplicate of Bug #25679  	Denial-of-Service against entire server if FEDERATED engine is compiled

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/commits/31945

ChangeSet@1.2488, 2007-08-01 13:57:07+04:00, kostja@bodhi.(none) +2 -0
  Add a test case for Bug#18287 create federated table always times out,
   error 1159 ' ' (fixed by the patch for Bug 25679)

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/commits/31990

ChangeSet@1.2488, 2007-08-02 03:32:54+04:00, kostja@bodhi.(none) +2 -0
  Add a test case for Bug#18287 create federated table always times out,
   error 1159 ' ' (fixed by the patch for Bug 25679)

Pushed into 5.0.48

Pushed into 5.1.22-beta