1

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!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Levi
  • 301
  • 3
  • 12
  • An example of an erroring query would be helpful – Simon at The Access Group Jun 11 '13 at 17:05
  • show a sample of the actual query from that dump that's causing this error. the error messages aren't enough to figure this out, though obviously your dump has invalid syntax. – Marc B Jun 11 '13 at 17:05
  • Apparently Stackoverflow thinks too risky to answer my own question so here it is in a comment. 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. – Levi Jun 11 '13 at 17:27

1 Answers1

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828