上游mysql通过DM同步到tidb从库的DDL lock问题

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:3.0.9
  • 【问题描述】:我从上游mysql通过dm同步到tidb从库,如下sql产生了ddl lock,导致主从同步停止,请问这个是什么原因呢,如何避免?上游mysql到tidb从库的表是一一对应。

task任务的状态如下: “subTaskStatus”: [ { “name”: “test”, “stage”: “Running”, “unit”: “Sync”, “result”: null, “unresolvedDDLLockID”: “test-hollycas.tbl_order_info”, “sync”: { “totalEvents”: “23531259”, “totalTps”: “801”, “recentTps”: “0”, “masterBinlog”: “(mysql-bin.002313, 689999667)”, “masterBinlogGtid”: “”, “syncerBinlog”: “(mysql-bin|000001.002283, 922100404)”, “syncerBinlogGtid”: “”, “blockingDDLs”: [ “ALTER TABLE hollycas.tbl_order_info ADD INDEX inx_create_time(create_time) USING BTREE” ], “unresolvedGroups”: [ { “target”: “hollycas.tbl_order_info”, “DDLs”: [ “ALTER TABLE hollycas.tbl_order_info ADD INDEX inx_create_time(create_time) USING BTREE” ], “firstPos”: “(mysql-bin|000001.002283, 922542857)”, “synced”: [ “hollycas.tbl_order_info” ], “unsynced”: [ ] } ], “synced”: false } }

dm-worker日志如下: [2020/03/06 18:48:37.743 +08:00] [INFO] [server.go:290] [request=FetchDDLInfo] [“ddl info”="task:“test” schema:“hollycas” table:“tbl_order_info” DDLs:“ALTER TABLE hollycas.tbl_order_info ADD INDEX inx_create_time(create_time) USING BTREE” "] [2020/03/06 18:48:37.744 +08:00] [INFO] [server.go:307] [“receive DDLLockInfo”] [request=FetchDDLInfo] [“ddl lock info”=“task:“test” ID:“test-hollycas.tbl_order_info” “] [2020/03/06 18:48:37.744 +08:00] [ERROR] [server.go:315] [“fail to record DDLLockInfo”] [request=FetchDDLInfo] [“ddl lock info”=“task:“test” ID:“test-hollycas.tbl_order_info” “] [error=”[code=40062:class=dm-worker:scope=internal:level=high] DDLLockInfo for task test already exists”] [errorVerbose=”[code=40062:class=dm-worker:scope=internal:level=high] DDLLockInfo for task test already exists\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/dm/worker.(*SubTask).SaveDDLLockInfo\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/worker/subtask.go:529\ngithub.com/pingcap/dm/dm/worker.(*Worker).RecordDDLLockInfo\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/worker/worker.go:445\ngithub.com/pingcap/dm/dm/worker.(*Server).FetchDDLInfo\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/worker/server.go:311\ngithub.com/pingcap/dm/dm/pb._Worker_FetchDDLInfo_Handler\n\t/home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/dm/pb/dmworker.pb.go:3865\ngoogle.golang.org/grpc.(*Server).processStreamingRPC\n\t/go/pkg/mod/google.golang.org/grpc@v1.25.1/server.go:1211\ngoogle.golang.org/grpc.(*Server).handleStream\n\t/go/pkg/mod/google.golang.org/grpc@v1.25.1/server.go:1291\ngoogle.golang.org/grpc.(*Server).serveStreams.func1.1\n\t/go/pkg/mod/google.golang.org/grpc@v1.25.1/server.go:722\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357”]

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

是分库分表场景吗,test- hollycas . tbl_order_info 表在上游是不是多个分表,所有的分表都执行了 add index 操作了吗?

sharding ddl lock 的常见场景和处理方法,参考下文档

不是分库分表场景,是一一对应关系。

我的dm版本是1.0.3。我使用连接中的方案解锁后可以恢复后继续,但是这样的场景出现2次了,上一次导致ddl lock的语句是:ALTER TABLE hollycas.tbl_sms_task_user_sta ADD COLUMN sum_rows BIGINT(20) NULL AFTER count1,想知道这个具体是什么原因导致呢,有没有办法可以避免。

dmctl > show-ddl-locks test 看下相关信息

» show-ddl-locks test
{
“result”: true,
“msg”: “”,
“locks”: [
{
“ID”: “test-hollycas.tbl_order_info”,
“task”: “test”,
“owner”: “xxx.xxx.xxx.190:8262”,
“DDLs”: [
“ALTER TABLE hollycas.tbl_order_info ADD INDEX inx_create_time(create_time) USING BTREE”
],
“synced”: [
“xxx.xxx.xxx.190:8262”
],
“unsynced”: [
“xxx.xxx.xxx.189:8262”
]
}
]
}

134.200.45.189 这个 dm worker 对应的上游 mysql 是否同样有 tbl_order_info 表?

这个dm-worker对应的是另一个mysql数据库,没有 tbl_order_info 这张表

能发下 task 配置文件吗

我传一个附件吧test.yml (6.3 KB)

先按照上面手动处理 ddl lock 文档处理下报错吧,然后将 is-sharding 参数设置为 false

好的,谢谢!

如有其他问题欢迎反馈