MySQL Replication Error: How to Fix 'Duplicate entry' (Slave_SQL_Running: No)

When Sync Stops: Troubleshooting the Duplicate Entry

Nothing kills a quiet afternoon like a production alert. One minute your replica is humming along; the next, it’s stalled. When replication breaks, the slave starts falling behind immediately. A lag of just 30 minutes can render your reporting dashboards useless or, in high-traffic environments, create a 12-hour recovery backlog that is a nightmare to clear.

After catching an alert on our monitoring stack, I logged into the replica database to investigate why the SQL thread had crashed.

Identifying the Error

To see exactly what went wrong, run this command on your Slave/Replica server:

SHOW SLAVE STATUS\G

Look specifically for the Slave_SQL_Running and Last_SQL_Error fields. In my case, the output looked like this:

             Slave_IO_Running: Yes
            Slave_SQL_Running: No
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table my_database.users; Duplicate entry '123' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000123, end_log_pos 4567

The error is blunt: Duplicate entry '123' for key 'PRIMARY'. The replication process stopped because it tried to write data that was already there.

Why This Happens

This error triggers because the Master is trying to insert a row with a Primary Key (ID 123) that already exists on the Slave. This usually points to one of three issues:

Out-of-band writes: Someone manually inserted data directly into the Slave instead of the Master.
Partial Failures: A previous crash caused data to be written to the disk but not recorded in the replication logs.
Application Logic: A misconfigured service is hitting the Slave IP for write operations.

How to Fix the Error

Your choice of fix depends on how much you value data consistency versus uptime.

Method 1: The Quick Skip (Non-GTID)

If you are certain the data on the slave is already correct, you can tell MySQL to ignore the failing transaction. Warning: This only works if you are NOT using GTID-based replication.

-- Stop the slave threads
STOP SLAVE;

-- Skip exactly one statement
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

-- Restart the slave threads
START SLAVE;

Run SHOW SLAVE STATUS\G again. If Slave_SQL_Running is now Yes, the replica is processing the backlog.

Method 2: The Data Integrity Fix (Delete and Re-sync)

A cleaner approach is to remove the conflicting row from the slave. This allows the replication thread to re-insert the "official" version from the master, ensuring both servers match perfectly.

-- On the SLAVE, inspect the conflicting row
SELECT * FROM my_database.users WHERE id = 123;

-- If the data is redundant, delete it
DELETE FROM my_database.users WHERE id = 123;

-- Resume replication
STOP SLAVE;
START SLAVE;

Method 3: Handling GTID Replication

If your MySQL setup uses Global Transaction Identifiers (GTID), the SQL_SLAVE_SKIP_COUNTER will fail. Instead, you must inject an empty transaction to "fake" the completion of the failing ID. First, find the failing GTID in your SHOW SLAVE STATUS\G output (e.g., 5340062a-1c22-11ed-9662-0242ac120002:101).

STOP SLAVE;
-- Commit an empty transaction for the blocked ID
SET GTID_NEXT = '5340062a-1c22-11ed-9662-0242ac120002:101';
BEGIN; COMMIT;

-- Return to normal operation
SET GTID_NEXT = 'AUTOMATIC';
START SLAVE;

Verifying the Recovery

After applying the fix, monitor the Seconds_Behind_Master value. It might spike initially as the slave processes the queue, but it should begin a steady countdown toward zero. Confirm that Last_SQL_Error is now an empty string.

Lessons Learned & Prevention

Fixing the error is a temporary patch. To prevent it from happening again, lock down your environment:

Set Slaves to Read-Only: Update your my.cnf to include read_only = 1. This stops standard users from making manual changes.
Use Super Read-Only: On MySQL 5.7.8+, use super_read_only = 1. This blocks even users with SUPER privileges from accidentally breaking the sync.
Audit Your App: Ensure your application connection strings clearly distinguish between your Writer (Master) and Reader (Slave) endpoints.