| Bug #98719 | Parallel CREATE TABLE statement binlogged in a wrong order | ||
|---|---|---|---|
| Submitted: | 24 Feb 2020 5:45 | Modified: | 24 Feb 2020 10:58 |
| Reporter: | Song Libing | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.7, 5.7.29, 5.6.47 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 Feb 2020 10:58]
MySQL Verification Team
Hello Libing, Thank you for the report and test case. Observed that 5.7.29 build is affected. regards, Umesh
[24 Feb 2020 10:59]
MySQL Verification Team
Test results - 5.7.29
Attachment: 98719_5.7.29.results (application/octet-stream, text), 257.20 KiB.
[24 Feb 2020 12:25]
MySQL Verification Team
Test results - 5.6.47
Attachment: 98719_5.6.47.results (application/octet-stream, text), 179.83 KiB.
[24 Feb 2020 12:26]
MySQL Verification Team
- 8.0.19 order looks fine
./mtr rpl_bug98719 --nocheck-testcases
Logging: ./mtr rpl_bug98719 --nocheck-testcases
MySQL Version 8.0.19
Too long tmpdir path '/export/umesh/server/source/bugs/src_build/fb_builds/98719/mysql-8.0.19/mysql-test/var/tmp' creating a shorter one
- Using tmpdir: '/tmp/b4t9jQwOud'
Checking supported features
- Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/umesh/server/source/bugs/src_build/fb_builds/98719/mysql-8.0.19/mysql-test/var'
Installing system database
Using parallel: 1
==============================================================================
TEST NAME RESULT TIME (ms) COMMENT
------------------------------------------------------------------------------
include/master-slave.inc
Warnings:
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
SET debug_sync = "create_table_before_binlog SIGNAL before_binog WAIT_FOR continue";
CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT) ENGINE = InnoDB;;
SET debug_sync = "now WAIT_FOR before_binog";
CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY(c2) REFERENCES t1(c1));
SET debug_sync = "now SIGNAL continue";
[connection master]
Warnings:
Warning 1639 debug sync point wait timed out
show binlog events;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 4 Format_desc 1 124 Server ver: 8.0.19-debug, Binlog ver: 4
master-bin.000001 124 Previous_gtids 1 155
master-bin.000001 155 Anonymous_Gtid 1 232 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
master-bin.000001 232 Query 1 379 use `test`; CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT) ENGINE = InnoDB /* xid=159 */
master-bin.000001 379 Anonymous_Gtid 1 456 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
master-bin.000001 456 Query 1 622 use `test`; CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY(c2) REFERENCES t1(c1)) /* xid=161 */
include/sync_slave_sql_with_master.inc
select * from t1;
c1 c2
select * from t2;
c1 c2
Description: If you create two tables which have reference relationship. They may be binlogged in a wrong order when they are executed parallel. Let's say T1 and T2. T2 references T1. They may be binlogged in the order: CREATE TABLE t2(...,FOREIGN KEY(c2) REFERENCES t1(c1)); CREATE TABLE t1 Slave will be broken with error 1215: 'Cannot add foreign key constraint' on query. To run the test, a debug point should be added to code: iff --git a/sql/sql_table.cc b/sql/sql_table.cc index 45f27278a0d..0f3c82cfb7d 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -5538,6 +5538,7 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table, (thd->is_current_stmt_binlog_format_row() && !(create_info->options & HA_LEX_CREATE_TMP_TABLE))) { + DEBUG_SYNC(thd, "create_table_before_binlog"); thd->add_to_binlog_accessed_dbs(create_table->db); result= write_bin_log(thd, true, thd->query().str, thd->query().length, is_trans); How to repeat: To run the test, a debug point should be added to code: ========================================================= iff --git a/sql/sql_table.cc b/sql/sql_table.cc index 45f27278a0d..0f3c82cfb7d 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -5538,6 +5538,7 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table, (thd->is_current_stmt_binlog_format_row() && !(create_info->options & HA_LEX_CREATE_TMP_TABLE))) { + DEBUG_SYNC(thd, "create_table_before_binlog"); thd->add_to_binlog_accessed_dbs(create_table->db); result= write_bin_log(thd, true, thd->query().str, thd->query().length, is_trans); ========================================================= --source include/have_binlog_format_row.inc --source include/master-slave.inc SET debug_sync = "create_table_before_binlog SIGNAL before_binog WAIT_FOR continue"; --send CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT) ENGINE = InnoDB; --connection default SET debug_sync = "now WAIT_FOR before_binog"; CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY(c2) REFERENCES t1(c1)); SET debug_sync = "now SIGNAL continue"; --source include/rpl_connection_master.inc --reap show binlog events; --source include/sync_slave_sql_with_master.inc select * from t1; select * from t2; Suggested fix: It not happens often and DDL on 5.6 and 5.7 are not atomic. Please document it if you think it is not worth to fix.