CODEWITHSUNDEEP
mysqlmigrationupgrade
Steve06
Steve06

Reputation: 771

Restoring dump file after MySQL server migration from 5.1.51 to 5.5.10 throws error 1064

On a Windows 7 machine running MySQL server 5.1.51 (both 64bit), before migrating to 5.5.10, I made a backup of my entire database using MySQL Administrator. After installing 5.5.10, I tried to restore the dump file but it throws one error:

ERROR 1064 (42000): 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 = MyISAM  DEFAULT CHARSET latin1 COLLATE latin1_german1_ci  ; SELECT co_n' at line 9"

I tried restore through several MySQL applications (Workbench, SQLYog) and the command line, but all throw the same error. It also throws several warnings that are however not displayed.

Two questions:

  1. Of course, can you help me resolve this issue? Do I have to edit the sql dump file in some way? That's cumbersome though, since it's over 3GB. I thought I had followed all the steps with respect to upgrading mentioned in the MySQL documentation, so now I am a bit frustrated and scared of data loss.

  2. When restoring from the command line, how can I make mysql.exe log all warnings and errors to a text file?

Upvotes: 3

Views: 7345

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

OBSERVATION #1

MySQL provides upgrade scripts when migrating data from a previous major release. I usually do not use them. Here is what I do whenever I migrate from one major release to another:

mysqldump all databases except the mysql schema. The mysql schema from major release to major release is different.

Start with a fresh install of MySQL 5.5.10

Load all databases from the mysqldumps (except mysql) in MySQL 5.5.10

Now what about the mysql schema? You can dump it in a very special way.

This is what I do for Linux to dump the mysql schema from MySQL 5.1.51:

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants51.sql

This will produce all the SQL GRANT commands to recreate the users and permissions.

Since you are using Windows, you can do this:

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A > C:\MySQLGrants51.sql

You would have to edit C:\MySQLGrants51.sql in notepad so that every line the file ends with a semicolon(;)

Now go into MySQL 5.5.10's client and run

source C:\MySQLGrants51.sql

OBSERVATION #2

"ERROR 1064 (42000): 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 = MyISAM DEFAULT CHARSET latin1 COLLATE latin1_german1_ci ;

This error message has 'TYPE = MyISAM'. Shouldn't that read 'ENGINE = MyISAM' ?

Upvotes: 2

MarkR
MarkR

Reputation: 63538

What you need to do, is repeat the dump, using the "mysqldump" tool. This is the only tool which is capable of making non-useless dumps. Other (particularly GUI) tools TRY, but they fail, because they aren't sufficiently well-tested, particularly in this kind of scenario.

Bin your dump, and make a new one using mysqldump. Use the version of mysqldump from 5.5, for preference.

Upvotes: 0

Related Questions