MySQL Bugs: #22615: MySQL Server incorrectly catogorizes the lost+found directory as a database

Bug #22615	MySQL Server incorrectly catogorizes the lost+found directory as a database
Submitted:	22 Sep 2006 20:53	Modified:	24 Sep 2011 16:21
Reporter:	Mike Muzinich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.0.18-4,5.1.34	OS:	Linux (RHEL AS 4 - Update 4, HPUX 11.x)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
After creating a dedicated logical volume and file system for /var/lib/mysql, mysqld incorrectly classifies the lost+found directory as a database location.  Though bug #15851 discusses this issue with regards to permissions with ensuring mysql has access to the directory, the code itself should ignore these directories as it is a reserved directory in the O.S. or at least make an option available in the configuration file to ignore it.

How to repeat:
Create a dedicated logical volume for /var/lib/mysql and show databases.

Thank you for a reasonable feature request.

Proposed patch sent to internals list:

http://lists.mysql.com/internals/34630

Another reason to implement this feature request is that it is not recommended to remove this directory on an ext3 file system.  If this folder is deleted and should some corruption occur, a forced fsck would likely fail or perhaps cause other problems.

Bug#42676 was marked a duplicate of this bug.

I don't think there is no workaround. Even though it is common practice to store the data on a dedicated mount, there is no specific reason why the data directory should be located at its top level. Moving it down one level is a very simple operation and an excellent workaround. (A simple configuration issue that can be addressed with little downtime.)

Above patch is not sufficient to close this bug report, as we will have similar situations on other platforms and file systems, and we will probably not manage to address all the oddities of every single file system out there. Maybe it is a good idea to make this configurable, i.e. introduce an option for mysqld that makes it ignore certain directory and file names in the data directory.

Above patch also fails to address that we must prevent creation of a database with a name that we ignore. Currently, `lost+found` is a perfectly valid name for a database and it will not be immediately apparent to users who are not familiar with UNIX file systems why trying to use it causes an error. Hence, applying the above patch could potentially cause a regression with data loss.

IMO, the proposed patch is sufficient.
True, the workaround of establishing the data directory one level down is sound, but it is not always applicable.
It is also true that 'lost+found' is a valid name for a database, but I question the wisdom of using such a name for a database.
Anyway, the patch could also be applied with a IFDEF testing for a Linux OS.

While I think the patch is very valid, I doubt that MySQL Enterprise users will ever be able to utilize it.  I suggest going the extra step to make it possible to configure MySQL to ignore certain directory names from the my.cnf file.  This way, the snippet can be expanded to ignore any directory name specified by the administrator.

Since we can't reliably rule out all files generated on a top level mount in different operating systems (and versions, and filesystems) and since there's a relatively good workaround (create a dedicated directory on the mount for mysql to use) I'm closing this request.

See also Bug#57049.

If you encounter this the manual now covers it at
http://dev.mysql.com/doc/refman/5.1/en/linux-installation-rpm.html :

"If you log in as the mysql user, you may find that MySQL displays “Invalid (old?) table or database name” errors that mention .mysqlgui, lost+found, .mysqlgui, .bash_history, .fonts.cache-1, .lesshst, .mysql_history, .profile, .viminfo, and similar files created by MySQL or operating system utilities. You can safely ignore these error messages or remove the files or directories that cause them if you do not need them. "

lost+found isn't mentioned directly there but the same advice covers it. It's most often encountered if you make the MySQL data directory the root directory of a mapped drive. Just move it to a subdirectory and that will fix the problem because the filesystem work files won't then appear in the MySQL directory.

OS-X creates .Trashes and .fseventsd, which causes the same problem.

You can use the same work-around, ie moving the datadir into a sub-directory.

I just wanted to comment to the idea that lost+found is a valid database name. Whether you are using command line mysql or Workbench it is displayed as #mysql50#lost+found which is not it's name.  You can't access as lost+found in the dataserver. Most backup scripts I have seen use some sort of shell script. Unfortunately the pound(#) symbol is a comment and anything from that point to the end of the line is ignored.  If you wanted to use it or something like it as a db you would have to exclude all other databases in your backup script.  The only conclusion I can see is that this is not valid as the documentation and error messages say.

Noted in 5.6.3 changelog.

mysqld now has a --ignore-db-dir option that tells the server not to
treat the named directory as a database directory in the data
directory. For example, if a MySQL configuration locates the data
directory at the root of a file system on Unix, the system might
create a lost+found directory there that the server should ignore.
Starting the server with --ignore-db-dir=lost+found accomplishes
that. 

To specify more than one database, use this option multiple times,
once for each database. Specifying the option with an empty value
(that is, as --ignore-db-dir=) resets the directory list to the empty
list. 

Instances of this option given at server startup are used to set the
ignore_db_dirs system variable.

Revised changelog entry:

mysqld now has a --ignore-db-dir option that tells the server to
ignore a given name for purposes of the SHOW DATABASES statement or
INFORMATION_SCHEMA tables. For example, if a MySQL configuration 
locates the data directory at the root of a file system on Unix, the
system might create a lost+found directory there that the server
should ignore. Starting the server with --ignore-db-dir=lost+found
causes that name not to be listed as a database.
      
To specify more than one name, use this option multiple times, once
for each name. Specifying the option with an empty value (that is, as
--ignore-db-dir=) resets the directory list to the empty list.
        
Instances of this option given at server startup are used to set the
ignore_db_dirs system variable.

Can this be backported to 5.5.x please?

Bug #81974 marked as duplicate of this

I would like to re-open this bug.

It's already clear, that the bug could be resolved with the option --ignore-db-dir=lost+found. But the MySQL packages directly contributed from MySQL/Oracle do not contain this option in startup scripts.

I can confirm this for the tested SLES package mysql-community-test-5.7.13-1.sles12.x86_64.rpm.