Copying database gives ERROR 1031 (HY000) at line 1440: Table storage engine doesn't have this option

Issue

When copying a database from production to a lower environment, you may encounter an error that looks like this:

ERROR 1031 (HY000) at line 1440: Table storage engine for 'example_some_table' doesn't have this option

Resolution

Compare the versions of MySQL between environments. If any of the environments are on MySQL 5.7 or later, it may be that you need to update the ROW_FORMAT value for any tables that are failing.

Note: To check which version of MySQL is running on one of your environments, run the following Drush command against the environment in your CLI:

drush sqlc

This logs you into your database and will print the version on the screen. Then you can exit the MySQL interface by entering quit.

Cause

This comes up in relation to a setting that is deprecated after MySQL 5.7; ROW_FORMAT=FIXED is no longer an option for the InnoDB Storage Engine.

According to MySQL 5.7 Innodb Default Row Format documentation:

• DYNAMIC, COMPACT, and REDUNDANT are allowed as default values
• COMPRESSED is not allowed to a default value, but you can explicitly use ROW_FORMAT=COMPRESSED
• ROW_FORMAT=FIXED is deprecated in MySQL 5.7