MySQL 1201: Duplicate key value.
When encountering the error "1201: Duplicate key value" from a MySQL database, follow these immediate actions:
- Identify the Query Causing the Error: Review the application logs to find the exact query that caused the error. This helps in understanding which table and which key are involved.
- Check for Duplicate Values:
- Use a SELECT query to check for existing values in the table that might conflict with the one you're trying to insert. For example, if you were trying to insert a value into a table named `users` with a unique key on the `email` column, you would run:SELECT * FROM users WHERE email = 'theemailyouwantto_insert';
- Analyze the Table Structure:
- To understand which columns are set as unique keys or part of a unique index, run the following:SHOW INDEXES FROM tablename WHERE Nonunique = 0;
Replace `table_name` with the name of the table you're working with. This will list all the unique indexes and keys, helping you identify which constraint is causing the issue.
- Check for Application Logic Errors:
- Ensure your application logic correctly checks for existing values before attempting to insert or update records in a way that might violate unique constraints.
- Manual Duplicate Removal (If Applicable):
- If duplicates have been accidentally introduced or if you're trying to insert a record that already exists, manually remove or update the duplicate entry. For removal:DELETE FROM table_name WHERE condition = 'value' LIMIT 1;
Replace `table_name`, `condition`, and `value` appropriately. Use this cautiously, as it will delete data.
- Use INSERT IGNORE or ON DUPLICATE KEY UPDATE:
- If your operation is an insert and it's acceptable to skip the insert if it would cause a duplicate key error, you can use:INSERT IGNORE INTO table_name (column1, column2) VALUES (value1, value2);
- If you want to update the record if it already exists instead of inserting a new one, use:INSERT INTO table_name (column1, column2) VALUES (value1, value2) ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2;
- Investigate for Potential Bugs or Unintended Writes:
- Review recent changes to the application that might have introduced a bug or changed the way data is written to the database, leading to duplicate keys.
- Monitor Database Metrics:
- Although not an immediate action to solve the problem, start monitoring insert/update rates, error rates, and other relevant metrics if not already doing so. This can help in early detection of similar issues in the future.
Master
MySQL
debugging in Minutes
— Grab the Ultimate Cheatsheet
(Perfect for DevOps & SREs)
MySQL
Cheatsheet
(Perfect for DevOps & SREs)
MORE ISSUES
Backed by
