MySQL Replication Error 1062 and 1032: How to Fix a Broken Replica Safely

MySQL replication error 1062 is a classic reason for a replica to stop: the replica tries to insert a row and finds that a row with the same key is already there. Its twin, error 1032, is the opposite: the source updated or deleted a row that the replica doesn't have. Both look like small, local problems, and both can be "fixed" in seconds by skipping the event. But both mean the same uncomfortable thing: the replica's data had already drifted away from the source before the error appeared.

Step 1: confirm what stopped

Start with SHOW REPLICA STATUS:

SHOW REPLICA STATUS\G
-- look at: Replica_IO_Running, Replica_SQL_Running,
--          Last_SQL_Errno, Last_SQL_Error, Last_SQL_Error_Timestamp

If Replica_IO_Running is Yes and Replica_SQL_Running is No, the replica is still receiving the source's changes but has stopped applying them. That is the situation this article is about. If both threads are running and the replica is simply behind, it is lag rather than breakage; see our guide to MySQL replication lag.

On a multi-threaded replica the useful details live in the replication_applier_status_by_worker table in Performance Schema:

SELECT CHANNEL_NAME, WORKER_ID,
       LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE, LAST_ERROR_TIMESTAMP,
       APPLYING_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
WHERE LAST_ERROR_NUMBER <> 0;

APPLYING_TRANSACTION gives you the GTID of the transaction the failing worker was applying. Write it down; you will need it.

What 1062 and 1032 actually mean

In the server error reference, 1062 is ER_DUP_ENTRY ("Duplicate entry ... for key ...") and 1032 is ER_KEY_NOT_FOUND ("Can't find record in ..."). On a replica, the source has already committed the transaction successfully. So the transaction itself is fine; the replica's copy of the data is not. Typical ways to get there:

• Someone wrote directly to the replica. A script, an admin fixing data "quickly", or an application pointed at the wrong host.
• The replica was built from a backup that didn't match its starting position, so some transactions were applied twice or not at all.
• Earlier skips. Every event skipped in the past leaves a small difference that can resurface weeks later as a new 1062 or 1032 on a related row.

Step 2: look at the transaction before deciding

Find the transaction by its GTID with SHOW RELAYLOG EVENTS on the replica, SHOW BINLOG EVENTS on the source, or mysqlbinlog around the error time. Then compare the affected row on the source and on the replica. The MySQL manual suggests asking three questions before skipping anything:

• Does the transaction come from an unknown or untrusted source? If so, investigate before restarting the replica.
• Does the transaction need to be applied on the replica? If yes, correct the data and reapply it, or reconcile the data manually.
• Should the transaction have happened on the source at all? If not, undo it on the server where it originally took place.

Step 3: choose the fix

Option A: correct the replica and let the transaction apply. If the replica has a stray row that the source never had (typical for 1062 after a direct write), and the source's version is the correct one, removing or fixing that row on the replica and running START REPLICA lets the original transaction apply cleanly. This is the cleanest outcome, because nothing is skipped. Be careful to make that correction without it being written to the replica's own binary log as a new transaction if this replica may become a source later; plan it with whoever owns the topology.

Option B: skip the transaction with GTIDs. With gtid_mode=ON, you skip a transaction by committing an empty transaction with its GTID. The manual says to stop both replication threads first:

STOP REPLICA;
SET GTID_NEXT = '3e11fa47-71ca-11e1-9e33-c80aa9429562:23';  -- the failing GTID
BEGIN;
COMMIT;
SET GTID_NEXT = 'AUTOMATIC';
START REPLICA;

The replica now considers that GTID executed and ignores the real transaction when it arrives. If binary logging is enabled on the replica, that empty transaction stays in its binary log and enters the replication stream if the replica later becomes a source; the manual describes using FLUSH LOGS and PURGE BINARY LOGS to avoid that. The method doesn't apply when ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS is enabled on the channel.

Option C: skip without GTIDs. On position-based replication, sql_replica_skip_counter tells the next START REPLICA to skip events:

STOP REPLICA;
SET GLOBAL sql_replica_skip_counter = 1;
START REPLICA;

It counts events, not transactions, and continues to the end of the event group so it doesn't stop in the middle of one. It cannot be used when gtid_mode=ON, and the next START REPLICA resets it to 0.

Option D: rebuild the replica. If errors keep coming back, or a consistency check shows widespread differences, stop patching. Rebuild the replica from a fresh, consistent backup of the source. That is also a good moment to confirm your backups actually restore; our guide to backup and restore testing covers how.

Two settings to leave alone

• replica_skip_errors makes the replica ignore listed error codes automatically. It is a startup option, so it is tempting to set 1062 once and forget about it. The manual is unusually blunt: indiscriminate use "results in replicas becoming hopelessly out of synchrony with the source, with you having no idea why this has occurred", and with the value all "there are no guarantees regarding the integrity of your data".
• replica_exec_mode = IDEMPOTENT suppresses duplicate-key and key-not-found errors. The default is STRICT, and the manual says IDEMPOTENT is meant for specific NDB Cluster multi-source and circular replication scenarios and "is not recommended for use in other cases".

After the fix: check consistency and prevent the next one

• Check the data. pt-table-checksum from Percona Toolkit performs an online consistency check between source and replicas, and pt-table-sync can repair differences it finds. Read the documentation first: the checksum relies on statement-based replication for its own queries and has limitations you should know before running it in production.
• Make replicas refuse client writes. With super_read_only=ON the server rejects client updates even from users with CONNECTION_ADMIN or SUPER, while updates from replication threads are still applied. It also forces read_only on. This closes the most common path to a 1062.
• Alert on Replica_SQL_Running and on Last_SQL_Errno, not only on lag. When the applier thread is not running, the manual says Seconds_Behind_Source is NULL, and a dashboard that only graphs lag, or treats NULL as zero, can miss a stopped replica for hours.
• Keep a record of every skip: GTID, table, row and reason. The next error on the same table usually makes sense only with that history.