Common causes
- Importing a dump into a table that already contains the same rows
- The id column lost its AUTO_INCREMENT attribute after a migration, so every insert uses 0 (common in WordPress: Duplicate entry '0')
- The AUTO_INCREMENT counter is behind the highest existing id after a partial import or manual insert
- Users submitting the same unique value twice, such as an email or username, or a double form submit
- Empty strings counting as a value in a UNIQUE column, while NULLs do not
- Two concurrent requests both checking 'does it exist?' and then both inserting
How to fix it
- Find the existing row. Read the value and key name from the message and query it: SELECT * FROM users WHERE email = '[email protected]'; MySQL 8.0.19+ prefixes the key with the table name, such as users.PRIMARY.
- Check AUTO_INCREMENT on the id. Run SHOW CREATE TABLE wp_posts; and confirm the id column says AUTO_INCREMENT. If it is missing, restore it with ALTER TABLE ... MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; after removing any row with id 0.
- Move the counter past the highest id. Check SELECT MAX(id) FROM t; and set ALTER TABLE t AUTO_INCREMENT = <max+1>; so new rows get unused ids.
- Use an upsert when the row may exist. INSERT ... ON DUPLICATE KEY UPDATE updates the existing row instead of failing. This also removes the race between a SELECT check and an INSERT.
- Import into an empty table or skip duplicates. Drop or truncate the target table before importing, or change INSERT INTO to INSERT IGNORE INTO in the dump. INSERT IGNORE also hides other errors, so review the warnings afterwards.
- Validate input in the app. Catch SQLSTATE 23000 and show a friendly 'already registered' message, and disable the submit button after the first click.
SQL
-- update instead of failing when the key exists
INSERT INTO users (email, name) VALUES ('[email protected]', 'Ana')
ON DUPLICATE KEY UPDATE name = VALUES(name);
-- realign the counter
SELECT MAX(id) FROM users;
ALTER TABLE users AUTO_INCREMENT = 1001;How to stop it happening again
- Keep AUTO_INCREMENT on primary keys and check it after migrations or engine conversions
- Use upserts or unique-index error handling instead of select-then-insert checks
- Import dumps into empty databases or use tools that drop tables first (mysqldump --add-drop-table)
- Store NULL rather than empty strings in optional unique columns