I recently upgraded my local database server from MySQL 4.1 to MySQL 5.1 on Windows XP. Got stuck in a jam involving root passwords and MySQL error 1045 that took hours to chase and fix. As a way for me to remember, and in the hope that it helps someone, else in a similar fix, I want to detail what happened and the silver bullet that did the job.
[As a side note, I should have done the upgrade ages ago. But I had so many local databases on my server, and though an in-place upgrade – just copy the data directory of the old version to the new version – is possible within a series, it is not advisable when jumping to a new series. The alternative is to export/dump the databases and then re-import. I certainly could have used
phpMyAdmin, but since I had so many local databases on my server, it would have been obnoxious to do it that way. I finally got around to hacking together a little backup script that queries the server for all its databases and calls
mysqldump on each one. I also wrote an analogous restore script that was used to import the dumped databases into the new server.]
So, after backing up everything, and removing the 4.1 installation – including a manual deletion of all the orphan files that the uninstaller leaves behind, as well as a manual hunt-and-destroy of all MySQL references in the registry – I set about installing the 5.1 version. All smooth, right up until the last step: Configuring the server and installing the daemon as a service using the Instance Configuration Wizard.
The wizard asks a bunch of questions about maximum concurrent connections, transaction support, multi-language support, all straightforward. But at the end, it wants to create a new root passwords and before it lets you do that, it asks for the old ones.
The sticky part was that no matter what I did, I could not get the Wizard to accept my new root password. I find this pretty goofy, since this is supposed to be a brand new install. Even worse, I find it mysterious. How does it even know that there are old passwords out there? Didn’t I delete everything?
But sometimes, you don’t have the luxury of contemplating the mysteries of the universe. Deadlines to meet. Mouths to feed. Plow ahead.
Apparently, I was not correctly reporting the old root password. I hardly ever use the root password, just generally safer to use a limited-privileges user account.
And no matter what I did – repeated uninstalls, manual deletes, restarts, etc – nothing could get past this critical step.
A bit of searching came up with a bunch of folks complaining in a MySQL bug report of a similar issue, but none of the solutions there worked for me. The hero of the day was Jay Alverson, who comments on a MySQL forum that you need to delete the folder:
C:\Documents and Settings\All Users\MySQL
Turns out that all my old databases were there, including the
mysql database that contains all the users, including the root user, and their passwords. Once that bad boy was gone, the wizard stopped asking me for the old root password, and simply allowed me to set the new one. Whew!
Hope it helps.