CREATE TABLE x LIKE some_federated_table crashes the server

[markus_albe]

The docs state that FEDERATED tables "does not support ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than DROP TABLE. "... seems this includes CREATE TABLE...LIKE, which results in this stack trace:

Thread pointer: 0x1fd22b950
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7eb37c967c88 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x8bdcec]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x6512a1]
/lib64/libpthread.so.0(+0xf710)[0x7f0a92750710]
/usr/sbin/mysqld(_ZN12ha_federated4infoEj+0xa7)[0xb209d7]
/usr/sbin/mysqld(_Z25mysql_prepare_alter_tableP3THDP5TABLEP24st_ha_create_informationP10Alter_infoP15Alter_table_ctx+0x204)[0x720304]
/usr/sbin/mysqld(_Z23mysql_create_like_tableP3THDP10TABLE_LISTS2_P24st_ha_create_information+0x25f)[0x7216df]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x56ab)[0x6d47ab]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5c8)[0x6d6168]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfb7)[0x6d7887]
/usr/sbin/mysqld(_Z26threadpool_process_requestP3THD+0xf8)[0x760038]
/usr/sbin/mysqld[0x760ffd]
/lib64/libpthread.so.0(+0x79d1)[0x7f0a927489d1]
/lib64/libc.so.6(clone+0x6d)[0x7f0a90e629dd]

To reproduce:

On "server1" I created a real table and added few records:

mysql> use test;
Database changed
mysql> CREATE TABLE `paypal_ipns` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `account_id` varchar(25) NOT NULL,
-> `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `ipn` text,
-> PRIMARY KEY (`id`),
-> KEY `account_id` (`account_id`),
-> KEY `date` (`date`),
-> KEY `account_by_date` (`account_id`,`date`)
-> ) ENGINE=myisam;
Query OK, 0 rows affected (0.05 sec)

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

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

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

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

mysql> select @@port, @@version;
+--------+-------------+
| @@port | @@version |
+--------+-------------+
| 5621 | 5.6.21-70.1 |
+--------+-------------+
1 row in set (0.00 sec)

Then on Server2 I run these:
)

mysql [localhost] {msandbox} (test) > DROP TABLE if exists paypal_ipns;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE `paypal_ipns` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `account_id` varchar(25) NOT NULL, `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `ipn` text, PRIMARY KEY (`id`), KEY `account_id` (`account_id`), KEY `date` (`date`), KEY `account_by_date` (`account_id`,`date`) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://msandbox:msandbox@127.0.0.1:5621/test/paypal_ipns';
Query OK, 0 rows affected (0.03 sec)

mysql [localhost] {msandbox} (test) > create table paypal_ooops like paypal_ipns;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Funny enough, if we issue a SELECT on the FEDERATED table before we attempt the CREATE TABLE...LIKE, then it will throw an error instead of crashing (which is the expected behavior):

mysql [localhost] {msandbox} (test) > DROP TABLE if exists paypal_ipns;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `paypal_ipns` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `account_id` varchar(25) NOT NULL,
-> `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `ipn` text,
-> PRIMARY KEY (`id`),
-> KEY `account_id` (`account_id`),
-> KEY `date` (`date`),
-> KEY `account_by_date` (`account_id`,`date`)
-> ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://msandbox:msandbox@127.0.0.1:5621/test/paypal_ipns';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from paypal_ipns;
+----+------------+---------------------+------+
| id | account_id | date | ipn |
+----+------------+---------------------+------+
| 1 | | 2015-03-24 19:56:03 | NULL |
| 2 | | 2015-03-24 19:56:03 | NULL |
| 3 | | 2015-03-24 19:56:04 | NULL |
| 4 | | 2015-03-24 19:56:05 | NULL |
+----+------------+---------------------+------+
4 rows in set (0.02 sec)

But here you can see it fails with different error, and I don't see any crash on the server:

mysql> create table paypal_ipns_oops like paypal_ipns;
ERROR 1432 (HY000): server name: '' doesn't exist!

[markus_albe (markus-albe)]

affects 5.5 and 5.6

[Muhammad Irfan (muhammad-irfan)]

I am able to reproduce the issue as described.

mysql [localhost] {root} (test) > SHOW VARIABLES LIKE '%version%';
+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| innodb_version | 5.5.38-35.2 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.38-35.2-log |
| version_comment | Percona Server (GPL), Release 35.2, Revision 674 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------------------+

As soon as I execute CREATE TABLE LIKE statement it crashes the server and restarts mysqld_safe.

mysql [localhost] {root} (test) > create table paypal_ooops like paypal_ipns;
ERROR 2013 (HY000): Lost connection to MySQL server during query

150326 15:31:13 [Note] /root/download/ps5.5.38/bin/mysqld: ready for connections.
Version: '5.5.38-35.2-log' socket: '/tmp/mysql_sandbox5538.sock' port: 5538 Percona Server (GPL), Release 35.2, Revision 674
20:32:52 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=153
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 343008 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x1c28aa0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fc5a102ed80 thread_stack 0x40000
/root/download/ps5.5.38/bin/mysqld(my_print_stacktrace+0x35)[0x7fa8c5]
/root/download/ps5.5.38/bin/mysqld(handle_fatal_signal+0x3f4)[0x6aebd4]
/lib64/libpthread.so.0(+0xf500)[0x7fc5b7a75500]
/root/download/ps5.5.38/bin/mysqld(_ZN12ha_federated4infoEj+0xd8)[0x959a18]
/root/download/ps5.5.38/bin/mysqld(_Z25mysql_prepare_alter_tableP3THDP5TABLEP24st_ha_create_informationP10Alter_info+0x1fe)[0x5ec4ae]
/root/download/ps5.5.38/bin/mysqld(_Z23mysql_create_like_tableP3THDP10TABLE_LISTS2_P24st_ha_create_information+0x27d)[0x5f17dd]
/root/download/ps5.5.38/bin/mysqld(_Z21mysql_execute_commandP3THD+0x5c36)[0x58e806]
/root/download/ps5.5.38/bin/mysqld(_Z11mysql_parseP3THDPcj...

[Laurynas Biveinis (laurynas-biveinis)]

Upstream?

[Laurynas Biveinis (laurynas-biveinis)]

Muhammad, was it you who reported this upstream? Is it closed now there for 5.5+?

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/56

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/60

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-886