MySQL error 1089: Incorrect prefix key
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keysShort answer
Error 1089 means an index prefix length was used where MySQL cannot accept it. Prefix lengths belong only to string columns, and they cannot exceed the column length. Remove the number in brackets from integer or date keys, and keep prefixes on CHAR, VARCHAR and TEXT keys within the column size.
Fix it fast
1Find out which cause you have
Find every key in the failing statement that has a number in brackets after a column name, such as id(11). Then read each column's type.
SHOW CREATE TABLE users\G2Match what you see, then jump to its fix
- 1
The key is written PRIMARY KEY (id(11)) or KEY (created_at(10)) on an INT, DATE or other non-string column.A prefix length is given on a column that is not a stringFix - 2
The key is written on a VARCHAR(50) column with a prefix such as (100).The prefix is longer than the columnFix - 3
The table uses the NDB engine.The storage engine does not support prefix keysFix
- 1
What does the error look like?
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys#1089 - Incorrect prefix key
Cause 1 of 3
A prefix length is given on a column that is not a string
1Check
The error text names this case: the used key part isn't a string. Prefix lengths in CREATE TABLE are interpreted as characters for CHAR, VARCHAR and TEXT, and as bytes for BINARY, VARBINARY and BLOB, so they have no meaning for a number.
SHOW COLUMNS FROM users;2Fix
Delete the bracketed number from the key and index the column as a whole.
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
email VARCHAR(190) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_email (email)
);3Verify
The CREATE TABLE statement runs without error.
SHOW CREATE TABLE users\GConfirmed by dev.mysql.com
Cause 2 of 3
The prefix is longer than the column
1Check
The error text names it: the used length is longer than the key part. For a unique index the manual says an oversized prefix always raises an error, whatever the SQL mode. For a nonunique index, strict mode raises the error, and non-strict mode shortens the prefix with a warning.
SHOW COLUMNS FROM users LIKE 'email';2Fix
Use a prefix no longer than the column, or drop the prefix on a column that fits in the index.
ALTER TABLE users ADD INDEX idx_email (email(50));3Verify
SHOW INDEX lists the index with the sub-part you set.
SHOW INDEX FROM users;Confirmed by dev.mysql.com
Cause 3 of 3
The storage engine does not support prefix keys
1Check
The manual says the NDB storage engine does not support prefixes. The error text also lists unique prefix keys as unsupported on some engines.
SHOW TABLE STATUS LIKE 'users'\G2Fix
Index the full column instead of a prefix, or keep the table on InnoDB, where prefixes up to the row format limit are allowed.
ALTER TABLE users ADD INDEX idx_email (email);3Verify
The index is created.
SHOW INDEX FROM users;Confirmed by dev.mysql.com
How do I stop it coming back?
- Use a prefix number only for long string columns that you really need to index partially.
- Remember that BLOB and TEXT columns need a prefix length in an index; other column types never do.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses to index a TEXT or BLOB column
ERROR 1170 (42000): BLOB/TEXT column 'title' used in key specification without a key length
MySQL cannot index a whole TEXT or BLOB value, so it demands a prefix length for the key.
3 causes, each with a fixDatabases
MySQL refuses to create an index: specified key was too long
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
Error 1071 means one index key is larger than the engine allows.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fix