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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Go to mysql edit table-  change column type to varchar 45

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can specify the key length in the alter table request  something like   alter table authors ADD UNIQUE name first 20   name second 20

User · Answer

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

User · Answer

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  lt   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

User · Answer

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

User · Answer

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

User · Answer

Use like this   Id  Column name    userEmailId   length 100  private String userEmailId

User · Answer

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

[mysql] MySQL error: key specification without a key length

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1170