跳过binlog该指定 开始位置还是结束位置呢??
“errors”: [
{
“ErrCode”: 44006,
“ErrClass”: “schema-tracker”,
“ErrScope”: “internal”,
“ErrLevel”: “high”,
“Message”: “startLocation: [position: (3308-binlog.000002, 3514), gtid-set: ], endLocation: [position: (3308-binlog.000002, 3640), gtid-set: ]: cannot track DDL: ALTER TABLE hu_test1
.t1
MODIFY COLUMN id3
VARCHAR(8)”,
“RawCause”: “[ddl:8200]Unsupported modify column: length 8 is less than origin 10”,
“Workaround”: “”
}
],
“detail”: null
},
“unresolvedDDLLockID”: “”,
“sync”: {
“totalEvents”: “14”,
“totalTps”: “0”,
“recentTps”: “0”,
“masterBinlog”: “(3308-binlog.000002, 3640)”,
“masterBinlogGtid”: “”,
“syncerBinlog”: “(3308-binlog.000002, 3449)”,
“syncerBinlogGtid”: “”,
“blockingDDLs”: [
],
“unresolvedGroups”: [
],
“synced”: false,
“binlogType”: “remote”
在迁移执行出错后,binlog position 可直接从 query-status
返回的 startLocation
中的 position
获得;
是取这段中的binlog位置吗? startLocation: [position: (3308-binlog.000002, 3514)
还有这个格式这么拼接呢,1.0版本中自动拼接格式了。如 mysql-bin|000001.000003:3270
。
现在改怎么拼接。
DM 2.0 增加 handle-error
命令用于处理增量过程中 DDL 复制相关的错误,请直接参考上面的链接。
[tidb@localhost ~]$ tiup dmctl --master-addr 10.200.25.254:8261 query-status test1
Starting component dmctl
: /home/tidb/.tiup/components/dmctl/v2.0.0/dmctl/dmctl --master-addr 10.200.25.254:8261 query-status test1
{
“result”: true,
“msg”: “”,
“sources”: [
{
“result”: true,
“msg”: “”,
“sourceStatus”: {
“source”: “mysql-replica-01”,
“worker”: “dm-10.200.25.227-8262”,
“result”: null,
“relayStatus”: null
},
“subTaskStatus”: [
{
“name”: “test1”,
“stage”: “Paused”,
“unit”: “Sync”,
“result”: {
“isCanceled”: false,
“errors”: [
{
“ErrCode”: 10006,
“ErrClass”: “database”,
“ErrScope”: “downstream”,
“ErrLevel”: “high”,
“Message”: “startLocation: [position: (3308-binlog.000002, 3896), gtid-set: ], endLocation: [position: (3308-binlog.000002, 4009), gtid-set: ]: execute statement failed: ALTER TABLE hu_test1
.t1
DROP PRIMARY KEY”,
“RawCause”: “Error 8200: Unsupported drop primary key when alter-primary-key is false”,
“Workaround”: “”
}
],
“detail”: null
},
“unresolvedDDLLockID”: “”,
“sync”: {
“totalEvents”: “14”,
“totalTps”: “0”,
“recentTps”: “0”,
“masterBinlog”: “(3308-binlog.000002, 4009)”,
“masterBinlogGtid”: “”,
“syncerBinlog”: “(3308-binlog.000002, 3449)”,
“syncerBinlogGtid”: “”,
“blockingDDLs”: [
],
“unresolvedGroups”: [
],
“synced”: false,
“binlogType”: “remote”
}
}
]
}
]
}
[tidb@localhost ~]$ tiup dmctl handle-error test1 --binlog-pos=3308-binlog|000001.000002:3896 skip
-bash: 000001.000002:3896: command not found
这么写格式哪里有问题??
1.最上面报错的原因是 “Error 8200: Unsupported drop primary key when alter-primary-key is false” ,主键默认情况下是不能修改的,需要将参数 alter-primary-key 改为 true,参考文档:
https://docs.pingcap.com/zh/tidb/stable/tidb-configuration-file#alter-primary-key
2. binlog-pos 格式说明如下,请根据你实际情况调整下:
我知道主键不能调整,我现在就是想根据binlog来跳过这个操作。
官方文档中是下面这么描述的,而我的报错是"startLocation: [position: (3308-binlog.000002, 3896)
- 在指定时表示操作将在
binlog-pos
与 binlog event 的 position 匹配时生效,格式为binlog-filename:binlog-pos
,如mysql-bin|000001.000003:3270
。 - 在迁移执行出错后,binlog position 可直接从
query-status
返回的startLocation
中的position
获得;在迁移执行出错前,binlog position 可在上游 MySQL 中使用SHOW BINLOG EVENTS
获得。
问题:根据官方文档000001.000003 中描述 0000001是哪里来的? DM2.0只有[position: (3308-binlog.000002, 3896)。。
我自己组成的语句是handle-error test1 -s mysql-replica-01 --binlog-pos=3308-binlog|000002:3896 skip
为啥跳不过这个报错呢?
» handle-error test1 -s mysql-replica-01 --binlog-pos=3308-binlog|000002:3896 skip
{
“result”: true,
“msg”: “”,
“sources”: [
{
“result”: true,
“msg”: “”,
“source”: “mysql-replica-01”,
“worker”: “dm-10.200.25.227-8262”
}
]
}
» query-status
{
“result”: true,
“msg”: “”,
“tasks”: [
{
“taskName”: “test1”,
“taskStatus”: “Error - Some error occurred in subtask. Please run query-status test1
to get more details.”,
“sources”: [
“mysql-replica-01”
]
},
» handle-error test1 -s mysql-replica-01 --binlog-pos=mysql-bin|3308-binlog.000002:3896 skip
{
“result”: true,
“msg”: “”,
“sources”: [
{
“result”: true,
“msg”: “”,
“source”: “mysql-replica-01”,
“worker”: “dm-10.200.25.227-8262”
}
]
}
» query-status
{
“result”: true,
“msg”: “”,
“tasks”: [
{
“taskName”: “test1”,
“taskStatus”: “Error - Some error occurred in subtask. Please run query-status test1
to get more details.”,
“sources”: [
“mysql-replica-01”
]
},
{
“taskName”: “test3309”,
“taskStatus”: “Running”,
“sources”: [
“mysql-replica-02”
]
}
]
直接 handle-error test1 skip 就行,2.0 会自动跳过当前错误ddl语句,不用特别指定binlogpos
这个方式是可以的,指定某个binlog没测试通过?
还测试了一种情况,表是有主键的
1 在tidb表插入了一行数据,commit
2 在mysql端插入同一样数据,主键一样,其他字段不一样
同步报错了
“subTaskStatus”: [
{
“name”: “test1”,
“stage”: “Paused”,
“unit”: “Sync”,
“result”: {
“isCanceled”: false,
“errors”: [
{
“ErrCode”: 10006,
“ErrClass”: “database”,
“ErrScope”: “not-set”,
“ErrLevel”: “high”,
“Message”: “startLocation: [position: (3308-binlog.000002, 6416), gtid-set: ], endLocation: [position: (3308-binlog.000002, 6459), gtid-set: ]: execute statement failed: commit”,
“RawCause”: “Error 1062: Duplicate entry ‘2’ for key ‘PRIMARY’”,
这种情况还是要指定binlog位置跳过吧, 命令改怎么写呢??
handle-error test1 skip
或者 handle-error test1 -b 3308-binlog.000002:6416 skip
handle-error test1 -b 3308-binlog.000002:6416 skip 该方式可以跳过。
顺便问下handle-error test1 skip 这个操作也是跳过当前的DDL DML都支持的吧
下个版本会限制只能处理ddl,不能处理dml。因为dml错误基本都是因为其他原因,跳过不能解决根本问题,还会造成数据不一致