dm 同步失败解决办法

通过dm，把mysql的数据同步到tidb中，到目前为止发生二次同步失败的情况

一，mysql修改表字段报错

"errors": [
{
"ErrCode": 44006,
"ErrClass": "schema-tracker",
"ErrScope": "internal",
"ErrLevel": "high",
"Message": "startLocation: [position: (mysql-bin.000022, 545530809), gtid-set: ], endLocation: [position: (mysql-bin.000022, 545530999), gtid-set: ]: cannot track DDL: ALTER TABLE `test`.`test` MODIFY COLUMN `advertiser_id` BIGINT(11) NOT NULL DEFAULT _UTF8MB4'0' COMMENT ''",
"RawCause": "[ddl:8200]Unsupported modify column: type bigint(11) not match origin varchar(36), and tidb_enable_change_column_type is false",
"Workaround": ""
}
],

解决办法：

1，把tidb中报错的表，只导出数据

2，把mysql中对应的表，导出结构

3，登录tidb数据库，把报错的表删除，导入mysql导出的表结构，然后在导入tidb导出的表数据

4，通过handle-error [task_name] skip跳过错误

[tidb@jiankong task]$ tiup dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.1/dmctl/dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "source": "mysql57",
            "worker": "dm-10.0.10.15-8262"
        }
    ]
}

这种方式个人觉得是最快的恢复的方式了。也有其他的方式，不过不推荐使用。例如：新建task，然后重新同步。

二，tidb报主键冲突

1，通过匹配报错来找出错误

{
"ErrCode": 10006,
"ErrClass": "database",
"ErrScope": "not-set",
"ErrLevel": "high",
"Message": "startLocation: [position: (, 0), gtid-set: ], endLocation: [position: (mysql-bin.000026, 535844593), gtid-set: ]: execute statement failed: commit",
"RawCause": "Error 1062: Duplicate entry '96000' for key 'PRIMARY'",
"Workaround": ""
},

从上面报错看，没有表名和数据名，startLocation为0，endLocation是有的，mysql的binlog文件也有，就好办了。

# mysqlbinlog --base64-output=DECODE-ROWS --stop-position=535844593 -v -d test ./mysql-bin.000026 |grep -B 3 96000

如果知道数据名就加上-d，不确定就拿掉

查找mysql binlog的日志

一般来说都在最后，因为发错就会中止同步。确定表了以后，对比tidb中的表数据和mysql表数据，如果数据不一样，就可以确定不同步了。

2，通过show binlog events，更方便

//找到执行sql的position
# mysqlbinlog --base64-output=DECODE-ROWS --stop-position=535844593 -v ./mysql-bin.000026|grep Query

。。。。。。。。。。。。。。。。。。。。。。。。省略。。。。。。。。。。。。。。。。。。。。。。。。。。。。

#210123 17:35:00 server id 1001020  end_log_pos 535652987 CRC32 0xc15382e9 	Query	thread_id=156492	exec_time=0	error_code=0
#210123 18:00:00 server id 1001020  end_log_pos 535727785 CRC32 0x09652fdb 	Query	thread_id=156603	exec_time=0	error_code=0

//根据最后一个position来找sql，并确定表
mysql> show binlog events in 'mysql-bin.000026' from 535727785 limit 5;

解决办法：

删除tidb表中的报错id。然后重新启动

# tiup dmctl --master-addr 10.0.10.18:8261 start-task ./mysql57-task.yaml

handle-error是不能解决问题的。

[tidb@jiankong task]$ tiup dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
Starting component `dmctl`: /home/tidb/.tiup/components/dmctl/v2.0.1/dmctl/dmctl --master-addr 10.0.10.18:8261 handle-error mysql57-task skip
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": false,
            "msg": "only support to handle ddl error currently, see https://docs.pingcap.com/tidb-data-migration/stable/error-handling for other errors",
            "source": "mysql57",
            "worker": ""
        }
    ]
}

handle-error [task_name] skip只支持跳过ddl的错误