MySQL install failure – Security settings cannot be applied

One of the reason I love MySQL is the fact that it is so easy to install. Take the MSI, run it, invoke MySQL Server Instance Configuration Wizard and done. I adore this simplicity and this enables me to focus on core problems rather than setting up the database server. However none of the system is bullet proof and so at times MySQL installation does fail. Today I am going to discuss one such failure.

First let me explain the scenario which led to failure as the scenario is of extreme importance. On my WINCACHE test server I was having an old install of MySQL and one day I decided that I will move to the latest version. I uninstalled the old MySQL and re-installed the new  version. And damn I got the error from MySQL Server Instance Configuration Wizard that ‘Security Settings cannot be applied.’. The reason specified was that the Wizard was unable to connect to the MySQL database with credentials supplied.

After some research I found that, MySQL configured by me was storing the InnoDB (actual database files) at location which is %ProgramData%\MySQL\MySQL Server 5.1 folder. MySQL allocates space here where it stores the databases one creates using CREATE DATABASE command. If you just do a bare minimum MySQL installation and login to MySQL using the MySQL command line utility and run the command SHOW DATABASES you will see a database named ‘mysql’ already present. Your MySQL password is stored here in ‘mysql’ database. Now when you uninstall MySQL, only the binaries gets uninstalled from your machine. The database created remains in tact. If you want to find out where your data directory is, open MySQL command line and type SHOW VARIABLES command. Now look for variable named ‘datadir’. The value of this identifies the location where MySQL has stored your databases. For me it was pointing to %ProgramData%\MySQL\MySQL Server 5.1. Remember that it can be a hidden directory at times, so you will need to enable ‘Show hidden files and folder’ in Windows Explorer under Tools->Folder Options->View. The other way to look at the data folder (and in case you have already uninstalled MySQL and no MySQL command line utility is available) is to open the the file my.ini which is not removed by the installer and is present in the MySQL install folder. Open this file and look for ‘datadir’ under ‘Server Section’.

This seems to be a good behavior as you do not want the entire database to be deleted as part of un-installation because you may want to upgrade as was my case. Because of this if you are re-installing MySQL after un-installing it and using a different password than the last time, MySQL Server Instance Configuration Wizard fails because as told the password is already written in ‘mysql’ database and it is not matching with supplied password and hence settings cannot be applied.

There are few solutions to this:

• Use the same password as last time. And re-configure the instance using MySQL Server Instance Configuration Wizard.
• Delete the data pointed by variable named ‘datadir’. However please be careful as this means you are getting rid of all your old database. But if you are unable to remember the old password, probably you do not need the old database.

Hope this helps few of you and till we meet again ‘Good Bye’.

Thanks,

Don.

PS: I have disabled anonymous comments on my blog as anonymous comments are getting deleted periodically and the comment section looks odd. So please login and comment. Thanks for understanding.