Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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

share|improve this answer
It's mostly about upgrading with mysql_upgrade, so no. It contains long lists of incompatibilties, but nothing that seems to have to do with my syntax error above. – Lennart Regebro Feb 19 '11 at 13:10
up vote 0 down vote accepted

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

share|improve this answer
Seems to have worked. – Lennart Regebro Feb 25 '11 at 13:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.