1660
MySQLERRORNotableInnoDBHIGH confidence
InnoDB full-text index cannot be created on temporary tables
Production Risk
Low — DDL is rejected; no data loss.
What this means
Attempting to create a full-text index on a temporary InnoDB table is not supported.
Why it happens
- 1A FULLTEXT index was specified on a CREATE TEMPORARY TABLE statement.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TEMPORARY TABLE tmp (id INT, body TEXT, FULLTEXT(body)) ENGINE=InnoDB;
expected output
ERROR 1660 (HY000): Cannot create FULLTEXT index on temporary InnoDB table.
Fix 1
Use a permanent table for full-text search
Use a permanent table for full-text search
CREATE TABLE search_tmp (id INT, body TEXT, FULLTEXT(body)) ENGINE=InnoDB;
Why this works
Full-text indexes on InnoDB require permanent tables.
Fix 2
Use MyISAM for temporary full-text search
Use MyISAM for temporary full-text search
CREATE TEMPORARY TABLE tmp (id INT, body TEXT, FULLTEXT(body)) ENGINE=MyISAM;
Why this works
MyISAM supports full-text indexes on temporary tables.
Sources
Official documentation ↗
MySQL 8.0 — 1660 ER_INNODB_NO_FT_TEMP_TABLE
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev