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!