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)
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 8.0.22
• OS: CentOS
• 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