mysql 1089_MySQL - error messages - Error: 1089 - Incorrect sub part key; the used key part isn&#039...

Discussion

This error message (shown in full below) arises from an ALTER TABLE operation where you try to define a key or index with a prefix improperly or it is not allowed by the storage engine.

Error: 1089 - Incorrect sub part 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 sub keys

SQLSTATE: HY000 (ER_WRONG_SUB_KEY)

The first part of the error message (Incorrect sub part key) is the general message. It's followed by three other reasons why the error might be caused:

The used key part isn’t a string

The used length is longer than the key part

The storage engine doesn’t support unique sub keys

For the first reason above (the used key part isn’t a string), this is suggesting that may have tried to create an index prefix on a column that is not a string. For example, if the column on which an index prefix is based is a non-string (i.e., INT, TIMESTAMP, etc.), you will receive this error:

CREATE TABLE table (a INT);

ALTER TABLE table1 ADD INDEX (col1(5));

ERROR 1089 (HY000): Incorrect sub part 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 sub keys

The second SQL statement is altering the table created in the first statement and is attempting to create an index prefix based on the first 5 bytes of column col1, but column col1 is an INT. This is not possible.

CREATE TABLE table1 (col1 TIMESTAMP);

ALTER TABLE table1 ADD UNIQUE (col1(1));

ERROR HY000: Incorrect sub part 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 sub keys

Similarly in this example, the second SQL statement is attempting to create an index prefix on the first byte of column col1, but column col1 is a TIMESTAMP in this example. This is not possible either.

As for the second component of the error message for error 1089, the used length is longer than the key part, this arises when you try to create an index prefix that is longer than the actual column you are indexing. Below is an example of this:

CREATE TABLE table1 (col1 CHAR(10));

ALTER TABLE table1 ADD INDEX (col1(15));

ERROR 1089 (HY000): Incorrect sub part 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 sub keys

In this example, column col1 is of type CHAR (which is acceptable) and it's 10 characters long. The ALTER TABLE statement, though, attempts to add an index prefix that requires the first 15 characters of col1. Since col1 is only 10 characters wide, the error is generated.

Finally, as for the third component of the error message, the storage engine doesn’t support unique sub keys, this suggests that you were trying to create an index prefix on a table in which the storage engine does not allow this. For example, prior to 4.0.14, there was restriction that prevented InnoDB tables from having indexes with column prefixes.

SELECT VERSION();

+---------------+

| version() |

+---------------+

| 4.0.13-nt-log |

+---------------+

1 row in set (0.00 sec)

CREATE TABLE table1 (col1 CHAR(10)) TYPE=InnoDB;

ALTER TABLE table1 ADD PRIMARY KEY (col1(2));

ERROR 1089 (00000): Incorrect sub part key. The used key

part isn't a string, the used length is longer than the key part

or the table handler doesn't support unique sub keys

This error doesn't occur with InnoDB as of version 4.0.14 of MySQL. Should another storage engine give you this error, then you cannot use index prefixes and you should either create the index on the entire column, or you could always change storage engines should you require the need for column index prefixes.