通过binlog同步Mysql数据到TIDB,偶尔发生主键冲突

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

  • 【TiDB 版本】:3.0.*
  • 【问题描述】:使用阿里云DTS工具同步线上Mysql数据到TIDB。经常报主键冲突,导致同步失败。TIDB的库是没有人为改动的。

日志: [2020/03/14 15:02:59.414 +08:00] [INFO] [server.go:413] [“new connection”] [conn=828] [remoteAddr=47.103.129.154:40128] [2020/03/14 15:02:59.445 +08:00] [INFO] [set.go:192] [“set session var”] [conn=828] [name=character_set_results] [val=NULL] [2020/03/14 15:02:59.458 +08:00] [INFO] [set.go:192] [“set session var”] [conn=828] [name=foreign_key_checks] [val=0] [2020/03/14 15:02:59.465 +08:00] [INFO] [set.go:192] [“set session var”] [conn=828] [name=time_zone] [val=+00:00] [2020/03/14 15:02:59.478 +08:00] [INFO] [set.go:192] [“set session var”] [conn=828] [name=sql_mode] [val=] [2020/03/14 15:02:59.491 +08:00] [INFO] [set.go:192] [“set session var”] [conn=828] [name=timestamp] [val=] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.34:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.34:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.31:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.32:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.31:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.32:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.31:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.31:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.34:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.33:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.33:20160] [cause=“context canceled”] [2020/03/14 15:02:59.513 +08:00] [WARN] [session.go:435] [“can not retry txn”] [conn=828] [label=general] [error="[kv:1062]Duplicate entry ‘2d0b1334-7ec1-49e5-9174-a19e8f3529e9’ for key ‘PRIMARY’"] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=false] [tidb_retry_limit=10] [tidb_disable_txn_auto_retry=true] [2020/03/14 15:02:59.513 +08:00] [WARN] [session.go:461] [“commit failed”] [conn=828] [“finished txn”=“Txn{state=invalid}”] [error="[kv:1062]Duplicate entry ‘2d0b1334-7ec1-49e5-9174-a19e8f3529e9’ for key ‘PRIMARY’"] [2020/03/14 15:02:59.513 +08:00] [WARN] [conn.go:682] [“dispatch error”] [conn=828] [connInfo=“id:828, addr:47.103.129.154:40128 status:0, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:0”] [sql=commit] [err=“previous statement: /DTS_0_yecj1pgp14tlb8q/ insert into dbname.pay_order (real_ip,amount,create_time,campus,pay_number,modify_time,pay_account,pay_method,close_time,uuid,pay_time,pay_status,refund_time,pay_type,pay_type_attach,order_id,user) values(‘211.94.236.8’,1.99,‘2020-03-13 20:57:44’,‘1cdd2449-22be-4556-984f-e4e5a0f746ee’,‘20200313205744211094236008254728’,‘2020-03-13 20:57:44’,‘627a99d8-f272-41bd-8670-1079e667093f’,‘3’,null,‘2d0b1334-7ec1-49e5-9174-a19e8f3529e9’,null,‘0’,null,‘3’,‘订单支付’,‘b77af90d-ae15-479e-818f-a30003c240c4’,‘d437101c-9861-417c-a448-ad04fb10c770’): [kv:1062]Duplicate entry ‘2d0b1334-7ec1-49e5-9174-a19e8f3529e9’ for key ‘PRIMARY’”]

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

另外想问一下,日志: [2020/03/14 15:02:59.513 +08:00] [WARN] [client_batch.go:570] [“wait response is cancelled”] [to=192.168.30.33:20160] [cause=“context canceled”]

代表什么意思

您好: 从日志看是已经存在该主键值,导致插入新数据时重复. 请检查数据库中是否已经存在数据。业务上是否会插入重复主键数据.

insert into dbname . pay_order ( real_ip , amount , create_time , campus , pay_number , modify_time , pay_account , pay_method , close_time , uuid , pay_time , pay_status , refund_time , pay_type , pay_type_attach , order_id , user ) values(‘211.94.236.8’,1.99,‘2020-03-13 20:57:44’,‘1cdd2449-22be-4556-984f-e4e5a0f746ee’,‘20200313205744211094236008254728’,‘2020-03-13 20:57:44’,‘627a99d8-f272-41bd-8670-1079e667093f’,‘3’,null,‘2d0b1334-7ec1-49e5-9174-a19e8f3529e9’,null,‘0’,null,‘3’,‘订单支付’,‘b77af90d-ae15-479e-818f-a30003c240c4’,‘d437101c-9861-417c-a448-ad04fb10c770’): [kv:1062]Duplicate entry ‘2d0b1334-7ec1-49e5-9174-a19e8f3529e9’ for key ‘PRIMARY’”]

不会的~tidb只有同步服务有写权限

能否查询一下当前pay_order表里的2d0b1334-7ec1-49e5-9174-a19e8f3529e9数据信息? 和这条冲突的数据对比一下,多谢. select * from dbname.pay_order where xxx=‘2d0b1334-7ec1-49e5-9174-a19e8f3529e9’;