Migrating mysql 4 to mysql 5
- by Lennart Regebro
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...
