ERROR 1062 (23000): Duplicate entry 'X' for key 'PRIMARY' on replica; Replica SQL thread stopped
ID: database/mysql-slave-sql-error
Version Compatibility
| Version | Status | Introduced | Deprecated | Notes |
|---|---|---|---|---|
| 8 | active | — | — | — |
Root Cause
The MySQL replica SQL thread (applier) stopped due to an error applying a binlog event from the primary. The most common causes are: duplicate key errors from writes applied directly to the replica, schema differences between primary and replica, or missing rows that the primary tries to update/delete. This breaks replication and the replica falls behind the primary.
genericWorkarounds
-
85% success Identify the specific error and fix the data inconsistency
1. Check error details: SHOW REPLICA STATUS\G (look at Last_SQL_Error and Last_SQL_Errno). 2. For duplicate key (1062): the row exists on the replica but not the same way on the primary. Delete the conflicting row on the replica, then START REPLICA. 3. For row not found (1032): the row was deleted on the replica but the primary tries to update it. Re-sync the specific table using pt-table-sync from Percona Toolkit. 4. Verify consistency after fix: pt-table-checksum.
-
92% success Rebuild the replica from a fresh backup of the primary
If the replica has diverged significantly: 1. Stop the replica: STOP REPLICA; 2. Take a consistent backup of the primary: mysqldump --single-transaction --source-data=2 -A > backup.sql (or use xtrabackup for large databases). 3. Restore on the replica: mysql < backup.sql. 4. Configure replication from the backup position: CHANGE REPLICATION SOURCE TO ... 5. Start: START REPLICA. This guarantees a consistent replica but causes downtime.
Dead Ends
Common approaches that don't work:
-
Using SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 repeatedly to skip past errors
75% fail
Skipping binlog events causes the replica to diverge from the primary. Skipped writes mean data exists on the primary but not the replica. Accumulated divergence can cause increasingly frequent errors and makes the replica unreliable for reads or failover. Each skip makes the problem worse.
-
Setting slave_skip_errors to auto-skip all duplicate key errors
70% fail
slave_skip_errors=1062 silently skips all duplicate key errors during replication. While this keeps replication running, it hides data inconsistencies. The replica silently diverges from the primary, making it unsafe for failover or read scaling. When promoted to primary, missing or stale data causes application errors.