Error 1062 occurs when you try to insert or update a record with a value that already exists in a column with a unique constraint (primary key or unique index). This can be fixed by checking for existing records, using INSERT...ON DUPLICATE KEY UPDATE, or properly managing your auto-increment values.
MySQL Error 1062 (SQLSTATE 23000) is an integrity constraint violation that occurs when you attempt to insert or update data with a value that already exists in a field protected by a unique constraint. This constraint can be a PRIMARY KEY, a UNIQUE INDEX, or a UNIQUE KEY. MySQL is designed to enforce data uniqueness for these fields, so any attempt to introduce duplicate values is immediately rejected. The error message includes the duplicate value and the key name, helping you identify which field caused the violation.
The error message shows you the key name: ERROR 1062 (...) for key 'column_name'. Note this value. If it says "PRIMARY", the duplicate is in your primary key column. If it shows a custom index name, that's your unique constraint.
-- Example error output:
-- ERROR 1062 (23000): Duplicate entry "42" for key "users.id"Query the table to see if the value you're trying to insert already exists:
-- For a primary key violation:
SELECT * FROM users WHERE id = 42;
-- For a unique index violation:
SELECT * FROM users WHERE email = '[email protected]';If the query returns a row, the duplicate exists and must be handled before proceeding.
Instead of failing on a duplicate, use the ON DUPLICATE KEY UPDATE clause to update the existing row:
-- Insert new user or update if email already exists:
INSERT INTO users (id, name, email, last_login)
VALUES (42, "John Doe", "[email protected]", NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
last_login = VALUES(last_login);This works for PRIMARY KEY and UNIQUE INDEX violations. The old row is updated instead of rejecting the insert.
If you want to ignore duplicates rather than update them:
-- Silently skip if duplicate:
INSERT IGNORE INTO users (id, name, email)
VALUES (42, "John Doe", "[email protected]");This demotes the error to a warning and doesn't insert or update the row. Useful for bulk imports where some records may already exist.
Never manually set an auto-increment primary key to a specific value unless you know it's unique. Instead, let MySQL generate the value:
-- Bad (may cause duplicate if 42 exists):
INSERT INTO users (id, name) VALUES (42, "John Doe");
-- Good (MySQL auto-generates the ID):
INSERT INTO users (name) VALUES ("John Doe");
-- Or explicitly use NULL:
INSERT INTO users (id, name) VALUES (NULL, "John Doe");If you manually set IDs, ensure you know the next safe value. Use SELECT MAX(id) FROM users; to find the highest ID in use.
If you're using TINYINT for your primary key, it maxes out at 127. When a new auto-increment tries to use 128, MySQL wraps it back to 127, causing a duplicate:
-- Check current data type:
DESC users;
-- Upgrade to INT (safe for ~2 billion records):
ALTER TABLE users MODIFY id INT AUTO_INCREMENT;
-- Or use BIGINT for massive datasets:
ALTER TABLE users MODIFY id BIGINT AUTO_INCREMENT;MySQL's default collation is case-insensitive. This means "John" and "john" are treated as the same value for unique constraints:
-- Check current collation:
SHOW CREATE TABLE users;
-- If case matters, use a binary collation:
ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_bin UNIQUE;Before inserting into production, load data into a temporary staging table and de-duplicate:
-- Create staging table with same structure:
CREATE TEMPORARY TABLE users_staging LIKE users;
-- Load data from file or source:
LOAD DATA INFILE '/data/users.csv' INTO TABLE users_staging ...
-- Remove duplicates within staging table:
DELETE FROM users_staging
WHERE id NOT IN (
SELECT MIN(id) FROM users_staging GROUP BY email
);
-- Now insert safely:
INSERT IGNORE INTO users SELECT * FROM users_staging;For MySQL replication environments, error 1062 can indicate that the same row is being inserted from multiple master servers. This happens when using multi-source replication without proper server IDs or row-level binlogging. Use SHOW SLAVE STATUS\G to check replication lag and SET sql_slave_skip_counter = 1; to skip a single errant statement, then verify replication continues. If dealing with very large tables (billions of rows), consider using PARTITION BY to isolate data and use range-based unique keys instead of pure auto-increment. For application-level handling in Python, catch mysql.connector.errors.IntegrityError exceptions; in Node.js/JavaScript, check for error code 1062 in the error object. Some ORMs (like Sequelize, TypeORM, Prisma) provide built-in support for handling duplicates gracefully with upsert operations.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions