Why MySQL Replication Shows ‘Duplicate Primary’ Errors – Binlog, Auto‑Increment & Trigger Insights

Phenomenon

When syncing a MySQL 5.0 master with a MySQL 5.7.17 slave, the slave worker threw a duplicate primary error. The error screenshot is shown below.

Analysis

The error appears normal at first glance—primary‑key duplication—but the situation is unusual. The slave was created with mydumper, and the error occurred after only a few rows were synced.

Inspecting the table revealed that a row with pk=13 already existed. Examining the relay log uncovered the offending statement, which turned out to be malformed.

Because the primary key uses auto_increment, the binlog records the explicit key value before the statement. The binlog shows that the statement should have inserted pk=91391, not pk=13.

One workaround is to export the problematic row from the master and import it manually, bypassing the error. A test database was created to verify that this approach works.

Further attempts included:

Altering the table, running mysql_upgrade, and checking the auto_increment value.

Re‑creating the synchronization from scratch.

Using INSERT … SELECT from a test database that already contained the row.

None of these steps resolved the issue.

Thoughts

Initial suspicion focused on the relay log or the table itself. The sql_thread attempted to replay a statement that the binlog recorded with pk=91391, yet the thread treated it as pk=13. This suggested a possible bug in the event handling of the sql_thread.

After confirming that the binlog is in STATEMENT format and that the statement does not explicitly assign all column values, the investigation shifted to the table’s trigger, which performs character‑set conversion.

Although the error message did not reference the trigger, the trigger was the only difference between the test table and the production table. The table’s auto_increment value was 91392, while the maximum existing key was 91390 and the failing insert attempted 91391.

Removing the trigger eliminated the error, indicating that the trigger somehow interfered with the replication process, even though the exact root cause remains unclear.

Conclusion

The duplicate‑primary error during MySQL version‑cross replication was ultimately resolved by dropping the problematic trigger. While the precise mechanism remains uncertain, the case highlights the importance of examining triggers, auto‑increment settings, and binlog formats when troubleshooting replication anomalies.