MySQL - error messages - Error: 1089 - In the used key part isn't a string, the used length is longer than the key part, or the ...
Error: 1089 - In the used key part isn't a string, the used length is longer than the key part, or the ...
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 - In
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): In 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: In 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): In 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 ): 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.}