MySQL Forums
Forum List  »  Newbie

Re: what is the right way to change a database to a new server which runs also a newer version of MySQL?
Posted by: Rick James
Date: April 19, 2014 07:39PM

Ouch!

IF you can get mysqldump (preferrable an old version of it) to work, you will be left with some or all of the following:
* (as you found) CREATE TABLE will say TYPE=MyISAM -- edit that to say ENGINE=MyISAM.
* Newer versions have a concept of "character set"; 4.0 and before did not. Recommend you make sure the system defaults to latin1 and each database is set to latin1. Then (hopefully), whatever stuff you have in strings will at least load without mangling into the new server. (SELECTing the text and making sense of it is another task.)
* If you have any SELECTs that mix commajoin and explicit JOIN, they may be broken.
* Do _not_ try to reload the database `mysql`. Instead, re-GRANT all the users. (Password encryption changed; many columns were added to tables; many tables were added; etc. Mysql_upgrade fixes them, but probably would fail somehow for this big an upgrade.)

There are lots more incompatibilities; those may get you started.

Whenever you move from one version to another (in place), be sure to run mysql_upgrade. (I suspect that did not exist for 4.0, maybe not for 4.1.)

Let this be a lesson. In any long-running project, plan on upgrading _everything_ (hardware, OS, software, UI, backup medium) every 5 years. You were lucky that some parts have some semblance of "sameness" after 15 years.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.