Migrating mysql 4 to mysql 5

Posted by Lennart Regebro on Server Fault See other posts from Server Fault or by Lennart Regebro
Published on 2011-02-19T12:09:20Z Indexed on 2011/02/19 15:27 UTC
Read the original article Hit count: 433

Filed under:
|
|

This seems to me to be a common use case, so I'm surprised so little information is about it, so sorry if it's a duplicate, but I have searched. :)

I'm migrating a clients website from one CMS to another, and of moving to newer faster machines all at the same time. As a part of this I'm moving a MySQL database from the old server to the new ones.

The problem is that the old server runs MySQL 4 and the new MySQL 5. So when i do a mysqldump at the old site and then try to run it on the new site I get syntax errors.

ERROR 1064 (42000) at line 178: 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 'BTREE (`id`),
  KEY `f_ChangedOnWeb` (`f_ChangedOnWeb`),
  KEY `f_AddressUpdate`' at line 56

I also tried to use an even older syntax by dumping with --compatible mysql323, but that just resulted in

ERROR 1062 (23000) at line 2283: Duplicate entry '??????????' for key 2`...

It seems to me this must be a reasonably common use case, yet I can't find any sort of help on this. Possibly all my Google searches just drown in irrelevant answers. Most seem to agree that mysqldump is the right answer, but noone mentions that you can get syntax errors...

© Server Fault or respective owner

Related posts about mysql

Related posts about migration