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?

  • How did you create the export? Did you check your local database if there are any tables already created (after testing it multiple times)? If so, delete all those tables before running a new import. – Anna Völkl Dec 18 '15 at 16:28
  • 1
    Otherwise: Maybe something like this: stackoverflow.com/a/32083169/865443 – Anna Völkl Dec 18 '15 at 16:29

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)

  • For me the following command worked : find -name "mysql_dump.sql" | xargs perl -pi -e 's/ROW_FORMAT=FIXED//g' – Wasiq Shahrukh Oct 10 at 12:52

I came across the same issue when attempting to import a Magento database dump from MySQL 5.5 into MySQL 5.7. The problem seems to be the table option ROW_FORMAT=FIXED on two InnoDB tables. Removing the two occurrences of this option from the database dump allowed the import to run without error.

It seems this option is only relevant to MyISAM tables, at least in MySQL 5.7.

  • 1
    Small addition: It is related to the setting innodb_strict_mode which used to be off by default, but is enabled by default starting from MySQL 5.7.7 (according to the MySQL documentation). – Jisse Reitsma Jan 19 '17 at 15:34

Another option would be to add innodb_strict_mode = off to your MySQL configuration file. I bumped into this error using the unattended installs of magerun which kept me from editing the SQL file. I'm not sure if disabling the InnoDb strict mode is desirable in a production environment, but at least it works in a development environment.

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

Work cross platform (Mac + Linux)

That table is expecting to be InnoDB. I would check your mysql settings to make sure you have that enabled/allowed. Also, the suggestion that you may have that table existing is possible. I would remove all tables and try it again after you validate that InnoDB is working.
Just going off the error message you provided, I would expect that InnodDB is not ready or working on your current install.

  • Just checked mysql. it's enabled and set as the default engine. I'm importing it on a brand new database with no tables at all. Will try the solution that @Anna Völkl said.. Hope it helps – Woulei Dec 19 '15 at 21:42

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.