MySQL Bugs: #53797: mysql should ignore hidden directories under the data directory

Bug #53797	mysql should ignore hidden directories under the data directory
Submitted:	19 May 2010 11:19	Modified:	24 May 2013 11:59
Reporter:	Daniël van Eeden	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.43	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	compatibility, netapp, nfs, regression, ZFS

Description:
We use a NetApp Filer as NFS storage. We mount a NetApp volume as our data directory. NetApp per default has a .snapshot directory for snapshots. 

This will cause these messages in the errorlog:
100519 13:09:47 [ERROR] Invalid (old?) table or database name '.snapshot'

And it will showup in the list with databases.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.snapshot |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

The ZFS filesystem uses .zfs for snapshots and presumably has the same issue.

How to repeat:
Create a hidden directory in the data directory

Suggested fix:
Ignore hidden directories in the data directory. This could also be an option in the my.cnf

This will improve compatibility with NetApp NFS and ZFS.

Added zfs as tag

Thank you for the bug report.

From http://dev.mysql.com/doc/refman/5.1/en/create-database.html :
"If you manually create a directory under the data directory (for example, with mkdir), the server considers it a database directory and it shows up in the output of SHOW DATABASES."

A deployment guide for MySQL would be nice or perhaps something like the Oracle OFA (Optimal Flexible Architecture). OFA is a set of standards about where to mount filesystems for the Oracle Database.
More information about OFA: http://download.oracle.com/docs/cd/B19306_01/install.102/b15660/app_ofa.htm

This is actually a regression - 5.0 did not display hidden directories.

(Confirmed with mysql-enterprise-gpl-5.0.84sp1-linux-x86_64-glibc23.tar.gz)

Workaround: make sure your data directory is a subdirectory of your NetApp volume.
This is a request for an incompatible change, since .zfs or .snapshot is a legal database name.

The comment from Konstantin Osipov is not true.

A "CREATE DABASE `.zfs`" will create a directory named "@002ezfs" in the datadir.

According to the docs[1] this is only valid for 5.1.6 and newer.

[1] - http://dev.mysql.com/doc/refman/5.1/en/identifier-mapping.html

Daniel,
correct, in 5.1 and up MySQL does not create .zfs directory. But this name may come from an older data directory, 5.0 and below.
That's what I meant.

Is this going to be fixed?  It has plagued us for 2 years.  We have been forced to configure the NetApp to hide the .snapshot directory from the NFS clients by setting "vol options <vol> nosnapdir on", but then we lose the huge benefit of having it available to the clients.  If MySQL would just ignore directories starting with ".", or provide an option to ignore certain directories when it scans its database directory on startup or upgrade, that would do the trick!

Agreed, there should be an option to ignore certain directories in the datadir!

See the --ignore-db-dir option introduced in

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