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

🌌 运维指南 TiDB 常见 FAQ
1

【问题澄清】

  • 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\
github.com/pingcap/dm/pkg/terror.(*Error).Delegate\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267\
github.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQLWithIgnoreError\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:193\
github.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore.func3\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:280\
github.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71\
github.com/pingcap/dm/pkg/conn.(*BaseConn).ApplyRetryStrategy\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:216\
github.com/pingcap/dm/syncer.(*DBConn).executeSQLWithIgnore\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:275\
github.com/pingcap/dm/syncer.(*Syncer).syncDDL\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:918\
github.com/pingcap/dm/syncer.(*Syncer).Run.func3\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1124\
runtime.goexit\
\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` \
MODIFY COLUMN `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL AFTER `id`\
github.com/pingcap/dm/pkg/terror.(*Error).Generate\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:232\
github.com/pingcap/dm/syncer.(*Syncer).handleQueryEvent\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1774\
github.com/pingcap/dm/syncer.(*Syncer).Run\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1340\
github.com/pingcap/dm/syncer.(*Syncer).Process\
\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:599\
runtime.goexit\
\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

【经典案例】