Error
Error Code:
4159
MariaDB Error 4159: Index Key Part Too Long
Description
This error indicates that you are attempting to create an index (e.g., primary key, unique key, or regular index) on a column, or a combination of columns, where one of the key parts exceeds the maximum length supported by the database's storage engine and character set. It commonly occurs when defining indexes on TEXT, BLOB, VARCHAR, or CHAR columns without specifying a prefix length, or when the full column length, when encoded, is too large.
Error Message
Specified key part was too long; max key part length is %u bytes
Known Causes
3 known causesColumn Data Type Length
Creating an index on `VARCHAR`, `TEXT`, or `BLOB` columns where the defined or potential full length of the column exceeds the maximum key part size allowed by the storage engine.
Multibyte Character Encoding
When using multibyte character sets like `utf8mb4`, the actual byte length of a column can be significantly larger than its character count, leading to the key part limit being exceeded.
Storage Engine & Row Format
The maximum key part length varies significantly between different storage engines (e.g., InnoDB) and their row formats (e.g., `COMPACT`, `DYNAMIC`), which can be a factor in hitting this limit.
Solutions
3 solutions available1. Shorten Index Key Part Length easy
Reduce the length of a column used in an index to fit within the maximum key part length.
1
Identify the table and column(s) causing the error. The error message often indicates the table name. You may need to examine your `CREATE TABLE` or `ALTER TABLE` statements.
2
Determine the maximum key part length. This is usually around 3072 bytes for InnoDB, but can vary based on MariaDB version and configuration. The error message itself provides this value.
3
Modify the column definition to reduce its length. For character types (VARCHAR, TEXT), this means decreasing the declared length. For binary types, it means reducing the byte size.
ALTER TABLE your_table_name MODIFY COLUMN your_column_name VARCHAR(255); -- Example: reducing VARCHAR length4
If the column is part of a multi-column index, you might need to adjust the length of one or more of these columns.
ALTER TABLE your_table_name MODIFY COLUMN column1 VARCHAR(100), MODIFY COLUMN column2 VARCHAR(100); -- Adjusting lengths for index columns5
Recreate or alter the index with the adjusted column lengths.
ALTER TABLE your_table_name ADD INDEX index_name (your_column_name(100)); -- Example: specifying a prefix length for the index2. Use Index Prefixes for Large Columns easy
Create an index that only uses a prefix of a large column, avoiding the need to index the entire column.
1
Identify the table and the specific column that is too large for an index key part. This is common with `TEXT` or `VARCHAR` columns with very large lengths.
2
When creating or altering an index, specify a prefix length for the problematic column. This tells MariaDB to only index the first N characters (or bytes) of the column's data.
ALTER TABLE your_table_name ADD INDEX index_name (large_text_column(255)); -- Indexing the first 255 characters3
If the index already exists, you can drop and recreate it with the prefix.
DROP INDEX index_name ON your_table_name;
ALTER TABLE your_table_name ADD INDEX index_name (large_text_column(255));4
Consider the trade-off: using prefixes can reduce index size and improve performance for queries filtering on the beginning of the column, but might not be as effective for queries filtering on later parts of the data.
3. Increase Max Key Length Configuration medium
Adjust MariaDB's server configuration to allow for larger index key parts.
1
Locate your MariaDB configuration file. This is typically `my.cnf` or `mariadb.conf.d/*.cnf` on Linux systems.
2
Open the configuration file with a text editor. You might need root privileges.
sudo nano /etc/mysql/my.cnf3
Add or modify the `innodb_large_prefix` and `innodb_page_size` settings within the `[mysqld]` section. `innodb_large_prefix` should be set to `ON` to enable larger key prefixes.
[mysqld]
innodb_large_prefix = ON
innodb_page_size = 16K # Or 8K, 4K, depending on your needs and system capabilities. 16K is common for larger prefixes.4
Save the configuration file.
5
Restart the MariaDB server for the changes to take effect.
sudo systemctl restart mariadb6
After restarting, you may need to `ALTER TABLE ... FORCE` for existing tables to re-enable large prefixes for them.
ALTER TABLE your_table_name FORCE;7
Important: This change requires that all tables in your database use the `DYNAMIC` or `COMPRESSED` row format. If you have older tables using `COMPACT` or `REDUNDANT` row formats, you'll need to convert them.
ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;