MySQL error: key specification without a key length

I have a table with a primary key that is a varchar(255). Some cases have arisen where 255 characters isn't enough. I tried changing the field to a text, but I get the following error:

BLOB/TEXT column 'message_id' used in key s开发者_运维百科pecification without a key length

how can I fix this?

edit: I should also point out this table has a composite primary key with multiple columns.

The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happens when there is a field/column type of TEXT or BLOB or those belong to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make a primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using BLOB or TEXT types as an index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support a key length limit on TEXT or BLOB. TEXT(88) simply won’t work.

The error will also pop up when you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.

The solution to the problem is to remove the TEXT or BLOB column from the index or unique constraint or set another field as primary key. If you can't do that, and wanting to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it. By default, VARCHAR is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e VARCHAR(200) will limit it to 200 characters long only.

Sometimes, even though you don’t use TEXT or BLOB related type in your table, the Error 1170 may also appear. It happens in a situation such as when you specify VARCHAR column as primary key, but wrongly set its length or characters size. VARCHAR can only accepts up to 256 characters, so anything such as VARCHAR(512) will force MySQL to auto-convert the VARCHAR(512) to a SMALLTEXT datatype, which subsequently fails with error 1170 on key length if the column is used as primary key or unique or non-unique index. To solve this problem, specify a figure less than 256 as the size for VARCHAR field.

Reference: MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length

You should define which leading portion of a TEXT column you want to index.

InnoDB has a limitation of 768 bytes per index key and you won't be able to create an index longer than that.

This will work fine:

CREATE TABLE t_length (
      mydata TEXT NOT NULL,
      KEY ix_length_mydata (mydata(255)))
    ENGINE=InnoDB;

Note that the maximum value of the key size depends on the column charset. It's 767 characters for a single-byte charset like LATIN1 and only 255 characters for UTF8 (MySQL only uses BMP which requires at most 3 bytes per character)

If you need your whole column to be the PRIMARY KEY, calculate SHA1 or MD5 hash and use it as a PRIMARY KEY.

You can specify the key length in the alter table request, something like:

alter table authors ADD UNIQUE(name_first(20), name_second(20));

MySQL disallows indexing a full value of BLOB, TEXT and long VARCHAR columns because data they contain can be huge, and implicitly DB index will be big, meaning no benefit from index.

MySQL requires that you define first N characters to be indexed, and the trick is to choose a number N that’s long enough to give good selectivity, but short enough to save space. The prefix should be long enough to make the index nearly as useful as it would be if you’d indexed the whole column.

Before we go further let us define some important terms. Index selectivity is ratio of the total distinct indexed values and total number of rows. Here is one example for test table:

+-----+-----------+
| id  | value     |
+-----+-----------+
| 1   | abc       |
| 2   | abd       |
| 3   | adg       |
+-----+-----------+

If we index only the first character (N=1), then index table will look like the following table:

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| a             | 1,2,3     |
+---------------+-----------+

In this case, index selectivity is equal to IS=1/3 = 0.33.

Let us now see what will happen if we increase number of indexed characters to two (N=2).

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| ab             | 1,2      |
| ad             | 3        |
+---------------+-----------+

In this scenario IS=2/3=0.66 which means we increased index selectivity, but we have also increased the size of index. Trick is to find the minimal number N which will result to maximal index selectivity.

There are two approaches you can do calculations for your database table. I will make demonstration on the this database dump.

Let's say we want to add column last_name in table employees to the index, and we want to define the smallest number N which will produce the best index selectivity.

First let us identify the most frequent last names:

select count(*) as cnt, last_name 
from employees 
group by employees.last_name 
order by cnt

+-----+-------------+
| cnt | last_name   |
+-----+-------------+
| 226 | Baba        |
| 223 | Coorg       |
| 223 | Gelosh      |
| 222 | Farris      |
| 222 | Sudbeck     |
| 221 | Adachi      |
| 220 | Osgood      |
| 218 | Neiman      |
| 218 | Mandell     |
| 218 | Masada      |
| 217 | Boudaillier |
| 217 | Wendorf     |
| 216 | Pettis      |
| 216 | Solares     |
| 216 | Mahnke      |
+-----+-------------+
15 rows in set (0.64 sec)

As you can see, the last name Baba is the most frequent one. Now we are going to find the most frequently occurring last_name prefixes, beginning with five-letter prefixes.

+-----+--------+
| cnt | prefix |
+-----+--------+
| 794 | Schaa  |
| 758 | Mande  |
| 711 | Schwa  |
| 562 | Angel  |
| 561 | Gecse  |
| 555 | Delgr  |
| 550 | Berna  |
| 547 | Peter  |
| 543 | Cappe  |
| 539 | Stran  |
| 534 | Canna  |
| 485 | Georg  |
| 417 | Neima  |
| 398 | Petti  |
| 398 | Duclo  |
+-----+--------+
15 rows in set (0.55 sec)

There are much more occurrences of every prefix, which means we have to increase number N until the values are almost the same as in the previous example.

Here are results for N=9

select count(*) as cnt, left(last_name,9) as prefix 
from employees 
group by prefix 
order by cnt desc 
limit 0,15;

