search

actiontech / dtle

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

MySQL-MySQL incr stage: set SetGtidNext=true , drop table cause error #1029

Closed asiroliu closed 1 year ago

asiroliu commented 1 year ago

Description

MySQL-MySQL incr stage: set SetGtidNext=true , drop table cause error

Steps to reproduce the issue

  1. src MySQL insert data 
    $shell> sysbench /usr/share/sysbench/oltp_common.lua --mysql-host=172.100.9.3 --mysql-port=3306 --mysql-user=test --mysql-password=test --create_secondary=off --report-interval=10 --time=0 --mysql-db=action_db_1 --tables=4 --table_size=5000 prepare
  2. create MySQL-MySQL job 
    {
"job_id": "oneToOne_table_level_1_db_n_table",
"is_password_encrypted": false,
"task_step_name": "all",
"failover": true,
"retry": 2,
"src_task": {
"task_name": "src",
"node_id": "a7eab779-8c59-f957-2317-1ca2e08ddd6a",
"mysql_src_task_config": {
  "gtid": "",
  "binlog_relay": false
},
"drop_table_if_exists": true,
"skip_create_db_table": false,
"repl_chan_buffer_size": 120,
"chunk_size": 2000,
"group_max_size": 1,
"group_timeout": 100,
"connection_config": {
  "database_type": "MySQL",
  "host": "172.100.9.3",
  "port": 3306,
  "user": "test_src",
  "password": "test_src"
},
"replicate_do_db": [
  {
    "table_schema": "action_db_1",
    "tables": [
      {
        "table_name": "sbtest1"
      },
      {
        "table_name": "sbtest2"
      },
      {
        "table_name": "sbtest3"
      }
    ]
  }
]
},
"dest_task": {
"task_name": "dest",
"node_id": "79964d2b-440e-b50c-1113-163ae97c6c5d",
"mysql_dest_task_config": {
  "use_my_sql_dependency": false,
  "dependency_history_size": 2500,
  "parallel_workers": 32,
  "set_gtid_next": true
},
"connection_config": {
  "database_type": "MySQL",
  "host": "172.100.9.2",
  "port": 3306,
  "user": "test_dest",
  "password": "test_dest"
}
}
}
  3. wait full stage finished
  4. src MySQL drop table 
    $sql> drop table sbtest3;
  5. check dest dtle log 
    2022-10-13T13:49:55.688+0800 [DEBUG] applier.ApplyBinlogEvent: binlogEntry.Events: job=oneToOne_table_level_1_db_n_table-migration gno=2022 event=0
2022-10-13T13:49:55.688+0800 [DEBUG] applier.ApplyBinlogEvent: not dml: job=oneToOne_table_level_1_db_n_table-migration query="DROP TABLE IF EXISTS `action_db_1`.`sbtest3` /* generated by server */ /*dtle_gtid1 oneToOne_table_level_1_db_n_table-migration 734d1553-4aba-11ed-83a8-0242ac640901 2022 dtle_gtid*/"
2022-10-13T13:49:55.688+0800 [DEBUG] applier.ApplyBinlogEvent: reset tableItem: job=oneToOne_table_level_1_db_n_table-migration schema=action_db_1 table=sbtest3
2022-10-13T13:49:55.688+0800 [DEBUG] applier.ApplyBinlogEvent.ParseQueryEventFlags: ParseQueryEventFlags: job=oneToOne_table_level_1_db_n_table-migration bytes=0000000000012000a055000000000603737464042d002d0008000c01616374696f6e5f64625f3100
2022-10-13T13:49:55.688+0800 [DEBUG] applier.ApplyBinlogEvent.ParseQueryEventFlags: Q_UPDATED_DB_NAMES: job=oneToOne_table_level_1_db_n_table-migration schema=action_db_1 i=39 j=0
2022-10-13T13:49:55.688+0800 [DEBUG] applier: execQuery: job=oneToOne_table_level_1_db_n_table-migration query="DROP TABLE IF EXISTS `action_db_1`.`sbtest3` /* generated by server */ /*dtle_gtid1 oneToOne_table_level_1_db_n_table-migration 734d1553-4aba-11ed-83a8-0242ac640901 2022 dtle_gtid*/"
2022-10-13T13:49:55.688+0800 [DEBUG] applier.ApplyBinlogEvent: Exec.after: job=oneToOne_table_level_1_db_n_table-migration query="DROP TABLE IF EXISTS `action_db_1`.`sbtest3` /* generated by server */ /*dtle_gtid1 oneToOne_table_level_1_db_n_table-migration 734d1553-4aba-11ed-83a8-0242ac640901 2022 dtle_gtid*/"
2022-10-13T13:49:55.688+0800 [DEBUG] applier.ApplyBinlogEvent: insert gno: job=oneToOne_table_level_1_db_n_table-migration gno=2022
2022-10-13T13:49:55.688+0800 [ERROR] applier: onError: job=oneToOne_table_level_1_db_n_table-migration err="insert gno: Error 1837: When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '734d1553-4aba-11ed-83a8-0242ac640901:2022'." hasShutdown=false
2022-10-13T13:49:55.688+0800 [DEBUG] applier: onError. nats published: job=oneToOne_table_level_1_db_n_table-migration
2022-10-13T13:49:55.688+0800 [DEBUG] applier: Shutting down: job=oneToOne_table_level_1_db_n_table-migration
2022-10-13
  6. check dest general log 
    2022-10-13T06:01:48.931031Z        12 Query     set gtid_next = 'f363905d-4abb-11ed-bbac-0242ac640903:22' /*dtle*/
