Skip to content
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

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

Open
asiroliu opened this issue May 12, 2022 · 2 comments
Open

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

asiroliu opened this issue May 12, 2022 · 2 comments
Assignees
@LordofAvernus
Labels
DDL DDL bug or issue Oracle

Comments

@asiroliu
Copy link
Collaborator

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"
    }
  }
}
  1. 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);
  1. 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
@asiroliu asiroliu added DDL DDL bug or issue Oracle labels May 12, 2022
@LordofAvernus LordofAvernus self-assigned this May 12, 2022
@LordofAvernus
Copy link
Collaborator

LordofAvernus commented May 12, 2022 •
edited

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
Copy link
Collaborator Author

RENAME语句报同样的错

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@LordofAvernus LordofAvernus

Labels
DDL DDL bug or issue Oracle
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asiroliu @LordofAvernus