How to Fix MySQL Error 1178: Storage Engine Doesn't Support This Option

ERROR 1178 (42000): The used table type doesn't support this option is raised when a DDL statement attempts to use a table feature or index type that the specified storage engine does not support. The error symbol is ER_ILLEGAL_HA.

Impact

The DDL statement that triggered the error — typically a CREATE TABLE or ALTER TABLE — fails completely and no changes are made to the schema. This is a hard error, not a warning, so the table will not be created or modified regardless of SQL mode settings.

Developers most commonly encounter this when switching a table's storage engine (for example, migrating from InnoDB to MEMORY or CSV) while the table definition retains features that the target engine does not support. It also appears when creating new tables with engine-incompatible options, such as adding a FULLTEXT index on a MEMORY table or declaring a FOREIGN KEY on a MyISAM table.

Common Causes

FULLTEXT index on an unsupported engine. FULLTEXT indexes are supported only on InnoDB (MySQL 5.6+) and MyISAM tables. Attempting to create one on a MEMORY, CSV, ARCHIVE, or NDB table raises error 1178.
FOREIGN KEY constraint on MyISAM or MEMORY. Foreign key enforcement is an InnoDB-only feature. Declaring FOREIGN KEY constraints in a CREATE TABLE ... ENGINE=MyISAM statement will trigger this error.
ALTER TABLE ... ENGINE=<engine> on a table with incompatible features. Converting an InnoDB table that has foreign keys or fulltext indexes to a storage engine that does not support those features will fail with this error.
AUTO_INCREMENT on engines that don't support it. Engines such as CSV do not support AUTO_INCREMENT columns. Using AUTO_INCREMENT in the table definition when the engine is set to CSV will produce this error.
Partitioning with an incompatible engine. Some storage engines (MERGE, CSV, FEDERATED) do not support table partitioning. Applying a PARTITION BY clause with these engines raises error 1178.

Troubleshooting and Resolution Steps

Identify what the table is using and what the target engine supports.
```
SHOW CREATE TABLE your_table_name\G
```
Review the output for FULLTEXT indexes, FOREIGN KEY constraints, AUTO_INCREMENT, and PARTITION BY clauses. Note the current ENGINE= value.
Fix FULLTEXT index issues — switch to InnoDB or MyISAM.

If you need full-text search, keep the engine as InnoDB (MySQL 5.6+) or MyISAM:
```
CREATE TABLE articles (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  body TEXT,
  FULLTEXT idx_body (body)
) ENGINE=InnoDB;
```
If you must use a different engine and full-text search is not required, drop the FULLTEXT index from the definition.
Fix FOREIGN KEY issues — use InnoDB or remove the constraint.

Foreign keys require InnoDB. Either change the engine:
```
ALTER TABLE child_table ENGINE=InnoDB;
```
Or, if referential integrity will be enforced at the application layer, remove the FOREIGN KEY clause from the CREATE TABLE statement.

Diagnose engine capabilities before altering a table.

Before converting a table's engine, check whether the target engine supports the features in use:

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table';

SELECT CONSTRAINT_TYPE, CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table';

Drop incompatible constraints and indexes before executing the ALTER TABLE ... ENGINE= statement.

Fix AUTO_INCREMENT on CSV or unsupported engines.

Remove the AUTO_INCREMENT attribute and manage primary key values at the application level, or choose a different engine:
```
CREATE TABLE log_data (
  id INT NOT NULL,
  message VARCHAR(255)
) ENGINE=CSV;
```

Fix partitioning errors — choose a partition-compatible engine.

Use InnoDB or MyISAM for partitioned tables:

CREATE TABLE sales (
  id INT NOT NULL,
  sale_date DATE NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

Additional Information

The SQLSTATE code 42000 indicates a syntax or access rule violation, which reflects that the statement is structurally invalid for the chosen engine.
MySQL 5.6 added FULLTEXT index support to InnoDB. Before 5.6, fulltext search required MyISAM. Code that targeted older MySQL versions may create tables with ENGINE=MyISAM solely for this reason; on modern MySQL, switching to InnoDB is preferred.
ORMs like Hibernate, SQLAlchemy, and ActiveRecord typically default to InnoDB and generate InnoDB-compatible DDL. Error 1178 in an ORM context usually points to a manually specified engine parameter or a migration script that overrides the default engine.
Related errors: 1005 (ER_CANT_CREATE_TABLE) — a broader table creation failure that can wrap engine-incompatibility issues; 1217 / 1216 — foreign key constraint errors specific to InnoDB referential integrity at DML time.
The NDB (MySQL Cluster) engine has its own set of unsupported options and may raise 1178 for features that work on InnoDB/MyISAM. Consult the NDB documentation for the specific supported DDL subset.

Frequently Asked Questions

Why does MySQL accept the FOREIGN KEY syntax on MyISAM without an error in some cases? In older MySQL versions, FOREIGN KEY syntax was silently ignored on MyISAM tables — the constraint was parsed but not enforced and not stored. Since MySQL 8.0.13+, such statements raise error 1178 more consistently. If you relied on silent acceptance in older versions, the behavior change can surface during upgrades.

Can I have FULLTEXT search on a MEMORY table? No. The MEMORY storage engine stores all data in RAM and uses hash or B-tree indexes only. FULLTEXT indexes are not supported. If you need in-memory full-text search, a common approach is to use InnoDB tables backed by a fast NVMe disk or offload full-text queries to a dedicated search engine such as Elasticsearch or OpenSearch.

I'm using ALTER TABLE my_table ENGINE=MEMORY to speed up a temp table, but I get error 1178. What should I check? Check for: (a) foreign key constraints referencing or referenced by the table, (b) FULLTEXT indexes, and (c) partitioning. Use SHOW CREATE TABLE my_table\G to see all these at once. Drop or disable each incompatible feature before running the ALTER TABLE ... ENGINE=MEMORY statement.

Does error 1178 affect transactions? The failing DDL statement is rolled back (DDL in MySQL is typically auto-committed), but no data or previously committed changes are lost. In a multi-statement migration script, the script will abort at the failing statement unless you have explicit error handling; check your migration tool's behavior on DDL errors to avoid partial schema migrations.