NoDDL doesn't work.

insert data on src MySQL

      | CREATE TABLE t1 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11));  | change name                |
      | CREATE TABLE t2 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11));  | change type                |
      | CREATE TABLE t3 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11));  | modify type                |
      | CREATE TABLE t4 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11));  | chang multiple columns     |
      | CREATE TABLE t5 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11));  | modify multiple columns    |
      | CREATE TABLE t6 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b TEXT);                | modify character set       |
      | CREATE TABLE t7 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11));             | alter columns set default  |
      | CREATE TABLE t8 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11) DEFAULT 8);   | alter columns drop default |
      | CREATE TABLE t11 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11)); | add                        |
      | CREATE TABLE t12 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11)); | add after                  |
      | CREATE TABLE t13 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11)); | add first                  |
      | CREATE TABLE t14 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11)); | drop                       |
      | CREATE TABLE t15 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11)); | add multiple               |
      | CREATE TABLE t16 (id INT(11) AUTO_INCREMENT PRIMARY KEY, b INT(11), c INT(11)); | drop multiple              |
      | CREATE TABLE t21 (id INT(11) AUTO_INCREMENT PRIMARY KEY, c CHAR(20));           | dml                        |
      | INSERT INTO t1 VALUES (NULL,1,1);                    | change name                |
      | INSERT INTO t2 VALUES (NULL,2,2);                    | change type                |
      | INSERT INTO t3 VALUES (NULL,3,3);                    | modify type                |
      | INSERT INTO t4 VALUES (NULL,4,4);                    | chang multiple columns     |
      | INSERT INTO t5 VALUES (NULL,5,5);                    | modify multiple columns    |
      | INSERT INTO t6 VALUES (NULL,'t6');                   | modify character set       |
      | INSERT INTO t7 VALUES (NULL,7);                      | alter columns set default  |
      | INSERT INTO t8 VALUES (NULL,8);                      | alter columns drop default |
      | INSERT INTO t11 VALUES (NULL,1,1);                   | add                        |
      | INSERT INTO t12 VALUES (NULL,2,2);                   | add after                  |
      | INSERT INTO t13 VALUES (NULL,3,3);                   | add first                  |
      | INSERT INTO t14 VALUES (NULL,4,4);                   | drop                       |
      | INSERT INTO t15 VALUES (NULL,5,5);                   | add multiple               |
      | INSERT INTO t16 VALUES (NULL,6,6);                   | drop multiple              |
      | INSERT INTO t21 VALUES (0, '1'), (0, '2'), (0, '3'); | dml                        |

create dtle job with sql_filter NoDDL

{
  "job_id": "sql_filter_NoDDL",
  "is_password_encrypted": false,
  "task_step_name": "all",
  "failover": true,
  "retry": 2,
  "src_task": {
    "task_name": "src",
    "node_id": "a79f74f4-59c2-52f6-1b25-d811cf138635",
    "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_dest"
    },
    "replicate_do_db": [],
    "sql_filter": [
      "NoDDL"
    ]
  },
  "dest_task": {
    "task_name": "dest",
    "node_id": "e405e3d8-e416-7984-2c55-aca6d17a442c",
    "mysql_dest_task_config": {
      "use_my_sql_dependency": false,
      "dependency_history_size": 2500,
      "parallel_workers": 32
    },
    "connection_config": {
      "database_type": "MySQL",
      "host": "172.100.9.2",
      "port": 3306,
      "user": "test_dest",
      "password": "test_dest"
    }
  }
}

