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.
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
First, identify what storage engine the table is using and what MySQL version you're running:
SHOW TABLE STATUS WHERE Name = 'your_table_name';Or for a specific database:
SHOW CREATE TABLE your_table_name;Look for the ENGINE specification in the output (typically InnoDB or MyISAM).
Also check your MySQL version:
SELECT VERSION();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:
CREATE TABLE my_table (
id INT PRIMARY KEY,
name VARCHAR(255)
) ENGINE=InnoDB ROW_FORMAT=FIXED;Change it to:
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:
-- 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;If you're importing a SQL dump or running an ALTER TABLE statement, look for these MyISAM-specific options being applied to InnoDB tables:
-- 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:
-- ALTER TABLE my_table DISABLE KEYS;
-- ... do something ...
-- ALTER TABLE my_table ENABLE KEYS;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:
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):
ALTER TABLE my_table ENGINE=MyISAM;Note: MyISAM doesn't support transactions or foreign keys, so this is rarely recommended for modern applications.
If you see TABLESPACE options, verify they're supported by your engine and MySQL version:
-- Check what you have
SHOW CREATE TABLE my_table\GFor partitioned InnoDB tables, remove MyISAM-specific options:
-- 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)
);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:
-- 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)
SET SESSION sql_mode = 'NO_AUTO_CREATE_USER';
SET SESSION innodb_strict_mode = 0;
-- Now run your CREATE/ALTER statementsBut it's better to fix the DDL itself to be compatible.
After making the changes, execute your statement again:
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:
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.
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
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions