MySQL Bugs: #41981: Filesystem .snap directory appears as a database

Bug #41981	Filesystem .snap directory appears as a database
Submitted:	8 Jan 2009 20:24	Modified:	28 Mar 2015 16:22
Reporter:	Gary Thornock	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	FreeBSD
Assigned to:		CPU Architecture:	Any

Description:
On a FreeBSD system where /var/db/mysql is located on its own separate UFS2 filesystem (which normally has a .snap directory), the .snap directory in the root of the filesystem now appears as a database in MySQL.

How to repeat:
1: Set up MySQL 5.1.30 on a FreeBSD system with the data directory (/var/db/mysql is the FreeBSD default) located on its own UFS2 filesystem.
2: SHOW DATABASES
3: See that there is a database called #mysql50#.snap corresponding to the UFS2 .snap directory

Suggested fix:
Directories that are part of the standard structure of the filesystem (.snap, Lost+Found, Windows' "System Volume Information", etc.) should not appear as databases in MySQL, even when the data directory is the top level of a mounted filesystem.

This is a known problem (see http://dev.mysql.com/doc/refman/5.0/en/create-database.html about any directory under the data directory, for example) that should be fixed some day.

This resembles the documentation referenced, but it still appears to be a new problem in that the 5.0.x series didn't do this (at least with default directories created by the OS).

This bug seems to completely break mysqldump --all

mysqldump: Got error: 1102: Incorrect database name '#mysql50#.snap' when selecting the database

This is related to bug #53797

Isn't this fixed with the introduction of --ignore-db-dir?

This is a duplicate of

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

The fix is the same, use the new --ignore-db-dir option that is available in 5.6.3 and later to hide any directories you want to hide.

If you don't agree that it is a duplicate please let us know, else we'll tag it as duplicate and close it in a few months.

James Day, MySQL Senior Principal Support Engineer, Oracle

Fixed in 5.6.3. 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.
Make comment private