MySQL Duplicate Entry 1062: Conflicts During SQL Import

During an SQL import, ERROR 1062: Duplicate entry means a PRIMARY or UNIQUE key would be violated. It does not necessarily mean the same row appears twice in the file. A previous partial import, an existing account with the same email, or changed comparison rules can produce the same error.

Read the key name in the message first. A numeric value may point to the primary key, while an email address or slug suggests another unique index. Removing the index only hides the conflict; it does not establish that the resulting data is valid.

Identify the conflicting key and record

Replace users with your table name. Inspect composite indexes too: looking at only one component can lead to the wrong diagnosis.

SHOW CREATE TABLE users;
SHOW INDEX FROM users;
SELECT id, email FROM users WHERE id = 15;
SELECT id, email FROM users WHERE email = 'sample@example.test';

Compare the stored row with the incoming candidate. The same ID with different content is not necessarily a row that was already imported successfully. The same email under another ID requires an explicit account-merging decision and a plan for dependent records.

A small reproducible conflict

Run this only in a separate test session. The email field uses a case-insensitive collation. The final INSERT produces 1062 even though its ID differs:

CREATE TEMPORARY TABLE import_example (
  id INT PRIMARY KEY,
  email VARCHAR(190) CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci NOT NULL,
  UNIQUE KEY uq_email (email)
) ENGINE=InnoDB;
INSERT INTO import_example VALUES (1, 'ali@example.test');
INSERT INTO import_example VALUES (2, 'ALI@example.test');

This demonstrates an equality rule, not database corruption. When source and target collations differ, uniqueness on the source may not transfer to the target. Include collation compatibility checks in the migration.

Was the earlier attempt only partially completed?

Some INSERT statements may have committed before an import timed out. Rerunning the same file then collides with those records. DDL in a dump also makes it unsafe to assume a surrounding transaction rolled everything back.

If the target exists solely for testing this restore, start with a new empty test area. If it is production, do not empty its tables. Preserve a backup and inspect import logs and stored records. The large SQL import workflow helps distinguish the original interruption from this later conflict.

Use staging for an actual merge

Merging two populated databases differs from restoring into an empty database. A separate staging table lets you inspect candidates without changing live tables. Retain a source row number or import identifier; do not make a potentially duplicated source ID the staging table's unique key.

The following assumes import_candidates contains source_id and email, with email using the target's comparison rules. The first query finds repeats among candidates, and the second finds target conflicts:

SELECT email, COUNT(*) AS total
FROM import_candidates
GROUP BY email
HAVING COUNT(*) > 1;

SELECT a.source_id, a.email, u.id AS target_id
FROM import_candidates AS a
JOIN users AS u
  ON u.id = a.source_id OR u.email = a.email;

A candidate matching several target rows deserves special attention: its ID may identify one account while its email identifies another. Review it before generating UPDATE statements. If IDs are remapped, use the same mapping for orders, comments, and permissions.

Three shortcuts that can hide lost data

• INSERT IGNORE can downgrade some errors to warnings and skip or adjust data. Inspect warnings and affected-row counts.
• REPLACE is not simply UPDATE. Its delete-and-insert semantics can affect relationships and triggers.
• ON DUPLICATE KEY UPDATE is useful with a defined merge policy, not as an automatic decision about which person owns a record.

Raising AUTO_INCREMENT does not fix explicit conflicting IDs or duplicate emails. WHERE NOT EXISTS can help a controlled import, but it is not sufficient to prevent races between concurrent writers; retain database uniqueness constraints.

Account for every candidate

Track whether each record was inserted, verified as identical, updated, or held for review. Those categories should add up to the input count. Do not silently discard skipped rows. Check dependent tables for orphan records, then apply the restore verification checks.

References: MySQL documentation for INSERT, REPLACE, and implicit commits.