How to fix ERROR 1170: BLOB/TEXT used in key without length in MySQL

MySQLINTERMEDIATEHIGHUpdated Dec 23, 2025· 205 views

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.

Quick fix

Fixes 1170

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title TEXT,
  content TEXT,
  KEY idx_title (title(255))
);
  1. Add a prefix length to your index
  2. For PRIMARY KEY, convert the column to VARCHAR instead
  3. Use FULLTEXT indexes for large text searches
  4. For unique constraints, create a hash column instead
mysql — error
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title TEXT,
  content TEXT,
  KEY idx_title (title(255))
);
ERROR 1170: BLOB/TEXT used in key without length

What this error means

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.

Typical symptoms

  • CREATE TABLE or ALTER TABLE statements fail with error 1170
  • Migrations fail when trying to add indexes to TEXT or BLOB columns
  • Cannot create unique constraints on BLOB/TEXT fields
  • ORMs like Doctrine, Laravel, or Django may trigger this error unexpectedly
  • Converting VARCHAR(512+) columns silently converts them to TEXT, triggering the error

Common causes

  1. Attempting to use BLOB, TEXT, TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, or LONGTEXT columns as a PRIMARY KEY without a prefix length
  2. Adding a regular INDEX or UNIQUE constraint to a TEXT/BLOB column without specifying a key length like KEY(column(100))
  3. Creating a table with a TEXT or BLOB column that already has an index defined
  4. Using VARCHAR(256+) in a primary key, which MySQL auto-converts to SMALLTEXT, triggering this error
  5. ORM frameworks auto-generating incorrect migration SQL for indexed text fields

How to fix "ERROR 1170: BLOB/TEXT used in key without length"

1Add a prefix length to your index

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:

sql
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title TEXT,
  content TEXT,
  KEY idx_title (title(255))
);

Or with ALTER TABLE:

sql
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).

2For PRIMARY KEY, convert the column to VARCHAR instead

If the column needs to be a PRIMARY KEY, convert it to VARCHAR with a reasonable length limit instead of using TEXT.

sql
-- 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).

3Use FULLTEXT indexes for large text searches

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.

sql
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');
4For unique constraints, create a hash column instead

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.

sql
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.

5Check for automatic type conversions in migrations

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):

php
// 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.

Advanced notes

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.

Frequently asked questions

What does "1170" mean?
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.
How do I fix "1170"?
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.
What causes "1170"?
Attempting to use BLOB, TEXT, TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, or LONGTEXT columns as a PRIMARY KEY without a prefix length Adding a regular INDEX or UNIQUE constraint to a TEXT/BLOB column without specifying a key length like KEY(column(100)) Creating a table with a TEXT or BLOB column that already has an index defined
Is the "1170" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading