MySQL error: key specification without a key length

Question

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

Alodee

A table cannot have multiple primary keys. Do you mean it has a composite primary key (that is including more than one column) or it has multiple UNIQUE keys? – Exclaim

In my case for some reason I had a TEXT type for an email column instead of VARCHAR. – Metallist

Use VARCHAR for unique alphanumeric. – Butz

Answer 1

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

Ernaldus

dev.mysql.com/doc/refman/5.0/en/char.html "Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns)" – Refuel

Where you say "MySQL can index only the first N chars of a BLOB or TEXT column", what is the value of N? – Heterogeneity

"When you index a BLOB or TEXT column, you must specify a prefix length for the index." dev.mysql.com/doc/refman/5.6/en/column-indexes.html – Georgiannageorgianne

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

Good answer. Heres a snippet for lazy python and sql alchemy users: from sqlalchemy.types import Date, VARCHAR df.to_sql(table_name, con=engine, index=index, if_exists='append', index_label=index_label, dtype={'date': Date, 'text': VARCHAR(25)}) – Beadsman

Seems the link at the end is broken – Countersignature

Until my edit is approved here is a fixed link – Zigmund

so final answer bro must include which column accept length bigger than 255 the varchar and accept be unique is it anyone – Vadavaden

Answer 2

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.

Exclaim

Is the size (255 here) really in characters and not in bytes? Because I could imagine that using characters for UTF-8 would be really complicated for no added benefits. – Liable

Sorry, I don't follow your question. From the docs: The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. REDUNDANT and COMPACT were the only formats available at the time this answer was given. – Exclaim

@AlexisWilke Yes, 255 is in characters, which leaves enough room for that old 768 byte limit. https://mcmap.net/q/75115/-mysql-varchar-index-length/… – Kruter

UTF-8 characters can be up to 4 bytes in lengths afaik, so this would actually exceed 768 bytes – Rochellerochemont

Answer 3

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

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

Sedberry

This was exactly what I needed to fix the same problem. Thanks! – Unkindly

You should be very careful with this approach! This is maybe the easiest solution, but in lot situations not the best one. Your key is consisting of two columns and column order is important. – Dragging

Best answer here. – Egghead

That's the answer! – Legitimist

Answer 4

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.

Dragging

This has not been updated enough. I found it to be much easier to understand than the accepted answer – Bullyboy

Answer 5

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));

Blunge

Simplest answer I believe and it worked for me straight away. Thanks. – Dayle

Thank you! Much better than the other answers. – Gene

Answer 6

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

Lethia

Answer 7

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.

Indelicacy

You should also be very careful with completely “random” strings, such as those produced by MD5(), SHA1(), or UUID(). Each new value you generate with them will be distributed in arbitrary ways over a large space, which can slow INSERT and some types of SELECT queries: – Dragging

Distribution of MD5, SHA1 over non-malicious data should be uniform --- that's what hashes are for. – Mroz

it would be great if you could provide some example. – Salamanca

This is the best answer @Salamanca here are some resources regarding the MD5 , SHA1 method Hash-based workarounds MD5 Over Multiple Columns – Bakery

Answer 8

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));

Sampan

Answer 9

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.

Unaesthetic

Answer 10

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.

Rhodic

From the docs: For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. This is because the fields can be very long. Same with sorting on these columns, you need to specify how many chars to consider in the sort. – Freudian

Answer 11

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

Erida

Answer 12

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.

Wrong

Answer 13

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.

Xylophone

Answer 14

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

Cay

Downvoted as other answers (which I used) clearly show that results can be achieved without compromising the original table's data structure (VARCHAR might truncate, this is why TEXT is used). – Catt

Answer 15

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

Bloc

You might consider adding an explanation and link to documentation. – Oscitant

Answer 16

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

Hazard

You might consider adding an explanation and link to documentation. – Oscitant

Answer 17

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

Hazard

Answer 18

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

Luiseluiza

Answer 19

Check if the column is indexed and delete it's index first.

DROP INDEX index_name ON table_name;

Chewink

Answer 20

Use like this

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

Cloth

You might consider adding an explanation and link to documentation. – Oscitant