Creating an index on a VARCHAR column with utf8mb4 encoding — utf8mb4 uses 4 bytes per character, so VARCHAR(255) = 1020 bytes, exceeding the 767-byte InnoDB limit for older row formats. Composite index with multiple large VARCHAR columns exceeds the 3072-byte limit (innodb_large_prefix enabled). Using the COMPACT or REDUNDANT InnoDB row format without innodb_large_prefix

Step 1: Enable large prefix (MySQL 5.6-5.7): `SET GLOBAL innodb_large_prefix=ON; SET GLOBAL innodb_file_format=Barracuda;` and use ROW_FORMAT=DYNAMIC.. Step 2: In MySQL 8.0+, use DYNAMIC row format by default: `CREATE TABLE t (...) ROW_FORMAT=DYNAMIC;`. Step 3: Reduce the indexed column length: create a prefix index: `CREATE INDEX idx ON your_table (col(191));` — 191 chars * 4 bytes = 764 bytes, under the limit.. Step 4: Use a hash or full-text index if prefix indexing is not suitable.

Medium severityschema

Power BI Refresh Error:
1071

What does this error mean?

An index key exceeds the maximum allowed key length for the storage engine and row format.

Common causes

1Creating an index on a VARCHAR column with utf8mb4 encoding — utf8mb4 uses 4 bytes per character, so VARCHAR(255) = 1020 bytes, exceeding the 767-byte InnoDB limit for older row formats
2Composite index with multiple large VARCHAR columns exceeds the 3072-byte limit (innodb_large_prefix enabled)
3Using the COMPACT or REDUNDANT InnoDB row format without innodb_large_prefix

How to fix it

1Step 1: Enable large prefix (MySQL 5.6-5.7): `SET GLOBAL innodb_large_prefix=ON; SET GLOBAL innodb_file_format=Barracuda;` and use ROW_FORMAT=DYNAMIC.
2Step 2: In MySQL 8.0+, use DYNAMIC row format by default: `CREATE TABLE t (...) ROW_FORMAT=DYNAMIC;`
3Step 3: Reduce the indexed column length: create a prefix index: `CREATE INDEX idx ON your_table (col(191));` — 191 chars * 4 bytes = 764 bytes, under the limit.
4Step 4: Use a hash or full-text index if prefix indexing is not suitable.

Frequently asked questions

Why is the limit 191 characters for utf8mb4 VARCHAR indexes?

InnoDB's default maximum key length is 767 bytes. With utf8mb4 (4 bytes per char), 191 chars * 4 bytes = 764 bytes — just under the limit. With DYNAMIC row format, the limit is 3072 bytes.

Does MySQL 8.0 have the same key length restrictions?

MySQL 8.0 uses DYNAMIC row format by default with a 3072-byte key limit, so most practical VARCHAR indexes are fine. The 767-byte restriction only applies to older COMPACT/REDUNDANT row formats.

Can MetricSign detect when an index creation failure blocks a migration?

Yes — when dbt or ADF fails during schema setup, MetricSign surfaces the incident with the MySQL error code.

Official documentation: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html