MySQL Bugs: #1120: Table name case sensitivity

Bug #1120	Table name case sensitivity
Submitted:	22 Aug 2003 8:20	Modified:	27 Aug 2003 13:14
Reporter:	m guckeyson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	Ver 13.5 Distrib 4.1.0-alpha	OS:	pc-linux (i686) (mandrake 9.1)
Assigned to:		CPU Architecture:	Any

Description:
I have two tables, Transactions and transactions, in my db... except I don't. "show tables" shows both tables, but "desc transactions" returns Transactions information. There is no transactions.frm in the directory, yet the table create statement did not return an error or warning. 

I'm running 4.1.0 on linux and do not have the lower_case_table_names flag set, which if I understand correctly should have forced Transactions to transactions in the first place.

I can fix this temporarily by renaming the one table I have control over, but since I've got some tables that third party applications rely on, I'm concerned I'll have a future collision problem should they change their table names or we get a new partner for whatever reason.

How to repeat:
Create a table with a capitialized table name, <Table> (I've personally tried Transactions and Quotes and seen the same behavior). Then create a table with a lower-cased version of the same name, <table>. Now get a description of the lower case table name, which for me is returning the capitialized table's description. Note that there is no <table>.frm in the appropriate directory.

Suggested fix:
I don't have a suggestion on how to fix this, but if for whatever reason this is actually desired/intended behavior, I'd bloodly love some sort of warning after the table create statement for the second table gets executed.

I tried repeating the bug, but could not. Am I overlooking something?

mysql> create table Quotes (big int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table quotes (small int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_bug |
+---------------+
| Quotes        |
| quotes        |
+---------------+
2 rows in set (0.00 sec)

mysql> desc Quotes;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| big   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> desc quotes;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| small | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc Quotes;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| big   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

...

beta# rm quotes.frm
beta# ls -la
total 16
drwx------   2 mysql  mysql   512 Aug 23 00:12 .
drwxrwxr-x  75 mysql  mysql  4096 Aug 23 00:11 ..
-rw-rw----   1 mysql  mysql     0 Aug 23 00:11 Quotes.MYD
-rw-rw----   1 mysql  mysql  1024 Aug 23 00:11 Quotes.MYI
-rw-rw----   1 mysql  mysql  8558 Aug 23 00:11 Quotes.frm
-rw-rw----   1 mysql  mysql     0 Aug 23 00:11 quotes.MYD
-rw-rw----   1 mysql  mysql  1024 Aug 23 00:11 quotes.MYI

...

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql> desc quotes;
ERROR 1146: Table 'bug.quotes' doesn't exist

mysql> desc Quotes;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| big   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Nope, you're not missing a thing. However, *I* am:

mysql> create table Quotes (big int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table quotes (small int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_bug |
+---------------+
| Quotes        |
| quotes        |
+---------------+
2 rows in set (0.00 sec)

mysql> desc Quotes;
+-------+---------+-----------+------+-----+---------+-------+
| Field | Type    | Collation | Null | Key | Default | Extra |
+-------+---------+-----------+------+-----+---------+-------+
| big   | int(11) | binary    | YES  |     | NULL    |       |
+-------+---------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc quotes; !This isn't correct!
+-------+---------+-----------+------+-----+---------+-------+
| Field | Type    | Collation | Null | Key | Default | Extra |
+-------+---------+-----------+------+-----+---------+-------+
| big   | int(11) | binary    | YES  |     | NULL    |       |
+-------+---------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc Quotes;
+-------+---------+-----------+------+-----+---------+-------+
| Field | Type    | Collation | Null | Key | Default | Extra |
+-------+---------+-----------+------+-----+---------+-------+
| big   | int(11) | binary    | YES  |     | NULL    |       |
+-------+---------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

Now this is where it gets even stranger:

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> desc quotes;
+-------+---------+-----------+------+-----+---------+-------+
| Field | Type    | Collation | Null | Key | Default | Extra |
+-------+---------+-----------+------+-----+---------+-------+
| small | int(11) | binary    | YES  |     | NULL    |       |
+-------+---------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc Quotes;
+-------+---------+-----------+------+-----+---------+-------+
| Field | Type    | Collation | Null | Key | Default | Extra |
+-------+---------+-----------+------+-----+---------+-------+
| small | int(11) | binary    | YES  |     | NULL    |       |
+-------+---------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

Is there some other information I can send along to make this easier to debug? I'm trying to find out exactly how it was built... anything else I should round up?

mySQL is the binary from mysql.com... the 4.1.0-Max version.

OK, I confirmed this with our development team - this is fixed in our source
tree and will be fixed in 4.1.1.