Dm中同步错误skip-sql无法跳过

🪐 TiDB 技术问题 备份&数据迁移
1

dm中同步错误，错误原因mysql中一次性修改两个字段的类型，tidb不支持导致同步出错。
错误日志：

"result": true,
            "worker": "192.168.10.101:8263",
            "msg": "",
            "subTaskStatus": [
                {
                    "name": "tidb",
                    "stage": "Paused",
                    "unit": "Sync",
                    "result": {
                        "isCanceled": false,
                        "errors": [
                            {
                                "Type": "ExecSQL",
                                "msg": "exec sqls[[ALTER TABLE `h3-price`.`t_price_group_ware_detail` CHANGE COLUMN `price_group_code` `price_group_inside_code` VARCHAR(60) CHARACTER SET UTF8 COLLATE utf8_general_ci NOT NULL COMMENT '价格组内编码' AFTER `cp_code`]] failed, err:Error 1105: unsupported modify column %!s(ast.ColumnOptionType=12)
github.com/pingcap/dm/syncer.(*Conn).executeSQL
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/db.go:140
github.com/pingcap/dm/syncer.(*Syncer).sync
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:888
github.com/pingcap/dm/syncer.(*Syncer).Run.func3
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1023
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1337"
                            }
                        ],
                        "detail": null
                    },
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "0",
                        "totalTps": "0",
                        "recentTps": "0",
                        "masterBinlog": "(dbm1_3306-bin.000003, 56207152)",
                        "masterBinlogGtid": "",
                        "syncerBinlog": "(dbm1_3306-bin|000001.000003, 56019374)",
                        "syncerBinlogGtid": "",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": false
                    }
                }
            ],
            "relayStatus": {
                "masterBinlog": "(dbm1_3306-bin.000003, 56207152)",
                "masterBinlogGtid": "",
                "relaySubDir": "07777bc0-9dd1-11e7-9d49-000c29652542.000001",
                "relayBinlog": "(dbm1_3306-bin.000003, 56207152)",
                "relayBinlogGtid": "",
                "relayCatchUpMaster": true,
                "stage": "Running",
                "result": null
            },
            "sourceID": "mysql2"

使用skip-sql执行：

sql-skip -w 192.168.10.101:8263 -b dbm1_3306-bin|000001.000003:56019374 tidb
或
sql-skip -w 192.168.10.101:8263 -b dbm1_3306-bin|000001.000003:56207152 tidb

返回日志：

{
    "result": true,
    "msg": "",
    "workers": [
        {
            "result": true,
            "worker": "",
            "msg": ""
        }
    ]
}

然后执行resume-task：

» resume-task -w 192.168.10.101:8263 tidb
{
    "op": "Resume",
    "result": true,
    "msg": "",
    "workers": [
        {
            "meta": {
                "result": true,
                "worker": "192.168.10.101:8263",
                "msg": ""
            },
            "op": "Resume",
            "logID": "12"
        }
    ]
}

然后查看状态还是同样的错误，同样的pos。

worker的日志：

[2019/08/13 15:37:16.567 +08:00] [INFO] [server.go:333] [request=HandleSQLs] [payload="name:"tidb" binlogPos:"dbm1_3306-bin|000001.000003:56207152" "]
[2019/08/13 15:37:16.567 +08:00] [WARN] [operator.go:133] ["overwrite operator"] [task=tidb] [unit="binlog replication"] ["old operator"="uuid: dcef21d9-3bc3-4aad-9d56-1a8c7a55109d, pos: (dbm1_3306-bin|000001.000003, 56207152), op: SKIP, args: "] ["new operator"="uuid: e33a247b-6f4a-4200-b6ed-560f69a298fb, pos: (dbm1_3306-bin|000001.000003, 56207152), op: SKIP, args: "]
[2019/08/13 15:37:16.567 +08:00] [INFO] [operator.go:136] ["set a new operator"] [task=tidb] [unit="binlog replication"] ["new operator"="uuid: e33a247b-6f4a-4200-b6ed-560f69a298fb, pos: (dbm1_3306-bin|000001.000003, 56207152), op: SKIP, args: "]
[2019/08/13 15:37:23.390 +08:00] [INFO] [server.go:239] [request=QueryStatus] [payload="name:"tidb" "]
[2019/08/13 15:37:45.557 +08:00] [INFO] [relay.go:380] ["ignore event by transformer"] [component="relay log"] [header="{"Timestamp":0,"EventType":27,"ServerID":10,"EventSize":43,"LogPos":56207152,"Flags":0}"]
2

可以修改下 meta 文件来跳过这个 binlog position 。具体操作为：停 DM-worker ，修改 relay.meta （binlog_file_name & position ）文件，启动 DM-worker 。由于该文件非实时更新，因此无法判断当前最新的 binlog 位置信息，可通过 dmctl 来获取最新位置信息。

3

好的，谢谢。
那skip-sql是什么作用？

4

skip-sql 也是跳过出错的 SQL，看到前面 skip-sql 没有生效，建议试下直接修改 meta 文件，暴力又直接一些。

5

好的。谢谢:ok_hand::+1:

6

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。