This error occurs when attempting to create a FULLTEXT index on a table with an incompatible storage engine. MySQL requires InnoDB (5.6+) or MyISAM to support FULLTEXT indexes. The fix depends on your MySQL version and application requirements.
ERROR 1214 indicates that you're trying to create or use a FULLTEXT index on a table using a storage engine that doesn't support this functionality. MySQL FULLTEXT indexes are only supported by InnoDB (MySQL 5.6.4 and later) and MyISAM storage engines. Other engines like MEMORY, ARCHIVE, or CSV do not support FULLTEXT indexes. This error commonly occurs when migrating databases between servers with different MySQL versions, or when importing SQL dumps created with newer versions into older MySQL installations.
Connect to your MySQL server and check the version:
SELECT VERSION();If you're running MySQL 5.6.4 or later, InnoDB supports FULLTEXT. Earlier versions require MyISAM for FULLTEXT support.
Identify which storage engine your table is using:
SHOW CREATE TABLE your_table_name\GOr:
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='your_table_name';Look for "ENGINE=InnoDB", "ENGINE=MyISAM", or another engine type in the output.
If your table uses MEMORY, ARCHIVE, or another unsupported engine, convert it to MyISAM (simpler) or InnoDB (better for modern MySQL):
For MyISAM (works on all MySQL versions):
ALTER TABLE your_table_name ENGINE = MyISAM;For InnoDB (MySQL 5.6.4+ only):
ALTER TABLE your_table_name ENGINE = InnoDB;Warning: Converting to MyISAM removes transactional support. InnoDB is recommended if your application uses transactions.
After converting the engine, create the FULLTEXT index:
ALTER TABLE your_table_name ADD FULLTEXT INDEX ft_index (column_name);Or for multiple columns:
ALTER TABLE your_table_name ADD FULLTEXT INDEX ft_index (column1, column2, column3);Test that FULLTEXT search now works:
SELECT * FROM your_table_name WHERE MATCH(column_name) AGAINST('search_term');If you're importing a .sql file that causes this error, preprocess the dump on Linux/macOS:
sed -i 's/) ENGINE=InnoDB/) ENGINE=MyISAM/g' your_db_dump.sqlOn macOS, you may need:
sed -i '' 's/) ENGINE=InnoDB/) ENGINE=MyISAM/g' your_db_dump.sqlOn Windows PowerShell:
(Get-Content your_db_dump.sql) -replace 'ENGINE=InnoDB', 'ENGINE=MyISAM' | Set-Content your_db_dump.sqlThen import the modified dump:
mysql -u username -p database_name < your_db_dump.sqlIf you're on MySQL 5.5 or earlier and need FULLTEXT with transaction support, upgrade to MySQL 5.6.4 or later (preferably 8.0+). Contact your hosting provider or follow MySQL upgrade documentation. After upgrading, you can use InnoDB with FULLTEXT indexes without losing transactional capabilities.
Partitioned tables do not support FULLTEXT indexes, regardless of storage engine. If you need both partitioning and FULLTEXT search, you must choose one or restructure your application (e.g., use separate unpartitioned tables for search, partitioned tables for data warehouse).
MyISAM vs InnoDB trade-offs: MyISAM offers faster FULLTEXT search performance but lacks transactions, crash recovery, and foreign keys. InnoDB (MySQL 5.6+) provides slower FULLTEXT search but supports transactions, crash safety, and referential integrity. For modern applications, InnoDB is strongly recommended.
For multi-byte character sets (UTF-8), ensure all columns in the FULLTEXT index use compatible collations. CJK languages (Chinese, Japanese, Korean) require special parsers (ngram or MeCab) for proper FULLTEXT tokenization.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed
How to fix "CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL