Skip to content

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

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
asiroliu opened this issue Oct 13, 2022 · 1 comment
Closed

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

asiroliu opened this issue Oct 13, 2022 · 1 comment
Labels
VERIFIED verified issue

Comments

@asiroliu
Copy link
Collaborator

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
  1. 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"
    }
  }
}
  1. wait full stage finished
  2. src MySQL drop table
$sql> drop table sbtest3;
  1. 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
  1. 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 asiroliu assigned ghost Oct 13, 2022
ghost pushed a commit that referenced this issue Oct 14, 2022
fix DDL error when using SetGtidNext #1029
a6c5b3b
@asiroliu
Copy link
Collaborator Author

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

ghost pushed a commit that referenced this issue Oct 17, 2022
mysql: remove currentSchema for create schema DDL #1029-2
f021814
@asiroliu asiroliu added the VERIFIED verified issue label Oct 18, 2022
ghost pushed a commit that referenced this issue Dec 12, 2022
fix DDL error when using SetGtidNext #1029
48d937f
ghost pushed a commit that referenced this issue Dec 12, 2022
mysql: remove currentSchema for create schema DDL #1029-2
f916da7
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
VERIFIED verified issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@asiroliu