Bug #98719 Parallel CREATE TABLE statement binlogged in a wrong order
Submitted: 24 Feb 5:45 Modified: 24 Feb 10:58
Reporter: Song Libing Email Updates:
Status: Verified Impact on me:
None 
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 5:45] Song Libing
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.
[24 Feb 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 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 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 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