mapping `ColumnMapFrom`: create table does not adjust the column order according to the configuration

[asiroliu]

Description

mapping ColumnMapFrom: create table does not adjust the column order according to the configuration

Steps to reproduce the issue

1. insert data on src MySQL

shell> sysbench /usr/share/sysbench/oltp_common.lua --mysql-host=172.100.9.1 --mysql-port=3306 --mysql-user=test --mysql-password=test --create_secondary=off --report-interval=10 --time=0 --mysql-db=test --tables=1 --table_size=1 prepare

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2. create column map mysql-mysql job

{
  "job_id": "mapping_column_map_full_repl",
  "is_password_encrypted": false,
  "task_step_name": "all",
  "failover": true,
  "retry": 2,
  "src_task": {
    "task_name": "src",
    "node_id": "3bd8c156-13da-3671-d565-44ec56b7755e",
    "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.1",
      "port": 3306,
      "user": "test_src",
      "password": "test_src"
    },
    "replicate_do_db": [
      {
        "table_schema": "test",
        "tables": [
          {
            "table_name": "sbtest1",
            "column_map_from": [
              "id",
              "c",
              "k",
              "pad"
            ]
          }
        ]
      }
    ]
  },
  "dest_task": {
    "task_name": "dest",
    "node_id": "469b0d3d-0caa-c421-01b0-390c2940d701",
    "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"
    }
  }
}

3. check dest MySQL

mysql> select * from sbtest1;
Empty set (0.00 sec)

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

4. check dest DTLE log

2022-03-17T15:30:47.751+0800 [DEBUG] client.driver_mgr.dtle: ApplyEventQueries. exec: driver=dtle @module=dtle.applier job=aaa-migration query="DROP TABLE IF EXISTS `test`.`sbtest1`" timestamp=2022-03-17T15:30:47.751+0800
2022-03-17T15:30:47.766+0800 [DEBUG] client.driver_mgr.dtle: ApplyEventQueries. exec: driver=dtle @module=dtle.applier 

# create table sbtest1 is same as src MySQL, and different from job config
job=aaa-migration query="CREATE TABLE `test`.`sbtest1` (`id` INT(11) NOT NULL AUTO_INCREMENT,`k` INT(11) NOT NULL DEFAULT '0',`c` CHAR(120) NOT NULL DEFAULT '',`pad` CHAR(60) NOT NULL DEFAULT '',PRIMARY KEY(`id`)) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARACTER SET = LATIN1" timestamp=2022-03-17T15:30:47.766+0800
2022-03-17T15:30:47.791+0800 [DEBUG] client.driver_mgr.dtle: after create gtid_executed table: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.791+0800
2022-03-17T15:30:47.791+0800 [DEBUG] client.driver_mgr.dtle: after createTableGtidExecutedV4: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.791+0800
2022-03-17T15:30:47.834+0800 [DEBUG] client.driver_mgr.dtle: after prepare stmt for gtid_executed table: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.834+0800
2022-03-17T15:30:47.835+0800 [DEBUG] client.driver_mgr.dtle: after SelectAllGtidExecuted: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.835+0800
2022-03-17T15:30:47.841+0800 [DEBUG] client.driver_mgr.dtle: ApplyEventQueries. after: driver=dtle job=aaa-migration nDumpEntry=1 @module=dtle.applier timestamp=2022-03-17T15:30:47.841+0800
2022-03-17T15:30:47.841+0800 [DEBUG] client.driver_mgr.dtle: ApplyEventQueries: driver=dtle @module=dtle.applier job=aaa-migration rows=1 schema=test table=sbtest1 timestamp=2022-03-17T15:30:47.841+0800
2022-03-17T15:30:47.842+0800 [DEBUG] client.driver_mgr.dtle: ApplyEventQueries. exec: driver=dtle job=aaa-migration query="replace into `test`.`sbtest1` values ('1','83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330','1','67847967377-48000963322-62604785301-91415491898-96926520291')" @module=dtle.applier timestamp=2022-03-17T15:30:47.842+0800
2022-03-17T15:30:47.843+0800 [ERROR] client.driver_mgr.dtle: ApplyEventQueries. exec error: driver=dtle job=aaa-migration @module=dtle.applier err="tx.Exec. queryStart replace in seq: Error 1264: Out of range value for column 'k' at row 1" timestamp=2022-03-17T15:30:47.842+0800
2022-03-17T15:30:47.843+0800 [ERROR] client.driver_mgr.dtle: onError: driver=dtle err="tx.Exec. queryStart replace in seq: Error 1264: Out of range value for column 'k' at row 1" job=aaa-migration @module=dtle.applier timestamp=2022-03-17T15:30:47.843+0800
2022-03-17T15:30:47.843+0800 [DEBUG] client.driver_mgr.dtle: onError. nats published: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.843+0800
2022-03-17T15:30:47.843+0800 [INFO]  client.driver_mgr.dtle: Shutting down: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.843+0800
2022-03-17T15:30:47.843+0800 [DEBUG] client.driver_mgr.dtle: Shutdown. a.ai.wg.Wait. after: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.843+0800
2022-03-17T15:30:47.849+0800 [INFO]  client.driver_mgr.dtle: DestroyTask: driver=dtle @module=dtle id=bc450208-bebb-f0c1-f8cf-0ab23c565e66/dest/7019dffa timestamp=2022-03-17T15:30:47.849+0800
2022-03-17T15:30:47.849+0800 [INFO]  client.driver_mgr.dtle: Shutting down: driver=dtle @module=dtle.applier job=aaa-migration timestamp=2022-03-17T15:30:47.849+0800

Output of ./dtle version:**

9.9.9.9-master-1a960d4

[asiroliu]

增量阶段发现同样的错误

# dest dtle log
2022-03-30T13:38:10.232+0800 [DEBUG] client.driver_mgr.dtle: execQuery: driver=dtle @module=dtle.applier job=mapping_column_map-migration query="CREATE TABLE sbtest1(
  id INTEGER NOT NULL AUTO_INCREMENT,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
) /*! ENGINE = innodb */ /*dtle_gtid1 mapping_column_map-migration 62c7874f-afeb-11ec-a260-0242ac640902 2028 dtle_gtid*/" timestamp=2022-03-30T13:38:10.232+0800

...

2022-03-30T13:38:10.252+0800 [DEBUG] client.driver_mgr.dtle: buildDMLEventQuery prepare query: driver=dtle @module=dtle.applier job=mapping_column_map-migration query="replace into `action_db_1`.`sbtest1`
(`id`, `k`, `c`, `pad`) values
(?, ?, ?, ?)" timestamp=2022-03-30T13:38:10.246+0800
2022-03-30T13:38:10.252+0800 [DEBUG] client.driver_mgr.dtle: buildDMLEventQuery.after: driver=dtle @module=dtle.applier.ApplyBinlogEvent job=mapping_column_map-migration nArgs=4 timestamp=2022-03-30T13:38:10.246+0800
2022-03-30T13:38:10.252+0800 [ERROR] client.driver_mgr.dtle: error at exec: driver=dtle err="Error 1264: Out of range value for column 'id' at row 1" gtid=62c7874f-afeb-11ec-a260-0242ac640902:2029 job=mapping_column_map-migration @module=dtle.applier.ApplyBinlogEvent timestamp=2022-03-30T13:38:10.247+0800
2022-03-30T13:38:10.253+0800 [ERROR] client.driver_mgr.dtle: onError: driver=dtle @module=dtle.applier err="Error 1264: Out of range value for column 'id' at row 1" job=mapping_column_map-migration timestamp=2022-03-30T13:38:10.247+0800