Error
Error Code:
86
MongoDB Error 86: Index Definition Mismatch
Description
This error occurs when attempting to create an index with specifications that conflict with an existing index on the same collection and key pattern. MongoDB requires that indexes sharing the same key pattern must also have compatible options, such as collation or type, to maintain data integrity and consistency.
Error Message
Index Key Specs Conflict
Known Causes
3 known causesConflicting Collation
An attempt was made to create an index with a different collation (e.g., case-insensitive comparison rules) than an already existing index on the exact same key fields.
Incompatible Index Options
The proposed index definition includes options (e.g., uniqueness, sparseness, partial filter expression) that are incompatible with an existing index on the same key pattern.
Index Rebuild Mismatch
An index rebuild operation, either explicit or implicit, encountered an existing index with specifications that conflict with the new or intended definition for that index.
Solutions
3 solutions available1. Identify and Remove Conflicting Index easy
Find and drop the index that is causing the conflict.
1
Connect to your MongoDB instance using the `mongosh` shell.
2
Switch to the database containing the collection with the error.
use your_database_name
3
List all existing indexes for the problematic collection to identify the one causing the mismatch. The error message usually indicates the field(s) involved.
db.your_collection_name.getIndexes()
4
Carefully examine the output of `getIndexes()` and compare the key specifications with the index you are trying to create or update. Identify the index that has the same fields but with incompatible ordering or options (e.g., one is ascending, the other descending, or one has a unique constraint and the other doesn't).
5
Once the conflicting index is identified, drop it. Replace 'index_name' with the actual name of the conflicting index found in the previous step.
db.your_collection_name.dropIndex('index_name')
6
Attempt to create or update your desired index again.
db.your_collection_name.createIndex({ field1: 1, field2: -1 })
2. Consolidate Index Definitions medium
Merge similar indexes into a single, comprehensive index.
1
Connect to your MongoDB instance using `mongosh`.
2
Switch to the relevant database.
use your_database_name
3
List all indexes for the collection. Analyze the output to find indexes that cover overlapping sets of fields or have similar ordering but are defined separately.
db.your_collection_name.getIndexes()
4
Determine the optimal combination of fields and their ordering that would satisfy the queries currently served by the separate, conflicting indexes. Consider the most frequent query patterns.
5
Drop all the individual indexes that can be consolidated. For example, if you have `{'a': 1}` and `{'a': 1, 'b': -1}`, and you decide `{'a': 1, 'b': -1}` is sufficient, drop `{'a': 1}`.
db.your_collection_name.dropIndex('index_name_to_drop')
6
Create the new, consolidated index. Ensure the field order and direction (1 for ascending, -1 for descending) are correct for your query needs.
db.your_collection_name.createIndex({ consolidated_field1: 1, consolidated_field2: -1 })
3. Adjust Index Options for Compatibility medium
Modify index options like unique constraints to resolve conflicts.
1
Connect to your MongoDB instance using `mongosh`.
2
Switch to the database.
use your_database_name
3
Inspect existing indexes to understand the conflicting options. Pay close attention to `unique` constraints, `sparse` options, and collation settings.
db.your_collection_name.getIndexes()
4
Identify which index has an option that is incompatible with the index you are trying to create. For example, trying to create a unique index on fields that already have a non-unique index with the same field order.
5
If the conflict is due to a `unique` constraint, either drop the existing unique index (if it's no longer needed or can be replaced by a non-unique one) or ensure your new index definition also includes the `unique` constraint if required, and that the data in the collection adheres to it.
db.your_collection_name.dropIndex('conflicting_unique_index_name')
6
If the conflict is due to collation or other options, adjust the options in your `createIndex` command to match or complement the existing index, or drop the conflicting index if it's not necessary.
db.your_collection_name.createIndex({ field: 1 }, { unique: true, collation: { locale: 'en', strength: 1 } })
7
Re-attempt to create or update your index.