EC
1662
MySQLERRORNotableInnoDBHIGH confidence

InnoDB full-text FTS_DOC_ID_INDEX index has wrong definition

Production Risk

Low — DDL is rejected.

What this means

The FTS_DOC_ID_INDEX index required for InnoDB full-text search is not defined as a unique index on the FTS_DOC_ID column.

Why it happens
  1. 1FTS_DOC_ID_INDEX exists but is not UNIQUE.
  2. 2FTS_DOC_ID_INDEX covers a different column.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE articles (
  FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
  body TEXT,
  INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID),
  FULLTEXT(body)
) ENGINE=InnoDB;

expected output

ERROR 1662 (HY000): Incorrect definition of table for use with FULLTEXT index. Wrong FTS_DOC_ID_INDEX.

Fix

Define FTS_DOC_ID_INDEX as UNIQUE

Define FTS_DOC_ID_INDEX as UNIQUE
CREATE TABLE articles (
  FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  body TEXT,
  UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
  FULLTEXT(body)
) ENGINE=InnoDB;

Why this works

A UNIQUE index on FTS_DOC_ID is required by InnoDB for full-text search.

Sources
Official documentation ↗

MySQL 8.0 — 1662 ER_INNODB_FT_WRONG_DOCID_INDEX

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors