ERROR

How to Fix MySQL ERROR 1062: Duplicate Entry

Quick Fix Summary

TL;DR

Identify the duplicate value, then use INSERT IGNORE, ON DUPLICATE KEY UPDATE, or delete the conflicting row before retrying.

MySQL ERROR 1062 (ER_DUP_ENTRY) occurs when an INSERT or UPDATE statement violates a UNIQUE constraint, PRIMARY KEY, or a column with a UNIQUE index. The operation is rolled back to maintain data integrity, which can halt critical workflows.

Diagnosis & Causes

Application logic retrying a failed insert without checking.

Race condition in concurrent application processes.

Manual data import with overlapping primary keys.

Misconfigured replication causing duplicate entries.

Application generating non-unique values for a UNIQUE column.

Recovery Steps

Step 1: Identify the Conflicting Row and Value

First, extract the duplicate value from the error message and find the existing row. This is critical for diagnosis.

bash

# Error message format: 'Duplicate entry 'XYZ' for key 'table_name.PRIMARY'
# Query to find the existing row:
SELECT * FROM your_table_name WHERE your_unique_column = 'duplicate_value_here';

Step 2: Immediate Production Fix - Use INSERT IGNORE

For non-critical duplicates where you can skip the failing row, use INSERT IGNORE to convert the error into a warning and continue.

sql

INSERT IGNORE INTO your_table (id, name, email) VALUES (123, 'John Doe', 'john@example.com');
# Check warnings:
SHOW WARNINGS;

Step 3: Robust Upsert with ON DUPLICATE KEY UPDATE

The preferred method for 'insert or update' logic. This updates the existing row if a duplicate key conflict occurs.

sql

INSERT INTO users (user_id, login_count, last_login)
VALUES (1001, 1, NOW())
ON DUPLICATE KEY UPDATE
login_count = login_count + 1,
last_login = NOW();

Step 4: Safely Delete the Conflicting Row (If Appropriate)

If the duplicate data is truly invalid and can be removed, delete it precisely before re-inserting. Use a transaction for safety.

sql

START TRANSACTION;
DELETE FROM your_table WHERE your_unique_column = 'duplicate_value' LIMIT 1;
-- Now re-run your original INSERT statement here --
COMMIT;

Step 5: Investigate and Fix the Root Cause in Application Logic

Prevent future errors by adding defensive checks in your application code before attempting insertion.

php

// Example Pseudo-Code for Safe Insert
function safeInsert($value, $uniqueKey) {
    if (!recordExists($uniqueKey, $value)) {
        executeInsert($value);
    } else {
        executeUpdate($uniqueKey, $value);
    }
}

Step 6: Handle Replication Conflicts (If Applicable)

If the error appears on a replica server, check for replication drift or misconfigured auto_increment settings.

bash

# On the replica, check for errors:
SHOW SLAVE STATUS\G
# Look for 'Last_SQL_Error' and 'Last_Errno'.
# Common fix is to skip one duplicate error (use with extreme caution):
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

Architect's Pro Tip

"For high-concurrency systems, 'SELECT ... FOR UPDATE' before insert in a transaction is more reliable than application-level 'check-then-insert' logic, which is prone to race conditions."

Frequently Asked Questions

What's the difference between INSERT IGNORE and ON DUPLICATE KEY UPDATE?

INSERT IGNORE silently discards the duplicate row, leaving the old data unchanged. ON DUPLICATE KEY UPDATE modifies the existing row with the new values, making it an 'upsert' operation.

Can ERROR 1062 occur on non-primary key columns?

Yes. It occurs on any column with a UNIQUE constraint or a UNIQUE index, not just the PRIMARY KEY.

Is it safe to use SQL_SLAVE_SKIP_COUNTER on a replica?

Only as a last resort to restart stalled replication. It skips the next N events, which can cause data inconsistency. Always investigate the root cause of the replication conflict first.

Related MySQL Guides

ERROR 1040