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: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 5.7.6, 5.7.8 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[14 Mar 2015 5:16]
zhai weixiang
[14 Mar 2015 5:17]
zhai weixiang
edit Synopsis
[20 Mar 2015 9:00]
MySQL Verification Team
Hello Zhai, Thank you for the report. Observed this with latest build of 5.7.8. Thanks, Umesh
[20 Mar 2015 9:01]
MySQL Verification Team
// 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] >
[31 Aug 2015 15:45]
David Moss
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.