MySQL Bugs: #81974: lost+found directory causes mysql

Bug #81974	lost+found directory causes mysql_upgrade to fail
Submitted:	22 Jun 2016 13:50	Modified:	23 Jun 2016 13:20
Reporter:	Björn Voigt (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.30	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	datadir, MySQL

Description:
Some people like to install the MySQL data directory (default: /var/lib/mysql) on it's own filesystem. This may be a fast SSD storage for instance.

The common EXT4 filesystem on Linux creates a "lost+found" directory on top of each EXT4 filesytem.

During filesystem checks, the lost+found directory may fillup with files.

mysql_upgrade misinterprets the lost+found directory as a database. MySQL upgrades fail on this directory. Also the user can't drop the "lost+found database" if it contains files.

I found this bug in MySQL 5.5.47 (SLES) and 5.6.30 (openSUSE Tumbleweed).

How to repeat:
1) Check, which datadir is used. I use /var/lib/mysql here.

2) Create and mount an EXT4 filesystem on /var/lib/mysql.
To simulate this, create a directory "lost+found" and one file:

mkdir /var/lib/mysql/lost+found
touch /var/lib/mysql/lost+found/file1

3) Run mysql_upgrade

4) If you run this command second time, you see error messages:

Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Failed to ALTER DATABASE `#mysql50#lost+found` UPGRADE DATA DIRECTORY NAME
Error: Can't create database 'lost+found'; database exists
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.

5) Start mysql client and list the databases

mysql
show databases;

You see databases like this:

6) Try to drop the useless databases. This only works for one database.

mysql
mysql> drop database `lost+found`;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database `#mysql50#lost+found`;
ERROR 1010 (HY000): Error dropping database (can't rmdir './lost+found/', errno: 17)

7) Check filesystem

The directory still exists and will again cause problems:

ls -l lost+found/
total 0
-rw-r--r-- 1 root root 0 Jun 22 15:40 file1

Suggested fix:
There are some alternative solutions:

1) Do not create databases from directories, which are no real MySQL databases. Some rules or heuristics is needed here.

I think, this is the best solution because it not only resolves the "lost+found" problem, but problems with other extra directories in datadir.

2) blacklist the lost+found directory

The mysql_upgrade/mysqlcheck functions can ignore lost+found with an blacklist.

Hello Björn Voigt,

Thank you for the report and feedback.
I'm not seeing the schema `#mysql50#lost+found`(I just tried in place binary upgrade of 5.5.50 to 5.6.31 and later mysql_upgrade which succedded) but observed same error while dropping the schema lost+found. 
Imho, similar issue was reported in Bug #22615 and the fix introduced for this was the option ignore-db-dir. Could you please see if this helps in your case? http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_ignore-db-dir

Thanks,
Umesh

Binary upgrades alone do not seem to trigger to bug, if the upgrade scripts do not call "mysql_upgrade".

For testing it is not necessary to have a version change of the MySQL server. It's enough to call "mysql_upgrade". Sometimes if is necessary to call "mysql_upgrade --force":

-f, --force         Force execution of mysqlcheck even if mysql_upgrade has
                    already been executed for the current version of MySQL.

I would saw now, that this bug is a duplicate of #22615.

I report this bug to openSUSE, so that MySQL start/stop scripts for versions >= 5.6.3 can be fixed there. Unfortunately also MySQL 5.5 versions can be found in SLES distributions, where --ignore-db-dir is unavailable.

>>I report this bug to openSUSE, so that MySQL start/stop scripts for versions >= 5.6.3 can be fixed there.

Sounds good to me, thank you for confirming.
Marking this as duplicate of Bug #22615

Thanks,
Umesh