MySQL Bugs: #61814: Users and databases disappear after upgading from an earlier version

Bug #61814	Users and databases disappear after upgading from an earlier version
Submitted:	10 Jul 2011 16:52	Modified:	11 Jul 2011 17:37
Reporter:	Anthony Marston	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S1 (Critical)
Version:	5.5.14	OS:	Windows (XP 32 bit)
Assigned to:		CPU Architecture:	Any

Description:
This is a follow-on from bug #61812 and bug #61813.

After upgrading to version 5.5.14 the Instance Config Wizard does not start automatically, so I start it manually. It then refuses to upgrade the existing instance and wants me to create a new one, which I do not want to do.

I reboot my system and see afterwards that the server instance now running is version 5.5.14, but I also see that all my users and all my application databases have disappeared. I recover from this dreadfull situation by replacing the existing data/mysql directory with a backup version.

THIS ERROR HAS APPEARED WITH EVERY VERSION SINCE 5.5 WAS RELEASED, AND IT IS BEGINNING TO P*SS ME OFF! IT USED TO WORK BEFORE, SO WHAT HAS GONE WRONG?? WHO HAS MEDDLED WITH WORKING CODE AND MADE IT NOT WORK??

How to repeat:
Upgrade from an earlier version to 5.5.14 and see what happens with the instance config wizard.

Antony, could you move you data directory to be different from <installation-root>/data. There are several ways to do it, probably the easiest one is to stop the service, and edit my.ini in *elevated* Notepad.

The reason you get the error is that you're using a location that was thought to only contain a copy of a clean initial database.
My thought behind installing a clean copy of initial database was this - MySQL on Windows, does not provide user with a convenient way to create a new instance, other than copy initial database directory and register database service using for example mysqld.exe --install. There is no Unix-alike mysql_install_db.exe (unless you're using MariaDB). So there must be a clean copy of data installed somewhere (I chose <install-root>\data), and just for ConfigWizard there must be another copy installed - by default, this directory would be under C:\ProgramData\MySQL\MySQL Server x.y\data .
The "clean" data is maintained by the installer, and gets updated with upgrade, the readwrite data is not maintained by the installer upon initial installation. Now, if you chose database data directory to be the same as clean data directory (which I strongly believe is what happened in your case), replacing system tables with pristine tables will happen on upgrade.

Admittedly, it might have not been the best idea to install clean data under <install-root>\data, so maybe somebody else after me can fix it. I cannot fix it anymore, as I'm not longer with Oracle. (But I have done my best to avoid this kind of problems in MariaDB installer)

I have always held my data in <install-root>\data ever since 2002 and have not had problems with any upgrade, so why is it only causing a problem now?

I shall move it to a folder outside <install-root> to see if that makes a difference.

I posted this some time ago:
http://bugs.mysql.com/bug.php?id=56926

The 'clean' and unused datadir in installation folder was a bad idea from the beginning in my opinion.  The MySQL 5.1 installer did not do it.  Why was it then introduced in 5.5? It is confusing and may lead to data loss (as this report tells).

I think it is explained in bug #56926 *why* this was done in 5.5.x. Vlad also tried to explain his reasons to add this feature. 

Please, check if you have any upgrade problems if datadir is different from that data subdirectory. I've just upgraded from 5.5.11, and got no real problems (or data loss) in the process, having datadir in location assumed by config. wizard since early 5.5.x days.

I have moved my data directory to "F:\MySQL\Data" instead of "F:\MySQL\MySQL Server 5.5\Data" and it now works OK.