[fix]-MySQL error key specification without a key length
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.
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:
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")
