MySQL Bugs: #73391: Mysql upgrade removed all databases

Bug #73391	Mysql upgrade removed all databases
Submitted:	25 Jul 2014 12:24	Modified:	11 Aug 2022 12:05
Reporter:	Markus Hedlund	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	5.6.21	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
Upgraded to 5.6.19 from .14 (I think) and all databases was lost. Good thing I made a copy of the data folder prior to upgrade. Is this intended or a bug?

I will happily provide more information as needed.

How to repeat:
Upgrade Mysql through the .dmg

Thank you for the report.

Please provide full MySQL server error log file.

Thank you for the feedback.

Error log does not contain messages about missed database (I expected it will complain about missed mysql database at least). Please send us output of `ls -R /usr/local/mysql/data`

Thank you for the feedback.

I see all your databases are in place. Why do you think MySQL removed all databases?

I of course restored the backuped data files immediately, as I could not work without my databases. The database files for my databases had been removed after the upgrade; they were not there anymore.

Thank you for the feedback.

But we need output of ls for problematic case, not after you solved the problem. I ask, because error log does not complain about missed databases and it certainly would if database mysql was not there.

Sorry, I was not clear enough: *my* databases had been removed. The default mysql databases were still there. Like the install script had removed my installation and data files completely and replaced them with a clean install.

Thank you for the feedback.

Was it possible that you used custom datadir location before upgrade?

Not to my knowledge.

Please provide the steps you did in the upgrade process. I did an upgrade from
5.6.19 to 5.6.20. The data was keep as:

[11:04:49][pochita:]~ miguel$ ls /usr/local/mysql-5.6.19-osx10.7-x86_64/data/
Pochita.local.err	XDB			ib_logfile0		ibdata1			performance_schema	test
Pochita.local.pid	auto.cnf		ib_logfile1		mysql			pochita.err
[11:04:59][pochita:]~ miguel$

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I downloaded the latest dmg from http://dev.mysql.com/downloads/mysql/ for OSX 10.9 and installed it.

Thank you for the feedback.

I finally got Mac OS X 10.9 and could verify the bug. Bug exists and verified.

To repeat:

1. Install 5.6.20 pkg, startup item and prefPane
2. Start 5.6.20 from he prefPane
3. Create database and table:

mysql> create database bug;
Query OK, 1 row affected (0.00 sec)

mysql> use bug
Database changed
mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bug                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

4. Do not stop 5.6.20 and install 5.6.21 pkg
5. Stop the server. I tried to use prefPane, but it did not work, so I run `sudo killall mysqld`
6. Start the server
7. Connect to the MySQL server and find that no database, called bug, exists:

Svetas-MacBook-Pro:~ sveta$ /usr/local/mysql/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Svetas-MacBook-Pro:~ sveta$ ls /usr//local/mysql/data/
Svetas-MacBook-Pro.local.err	auto.cnf			ib_logfile1			mysql				test
Svetas-MacBook-Pro.local.pid	ib_logfile0			ibdata1				performance_schema

I am sorry: database exists, simply new link is created.

Svetas-MacBook-Pro:src sveta$ ls -l /usr/local/ | grep mysql
lrwxr-xr-x   1 root   wheel     27 Oct 16 23:15 mysql ->
mysql-5.6.21-osx10.8-x86_64
drwxr-xr-x   4 root   wheel    136 Oct 18 13:30 mysql-5.6.19-osx10.7-x86_64
drwxr-xr-x   4 root   wheel    136 Oct 18 13:30 mysql-5.6.20-osx10.7-x86_64
drwxr-xr-x  17 root   wheel    578 Oct 18 13:30 mysql-5.6.21-osx10.8-x86_64

Old databases are still in place. However, since this is GUI installer would
be good if we:

1. Show alert dialog about new link is created and databases should be copied
2. Document this behavior in our user reference manual.

So I am leaving this report "Verified", although now I think it should have
lower priority.

Posted by developer:
 
I'm closing this bug mostly because it's old, but it's also based on a misunderstanding. The installer doesn't delete existing data directories when installing a new version of MySQL Server, but it does create a new data directory in a different location for the new server. So old date must be migrated manually. We are considering a different approach to installation on macOS that can accommodate upgrades better, but the behaviour of the existing installers will not be changed.