search

actiontech / dtle

Distributed Data Transfer Service for MySQL
https://actiontech.github.io/dtle-docs-cn
Mozilla Public License 2.0
550 stars 132 forks source link

MySQL circle - two jobs send ddl cyclically #800

Open asiroliu opened 2 years ago

asiroliu commented 2 years ago

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 commented 2 years ago

分析

  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.