Bug #101811 replication interrupted when slave restart after crash
Submitted: 1 Dec 2020 3:44 Modified: 7 Dec 2020 10:36
Reporter: long jlong (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:8.0.22 OS:CentOS
Assigned to: Jon Stephens CPU Architecture:Any

[1 Dec 2020 3:44] long jlong 
Description:
mysql.slave_master_info may record Master_log_pos at the middle of a transaction when slave crash. Then after restart, start slave will replicate from middle of a transaction. This may report error:Duplicate entry '39408' for key 't1.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY.

How to repeat:
1.create table t1(a int primary key); /*on master*/
2.stop slave;/*on slave*/
3.insert 100000 records into t1, every 100 records in one transaction./*master*/
4.start slave, and kill -9 slave.(we can select * from mysql.slave_master_info to check Master_log_pos)
5.start slave. /*after restart*/
6.replication will be interrupted, and report error:Duplicate entry 'xxxx' for key 't1.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY

Suggested fix:
Master_log_pos should save the last XID_EVENT pos.
[7 Dec 2020 10:36] MySQL Verification Team 
Hello long jlong,

Thank you for the report and steps.
Verified as described with 8.0.22 build.

regards,
Umesh
[7 Dec 2020 10:46] MySQL Verification Team 
MySQL Server 8.0.22 test results

Attachment: 101811_8.0.22.results.txt (text/plain), 15.02 KiB.
[28 Dec 2020 16:09] Trebonius - 
I was able to repro this bug too, on Ubuntu 20.04, and i tested both MyISAM and InnoDB: both seem to be affected by the issue
[14 Jul 2022 15:53] Jon Stephens 
Should be Docs, not Server:RPL.

Fixed category, reassigned to myself for handling.