+-----+-----------+
| cnt | prefix    |
+-----+-----------+
| 336 | Schwartzb |
| 226 | Baba      |
| 223 | Coorg     |
| 223 | Gelosh    |
| 222 | Sudbeck   |
| 222 | Farris    |
| 221 | Adachi    |
| 220 | Osgood    |
| 218 | Mandell   |
| 218 | Neiman    |
| 218 | Masada    |
| 217 | Wendorf   |
| 217 | Boudailli |
| 216 | Cummings  |
| 216 | Pettis    |
+-----+-----------+

Here are results for N=10.

+-----+------------+
| cnt | prefix     |
+-----+------------+
| 226 | Baba       |
| 223 | Coorg      |
| 223 | Gelosh     |
| 222 | Sudbeck    |
| 222 | Farris     |
| 221 | Adachi     |
| 220 | Osgood     |
| 218 | Mandell    |
| 218 | Neiman     |
| 218 | Masada     |
| 217 | Wendorf    |
| 217 | Boudaillie |
| 216 | Cummings   |
| 216 | Pettis     |
| 216 | Solares    |
+-----+------------+
15 rows in set (0.56 sec)

This are very good results. This means that we can make index on column last_name with indexing only first 10 characters. In table definition column last_name is defined as VARCHAR(16), and this means we have saved 6 bytes (or more if there are UTF8 characters in the last name) per entry. In this table there are 1637 distinct values multiplied by 6 bytes is about 9KB, and imagine how this number would grow if our table contains million of rows.

You can read other ways of calculating number of N in my post Prefixed indexes in MySQL.

I got this error when adding an index to a table with text type columns. You need to declare the size amount you want to use for each text type.

Put the size amount within the parenthesis ( )

If too many bytes are used you can declare a size in the brackets for varchar to decrease the amount used for indexing. This is even if you declared a size for a type already like varchar(1000). You don't need to create a new table like others have said.

Adding index

alter table test add index index_name(col1(255),col2(255));

Adding unique index

alter table test add unique index_name(col1(255),col2(255));

alter table authors ADD UNIQUE(name_first(767), name_second(767));

NOTE : 767 is the number of characters limit upto which MySQL will index columns while dealing with blob/text indexes

Ref : http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Another excellent way of dealing with this is to create your TEXT field without the unique constraint and add a sibling VARCHAR field that is unique and contains a digest (MD5, SHA1, etc.) of the TEXT field. Calculate and store the digest over the entire TEXT field when you insert or update the TEXT field then you have a uniqueness constraint over the entire TEXT field (rather than some leading portion) that can be searched quickly.

Don't have long values as primary key. That will destroy your performance. See the mysql manual, section 13.6.13 'InnoDB Performance Tuning and Troubleshooting'.

Instead, have a surrogate int key as primary (with auto_increment), and your loong key as a secondary UNIQUE.

I know it's quite late, but removing the Unique Key Constraint solved the problem. I didn't use the TEXT or LONGTEXT column as PK , but I was trying to make it unique. I got the 1170 error, but when I removed UK, the error was removed too.

I don't fully understand why.

Add another varChar(255) column (with default as empty string not null) to hold the overflow when 255 chars are not enough, and change this PK to use both columns. This does not sound like a well designed database schema however, and I would recommend getting a data modeler to look at what you have with a view towards refactoring it for more Normalization.

The solution to the problem is that in your CREATE TABLE statement, you may add the constraint UNIQUE ( problemtextfield(300) ) after the column create definitions to specify a key length of 300 characters for a TEXT field, for example. Then the first 300 characters of the problemtextfield TEXT field would need to be unique, and any differences after that would be disregarded.

Nobody mentioned it so far... with utf8mb4 which is 4-byte and can also store emoticons (we should never more use 3-byte utf8) and we can avoid errors like Incorrect string value: \xF0\x9F\x98\... we should not use typical VARCHAR(255) but rather VARCHAR(191) because in case utf8mb4 and VARCHAR(255) same part of data are stored off-page and you can not create index for column VARCHAR(255) but for VARCHAR(191) you can. It is because the maximum indexed column size is 767 bytes for ROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT.

For newer row formats ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED (which requires newer file format innodb_file_format=Barracuda not older Antelope) maximum indexed column size is 3072. It is available since MySQL >= 5.6.3 when innodb_large_prefix=1 (disabled by default for MySQL <= 5.7.6 and enabled by default for MySQL >= 5.7.7). So in this case we can use VARCHAR(768) for utf8mb4 (or VARCHAR(1024) for old utf8) for indexed column. Option innodb_large_prefix is deprecated since 5.7.7 because its behavior is built-in MySQL 8 (in this version is option removed).

Also, if you want to use index in this field, you should use the MyISAM storage engine and the FULLTEXT index type.

In case your data type is TEXT - you will have to change it to VARCHAR
solution 1: Query

ALTER TABLE table_name MODIFY COLUMN col_name datatype;
ALTER TABLE my_table MODIFY COLUMN my_col VARCHAR(255);

solution 2: GUI (MySQL workbench)
step1 - write in the text box

step2 - edit data type, apply

I used to use this one with the same error you mentioned:

CREATE INDEX idx_col1 ON my_table (col1);

Then I replaced it with this one and solved:

CREATE INDEX idx_col1 ON my_table (col1(255));

You have to change column type to varchar or integer for indexing.

Go to mysql edit table-> change column type to varchar(45).

DROP that table and again run Spring Project. That might help. Sometime you are overriding foreignKey.

Use like this

@Id
@Column(name = "userEmailId", length=100)
private String userEmailId;