Warning
Error Code:
1831
MariaDB Error 1831: Duplicate Index Definition
Description
Error 1831 indicates that an attempt was made to define an index that already exists on the specified table. While currently a warning, this redundant index definition is deprecated and will become a critical error in future MariaDB releases, preventing schema changes.
Error Message
Duplicate index '%s' defined on the table '%s.%s'. This is deprecated and will be disallowed in a future release.
Known Causes
3 known causesRedundant CREATE INDEX Statement
An explicit `CREATE INDEX` statement was executed for an index that already exists on the table, leading to a duplicate definition.
Duplicate Index in CREATE TABLE
The `CREATE TABLE` statement or an `ALTER TABLE` operation includes multiple identical definitions for the same index within the table's schema.
Schema Migration Script Error
A database migration script or tool applied a schema change that inadvertently attempted to create an index that was already present.
Solutions
3 solutions available1. Identify and Drop the Duplicate Index easy
Find and remove the redundant index definition that is causing the error.
1
Connect to your MariaDB instance using a client like `mysql` or `mariadb`.
2
Execute a query to list all indexes for the affected table. Replace `your_database_name` and `your_table_name` with your actual database and table names.
SHOW INDEX FROM your_database_name.your_table_name;3
Analyze the output to identify the index name that is appearing more than once for the same set of columns. The error message usually provides the name of the duplicate index.
4
Once identified, drop the duplicate index. Replace `your_database_name`, `your_table_name`, and `duplicate_index_name` with the correct values.
DROP INDEX duplicate_index_name ON your_database_name.your_table_name;2. Review and Refactor Table Creation Script medium
Correct the table definition in your SQL script to avoid defining the same index twice.
1
Locate the SQL script used to create or alter the table that is producing the error.
2
Carefully examine the `CREATE TABLE` or `ALTER TABLE` statements, specifically looking for `INDEX` or `KEY` definitions.
3
Identify any instances where the same index (same name and same set of columns) is defined multiple times. This can happen with explicit `INDEX` definitions and implicit `UNIQUE KEY` or `PRIMARY KEY` definitions that happen to use the same name and columns.
4
Remove the redundant `INDEX` or `KEY` definition. Ensure that only one definition for each unique index exists.
5
Re-run the corrected SQL script against your MariaDB database.
3. Use `ALTER TABLE ... ADD UNIQUE INDEX` Carefully medium
Ensure you don't accidentally create a duplicate unique index when one already exists.
1
If you are using `ALTER TABLE ... ADD UNIQUE INDEX` and encountering this error, it means a unique index with the same name or on the same columns already exists.
2
Before attempting to add a unique index, check if one already exists for the specified columns.
SHOW INDEX FROM your_database_name.your_table_name WHERE Column_name IN ('column1', 'column2') AND Non_unique = 0;3
If the query returns results, a unique index already exists. You can either drop the existing one (if it's truly a duplicate and not needed) or choose a different name for your new unique index.
4
To drop an existing unique index, use `DROP INDEX index_name ON table_name;`.
DROP INDEX existing_unique_index_name ON your_database_name.your_table_name;5
If you wish to keep the existing index and add a new one with a different name, specify the new name in your `ADD UNIQUE INDEX` statement.
ALTER TABLE your_database_name.your_table_name ADD UNIQUE INDEX new_unique_index_name (column1, column2);