This error occurs when you try to create an index, PRIMARY KEY, or UNIQUE constraint on a BLOB or TEXT column without specifying a prefix length. MySQL requires a fixed number of bytes for indexes, so variable-length text fields need an explicit length specification.
MySQL raises error 1170 when you attempt to create a key (PRIMARY KEY, UNIQUE, or INDEX) that includes a BLOB or TEXT column without defining how many initial bytes should be indexed. The underlying issue is that BLOB and TEXT columns store variable-length data, but indexes require fixed storage sizes. MySQL cannot determine the key size for a variable-length field, so it requires you to specify a prefix lengthโhow many characters (bytes) from the beginning of the column should be indexed. This error typically occurs during CREATE TABLE or ALTER TABLE ... ADD INDEX statements that reference BLOB/TEXT columns directly in index definitions.
If you need to index a BLOB or TEXT column, specify a prefix length in parentheses. The length is in characters (bytes) and indicates how much of the column should be indexed.
For example:
CREATE TABLE articles (
id INT PRIMARY KEY,
title TEXT,
content TEXT,
KEY idx_title (title(255))
);Or with ALTER TABLE:
ALTER TABLE articles ADD INDEX idx_content (content(255));For utf8mb4 collation, MySQL supports a maximum of 3072 bytes per index key, which means up to 768 characters (3072 / 4 bytes per character).
If the column needs to be a PRIMARY KEY, convert it to VARCHAR with a reasonable length limit instead of using TEXT.
-- Instead of:
-- CREATE TABLE users (name TEXT PRIMARY KEY);
-- Use:
CREATE TABLE users (
name VARCHAR(191) PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE
);VARCHAR(191) is the safe default for utf8mb4 collation (allowing utf8mb4 to index the full column without issues).
If you need to search within BLOB or TEXT content, use FULLTEXT indexes instead of regular indexes. FULLTEXT indexes handle variable-length text properly without requiring a prefix length.
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content LONGTEXT NOT NULL,
FULLTEXT INDEX ft_search (title, content)
);
-- Search across the full text:
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('mysql error');If you need a UNIQUE constraint on a large TEXT field (like URLs or content hashes), create a separate hash column and make that unique.
CREATE TABLE documents (
id INT PRIMARY KEY AUTO_INCREMENT,
content LONGTEXT NOT NULL,
content_hash CHAR(32) NOT NULL UNIQUE,
INDEX idx_content_hash (content_hash)
);
-- When inserting, compute MD5:
INSERT INTO documents (content, content_hash)
VALUES ('some long content...', MD5('some long content...'));This approach provides both a UNIQUE constraint and fast lookups without the length restriction.
If you are using an ORM (Doctrine, Eloquent, Django), verify that VARCHAR columns with lengths >= 256 are not being auto-converted to TEXT.
For example, in Eloquent (Laravel):
// Avoid this - may convert to TEXT internally:
Schema::create('posts', function (Blueprint $table) {
$table->string('slug', 512)->unique(); // Too long!
});
// Do this instead:
Schema::create('posts', function (Blueprint $table) {
$table->string('slug', 191)->unique(); // Stays as VARCHAR
});Always keep VARCHAR lengths <= 191 for utf8mb4 to avoid implicit conversion.
Collation and Maximum Key Length: MySQL enforces a maximum key length of 3072 bytes per column. With utf8mb4 (4 bytes per character), the maximum practical prefix length is 768 characters. With utf8 (3 bytes per character), it is 1024.
InnoDB vs MyISAM: Different storage engines have different key length limits. InnoDB has stricter limits than MyISAM. Always use InnoDB for new projects.
MariaDB Compatibility: MariaDB has the same limitation as MySQL. The error message and solutions are identical.
Implicit Type Conversion: Be aware that some frameworks automatically convert VARCHAR(256+) to MEDIUMTEXT. Always specify a reasonable VARCHAR length if you need indexing.
Performance Considerations: A shorter prefix (e.g., KEY(url(100))) indexes faster than longer prefixes, but may have more collisions. Balance between selectivity and index size based on your data.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL