Migrating mysql 4 to mysql 5

Question

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

Answer 1

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

Answer 2

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

Answer 3

I would try playing some games with mysqldump options

1) I would perform a mysqldump using --no-data to MySQLSchema.sql and then mysql using --no-create-info to MySQLData.sql. Then you could spend time loading MySQLSchema.sql and editing it until all Syntax Errors are gone. Then, load MySQLData.sql.

2) Question: What version of software did you use to mysqldump the MySQL4 data, MySQL4 or MySQL5 ??? Try using the mysqldump (--no-data) v4 to do mysqldump v4 against mysqlk v4 database and mysqldump v5 (--no-data) against mysql v4 database and see if they generate the same schema.

Give it a Try and let us know !!!