Search code examples
mysqlsequelpro

Import of older MySQL databases (5.6.10) into 5.6.12 generates errors

I created what I thought was a simple sql dump from the older database before installing the latest version, 5.6.12 on Mac OS X Server. Now I'm trying to import the files but they produce all produce endless numbers of syntax errors.

Example of errors: 

[ERROR in query 9] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=InnoDB AUTO_INCREMENT=6' at line 8
[ERROR in query 10] Table 'some.table' doesn't exist

Any tips?

Thanks in advance!

Solution

  • You answered your own question:

    The problem was: TYPE=InnoDB . As I understand it should be ENGINE=InnoDB . Sequel Pro on Mac (1.0.2) produces an sql dump with "TYPE" by default.

    Yes, this is deprecated syntax.

    http://dev.mysql.com/doc/refman/5.1/en/create-table.html says:

    Note
    The older TYPE option was synonymous with ENGINE. TYPE has been deprecated since MySQL 4.0 but is still supported for backward compatibility in MySQL 5.1 (excepting MySQL 5.1.7). Since MySQL 5.1.8, it produces a warning. It is removed in MySQL 5.5. You should not use TYPE in any new applications, and you should immediately begin conversion of existing applications to use ENGINE instead. (See the Release Notes for MySQL 5.1.8.)

    So the keyword has been deprecated since 2006, and removed from the product since 2010!

    I found this bug already reported here: https://code.google.com/p/sequel-pro/issues/detail?id=1668

    The dev's reply is that Sequel Pro uses SHOW CREATE TABLE to export table definitions. If your SQL_MODE = "MYSQL323" or "MYSQL40", then SHOW CREATE TABLE will use the outdated TYPE option instead of the proper ENGINE option. So you can work around the issue by making sure your SQL_MODE is not configured for compatibility with an old version of MySQL.