Error
Error Code:
3907
MySQL Error 3907: Functional Index Data Too Long
Description
MySQL Error 3907, "Data too long for functional index '%s'.", occurs when the evaluated result of a functional index expression exceeds the maximum allowed length for an index. This typically happens during data modification operations (INSERT, UPDATE) or schema changes (ALTER TABLE) where the data provided for a column involved in a functional index results in an index entry that is too long.
Error Message
Data too long for functional index '%s'.
Known Causes
3 known causesEvaluated Expression Exceeds Index Length
The computed output of the functional index expression for the data being processed is longer than the maximum byte length permitted for the index.
Large Column Data Used in Index
The underlying column contains excessively long data, causing the functional index's evaluated result to exceed its length constraints.
Multi-byte Character Set Impact
Using multi-byte character sets (e.g., UTF-8) can increase the byte length of the functional index's evaluated result, leading to an early hit on the index length limit.
Solutions
3 solutions available1. Increase the `innodb_index_max_length` Variable easy
Temporarily or permanently increase the maximum allowed size for InnoDB indexes.
1
Connect to your MySQL server using a client (e.g., `mysql` command-line client, MySQL Workbench).
2
Check the current value of `innodb_index_max_length`. The default is usually 3072 bytes.
SHOW VARIABLES LIKE 'innodb_index_max_length';3
To temporarily increase the limit for the current session, use the `SET GLOBAL` command. Choose a value that accommodates the longest possible output of your functional index expression. For example, to set it to 8192 bytes:
SET GLOBAL innodb_index_max_length = 8192;4
To make this change permanent, edit your MySQL configuration file (e.g., `my.cnf` or `my.ini`). Locate the `[mysqld]` section and add or modify the `innodb_index_max_length` variable. Restart the MySQL server for this change to take effect.
[mysqld]
innodb_index_max_length = 81925
After adjusting the variable, you may need to rebuild or re-create the affected functional index for the changes to be fully applied.
ALTER TABLE your_table_name DROP INDEX your_index_name;
ALTER TABLE your_table_name ADD INDEX your_index_name ((your_functional_expression));2. Optimize the Functional Index Expression medium
Modify the expression used in the functional index to produce shorter results.
1
Identify the exact functional index that is causing the error. The error message will typically include the index name or a hint about the expression.
2
Examine the expression used to create the functional index. For example, if your index is on `(LOWER(column_name))` and `column_name` contains very long strings, the `LOWER()` function might still produce long results.
3
Consider alternative expressions that can achieve a similar indexing goal but with shorter output. For instance:
Instead of: CREATE INDEX idx_lower_col ON your_table ((LOWER(long_text_column)));
Consider: CREATE INDEX idx_first_10_chars ON your_table ((SUBSTRING(long_text_column, 1, 10)));
Or if the data has a predictable structure, use a more specific extraction.4
If the long output is due to a concatenation of multiple columns, assess if all concatenated parts are necessary for the index's purpose.
5
After modifying the expression, drop and re-create the functional index.
ALTER TABLE your_table_name DROP INDEX your_index_name;
ALTER TABLE your_table_name ADD INDEX your_new_index_name ((your_optimized_functional_expression));3. Reduce the Length of Data Being Indexed advanced
Modify the source columns to store shorter data if possible.
1
Analyze the data in the columns used by the functional index. Determine if there's a legitimate reason for the data to be so long.
2
If the long data is not critical or can be truncated without significant loss of information for the purpose of the indexed queries, consider altering the column type or length. For example, if you have a `TEXT` column that is being indexed and most values are under a certain length, you might consider changing it to `VARCHAR` with a more appropriate length.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255);3
If the data needs to be long, but only a prefix is relevant for indexing, consider creating a new column that stores the truncated or relevant part of the data and index that new column. This is often combined with triggers to keep the new column synchronized.
ALTER TABLE your_table_name ADD COLUMN indexed_prefix VARCHAR(255);
-- Then create a trigger to update indexed_prefix when your_column_name changes.4
After modifying the data or schema, you will likely need to re-create the functional index.
ALTER TABLE your_table_name DROP INDEX your_index_name;
ALTER TABLE your_table_name ADD INDEX your_index_name ((your_functional_expression));