MySQL Bugs: #51138: unknown database name when using some (soft link) names

Bug #51138	unknown database name when using some (soft link) names
Submitted:	12 Feb 2010 11:30	Modified:	21 Feb 2010 10:00
Reporter:	yoav morag	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.37-1ubuntu5 (Ubuntu), 5.1.45, 5.5.99	OS:	Linux (64 bit,ec2 machine)
Assigned to:		CPU Architecture:	Any
Tags:	Database name, error, regression, ubuntu

Description:
I have a database directory on a separate disk (/mnt/data/db/COM2) . 
when I create a link to it from datadir (the default /var/lib/mysql), if I use the name "COM2" mysql reports unknown databse - other link names seem to work ok, but any name of the format COM<single digit> seem to fail ! 

all tables within db are MyISAM.

How to repeat:

console output:
1. create 3 soft links pointing to same place : 

root@domU-12-31-39-06-C8-C1:/var/lib/mysql# ln -s /mnt/data/db/COM2 COM2
root@domU-12-31-39-06-C8-C1:/var/lib/mysql# ln -s /mnt/data/db/COM2 COM3
root@domU-12-31-39-06-C8-C1:/var/lib/mysql# ln -s /mnt/data/db/COM2 COM666

root@domU-12-31-39-06-C8-C1:/var/lib/mysql# ls -al

total 20540
drwxr-xr-x  4 mysql mysql     4096 2010-02-12 11:18 .
drwxr-xr-x 40 root  root      4096 2010-02-08 13:20 ..
lrwxrwxrwx  1 root  root        17 2010-02-12 11:16 COM2 -> /mnt/data/db/COM2
lrwxrwxrwx  1 root  root        17 2010-02-12 11:18 COM666 -> /mnt/data/db/COM2
lrwxrwxrwx  1 root  root        17 2010-02-12 11:19 COM3 -> /mnt/data/db/COM2
-rw-r--r--  1 mysql mysql        0 2010-02-08 13:20 debian-5.1.flag
-rw-rw----  1 mysql mysql 10485760 2010-02-12 11:08 ibdata1
-rw-rw----  1 mysql mysql  5242880 2010-02-12 11:08 ib_logfile0
-rw-rw----  1 mysql mysql  5242880 2010-02-08 13:20 ib_logfile1
drwxr-xr-x  2 mysql mysql     4096 2010-02-08 13:20 mysql
-rw-------  1 mysql mysql        6 2010-02-08 13:20 mysql_upgrade_info
drwx------  2 mysql mysql     4096 2010-02-08 13:22 TEST_DB

root@domU-12-31-39-06-C8-C1:/var/lib/mysql# mysql -e'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| COM2               |
| COM3               |
| COM666             |
| TEST_DB            |
| mysql              |
+--------------------+
root@domU-12-31-39-06-C8-C1:/var/lib/mysql# mysql -e'show tables from COM2'
ERROR 1049 (42000) at line 1: Unknown database 'COM2'
root@domU-12-31-39-06-C8-C1:/var/lib/mysql# mysql -e'show tables from COM3'
ERROR 1049 (42000) at line 1: Unknown database 'COM3'
root@domU-12-31-39-06-C8-C1:/var/lib/mysql# mysql -e'show tables from COM666'
+------------------+
| Tables_in_COM666 |
+------------------+
| DESCRIPTORS      |
| DISCOVERED       |
| HASHCODE1        |
| HASHCODE1_cnt    |
| HASHCODE2        |
| HASHCODE2_cnt    |
| HASHCODE3        |
| HASHCODE3_cnt    |
| HASHCODE4        |
| HASHCODE4_cnt    |
| LINKS            |
| MATCHGROUP       |
| MEDIA            |
| PROCESSED        |
| SCORE_TABLES     |
| THUMBNAILS       |
| VISITED          |
+------------------+

Suggested fix:
I would speculate there is some special internal usage for this numeric digit within mysql that should be eliminated ?

Thank you for the report.

Verified as described. Not repeatable with version 5.0.

Test case for the test suite:

--exec mkdir $MYSQL_TEST_DIR/var/tmp/COM2
--exec mkdir $MYSQL_TEST_DIR/var/tmp/COM3
--exec mkdir $MYSQL_TEST_DIR/var/tmp/COM666

--let $MYSQLD_DATADIR=`select @@datadir`
--exec ln -s $MYSQL_TEST_DIR/var/tmp/COM2 $MYSQLD_DATADIR/COM2
--exec ln -s $MYSQL_TEST_DIR/var/tmp/COM3 $MYSQLD_DATADIR/COM3
--exec ln -s $MYSQL_TEST_DIR/var/tmp/COM666 $MYSQLD_DATADIR/COM666

--exec ls -l $MYSQLD_DATADIR

show databases;
--error 1049
show tables from COM2;
--error 1049
show tables from COM3;
show tables from COM666;

Repeatable since very early 5.1 versions, so this is not recent regression.

related: bug #17870

COMn LPTn etc are reserved words in Windows so when handled directly MySQL ads @@@ to the names which does not work with Direct soft links.

Although these are devices in Windows - MySQL views these as reserved words on all platforms, to avoid issues with application migration and platform dependencies.

nice, no problems with this policy - but MySQL should report a proper error when trying to access one of these illegal db name, or disallow using these names altogether (i.e. eliminate the @@@ trick).