I was working on a startup’s site yesterday and ran into this MySQL error “ERROR 1170 (42000): BLOB/TEXT column ‘XXX’ used in key specification without a key length”.
My scenario is this, I have a table of products including a merchant_id and duplicate_name. My table is defined to use an integer for the merchant_id and a TEXT for the duplicate_name. When I attempted to create an index on the table ALTER TABLE product ADD INDEX ix_product_duplicates (merchant_id, duplicate_name), I received the error message above.
A quick google yielded a helpful summary of the problem.
So, changing my statement to include a key length on the TEXT field (duplicate_name) was the solution to my problem. I selected a length that I thought would be appropriate to narrow the results sufficiently without creating an excessively large index.
ALTER TABLE product ADD INDEX ix_product_duplicates (merchant_id, duplicate_name(250));