MySQL circle - two jobs send ddl cyclically

[asiroliu]

Description

MySQL circle - two jobs send ddl cyclically

Steps to reproduce the issue

1. MySQL-1 create table test.tb1, MySQL-2 create table test.tb2

MySQL-1> CREATE TABLE tb1 (id INT(11) AUTO_INCREMENT PRIMARY KEY);
MySQL-2> CREATE TABLE tb2 (id INT(11) AUTO_INCREMENT PRIMARY KEY);

2. create 2 database level job copy data

# job-1 MySQL-1 to MySQL-2
"ReplicateDoDb": [
  {
    "TableSchema": "test",
    "Tables": []
  }
],
"DropTableIfExists": false,
"SkipCreateDbTable": true

# job-2 MySQL-2 to MySQL-1
"ReplicateDoDb": [
  {
    "TableSchema": "test",
    "Tables": []
  }
],
"DropTableIfExists": false,
"SkipCreateDbTable": true

3. open MySQL-1 / MySQL-2 general log

SQL> set global general_log = ON;
SQL> set global general_log_file = '/tmp/general.log';

4. both MySQL have been drop tables

2021-11-02T09:49:25.845630Z        12 Query     START TRANSACTION
2021-11-02T09:49:25.845845Z        12 Query     USE `test`
2021-11-02T09:49:25.846258Z        12 Query     set @@session.foreign_key_checks = 0
2021-11-02T09:49:25.846396Z        12 Query     DROP TABLE IF EXISTS `tb1` /* generated by server */ /*dtle_gtid1 dtle-6-1-migration e9ebc730-3bbf-11ec-ad98-0242ac640901 279483 dtle_gtid*/
2021-11-02T09:49:25.846845Z        12 Query     set @@session.foreign_key_checks = 1
2021-11-02T09:49:25.846986Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2021-11-02T09:49:25.847674Z        12 Query     COMMIT
2021-11-02T09:49:25.848977Z        12 Query     START TRANSACTION
2021-11-02T09:49:25.849258Z        12 Query     USE `test`
2021-11-02T09:49:25.849541Z        12 Query     set @@session.foreign_key_checks = 0
2021-11-02T09:49:25.849692Z        12 Query     DROP TABLE IF EXISTS `tb2` /* generated by server */ /*dtle_gtid1 dtle-6-1-migration e9ebc730-3bbf-11ec-ad98-0242ac640901 279485 dtle_gtid*/
2021-11-02T09:49:25.850357Z        12 Query     set @@session.foreign_key_checks = 1
2021-11-02T09:49:25.850513Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2021-11-02T09:49:25.851050Z        12 Query     COMMIT
2021-11-02T09:49:25.851559Z        12 Query     START TRANSACTION
2021-11-02T09:49:25.851801Z        12 Query     USE `test`
2021-11-02T09:49:25.852055Z        12 Query     set @@session.foreign_key_checks = 0
2021-11-02T09:49:25.852273Z        12 Query     DROP TABLE IF EXISTS `tb1` /* generated by server */ /*dtle_gtid1 dtle-6-1-migration e9ebc730-3bbf-11ec-ad98-0242ac640901 279487 dtle_gtid*/
2021-11-02T09:49:25.852882Z        12 Query     set @@session.foreign_key_checks = 1
2021-11-02T09:49:25.853122Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2021-11-02T09:49:25.853685Z        12 Query     COMMIT
2021-11-02T09:49:25.854816Z        12 Query     START TRANSACTION
2021-11-02T09:49:25.854968Z        12 Query     USE `test`
2021-11-02T09:49:25.855167Z        12 Query     set @@session.foreign_key_checks = 0
2021-11-02T09:49:25.855282Z        12 Query     DROP TABLE IF EXISTS `tb2` /* generated by server */ /*dtle_gtid1 dtle-6-1-migration e9ebc730-3bbf-11ec-ad98-0242ac640901 279489 dtle_gtid*/
2021-11-02T09:49:25.855847Z        12 Query     set @@session.foreign_key_checks = 1
2021-11-02T09:49:25.856010Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2021-11-02T09:49:25.856398Z        12 Query     COMMIT
2021-11-02T0

Describe the results you expected

the dll should execute once

Output of ./dtle version:**

9.9.9.9-master-ef8ac77

Additional information

(e.g. issue happens only occasionally)

Additional details (log, config, job config etc):

[ghost]

分析

1. 对于drop table ... /*dtle gtid tag*/, MySQL (5.7.34) 会在binlog中重写为 DROP TABLE /* generated by server */
   • 所有注释会被丢弃
2. dtle无法从binlog中获取OSID, 则无法避免循环复制.

对于不带if exists的drop table语句, dtle“歪打正着”避免了循环复制. 其原因为: drop 再次执行时, 表不存在, 失败. 语句不会写入binlog.