How to Fix MySQL Error 1022: Can't Write — Duplicate Key in Table

MySQL error 1022 (ER_DUP_KEY) is reported with the message ERROR 1022 (23000): Can't write; duplicate key in table '<table_name>'. It maps to SQLSTATE 23000 (integrity constraint violation) and is raised when an INSERT or UPDATE statement would create a duplicate value in an index that requires uniqueness.

What This Error Means

Error 1022 is one of two duplicate-key errors in MySQL. The better-known sibling is error 1062 (ER_DUP_ENTRY), which is raised for violations of PRIMARY KEY and UNIQUE constraints and produces the more descriptive message Duplicate entry '<value>' for key '<key_name>'. Error 1022 uses a less informative message and historically appeared in storage engines — particularly the older MyISAM engine — when a write was rejected due to a key collision, especially on certain internal or implicit key structures.

In modern InnoDB-only MySQL deployments you are more likely to encounter error 1062 than 1022 for standard UNIQUE and PRIMARY KEY violations. Error 1022 still surfaces in a few specific situations: during ALTER TABLE operations that rebuild an index and find pre-existing duplicate values, when loading data with LOAD DATA INFILE into tables with unique indexes, when using replication and applying row events that produce conflicts, or in some connectors and storage engines that map low-level key errors to this code rather than 1062.

Because SQLSTATE 23000 covers the entire "integrity constraint violation" class, most application drivers (JDBC, Connector/J, PDO, Go's database/sql, etc.) surface both 1022 and 1062 through the same exception type. The distinction between the two codes is usually relevant only when you need to handle a specific failure path or are investigating replication issues.

Common Causes

1. ALTER TABLE adding a UNIQUE or PRIMARY KEY on a column with existing duplicate values. MySQL scans the table and rejects the DDL if any duplicates would violate the new constraint, reporting error 1022.
2. LOAD DATA INFILE or INSERT ... SELECT introducing duplicates. Bulk loads do not always process rows in a deterministic order; two rows may carry the same key value and the second write fails.
3. Multi-row INSERT statements containing duplicate key values within the same statement. For example, INSERT INTO t (id, val) VALUES (1, 'a'), (1, 'b') — both rows share the same id before either is committed.
4. Replication conflicts. On a replica, a row-based replication event may attempt to insert a row whose key already exists (due to out-of-order events, manual data changes on the replica, or diverged state), triggering this error in the replication applier.
5. MyISAM tables with concurrent inserts. Under high-concurrency MyISAM workloads, race conditions in key writes can produce error 1022 rather than 1062.

How to Fix ER_DUP_KEY

1. Identify the duplicate rows before the failing operation. Query the table to find values that would conflict with the index:

   -- Find duplicates on a specific column before adding a UNIQUE key
   SELECT email, COUNT(*) AS cnt
   FROM users
   GROUP BY email
   HAVING cnt > 1;
   

2. Deduplicate data before running an ALTER TABLE. Remove or merge duplicate rows so that the new constraint can be applied cleanly:

   -- Keep the row with the lowest id, delete the rest
   DELETE u1
   FROM users u1
   INNER JOIN users u2
     ON u1.email = u2.email AND u1.id > u2.id;
   
   -- Now the ALTER can succeed
   ALTER TABLE users ADD UNIQUE KEY ux_users_email (email);
   

3. Use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE for intentional upsert patterns. If your application legitimately tries to write rows that may already exist, handle duplicates explicitly:

   -- Silently skip duplicates
   INSERT IGNORE INTO users (id, email) VALUES (1, 'alice@example.com');
   
   -- Or update on conflict
   INSERT INTO users (id, email, updated_at)
   VALUES (1, 'alice@example.com', NOW())
   ON DUPLICATE KEY UPDATE updated_at = VALUES(updated_at);
   

4. Fix replication divergence. If the error appears in a replica's error log (SHOW SLAVE STATUS\G or SHOW REPLICA STATUS\G), the replica's data is out of sync with the source. Options include skipping a single event (not recommended for production unless you understand the data impact) or re-syncing the replica from a fresh snapshot. For routine conflicts on replicas where the idempotent result is acceptable, set slave_exec_mode = IDEMPOTENT (or replica_exec_mode in MySQL 8.0+) to make the applier treat duplicate-key and no-row errors as non-fatal warnings.

   -- On the replica (session or global)
   SET GLOBAL replica_exec_mode = 'IDEMPOTENT';
   

5. Remove or consolidate duplicates introduced by LOAD DATA INFILE. Add a dedup step after the load, or pre-process the source file to eliminate duplicate keys before importing.

Additional Information

• SQLSTATE 23000 (integrity constraint violation) covers both error 1022 and error 1062, as well as error 1048 (column cannot be null) and error 1216/1217 (foreign key constraint failures). Applications that catch by SQLSTATE rather than error number will catch all of these together.
• In MySQL 8.0, SHOW SLAVE STATUS and related slave-side terminology has been replaced by SHOW REPLICA STATUS, replica_exec_mode, etc.
• The REPLACE INTO statement avoids error 1022 by deleting the conflicting row before inserting the new one, but this has a side effect: it always performs a delete + insert, which can break foreign key references and triggers that distinguish between insert and update events.
• MyISAM is deprecated and removed from most production use cases in favor of InnoDB. If you are still using MyISAM tables and seeing error 1022 under concurrency, migrating to InnoDB resolves the race-condition class of duplicates by using proper row-level locking.
• ORMs such as Hibernate/JPA, Eloquent, ActiveRecord, and SQLAlchemy typically translate both 1022 and 1062 into the same application-level exception (e.g., org.hibernate.exception.ConstraintViolationException, ActiveRecord::RecordNotUnique). Check the original SQL state and MySQL error number from the underlying exception cause to distinguish them when needed.

Frequently Asked Questions

What is the difference between MySQL error 1022 and error 1062? Both indicate a duplicate key violation and share SQLSTATE 23000. Error 1062 (ER_DUP_ENTRY) is the more common and descriptive one, raised by InnoDB for PRIMARY KEY and UNIQUE constraint violations with a message that includes the duplicate value and key name. Error 1022 (ER_DUP_KEY) uses a less descriptive message and surfaces more often during bulk operations, ALTER TABLE rebuilds, or in storage engines other than InnoDB.

Why does error 1022 appear during ALTER TABLE? When you add a UNIQUE KEY or PRIMARY KEY to an existing table, MySQL must scan every row to build the new index. If it finds two rows that would collide under the new constraint, it cannot complete the rebuild and raises error 1022 to signal that the table already contains data incompatible with the requested uniqueness requirement.

Can I suppress error 1022 in replication without re-syncing the replica? Setting replica_exec_mode = 'IDEMPOTENT' (MySQL 8.0+) or slave_exec_mode = 'IDEMPOTENT' (older versions) tells the replication applier to treat duplicate-key errors as warnings rather than stopping the replica. This is useful for temporary resynchronization but should not be left on permanently, as it can mask genuine data drift between source and replica.

My application gets error 1022 intermittently under load — what should I check? Intermittent occurrence under concurrent writes usually points to application-level logic that generates duplicate key values (e.g., application-side ID generation that is not guaranteed unique) or to a MyISAM table with concurrency issues. Switching to InnoDB, using AUTO_INCREMENT or a UUID strategy for key generation, and reviewing transaction boundaries on the write path usually resolves it.