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

[asiroliu]

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]

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]

RENAME语句报同样的错