2022-10-13T06:01:48.931289Z        12 Query     USE `action_db_1`
2022-10-13T06:01:48.931502Z        12 Query     DROP TABLE `sbtest3` /* generated by server */ /*dtle_gtid1 oneToOne_table_level_1_db_n_table-migration f363905d-4abb-11ed-bbac-0242ac640903 22 dtle_gtid*/
2022-10-13T06:01:48.937297Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values ('oneToOne_table_level_1_db_n_table-migration', '\óc<90>]J»^Qí»¬^BB¬d    ^C', 22, null)
2022-10-13T06:01:48.937843Z        44 Quit
2022

Output of ./dtle version:**

9.9.9.9-master-332d0ac
asiroliu commented 1 year ago

version: 9.9.9.9-master-a6c5b3b

  1. CREATE DATABASE cause same error 
    2022-10-17T13:13:28.238+0800 [DEBUG] applier: execQuery: job=oneToOne_table_level_1_db_n_table-migration query="USE `action_db_1`"
2022-10-17T13:13:28.238+0800 [WARN]  applier.ApplyBinlogEvent: Ignore error: job=oneToOne_table_level_1_db_n_table-migration err="tx.Exec. gno 2024 queryBegin USE `actio workerIdx 9: Error 1049: Unknown database 'action_db_1'"
2022-10-17T13:13:28.238+0800 [DEBUG] applier.ApplyBinlogEvent.ParseQueryEventFlags: ParseQueryEventFlags: job=oneToOne_table_level_1_db_n_table-migration bytes=0000000000012000a055000000000603737464042d002d0008000c01616374696f6e5f64625f3100
2022-10-17T13:13:28.238+0800 [DEBUG] applier.ApplyBinlogEvent.ParseQueryEventFlags: Q_UPDATED_DB_NAMES: job=oneToOne_table_level_1_db_n_table-migration schema=action_db_1 i=39 j=0
2022-10-17T13:13:28.238+0800 [DEBUG] applier: execQuery: job=oneToOne_table_level_1_db_n_table-migration query="CREATE DATABASE IF NOT EXISTS `action_db_1` /*dtle_gtid1 oneToOne_table_level_1_db_n_table-migration 206a939a-4dda-11ed-9d65-0242ac640902 2024 dtle_gtid*/"
2022-10-17T13:13:28.238+0800 [ERROR] applier.ApplyBinlogEvent: Exec sql error: job=oneToOne_table_level_1_db_n_table-migration err="tx.Exec. gno 2024 queryBegin CREATE DAT workerIdx 9: Error 1837: When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '206a939a-4dda-11ed-9d65-0242ac640902:2024'."
2022-10-17T13:13:28.238+0800 [ERROR] applier: onError: job=oneToOne_table_level_1_db_n_table-migration err="tx.Exec. gno 2024 queryBegin CREATE DAT workerIdx 9: Error 1837: When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '206a939a-4dda-11ed-9d65-0242ac640902:2024'." hasShutdown=false
2022-10-17T13:13:28.238+0800 [DEBUG] applier: onError. nats published: job=oneToOne_table_level_1_db_n_table-migration
2022-10-17T13:13:28.238+0800 [DEBUG] applier: Shutting down: job=oneToOne_table_level_1_db_n_table-migration