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

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\G

For 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 statements

But 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.