CREATE INDEX or ADD INDEX on a TEXT or BLOB column without a prefix length — MySQL cannot build a B-tree key from an unbounded value.. Adding a UNIQUE constraint on a TEXT column. MySQL requires a prefix length for uniqueness checks on TEXT, e.g., UNIQUE INDEX (col(255)).. ORM migration (Django, SQLAlchemy, Prisma) that changed a column from VARCHAR(255) to TEXT but left the existing index definition unchanged — the index now references an unbounded type.. dbt model with a unique_key config that targets a TEXT column. During schema creation, dbt emits a CREATE UNIQUE INDEX without a prefix length.. ADF copy activity with 'auto create table' enabled writes TEXT columns for long string fields and then attempts to add indexes defined in the mapping metadata.. Composite index that includes a TEXT column alongside other columns — the TEXT column still needs its own prefix length even in multi-column indexes.. Restoring a mysqldump from a DYNAMIC row-format table into a server configured with COMPACT or REDUNDANT row format, where the 767-byte index prefix limit is stricter.

Step 1: Identify the offending index and column: `SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SUB_PART FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db' AND COLUMN_NAME IN (SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND DATA_TYPE IN ('text','blob','mediumtext','longtext','mediumblob','longblob'));`. Step 2: Add a prefix length to the index. For a regular index: `CREATE INDEX idx_col ON your_table (text_col(255));` Choose 191 for utf8mb4 on COMPACT/REDUNDANT row format (191 × 4 bytes = 764, under the 767-byte limit).. Step 3: For unique constraints, use a prefix: `ALTER TABLE your_table ADD UNIQUE INDEX uq_col (text_col(255));` Note: uniqueness is only enforced on the first 255 characters, not the full value.. Step 4: If you need full-value uniqueness, switch the column to VARCHAR with a defined max length: `ALTER TABLE your_table MODIFY text_col VARCHAR(500); ALTER TABLE your_table ADD UNIQUE INDEX (text_col);`. Step 5: For search-oriented indexes, use FULLTEXT instead of B-tree: `ALTER TABLE your_table ADD FULLTEXT INDEX ft_col (text_col);` FULLTEXT indexes do not require a prefix length.. Step 6: In dbt, override the index definition in your model config: `{{ config(materialized='table', indexes=[{'columns': ['text_col(191)'], 'unique': True}]) }}`. Step 7: If using ADF auto-create, disable auto-indexing and run the DDL separately with correct prefix lengths. In the copy activity sink settings, set tableOption to none and run a pre-copy script with the CREATE TABLE + indexes.

Medium severityschema

MySQL Error:
1170

What does this error mean?

MySQL raises error 1170 when a CREATE INDEX, ADD INDEX, or ADD UNIQUE statement references a BLOB or TEXT column without specifying how many characters to include in the index (the prefix length). This happens because TEXT and BLOB types have no fixed upper bound — MySQL's B-tree index engine cannot build a key from an unbounded value. In data pipelines, this error typically surfaces during schema migrations: a dbt run that creates a unique key on a TEXT column, an ADF copy activity that auto-creates staging tables, or an ORM migration that changed a VARCHAR to TEXT without updating the index definition. The pipeline fails at the DDL step, before any data is loaded. You will see the error in the MySQL error log or in the connector's activity output as ER_BLOB_KEY_WITHOUT_LENGTH.

Common causes

1CREATE INDEX or ADD INDEX on a TEXT or BLOB column without a prefix length — MySQL cannot build a B-tree key from an unbounded value.
2Adding a UNIQUE constraint on a TEXT column. MySQL requires a prefix length for uniqueness checks on TEXT, e.g., UNIQUE INDEX (col(255)).
3ORM migration (Django, SQLAlchemy, Prisma) that changed a column from VARCHAR(255) to TEXT but left the existing index definition unchanged — the index now references an unbounded type.
4dbt model with a unique_key config that targets a TEXT column. During schema creation, dbt emits a CREATE UNIQUE INDEX without a prefix length.
5ADF copy activity with 'auto create table' enabled writes TEXT columns for long string fields and then attempts to add indexes defined in the mapping metadata.
6Composite index that includes a TEXT column alongside other columns — the TEXT column still needs its own prefix length even in multi-column indexes.
7Restoring a mysqldump from a DYNAMIC row-format table into a server configured with COMPACT or REDUNDANT row format, where the 767-byte index prefix limit is stricter.

