[fix]-MySQL error key specification without a key length

Wikitechy | 5059 Views | mysql | 01 Jun 2016

 

Scenario:

A table has a primary key field with varchar(255) data type. Sometimes a length of 255 characters is not enough. So the field type is updated as text. During update the below error occurs:

BLOB/TEXT column ‘column_id' used in key specification without a key length

Error:

MySQL error: key specification without a key length

Reason for this issue:

1. MySQL will be able to index only the first N chars of a BLOB or TEXT column. 

2. The error occurs when one of the below column types is assigned to a primary key column. 

  • TEXT or BLOB,
  • TINYBLOB, 
  • MEDIUMBLOB, 
  • LONGBLOB, 
  • TINYTEXT, 
  • MEDIUMTEXT, and 
  • LONGTEXT 

3. In BLOB or TEXT type, there is no length specification. MySQL cannot guarantee the uniqueness of the column due to its dynamic size. 

Fix 1:

Use an integer auto_increment surrogate key column as primary key and the text column with UNIQUE constraint.

Fix 2:

1. Create the TEXT field without the unique constraint.

2. Add a sibling VARCHAR field that is unique and contains a MD5 or SHA1 encrypted value of the TEXT field. 

3. Calculate and store the encrypted value and check its TEXT field.

Fix 3:

To use index in TEXT field, utilize the MyISAM storage engine and the choose data type as FULLTEXT for the index column.

Fix 4: 

The solution is to specify the index length.

ALTER TABLE [tale_name] ADD INDEX (content(255));
alter table wikitechy_table ADD UNIQUE(emp_name(767), emp_address(767));

NOTE: 767 is the number of characters’ limit up to which MySQL will index columns while dealing with BLOB/TEXT indexes.

Fixes are applicable to the following versions of MySql:

  • MySQL 3.23
  • MySQL 4.0
  • MySQL 4.1
  • MySQL 5.0
  • MySQL 5.7

Related Error Tags:

  • BLOB/TEXT column ‘value’ used in key specification without a key length
  • MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
  • DatabaseError: (1170, "BLOB/TEXT column 'tree_path' used in key specification without a key length")



Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise