Re: Need to upgrade several 4.0.18 databases to 5.6

[Rick James]

4.0.18 was released in 2004 -- a decade ago! Let this be a lesson for everyone; plan on upgrading at least once every 5 years.

You are actually lucky that there is an upgrade path. But as Peter says, and Oracle hints, it is fraught with perils.

I think the biggest risk is in character set handling. If you have anything but English (or perhaps Western European characters), you can get away with "latin1". However, you should specify it in 5.6. (CHARACTER SETs did not really exist in 4.0.)

I would take the approach of using mysqldump and try to load the dump onto 5.6. I would try both the 4.0 mysqldump and the 5.6 mysqldump.

The 4.0 dump will say (I think) TYPE=MyISAM. That syntax is no longer valid; it is now ENGINE=MyISAM. Editing the dump will probably suffice for getting a successful load.

The 5.6 mysqldump might avoid some issues.

Bottom line: Regardless of how you do it (multiple upgrades vs mysqldump+reload), plan on doing the exercise multiple times until you get all the kinks out. It would be best to have a separate machine for the new 5.6.

Another syntax problem is in TIMESTAMP -- if the dump says TIMESTAMP(14), change it to just plain TIMESTAMP.

DECIMAL may have some minor hiccups, but I doubt it.

If you would like to present SHOW CREATE TABLE, I will review that for other hiccups.

There are hundreds of new features in 5.6 (versus 4.0). I think that subqueries is the most valuable. It would be wise to think about moving from MyISAM to InnoDB sometime in the near future. (InnoDB was new in 4.0, but has improved greatly since then.)