search

actiontech / dtle

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

Oracle-MySQL 8 job ddl : ALTER TABLE ... DROP column cause playback error #972

Open asiroliu opened 2 years ago

asiroliu commented 2 years ago

Description

Oracle-MySQL 8 job ddl : ALTER TABLE ... DROP column cause playback error

Steps to reproduce the issue

  1. create dtle job 
    {
"job_id": "drop_col",
"is_password_encrypted": false,
"task_step_name": "all",
"failover": true,
"retry": 2,
"src_task": {
"task_name": "src",
"node_id": "4450dab1-ec1a-7f1a-4717-ab1fa6c9c0c4",
"binlog_relay": false,
"repl_chan_buffer_size": 120,
"group_max_size": 1,
"group_timeout": 100,
"oracle_src_task_config": {
  "scn": 0
},
"connection_config": {
  "database_type": "Oracle",
  "host": "172.100.9.31",
  "port": 1521,
  "user": "roma_logminer",
  "password": "oracle",
  "service_name": "XE"
},
"replicate_do_db": [
  {
    "table_schema": "ACTION_DB",
    "tables": [
      {
        "table_name": "DROP_COL"
      }
    ]
  }
]
},
"dest_task": {
"task_name": "dest",
"node_id": "90a2a7b7-5400-34d4-e101-6ecec8f35f79",
"mysql_dest_task_config": {},
"connection_config": {
  "database_type": "MySQL",
  "host": "172.100.9.1",
  "port": 3306,
  "user": "test_dest",
  "password": "test_dest"
}
}
}
  2. create table and drop column on src Oracle 
    sql> CREATE TABLE ACTION_DB.DROP_COL (col1 VARCHAR(20), col2 NUMBER, col3 INT);
sql> ALTER TABLE ACTION_DB.DROP_COL DROP ("COL1", col2);
  3. check dest dtle log 
    2022-05-12T10:46:15.533+0800 [DEBUG] client.driver_mgr.dtle: binlogEntry.Events: driver=dtle @module=dtle.applier.ApplyBinlogEvent event=0 gno=0 job=drop_col-migration timestamp=2022-05-12T10:46:15.533+0800
2022-05-12T10:46:15.533+0800 [DEBUG] client.driver_mgr.dtle: not dml: driver=dtle job=drop_col-migration query="ALTER TABLE `ACTION_DB`.`DROP_COL` DROP COLUMN `ACTION_DB`.`DROP_COL`.`COL1`, DROP COLUMN `ACTION_DB`.`DROP_COL`.`COL2`" @module=dtle.applier.ApplyBinlogEvent timestamp=2022-05-12T10:46:15.533+0800
2022-05-12T10:46:15.533+0800 [DEBUG] client.driver_mgr.dtle: execQuery: driver=dtle job=drop_col-migration query="USE `ACTION_DB`" @module=dtle.applier timestamp=2022-05-12T10:46:15.533+0800
2022-05-12T10:46:15.533+0800 [DEBUG] client.driver_mgr.dtle: reset tableItem: driver=dtle table=DROP_COL @module=dtle.applier.ApplyBinlogEvent job=drop_col-migration schema=ACTION_DB timestamp=2022-05-12T10:46:15.533+0800
2022-05-12T10:46:15.533+0800 [DEBUG] client.driver_mgr.dtle: ParseQueryEventFlags: driver=dtle job=drop_col-migration @module=dtle.applier.ApplyBinlogEvent.ParseQueryEventFlags bytes= timestamp=2022-05-12T10:46:15.533+0800
2022-05-12T10:46:15.533+0800 [DEBUG] client.driver_mgr.dtle: execQuery: driver=dtle @module=dtle.applier job=drop_col-migration query="ALTER TABLE `ACTION_DB`.`DROP_COL` DROP COLUMN `ACTION_DB`.`DROP_COL`.`COL1`, DROP COLUMN `ACTION_DB`.`DROP_COL`.`COL2`" timestamp=2022-05-12T10:46:15.533+0800
2022-05-12T10:46:15.534+0800 [ERROR] client.driver_mgr.dtle: Exec sql error: driver=dtle @module=dtle.applier.ApplyBinlogEvent err="tx.Exec. gno 0 iEvent 0 queryBegin ALTER TABL workerIdx 0: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`DROP_COL`.`COL1`, DROP COLUMN `ACTION_DB`.`DROP_COL`.`COL2`' at line 1" job=drop_col-migration timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.534+0800 [ERROR] client.driver_mgr.dtle: onError: driver=dtle @module=dtle.applier err="tx.Exec. gno 0 iEvent 0 queryBegin ALTER TABL workerIdx 0: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`DROP_COL`.`COL1`, DROP COLUMN `ACTION_DB`.`DROP_COL`.`COL2`' at line 1" job=drop_col-migration timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.534+0800 [DEBUG] client.driver_mgr.dtle: onError. nats published: driver=dtle @module=dtle.applier job=drop_col-migration timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.534+0800 [INFO]  client.driver_mgr.dtle: Shutting down: driver=dtle job=drop_col-migration @module=dtle.applier timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.534+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. a.ai.wg.Wait. after: driver=dtle @module=dtle.applier job=drop_col-migration timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.534+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. a.wg.Wait. after: driver=dtle job=drop_col-migration @module=dtle.applier timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.534+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. CloseDB. after: driver=dtle @module=dtle.applier job=drop_col-migration timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.534+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. CloseConns. after: driver=dtle job=drop_col-migration @module=dtle.applier timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.535+0800 [INFO]  client.driver_mgr.dtle: Shutdown: driver=dtle @module=dtle.applier job=drop_col-migration timestamp=2022-05-12T10:46:15.534+0800
2022-05-12T10:46:15.536+0800 [DEBUG] client.driver_mgr.dtle: Stats: driver=dtle job=drop_col-migration @module=dtle.applier timestamp=2022-05-12T10:46:15.536+0800
2022-05-12T10:46:15.536+0800 [DEBUG] client.driver_mgr.dtle: TimestampContext.GetDelay: driver=dtle @module=dtle.applier delay=-28758 job=drop_col-migration timestamp=2022-05-12T10:46:15.536+0800
2022-05-12T10:46:15.545+0800 [INFO]  client.driver_mgr.dtle: DestroyTask: driver=dtle @module=dtle id=4130e2fb-f9a6-4b82-044b-7b2afa69e705/dest/1f69cac3 timestamp=2022-05-12T10:46:15.545+0800
2022-05-12T10:46:15.545+0800 [INFO]  client.driver_mgr.dtle: Shutting down: driver=dtle job=drop_col-migration @module=dtle.applier timestamp=2022-05-12T10:46:15.545+0800

Output of ./dtle version:**

9.9.9.9-master-352256e
LordofAvernus commented 2 years ago

Plan

In the oracle-MySQL scenario, DDL is converted to mysql5.7 SQL by default At present, it is considered to convert the DDL on the target side again according to the MySQL version of the source/target side

asiroliu commented 2 years ago

RENAME语句报同样的错