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
Tags: federated, sr5_1

[16 Mar 2006 19:48] Jacek Becla
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';
[22 Mar 2006 15:08] Valerii Kravchuk
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.
[28 Jun 2006 17:38] Vadim Tkachenko
I can repeat it with 5.0.22 release.
I'd set Severity as Critical, as this bug makes FEDERATED table totaly unsuable.
[13 Jul 2006 3:07] Patrick Galbraith
looking into this.
[13 Jul 2006 3:40] Patrick Galbraith
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)
[13 Jul 2006 3:42] Patrick Galbraith
I'm going to scrutinise 'check_foreign_data_src' which I assume where the problem is.
[14 Jul 2006 19:51] Patrick Galbraith
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.
[19 Jul 2006 12:33] Patrick Galbraith
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.
[25 Jul 2006 15:28] Patrick Galbraith
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.
[25 Jul 2006 16:09] Patrick Galbraith
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.
[23 Oct 2006 17:19] [ name withheld ]
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)
[23 Oct 2006 18:19] [ name withheld ]
Also fails in 5.0.26.  Has this ever been fixed?
[24 Oct 2006 8:18] Valerii Kravchuk
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.
[17 Jan 2007 14:19] Philip Stoev
Please see bug #25679 for a similar issue
[23 Mar 2007 20:26] Michael Doris
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
[31 May 2007 20:47] Frans Stekelenburg
Howcome that this has not been fixed for over a year.
While marked as show stopper.
[24 Jun 2007 11:31] Kris Buytaert
Seems still present in 5.1.16 I`ll check 5.1.19 asap.
[24 Jun 2007 11:48] Kris Buytaert
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.
[6 Jul 2007 20:17] Devin Hayes
Same issue is present in 5.0.41 ..
[1 Aug 2007 9:49] Konstantin Osipov
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
[1 Aug 2007 9:58] Bugs System
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)
[1 Aug 2007 23:33] Bugs System
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)
[9 Aug 2007 13:17] Bugs System
Pushed into 5.0.48
[9 Aug 2007 13:18] Bugs System
Pushed into 5.1.22-beta