MySQL #1031 - Table storage engine for 'catalog_product_relation' doesn't have this option

[hanka2]

Hi, 

after migration from magento 1.7.0.2 (database mysql 5.1) to version 1.9.3.10 (database mysql 5.7)  I'm receiving this SQL error (#1031 - Table storage engine for 'catalog_product_relation' doesn't have this option) in phpmyadmin - using import of database. 

Thank you very much for your support. 

[Manish Mittal]

Hello @hanka2

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