This error occurs when you try to create a table, index, or constraint with a name that already exists in the table. Common causes include running migrations twice or conflicting auto-generated index names. Fix it by checking existing indexes and either dropping the duplicate or using IF NOT EXISTS syntax in MySQL 8.0+.
ERROR 1061 (ER_DUP_KEYNAME) occurs when MySQL encounters a CREATE TABLE, ALTER TABLE, or CREATE INDEX statement that attempts to create an index or constraint with a name that already exists in the table. MySQL requires that each index and constraint name be unique within a table. This typically happens during database migrations, schema updates, or when multiple development branches introduce conflicting index definitions. When this error occurs, MySQL rolls back the entire statement, leaving the table structure unchanged until you resolve the duplicate.
The error message includes the name of the duplicate key. Look for the key name in parentheses in the error output:
ERROR 1061 (42000): Duplicate key name 'index_name'Note the exact name of the problematic index or key.
Connect to your MySQL database and run the SHOW INDEX command to list all existing indexes:
SHOW INDEX FROM table_name;Replace table_name with the actual table name from your error. This will display all current indexes, their columns, and their names. Look for the duplicate key name in the Key_name column.
If you identify that the index is redundant or was created by mistake, drop it using ALTER TABLE:
ALTER TABLE table_name DROP INDEX index_name;Replace table_name with your table name and index_name with the name of the duplicate index. This removes the conflicting index so your migration can proceed.
To prevent this error in the future, use the IF NOT EXISTS clause in your CREATE INDEX statements:
CREATE INDEX IF NOT EXISTS index_name ON table_name (column_name);This syntax tells MySQL to only create the index if it does not already exist, making your migrations safe to run multiple times. This is the recommended approach for modern MySQL versions.
After resolving the duplicate index, retry the operation that originally failed:
-- Your original CREATE TABLE or ALTER TABLE statement
ALTER TABLE table_name ADD INDEX index_name (column_name);If you modified the index name in your script, ensure the new name is unique. Test the migration in a development environment before applying it to production.
In MySQL 8.0 and later, the IF NOT EXISTS and IF NOT ERROR clauses make migrations idempotent, allowing you to safely rerun them without encountering duplicate key errors. On case-insensitive file systems or with case-insensitive MySQL configurations, index names that differ only in case may still be treated as duplicates. Always use lowercase or consistent casing for index names. Some storage engines like Aria may have stricter index naming rules. If this error occurs during foreign key creation, ensure that automatic indexes created by the foreign key constraint do not conflict with manually defined indexes on the same columns. When using ORM frameworks, check their migration generation logic to ensure they use IF NOT EXISTS syntax or implement conflict detection.
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