How to fix it

1Step 1: Identify the offending index and column: `SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SUB_PART FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db' AND COLUMN_NAME IN (SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND DATA_TYPE IN ('text','blob','mediumtext','longtext','mediumblob','longblob'));`
2Step 2: Add a prefix length to the index. For a regular index: `CREATE INDEX idx_col ON your_table (text_col(255));` Choose 191 for utf8mb4 on COMPACT/REDUNDANT row format (191 × 4 bytes = 764, under the 767-byte limit).
3Step 3: For unique constraints, use a prefix: `ALTER TABLE your_table ADD UNIQUE INDEX uq_col (text_col(255));` Note: uniqueness is only enforced on the first 255 characters, not the full value.
4Step 4: If you need full-value uniqueness, switch the column to VARCHAR with a defined max length: `ALTER TABLE your_table MODIFY text_col VARCHAR(500); ALTER TABLE your_table ADD UNIQUE INDEX (text_col);`
5Step 5: For search-oriented indexes, use FULLTEXT instead of B-tree: `ALTER TABLE your_table ADD FULLTEXT INDEX ft_col (text_col);` FULLTEXT indexes do not require a prefix length.
6Step 6: In dbt, override the index definition in your model config: `{{ config(materialized='table', indexes=[{'columns': ['text_col(191)'], 'unique': True}]) }}`
7Step 7: If using ADF auto-create, disable auto-indexing and run the DDL separately with correct prefix lengths. In the copy activity sink settings, set tableOption to none and run a pre-copy script with the CREATE TABLE + indexes.

Example log output

ERROR 1170 (42000) at line 14: BLOB/TEXT column 'description' used in key specification without a key length
mysql> CREATE UNIQUE INDEX uq_desc ON products (description);
ERROR 1170 (42000): BLOB/TEXT column 'description' used in key specification without a key length

Frequently asked questions

Why does MySQL require a prefix length for TEXT column indexes?

TEXT and BLOB columns store up to 4 GB of data. MySQL's B-tree index needs fixed-size keys to compare and sort. A prefix length (e.g., 255) tells MySQL to index only the first N characters, making the key bounded. Without it, MySQL cannot determine the key size at index creation time and rejects the DDL.

What prefix length should I use for a TEXT column index?

It depends on your character set and row format. For utf8mb4 with COMPACT or REDUNDANT row format, use 191 (191 × 4 bytes = 764, under the 767-byte limit). For DYNAMIC or COMPRESSED row format, you can go up to 3072 bytes (768 characters in utf8mb4). Check your row format with: SHOW TABLE STATUS WHERE Name = 'your_table';

Can I create a unique constraint on a TEXT column in MySQL?

Yes, but only with a prefix: ALTER TABLE t ADD UNIQUE INDEX (col(255)). The uniqueness check applies only to the first 255 characters. Two rows with identical first 255 chars but different tails will violate the constraint even though their full values differ. If you need full-value uniqueness, switch the column to VARCHAR with a defined length.

How do I fix error 1170 in a dbt project without changing the column type?

Override the index in your dbt model config: {{ config(indexes=[{'columns': ['text_col(191)'], 'unique': True}]) }}. If your dbt adapter does not support prefix lengths in index configs, create the index in a post-hook: {{ config(post_hook='CREATE UNIQUE INDEX uq_col ON {{ this }} (text_col(191))') }}.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html