Difficult to debug replication failure between MySQL 5.7 primary and MySQL 8 replica
We have a MySQL primary running on 5.7.40. We are in process of testing the upgrade to 8.0.36 (this is on AWS RDS). We had 2 MySQL-8 replicas and 2 MySQL-5.7 replicas replicating from the primary. At almost the same time, both the MySQL 8.0 replicas stopped replicating and complained on `HA_ERR_FOUND_DUPP_KEY`
```
Replica SQL for channel '': Worker 2 failed executing transaction '87953f5d-7595-11ed-830d-02f4790d85ab:57805008598' at source log mysql-bin-changelog.676514, end_log_pos 52858646; Could not execute Write_rows event on table ebdb.bike_issues; Duplicate entry '177235118' for key 'bike_issues.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's source log mysql-bin-changelog.676514, end_log_pos 52858646, Error_code: MY-001062
```
Weirdly though the replicas complained on different key values, but the timestamp was nearly the same (few ms apart). The MySQL 5.7 replicas were fine, so probably there was no hiccups on the primary side. Nothing shows up in the logs of primary around this time either.
The table, it complained on, is very commonly written table and we had this MySQL 8 replicas running for over a week now, without any replication issues. We do Row-based, GTID based replication (gtid_mode ON, enforce_gtid_consistency ON)
I was able to resume replication by setting `slave_exec_mode` to `IDEMPOTENT` temporarily. When I check the error logs after the replication was in sync, I didn't see errors for the first replica's key in the second replica's error logs & vice versa i.e. they both failed on different keys and did not overlap. Could this be some issue on the replication receiver part? Or possibly some bug due to version mismatch? Or some MySQL variable mismatch?
How can I debug this further? What could have possibly caused this blip?
Subject
Views
Written By
Posted
Difficult to debug replication failure between MySQL 5.7 primary and MySQL 8 replica
572
March 15, 2024 09:33PM
Sorry, only registered users may post in this forum.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.