MySQL refuses to index a TEXT or BLOB column
ERROR 1170 (42000): BLOB/TEXT column 'title' used in key specification without a key lengthShort answer
MySQL cannot index a whole TEXT or BLOB value, so it demands a prefix length for the key. Write the column as name(255) in the index, or change the column to VARCHAR when the values are short. A prefix length counts characters for text types, and the key has a byte limit.
Fix it fast
1Find out which cause you have
Find which column is TEXT or BLOB and which key mentions it. The error names the column.
SHOW CREATE TABLE posts\G2Match what you see, then jump to its fix
- 1
The error names a TEXT, MEDIUMTEXT, LONGTEXT or BLOB column used in PRIMARY KEY, UNIQUE or INDEXA TEXT or BLOB column is in a key without a prefixFix - 2
The TEXT column is a name, slug or email that is used for lookups and uniquenessThe column holds short values and should be VARCHARFix - 3
A prefix on the column is not enough, for example a lookup on the first part of a long stringYou want an index on an expression of a long columnFix
- 1
What does the error look like?
ERROR 1170 (42000): BLOB/TEXT column 'title' used in key specification without a key length
Cause 1 of 3
A TEXT or BLOB column is in a key without a prefix
1Check
Prefixes are mandatory for BLOB and TEXT key parts. Read the column types in the table definition.
SHOW COLUMNS FROM posts;2Fix
Give the key part a length. For nonbinary types such as TEXT the length is counted in characters, for BLOB types in bytes.
CREATE INDEX idx_title ON posts (title(191));3Verify
The index is listed with a Sub_part value.
SHOW INDEX FROM posts;Confirmed by dev.mysql.com
Cause 2 of 3
The column holds short values and should be VARCHAR
1Check
Find the longest value actually stored. A prefix index on a UNIQUE key only enforces uniqueness for the prefix, so a column that is searched whole is better as VARCHAR.
SELECT MAX(CHAR_LENGTH(title)) FROM posts;2Fix
Change the type to VARCHAR with enough room, then add the index. For InnoDB the key limit is 3072 bytes for DYNAMIC and COMPRESSED row formats and 767 bytes for REDUNDANT and COMPACT, counted in bytes, so a multibyte character set uses more of it per character.
ALTER TABLE posts MODIFY title VARCHAR(255) NOT NULL;
CREATE UNIQUE INDEX uq_title ON posts (title);3Verify
The statement runs without error 1170.
SHOW CREATE TABLE posts\GConfirmed by dev.mysql.com
Cause 3 of 3
You want an index on an expression of a long column
1Check
A functional key part cannot be a bare column name and does not accept a prefix length. The manual's way to shorten a column there is SUBSTRING() or CAST().
SELECT VERSION();2Fix
Index the expression, wrapped in a second pair of parentheses.
CREATE INDEX idx_title_part ON posts ((SUBSTRING(title, 1, 100)));3Verify
Queries that use the same expression can use the index; confirm it appears in the index list.
SHOW INDEX FROM posts;Confirmed by dev.mysql.com
How do I stop it coming back?
- Choose VARCHAR for anything that you search or enforce as unique, and keep TEXT for body content.
- Check the character set when you pick a prefix length, since the limit is counted in bytes.
- Run schema changes on a copy first so an index error does not stop a deploy halfway.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses to create an index: specified key was too long
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
Error 1071 means one index key is larger than the engine allows.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix