[FAQ] dm syncer 阶段同步报错:execute statement failed

【问题澄清】

  • TIDB v3.0.12 + DM 1.0.4
  • 在上游 mysql 端有将字符类型 varchar(255) 更改为 varchar(20), 在 DM 数据同步报错

query-status 信息如下

» query-status qh
{
    "result": true,
    "msg": "",
    "workers": [
        {
            "result": true,
            "worker": "172.16.5.169:18262",
            "msg": "",
            "subTaskStatus": [
                {
                    "name": "qh",
                    "stage": "Paused",
                    "unit": "Sync",
                    "result": {
                        "isCanceled": false,
                        "errors": [
                            {
                                "Type": "ExecSQL",
                                "msg": "[code=10006:class=database:scope=downstream:level=high] execute statement failed: ALTER TABLE `test2`.`t` MODIFY COLUMN `name` VARCHAR(20) CHARACTER SET UTF8MB4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `id`: Error 8200: Unsupported modify column: length 20 is less than origin 255\ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQLWithIgnoreError\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:193\ngithub.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore.func3\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:280\ngithub.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71\ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ApplyRetryStrategy\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:216\ngithub.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:275\ngithub.com/pingcap/dm/syncer.(*Syncer).syncDDL\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:918\ngithub.com/pingcap/dm/syncer.(*Syncer).Run.func3\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1124\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357",
                                "error": null
                            },
                            {
                                "Type": "UnknownError",
                                "msg": "[code=36061:class=sync-unit:scope=internal:level=high] current pos (mysql-bin|000001.000044, 204337905): fail to handle ddl job for ALTER TABLE `test2`.`t` \nMODIFY COLUMN `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `id`\ngithub.com/pingcap/dm/pkg/terror.(*Error).Generate\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:232\ngithub.com/pingcap/dm/syncer.(*Syncer).handleQueryEvent\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1774\ngithub.com/pingcap/dm/syncer.(*Syncer).Run\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1340\ngithub.com/pingcap/dm/syncer.(*Syncer).Process\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:599\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357",
                                "error": null
                            }
                        ],
                        "detail": null
                    },
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "0",
                        "totalTps": "0",
                        "recentTps": "0",
                        "masterBinlog": "(mysql-bin.000044, 204337905)",
                        "masterBinlogGtid": "1a09d366-75ba-11ea-ae29-fab5529d77b8:1-882246",
                        "syncerBinlog": "(mysql-bin.000044, 204337629)",
                        "syncerBinlogGtid": "",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": false
                    }
                }
            ],
            "relayStatus": {
                "masterBinlog": "(mysql-bin.000044, 204337905)",
                "masterBinlogGtid": "1a09d366-75ba-11ea-ae29-fab5529d77b8:1-882246",
                "relaySubDir": "1a09d366-75ba-11ea-ae29-fab5529d77b8.000001",
                "relayBinlog": "(mysql-bin.000044, 204337905)",
                "relayBinlogGtid": "",
                "relayCatchUpMaster": true,
                "stage": "Running",
                "result": null
            },
            "sourceID": "104dmtest"
        }
    ]
}
»     

【问题原因】

【解决方案】

  • 解决本问题的基本思路就是统一上下游表结构,目前测试通过以下步骤可恢复同步:
  1. stop-task qh
  2. 下游 tidb 创建新的表解决 t_new

CREATE TABLE t_new (id INT (11) NOT NULL,name VARCHAR (20) DEFAULT NULL,time datetime DEFAULT NULL,new_column INT (11) DEFAULT NULL,PRIMARY KEY (id),KEY idx_new (new_column)) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

  1. 将旧数据导入新表结构中:

insert into t_new select * from t;

  1. 交换表名

alter table t rename to t_lod;
alter table t_new rename to t;

  1. start-task ./conf/a.yaml
  • 效果如下:

image

【经典案例】