execute ddl on src MySQL

      | ALTER TABLE t1 CHANGE COLUMN b d INT(11) DEFAULT NULL;                          | change name                |
      | ALTER TABLE t2 CHANGE b b BIGINT DEFAULT NULL;                                  | change type                |
      | ALTER TABLE t3 MODIFY COLUMN b BIGINT DEFAULT NULL;                             | modify type                |
      | ALTER TABLE t4 CHANGE b d INT(11) DEFAULT NULL, CHANGE c e BIGINT DEFAULT NULL; | chang multiple columns     |
      | ALTER TABLE t5 MODIFY b BIGINT DEFAULT NULL, MODIFY c BIGINT DEFAULT NULL;      | modify multiple columns    |
      | ALTER TABLE t6 MODIFY b TEXT CHARACTER SET utf8;                                | modify character set       |
      | ALTER TABLE t7 ALTER COLUMN b SET DEFAULT 7;                                    | alter columns set default  |
      | ALTER TABLE t8 ALTER COLUMN b DROP DEFAULT;                                     | alter columns drop default |
      | ALTER TABLE t11 ADD d CHAR(20);                                                 | add                        |
      | ALTER TABLE t12 ADD d CHAR(20) AFTER b;                                         | add after                  |
      | ALTER TABLE t13 ADD d CHAR(20) FIRST;                                           | add first                  |
      | ALTER TABLE t14 DROP b;                                                         | drop                       |
      | ALTER TABLE t15 ADD (d CHAR(20), e INT(11));                                    | add multiple               |
      | ALTER TABLE t16 DROP b, DROP COLUMN c;                                          | drop multiple              |

check general log on dest MySQL, all DDL is copy to dest MySQL

