MySQL Bugs: #76320: Kill `CREATE TABLE ..SELECT` may lead to replication broken

Bug #76320	Kill `CREATE TABLE ..SELECT` may lead to replication broken
Submitted:	14 Mar 2015 5:16	Modified:	31 Aug 2015 15:45
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.6, 5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Prior to 5.7.6,  If `create table ..select` was killed during inserting data, it will throw away all binlog entries in cache and drop the table.

 In 5.7.6, A `CREATE TABLE ..SEELCT ` is changed to :
Anonymous_Gtid
CREATE TABLE (without SELECT) 
Anonymous_Gtid
BEGIN;
INSERT;
...
COMMIT

The CREATE TABLE (without select) statement is written to binlog file first no matter if the sql is killed latter.

Later if the statement is killed, the newly created table is dropped without any new binlog entry (something like DROP TABLE ..IF EXISTS)

How to repeat:
root@test 01:06:48>create table xx as select * from sb1.sbtest1;
ERROR 1317 (70100): Query execution was interrupted
====>KILL query from another session

root@test 01:06:57>show create table xx;
ERROR 1146 (42S02): Table 'test.xx' doesn't exist

root@test 01:07:32>show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       459 |
+------------------+-----------+
1 row in set (0.00 sec)

root@test 01:07:38>show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id  | End_log_pos | Info                                                                                                                                                                                       |
+------------------+-----+----------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    | 3017441834 |         122 | Server ver: 5.7.6-m16-tb2014-log, Binlog ver: 4                                                                                                                                            |
| mysql-bin.000001 | 122 | Previous_gtids | 3017441834 |         153 |                                                                                                                                                                                            |
| mysql-bin.000001 | 153 | Anonymous_Gtid | 3017441834 |         218 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                       |
| mysql-bin.000001 | 218 | Query          | 3017441834 |         459 | use `test`; CREATE TABLE `xx` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT ''
) |
+------------------+-----+----------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Suggested fix:
Add a query event if the statement is killed: DROP TABLE ....IF EXISTS...

edit Synopsis

Hello Zhai,

Thank you for the report.
Observed this with latest build of 5.7.8.

Thanks,
Umesh

// 5.7.8

/export/umesh/mysql-5.7.8: bin/mysql_install_db --basedir=/export/umesh/mysql-5.7.8 --datadir=/export/umesh/mysql-5.7.8/76401 -v
bin/mysqld --no-defaults --log_bin=master-bin --server_id=1  --basedir=/export/umesh/mysql-5.7.8 --datadir=/export/umesh/mysql-5.7.8/76401 --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/mysql-5.7.8/76401/log.err 2>&1 &

#build

commit: 4f39cfb13b465bb1bfcadf88c4740e26ce3c45fa
date: 2015-03-14 06:44:57 +0100
build-date: 2015-03-14 18:00:53 +0100
short: 4f39cfb
branch: mysql-5.7

MySQL source 5.7.8

[umshastr@hod03]/export/umesh/mysql-5.7.8: bin/mysql -uroot -p -S /tmp/mysql_ushastry.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.8-rc-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)] >
root@localhost [(none)] > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost [test] > drop table if exists keyvalue;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [test] > CREATE TABLE `keyvalue` (
    ->   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `name1` varchar(250),
    ->   `name2` varchar(250),
    ->   `name3` varchar(250),
    ->   `name4` varchar(250),
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=innodb ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test] >
root@localhost [test] > set @id:=0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test] >
root@localhost [test] >  insert into `keyvalue` values
    ->   (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000))
    -> ,  (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000))
    -> , (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000))
    -> , (@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@localhost [test] >
root@localhost [test] >  insert into `keyvalue`(`id`,`name1`,`name2`,`name3`,`name4`)
    ->    select  @id:=@id+1,md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000),md5(rand()*1000000)  from
    ->     `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9,
    -> `keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd limit 1000000;
Query OK, 1000000 rows affected (1 min 2.05 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

// flush logs

//
root@localhost [test] > show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 | 141605649 |
| master-bin.000002 |       154 |
+-------------------+-----------+
2 rows in set (0.00 sec)

// kill query from another session
root@localhost [(none)] > kill 3;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test] > create table xx as select * from test.keyvalue;
ERROR 2013 (HY000): Lost connection to MySQL server during query

root@localhost [test] >
root@localhost [test] > show binlog events in 'master-bin.000002';

+-------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                  |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| master-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.8-rc-enterprise-commercial-advanced-log, Binlog ver: 4                                                                                                                                                                |
| master-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                                                                                                                                                                                                                       |
| master-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                  |
| master-bin.000002 | 219 | Query          |         1 |         503 | use `test`; CREATE TABLE `xx` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name1` varchar(250) DEFAULT NULL,
  `name2` varchar(250) DEFAULT NULL,
  `name3` varchar(250) DEFAULT NULL,
  `name4` varchar(250) DEFAULT NULL
) |
+-------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.10 sec)

root@localhost [test] > show create table xx;
ERROR 1146 (42S02): Table 'test.xx' doesn't exist
root@localhost [test] >

Hello Zhai,
thanks for your feedback. This has been fixed in upcoming versions and the following was noted in the 5.7.9 changelog:
When using MySQL 5.7.6 and later with binlog_format=row and gtid_mode=off, if CREATE ... SELECT was killed during execution it could lead to an inconsistent state, breaking replication. The cause was that in MySQL 5.7.6 the way CREATE ... SELECT was logged was changed, so that a commit was introduced between the CREATE TABLE and SELECT steps. The fix ensures that CREATE ... SELECT does not commit in the middle of the transaction when binlog_format=row.