dtle DTLE -> DBLE, Duplicate entry ... for key 'PRIMARY'"...

it's ok when using DTLE from source to a normal target . But will not perfect when from the same source to DBLLE.

DBLE version : 3.21.10.0 DTLE version : 4.22.01.0

error log : [ERROR] client.driver_mgr.dtle: onError: driver=dtle err="Error 1062: Duplicate entry 'xxx_job-\xEF\xBF\xBD\x04/\xEF\xBF\xBD\xEF\xBF\xBD\xEF\xB' for key 'PRIMARY'" job=xxx_job @module=dtle.applier timestamp=2022-05-03T21:10:04.081+0800

Maybe duplicate PK appear in schema : dtle , in table: gtid_executed_v4 . 'xxx_job-\xEF\xBF\xBD\x04/\xEF\xBF\xBD\xEF\xBF\xBD\xEF\xB' -> PRIMARY KEY (job_name,source_uuid,gtid)

I also refer to the article "https://mp.weixin.qq.com/s?__biz=MzU2NzgwMTg0MA==&mid=2247500157&idx=1&sn=008f05aea46500ca773307d86c1890e9&chksm=fc9523e2cbe2aaf49c31ba77a283848311893023b34d4bcec699da3f637232d194a230098289&scene=178&cur_album_id=1662940215407067142#rd". Maybe the test data number in this article is less than my prodution data case.

I'm sorry for can't provide more detail info. error_log.txt

May 03 '22 13:05 a7x9tzz

can you provide MySQL general log？

May 05 '22 01:05 asiroliu

ok, attachement is general log in target DB(DBLE) when that error occurred. The last sql seems insert gtid=0 again. Appreciate for your help general.log ！

May 05 '22 07:05 a7x9tzz

考虑DBLE中此delete没有按预期删除数据，有数据残留。后续insert和残留数据冲突。

2022-05-05T15:26:16.278  24 Execute     delete from dtle.gtid_executed_v4 where job_name = 'certificate1_job' and hex(source_uuid) = 'ED042FFAC7D811EAA8DF1C34DA5C1130'
2022-05-05T15:26:16.287  24 Prepare     insert into dtle.gtid_executed_v4 values (?,?,0,?)
2022-05-05T15:26:16.288  24 Execute     insert into dtle.gtid_executed_v4 values ('certificate1_job','�^D/���^Q��^\4�\\^Q0',0,'2109732843-2109736936')

需验证：

在DBLE中构造dtle.gtid_executed_v4表相关数据
- 通过dtle运行job，或手动insert
执行delete from dtle.gtid_executed_v4 where job_name = 'certificate1_job' and hex(source_uuid) = 'ED042FFAC7D811EAA8DF1C34DA5C1130'
执行SELECT job_name, HEX(source_uuid), gtid, gtid_set FROM dtle.gtid_executed_v4 where job_name = 'certificate1_job' and hex(source_uuid) = 'ED042FFAC7D811EAA8DF1C34DA5C1130'
观察结果是否为空

May 11 '22 16:05 ghost

一个之前的问题：

对于该语句 delete from dtle.gtid_executed_v4 where job_name = ? and source_uuid = ?
- source_uuid参数使用bytes
DBLE和MySQL行为不一致，DBLE有数据残留
dtle commit 596f88c4中，该部分改成了hex(source_uuid) = ?
- 参数使用string
- 该commit包含于4.22.01.0
实际效果尚未有人反馈

May 11 '22 16:05 ghost

按照步骤验证的结果： 1.通过dtle运行job, 相同报错停止。 2.查看general log:

2022-05-12T10:35:28.476 6 Execute delete from dtle.gtid_executed_v4 where job_name = 'certificate1_job' and hex(source_uuid) = 'ED042FFAC7D811EAA8DF1C34DA5C1130'

2022-05-12T10:35:28.485 6 Prepare insert into dtle.gtid_executed_v4 values (?,?,0,?)

2022-05-12T10:35:28.485 6 Execute insert into dtle.gtid_executed_v4 values ('certificate1_job','�/��4�\0',0,'1-2137263450')

3.在dtle中执行查询：select job_name, HEX(source_uuid), gtid, gtid_set from dtle.gtid_executed_v4 where job_name = 'certificate1_job' and hex(source_uuid) = 'ED042FFAC7D811EAA8DF1C34DA5C1130' ，结果为空。

4.在dtle中执行查询：select job_name, HEX(source_uuid), gtid, gtid_set from dtle.gtid_executed_v4 where job_name = 'certificate1_job'，结果贴在附件（两张图一个是前几行，一个是后几行），是有结果的。

5.再结合general log最后3个sql， insert into dtle.gtid_executed_v4 values (?,?,0,?) ，所以看起来是删除了uuid为‘ED042FFAC7D811EAA8DF1C34DA5C1130’的，但是表中都是uuid为‘EFBFBD042FEFBFBDEFBFBDEFBFBD11EF’的数据，最后插入gtid为0 的数据时重复了。 dtle1 dtle2