MySQL Issue: Table storage engine for ‘catalog_product_relation’ doesn’t have this option

When you export the Magento database dump from MySQL 5.5.x and try to import in MySQL 5.7.x, You are likely to face an error:

ERROR 1031 (HY000) at line 3002: Table storage engine for ‘catalog_product_relation’ doesn’t have this option

PROBLEM

This is probably due to the table option that you have in your CREATE TABLE DDL: ROW_FORMAT=FIXED

Let’s check if there is any such string in the SQL dump (Ex: magento-db-dump.sql).


cat magento-db-dump.sql | grep '=FIXED'

Which resulted as:


) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Catalog Product Relation Table';
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Catalog Product To Website Linkage Table';

Refer – MySQL Row Format Option

SOLUTION

Removing ROW_FORMAT=FIXED option from CREATE TABLE DDL will fix the issue.
So let’s try possible solutions.

#1


sed -i 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

This didn’t work for me in MacOSx which resulted in the following error:

sed: 1: “magento-db-dump.sql”: invalid command code m

#2


sed -i '' 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

And even this resulted as:

sed: RE error: illegal byte sequence

#3
But this one worked for me in MacOSx (Refer Mac OS X, Sed, and strange document encoding to know more about the issue)


LC_ALL=C sed -i '' 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

Re-check if the string has been removed or not:


cat magento-db-dump.sql | grep '=FIXED'

If the string is removed, now try to import as:


mysql -u <user> -p <db-name> < magento-db-dump.sql

Yay! Now it imported successfully.
Please do share and care if you liked this article.

Cheers!