How to fix ERROR 1031: Table storage engine doesn't have this option in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 25, 2025· 86 views

This error occurs when you try to use a table option or feature that your storage engine (InnoDB, MyISAM, etc.) does not support. Common causes include deprecated options like ROW_FORMAT=FIXED, unsupported INDEX DIRECTORY clauses, or engine-specific features being used with incompatible engines.

Quick fix

Fixes 1031

SHOW TABLE STATUS WHERE Name = 'your_table_name';
  1. Check your table's current storage engine
  2. Review your CREATE/ALTER TABLE statement for deprecated options
  3. Remove unsupported ALTER TABLE options
  4. Convert the table to a compatible storage engine if needed
mysql — error
SHOW TABLE STATUS WHERE Name = 'your_table_name';
ERROR 1031: Table storage engine doesn't have this option

What this error means

MySQL ERROR 1031 (ER_ILLEGAL_HA) signals that you're attempting to use a table creation or modification option that is incompatible with your table's storage engine. Each MySQL storage engine (InnoDB, MyISAM, Memory, etc.) supports a specific set of features and options. When you specify an unsupported option, MySQL cannot process the CREATE TABLE or ALTER TABLE statement and returns this error. Common incompatibilities include: - Using deprecated options that were removed in newer MySQL versions - Attempting engine-specific features on the wrong engine type - Using options designed for one storage engine with a different engine - Enabling options that conflict with strict mode or innodb_strict_mode settings

Typical symptoms

  • CREATE TABLE or ALTER TABLE commands fail with ERROR 1031
  • Database imports or migrations fail unexpectedly
  • SQL dumps from one MySQL version fail to import into another
  • Table creation works on one MySQL instance but fails on another
  • Tool-generated DDL statements (from migrations or schema managers) produce this error

Common causes

  1. Using ROW_FORMAT=FIXED (deprecated in MySQL 5.7+, removed in 8.0) - InnoDB no longer supports this format
  2. Specifying INDEX DIRECTORY or DATA DIRECTORY with InnoDB (MyISAM-only features)
  3. Using TABLESPACE options unsupported by the target storage engine
  4. Attempting to add foreign key constraints to MyISAM tables (InnoDB-only feature)
  5. Using partitioning features not supported by the storage engine
  6. ALTER TABLE DISABLE/ENABLE KEYS on InnoDB tables (MyISAM-specific feature)
  7. Mixing MySQL versions with incompatible DDL syntax
  8. Strict mode (sql_mode or innodb_strict_mode) rejecting invalid table definitions

How to fix "ERROR 1031: Table storage engine doesn't have this option"

1Check your table's current storage engine

First, identify what storage engine the table is using and what MySQL version you're running:

sql
SHOW TABLE STATUS WHERE Name = 'your_table_name';

Or for a specific database:

sql
SHOW CREATE TABLE your_table_name;

Look for the ENGINE specification in the output (typically InnoDB or MyISAM).

Also check your MySQL version:

sql
SELECT VERSION();
2Review your CREATE/ALTER TABLE statement for deprecated options

Examine the SQL statement causing the error and look for these common culprits:

ROW_FORMAT=FIXED: This is deprecated since MySQL 5.7 and removed in 8.0. If you see:

sql
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=FIXED;

Change it to:

sql
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Valid options for InnoDB are: DYNAMIC, COMPACT, REDUNDANT, COMPRESSED.

INDEX/DATA DIRECTORY on InnoDB: These are MyISAM-only features:

sql
-- WRONG: InnoDB doesn't support this
CREATE TABLE my_table (...) ENGINE=InnoDB INDEX DIRECTORY='/path/to/dir';

-- CORRECT: Remove the option or use MyISAM
CREATE TABLE my_table (...) ENGINE=InnoDB;
3Remove unsupported ALTER TABLE options

If you're importing a SQL dump or running an ALTER TABLE statement, look for these MyISAM-specific options being applied to InnoDB tables:

sql
-- This may be in your dump file:
ALTER TABLE my_table DISABLE KEYS;
-- ... do something ...
ALTER TABLE my_table ENABLE KEYS;

InnoDB doesn't recognize these options. Remove or comment them out:

sql
-- ALTER TABLE my_table DISABLE KEYS;
-- ... do something ...
-- ALTER TABLE my_table ENABLE KEYS;
4Convert the table to a compatible storage engine if needed