2022-04-02T10:34:18.385080Z	  156 Query	START TRANSACTION
2022-04-02T10:34:18.385526Z	  156 Query	USE `action_db_1`
2022-04-02T10:34:18.386046Z	  156 Query	ALTER TABLE t1 CHANGE COLUMN b d INT(11) DEFAULT NULL /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17629 dtle_gtid*/
2022-04-02T10:34:18.387821Z	  156 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.388436Z	  156 Query	COMMIT
2022-04-02T10:34:18.388977Z	  164 Query	START TRANSACTION
2022-04-02T10:34:18.389241Z	  164 Query	USE `action_db_1`
2022-04-02T10:34:18.389545Z	  164 Query	ALTER TABLE t2 CHANGE b b BIGINT DEFAULT NULL /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17630 dtle_gtid*/
2022-04-02T10:34:18.394721Z	  164 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.395164Z	  164 Query	COMMIT
2022-04-02T10:34:18.395529Z	  143 Query	START TRANSACTION
2022-04-02T10:34:18.395765Z	  143 Query	USE `action_db_1`
2022-04-02T10:34:18.396072Z	  143 Query	ALTER TABLE t3 MODIFY COLUMN b BIGINT DEFAULT NULL /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17631 dtle_gtid*/
2022-04-02T10:34:18.400560Z	  143 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.401122Z	  143 Query	COMMIT
2022-04-02T10:34:18.401381Z	  161 Query	START TRANSACTION
2022-04-02T10:34:18.401569Z	  161 Query	USE `action_db_1`
2022-04-02T10:34:18.401709Z	  161 Query	ALTER TABLE t4 CHANGE b d INT(11) DEFAULT NULL, CHANGE c e BIGINT DEFAULT NULL /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17632 dtle_gtid*/
2022-04-02T10:34:18.406581Z	  161 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.407129Z	  161 Query	COMMIT
2022-04-02T10:34:18.407365Z	  155 Query	START TRANSACTION
2022-04-02T10:34:18.407604Z	  155 Query	USE `action_db_1`
2022-04-02T10:34:18.407858Z	  155 Query	ALTER TABLE t5 MODIFY b BIGINT DEFAULT NULL, MODIFY c BIGINT DEFAULT NULL /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17633 dtle_gtid*/
2022-04-02T10:34:18.412404Z	  155 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.412811Z	  155 Query	COMMIT
2022-04-02T10:34:18.413250Z	  141 Query	START TRANSACTION
2022-04-02T10:34:18.413465Z	  141 Query	USE `action_db_1`
2022-04-02T10:34:18.413753Z	  141 Query	ALTER TABLE t6 MODIFY b TEXT CHARACTER SET utf8 /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17634 dtle_gtid*/
2022-04-02T10:34:18.418512Z	  141 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.418829Z	  141 Query	COMMIT
2022-04-02T10:34:18.419135Z	  138 Query	START TRANSACTION
2022-04-02T10:34:18.419579Z	  138 Query	USE `action_db_1`
2022-04-02T10:34:18.419980Z	  138 Query	ALTER TABLE t7 ALTER COLUMN b SET DEFAULT 7 /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17635 dtle_gtid*/
2022-04-02T10:34:18.421137Z	  138 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.421726Z	  138 Query	COMMIT
2022-04-02T10:34:18.422124Z	  153 Query	START TRANSACTION
2022-04-02T10:34:18.422285Z	  153 Query	USE `action_db_1`
2022-04-02T10:34:18.422499Z	  153 Query	ALTER TABLE t8 ALTER COLUMN b DROP DEFAULT /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17636 dtle_gtid*/
2022-04-02T10:34:18.423345Z	  153 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.423652Z	  153 Query	COMMIT
2022-04-02T10:34:18.449521Z	  164 Query	START TRANSACTION
2022-04-02T10:34:18.449888Z	  164 Query	USE `action_db_1`
2022-04-02T10:34:18.450246Z	  164 Query	ALTER TABLE t11 ADD d CHAR(20) /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17637 dtle_gtid*/
2022-04-02T10:34:18.477068Z	  164 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.477589Z	  164 Query	COMMIT
2022-04-02T10:34:18.478412Z	  143 Query	START TRANSACTION
2022-04-02T10:34:18.478675Z	  143 Query	USE `action_db_1`
2022-04-02T10:34:18.479018Z	  143 Query	ALTER TABLE t12 ADD d CHAR(20) AFTER b /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17638 dtle_gtid*/
2022-04-02T10:34:18.505975Z	  143 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.506465Z	  143 Query	COMMIT
2022-04-02T10:34:18.508724Z	  155 Query	START TRANSACTION
2022-04-02T10:34:18.509068Z	  155 Query	USE `action_db_1`
2022-04-02T10:34:18.509468Z	  155 Query	ALTER TABLE t13 ADD d CHAR(20) FIRST /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17639 dtle_gtid*/
2022-04-02T10:34:18.536039Z	  155 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.536431Z	  155 Query	COMMIT
2022-04-02T10:34:18.536870Z	  161 Query	START TRANSACTION
2022-04-02T10:34:18.537207Z	  161 Query	USE `action_db_1`
2022-04-02T10:34:18.537521Z	  161 Query	ALTER TABLE t14 DROP b /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17640 dtle_gtid*/
2022-04-02T10:34:18.564308Z	  161 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.564746Z	  161 Query	COMMIT
2022-04-02T10:34:18.567057Z	  141 Query	START TRANSACTION
2022-04-02T10:34:18.567468Z	  141 Query	USE `action_db_1`
2022-04-02T10:34:18.567761Z	  141 Query	ALTER TABLE t15 ADD (d CHAR(20), e INT(11)) /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17641 dtle_gtid*/
2022-04-02T10:34:18.594860Z	  141 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.595247Z	  141 Query	COMMIT
2022-04-02T10:34:18.596789Z	  138 Query	START TRANSACTION
2022-04-02T10:34:18.597032Z	  138 Query	USE `action_db_1`
2022-04-02T10:34:18.597337Z	  138 Query	ALTER TABLE t16 DROP b, DROP COLUMN c /*dtle_gtid1 sql_filter_NoDDL-migration 515d7425-b260-11ec-89b4-0242ac640903 17642 dtle_gtid*/
2022-04-02T10:34:18.599131Z	  176 Connect	test@172.100.9.253 on  using TCP/IP
2022-04-02T10:34:18.599992Z	  176 Query	SHOW MASTER STATUS
2022-04-02T10:34:18.623957Z	  138 Execute	replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values (?, ?, ?, null)
2022-04-02T10:34:18.624273Z	  138 Query	COMMIT
2022-

more sql filter #927

Description

Case 1 add more filters

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions