metricsign
Start free
Medium severityschema

Power BI Refresh Error:
1170

What does this error mean?

An index definition includes a BLOB or TEXT column without specifying a prefix key length.

Common causes

  • 1Creating an index on a TEXT or BLOB column without specifying a prefix length
  • 2Unique constraint on a TEXT column (which requires a prefix length in MySQL)
  • 3ORM generates an index on a column that was later changed from VARCHAR to TEXT

How to fix it

  1. 1Step 1: Add a prefix length to the index: `CREATE INDEX idx ON your_table (text_col(255));`
  2. 2Step 2: For unique constraints on TEXT, use a prefix: `ALTER TABLE your_table ADD UNIQUE INDEX (text_col(255));`
  3. 3Step 3: Consider switching the column to VARCHAR with a defined length if full-column indexing is needed.
  4. 4Step 4: For full-text search, use a FULLTEXT index instead: `ALTER TABLE your_table ADD FULLTEXT INDEX (text_col);`

Frequently asked questions

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

TEXT columns have variable, potentially unbounded length. MySQL cannot index the full content without a prefix length that limits how much of the text is included in the B-tree index.

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

Choose based on the selectivity needed. Common choices: 100-255 characters. For utf8mb4, 191 chars = 764 bytes (under the 767-byte limit for non-DYNAMIC row formats).

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

Yes, but only with a prefix length: `ADD UNIQUE INDEX (text_col(255))` — the uniqueness check is limited to the first 255 characters, not the full column value.

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

Other schema errors