36

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?

Improve this question
2
  • 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
    Commented Dec 18, 2015 at 16:28
  • 1
    Otherwise: Maybe something like this: stackoverflow.com/a/32083169/865443
    – Anna Völkl
    Commented Dec 18, 2015 at 16:29

5 Answers 5

Reset to default
65

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)

Improve this answer
4
  • For me the following command worked : find -name "mysql_dump.sql" | xargs perl -pi -e 's/ROW_FORMAT=FIXED//g'
    – Wasiq Shahrukh
    Commented Oct 10, 2018 at 12:52
  • 1. worked for me on ubuntu
    – Joel Davey
    Commented Dec 31, 2018 at 14:43
  • @JoelDavey Yes 1# works in Ubuntu. I was referring to MacOS.
    – MagePsycho
    Commented Jul 9, 2019 at 5:04
  • #1 - worked fine on Fedora 34
    – Sergey Uskov
    Commented Feb 3, 2022 at 13:52
13 
sed -ie 's/ROW_FORMAT=FIXED//g' magento-db-dump.sql

Work cross platform (Mac + Linux)

Improve this answer
1
  • worked perfectly on linux mint....
    – Andrew Smith
    Commented Jul 21, 2019 at 6:27
10

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.

Improve this answer
1
  • 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
    Commented Jan 19, 2017 at 15:34
7

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.

Improve this answer
1

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.

Improve this answer
1
  • 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
    Commented Dec 19, 2015 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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