If the error is because you need a feature that your storage engine doesn't support, convert the table:

To change from MyISAM to InnoDB:

sql
ALTER TABLE my_table ENGINE=InnoDB;

This allows you to use InnoDB features like foreign keys and transactions.

To change from InnoDB to MyISAM (if you specifically need MyISAM features):

sql
ALTER TABLE my_table ENGINE=MyISAM;

Note: MyISAM doesn't support transactions or foreign keys, so this is rarely recommended for modern applications.

5Fix TABLESPACE and partitioning options

If you see TABLESPACE options, verify they're supported by your engine and MySQL version:

sql
-- Check what you have
SHOW CREATE TABLE my_table\G

For partitioned InnoDB tables, remove MyISAM-specific options:

sql
-- WRONG:
CREATE TABLE sales (
  id INT,
  sale_date DATE
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2022 VALUES LESS THAN (2023) INDEX DIRECTORY='/path',
  PARTITION p2023 VALUES LESS THAN (2024)
);

-- CORRECT:
CREATE TABLE sales (
  id INT,
  sale_date DATE
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024)
);
6Check and adjust strict mode settings

Some tools enable strict SQL modes that are more aggressive about validating table definitions. If you're using a schema migration tool (like Skeema), check its configuration:

sql
-- View current strict mode
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'innodb_strict_mode';

If you see many validation errors, you may need to:
- Update your DDL to be fully compatible, OR
- Temporarily disable strict mode for the import (not recommended for production)

sql
SET SESSION sql_mode = 'NO_AUTO_CREATE_USER';
SET SESSION innodb_strict_mode = 0;
-- Now run your CREATE/ALTER statements

But it's better to fix the DDL itself to be compatible.

7Re-run your CREATE/ALTER TABLE statement

After making the changes, execute your statement again:

sql
CREATE TABLE my_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

If successful, you'll see a message like:

bash
Query OK, 0 rows affected (0.05 sec)

If you still get ERROR 1031, double-check that all options match your storage engine's supported features.

Advanced notes

Storage Engine Compatibility Matrix:

Not all options work with all engines:

- InnoDB: Supports ROW_FORMAT (DYNAMIC, COMPACT, REDUNDANT, COMPRESSED), KEY_BLOCK_SIZE, TABLESPACE, partitioning, foreign keys, transactions
- MyISAM: Supports ROW_FORMAT (FIXED, DYNAMIC, COMPRESSED), KEY_BLOCK_SIZE, INDEX DIRECTORY, DATA DIRECTORY, but no foreign keys or transactions
- Memory: Limited options, primarily in-memory storage
- CSV, Archive: Specialized engines with very limited DDL options

Version-Specific Issues:

RowFormat=FIXED was deprecated in MySQL 5.7.2 and removed entirely in MySQL 8.0. If you're migrating from older versions, this is the most common cause of ERROR 1031.

Migration Tools and ERROR 1031:

Tools like Skeema, Liquibase, and Flyway generate DDL statements that may include version-specific or engine-specific options. If you're using such a tool, ensure:
1. The tool is configured for your MySQL version
2. The DDL templates match your target schema
3. Your source and destination MySQL versions are compatible

Database-Level Investigation:

If many tables are failing with this error, check if:
- You're importing a dump from a newer MySQL version into an older one (incompatible options)
- Your innodb_strict_mode is enabled, causing strict validation
- The storage engine being specified in the dump doesn't exist on the target server

Frequently asked questions

What does "1031" mean?
MySQL ERROR 1031 (ER_ILLEGAL_HA) signals that you're attempting to use a table creation or modification option that is incompatible with your table's storage engine. ) supports a specific set of features and options. When you specify an unsupported option, MySQL cannot process the CREATE TABLE or ALTER TABLE statement and returns this error.
How do I fix "1031"?
This error occurs when you try to use a table option or feature that your storage engine (InnoDB, MyISAM, etc.) does not support. Common causes include deprecated options like ROW_FORMAT=FIXED, unsupported INDEX DIRECTORY clauses, or engine-specific features being used with incompatible engines.
What causes "1031"?
Using ROW_FORMAT=FIXED (deprecated in MySQL 5.7+, removed in 8.0) - InnoDB no longer supports this format Specifying INDEX DIRECTORY or DATA DIRECTORY with InnoDB (MyISAM-only features) Using TABLESPACE options unsupported by the target storage engine
Is the "1031" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading