Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL error 1089: Incorrect prefix key

ErrorERROR 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

Short 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.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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\G
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The key is written on a VARCHAR(50) column with a prefix such as (100).The prefix is longer than the columnFix
    3. 3The table uses the NDB engine.The storage engine does not support prefix keysFix

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\G

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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'\G

2Fix

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

Did this fix it?

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:

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.