Develop

Jul 9, 2007

My Digital Life Editorial Team

17 Comments

Delicious

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

Tweet

When creating a new table or altering an existing table with primary keys, unique constraints and indexes, or when defining a new index with Alter Table manipulation statement in MySQL database, the following error may occur and prohibit the the command from completing:

ERROR 1170 (42000): BLOB/TEXT column ‘field_name’ used in key specification without a key length

The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as 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 index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support 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 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 fail 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.

Related posts:

1. How to Find and Replace Text in MySQL Database using SQL
2. WordPress MySQL SQL Query Error in WPDB Class
3. Change and Reset MySQL root Password
4. Installing Web Server in FreeBSD 6.0 with Apache 2.2, MySQL 5.0 and PHP 5 – Part 3
5. Enable Logging of Slow Queries (Slow Query Log) in MySQL Database

17 Comments

• 
  Alec January 25, 2012 at 8:52 pm

  Great post, explained the problem very well and offered flexible solutions!

  Reply
• 
  Aravind May 18, 2011 at 3:27 am

  Thanks dude…..gr8 help!!!!!!!!!!!!!!

  Reply
• 
  sindhuja April 11, 2011 at 11:51 pm

  Hi i want to make the longblob field as primary key. I created longblob field in the name fpimg longblob. wen i am making it as primary key its sayin as key length not specified.

  But wen i am tryin to alter the lonblob column also its givin error… somebody help me out …

  Reply
• 
  Kyle January 4, 2011 at 10:21 am

  That was extremely helpful. Thank you.

  Reply
• 
  Grover November 26, 2010 at 7:21 am

  thank you very so much, your blog is great!!!

  Reply
• 
  Varun August 26, 2010 at 12:41 am

  really helpfull!

  thanks

  Reply
• 
  fgm2003 June 21, 2010 at 11:32 pm

  thanks very much, helpful to solve my problem.

  Reply
• 
  Nick Yeoman January 27, 2010 at 7:56 am

  Excellent post! this must have saved me hours!

  I'm glad someone actually blogs about problems and why they happen! (opposed to copy and pasted code that they don't understand)

  Reply
• 
  Eli Manobella May 1, 2009 at 6:32 am

  Thank you for the post.

  I need a key that is longer then 256.

  What can I do?

  googling it for the past 3 hours with no luck…

  (Paco Zarabozo comment did not work for me)

  Reply
• Preaching to the UTF8 converted at hontou ni sou omou? December 13, 2008 at 10:20 pm

  [...] I ran into some problems, I back-pedalled to square one. Thankfully my backup restored the Latin1 database without any [...]

  Reply
• 
  Paco Zarabozo October 29, 2008 at 6:16 pm

  Well, here's another solution. You can actually specify the key length for your index while keeping your field as longtext. This is an example:

  create table messages (

  id int(20) not null auto_increment,

  message longtext,

  index (message(996), id)

  );

  desc messages;

  show index from messages;

  996 is the maximium key length mySQL has accepted for me.

  Cheers,

  Paco

  Reply
• 
  David October 28, 2008 at 3:50 am

  Thanks a lot!

  Reply
• 
  Marius Vilaia October 6, 2008 at 11:50 am

  Thank you very much. I had this problem and you were a life saver!

  Reply
• 
  Reza March 7, 2008 at 12:15 pm

  Thanks, very good defined

  Reply
• 
  Jamie Slater February 23, 2008 at 11:40 am

  Thanks for the post. Very helpful.

  Reply
• 
  Ben Hardy February 20, 2008 at 10:45 am

  Thanks! Very useful post.

  Reply
• 
  Felix August 31, 2007 at 2:33 am

  Thanks for putting this up. I didn't get the error and you saved me a lot o frustration. I'd put you in the 'Goog people' category anytime

  Reply