Home / server / Questions / 237600
Accepted
Lennart Regebro
Asked: 2011-02-20 04:09:20 +0800 CST

Migrating mysql 4 to mysql 5

  • 772

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...

migration mysql

2 Answers

  1. There is documentation on the MySQL website for migration. Maybe this helps?

    • 0
  2. Best Answer

    The import succeeded after I changed all PRIMARY KEY TYPE BTREE to PRIMARY KEY USING BTREE. I don't know if that change is correct or not, but I guess we'll see...

    • 0