Magento – How to fix thesql error 1031 – Table storage engine for ‘catalog_product_relation’ doesn’t have this option

databasemagento-1.9

I'm trying to make a new development version of our Magento store.
I made a clean install of El Capitan and got apache running with PHP, Mysql(version 5.7.10) etc. I also have phpmyadmin running and created a database for Magento. However, when I try to import the backup from our production site I get the error:

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

I tried to import the .sql file through the Terminal with the command:

mysql -h localhost -u <user> -D <database> -p < <file>

I'm lost here. Google for quit some time but couldn't find the solution.
I'm using the same engine as in the production databse.

Anyone have any idea's?

Best Answer

Recently, I had the same problem with the import.

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';

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

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

(source)

Related Topic