Fix MySQL Replica Error 1062 Duplicate Entry

This article provides a step-by-step guide on how to recover a broken MySQL replica experiencing a duplicate key error (Error 1062). You will learn how to identify the conflicting data, safely resolve the inconsistency by either deleting the duplicate row on the replica or skipping the offending transaction, and restart the replication process to restore database health.

Step 1: Identify the Replication Error

First, log into your MySQL replica instance and check the replication status to confirm the duplicate key error.

Run the following command:

SHOW REPLICA STATUS\G

(Note: Use SHOW SLAVE STATUS\G if you are using MySQL version 5.7 or older.)

Look for the following fields in the output: * Last_SQL_Errno: This will show 1062. * Last_SQL_Error: This will display a message similar to: Coordinator stopped because there were error(s) in the worker lanes... Duplicate entry 'X' for key 'PRIMARY'. * Retrieved_Gtid_Set and Executed_Gtid_Set: Note these down if you are using GTID-based replication.

Step 2: Choose a Resolution Strategy

You have two main paths to resolve this error: manually deleting the conflicting row on the replica, or skipping the transaction that causes the failure.

Method A: Delete the Conflicting Row (Recommended)

This is the safest method because it preserves data consistency between the source and the replica. Since the source is trying to insert a row that already exists on the replica, deleting the replica’s local copy of that row allows the replica to write the incoming source data successfully.

Identify the table and the duplicate key value from the Last_SQL_Error output.

Delete the offending row from the replica:

DELETE FROM database_name.table_name WHERE primary_key_column = 'duplicate_value';

Start the replica:
```
START REPLICA;
```

Method B: Skip the Transaction (GTID Enabled)

If you are using Global Transaction Identifiers (GTID), you can skip the specific transaction that contains the duplicate write.

Find the transaction GTID that failed (usually indicated in the Last_SQL_Error or as the next GTID in the sequence).
Stop the replica (if not already stopped):
```
STOP REPLICA;
```

Inject an empty transaction for the failing GTID to skip it:

SET GTID_NEXT='source_uuid:transaction_number';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

Start the replica:
```
START REPLICA;
```

Method C: Skip the Transaction (Non-GTID)

If your database does not use GTID replication, you can skip the SQL statement using the skip counter.

Stop the replica:
```
STOP REPLICA;
```
Skip one statement:
```
SET GLOBAL sql_replica_skip_counter = 1;
```
(Note: Use sql_slave_skip_counter for MySQL 5.7 or older.)
Start the replica:
```
START REPLICA;
```

Step 3: Verify Replication Health

After applying one of the fixes, verify that replication has resumed and is running without errors.

Run the status command again:

SHOW REPLICA STATUS\G

Ensure the following values are set to Yes and that the seconds behind source are decreasing: * Replica_IO_Running: Yes * Replica_SQL_Running: Yes * Seconds_Behind_Source: [Decreasing integer or 0]