MySQL Bugs: #3329: Incomplete lower_case_table

Bug #3329	Incomplete lower_case_table_names=2 implementation
Submitted:	30 Mar 2004 0:23	Modified:	20 May 9:26
Reporter:	Serguei Kulakov (Candidate Quality Contributor)
Status:	Verified
Category:	Server	Severity:	S3 (Non-critical)
Version:	4.0.18/4.0.19 Source, 5.0, 5.1, 6.0 bzr	OS:	Any (Windows, MacOSX)
Assigned to:		Target Version:
Tags:	qc
Triage:	Triaged: D3 (Medium)

Description:
As you might guess, I use lower_case_table_names=2 on Windows. It works as expected with
"create table", "show tables" and "show databases" for example. And here are 2 cases when
the setting seems to be neglected:
-the function Database() returns current database name in lowercase;
-Check table returns 2 rows (it may vary), the first of which says "Found 16953 parts
should be 0 parts" and mentions the table as "search.Rlstorgn", that's using lowercase
for the database; the second prints "check status OK" and ... "search.rlstorgn", that's
it uses lowercase for both table and database names (?!). 

How to repeat:
Create Database Search;
Use Search;
Select Database();

+------------+
| database() |
+------------+
| search     |
+------------+

Check Table Rlstorgn;
(I can't provide instructions to make a table faulty in this way, probably, every table
is permanently in this state on Windows)

...
search.Rlstorgn | check | warning  | Found 16953 parts should be 0 parts 
^
search.rlstorgn | check | status   | OK
^      ^(!)
...

Suggested fix:
I couldn't find the reason debugging the source. I guess there is some structure field
that keeps the current database name in lowercase whenever lower_case_table_names=2 or 0,
and the fact itself is not a bug, the probable bug is that it is used when the function
Database() is called. As for check table, I have no ideas, but I guess it's all around
using right fields and making right comparisons considering lower_case_table_names.

I only was able to repeat the behavior of  Select Database();
and not the check table command maybe I need a snapshot of
your table for or specific intructions for to build them.

I've uploaded 2 zipped tables at http://www.rlsnet.ru/CheckTables.zip. One of them is
correct, the other is faulty (in the sense that check table reports a problem but it
works okay). They should be in the database `Search`. For the correct table the output
is:

Check Table Search.BadFirmsPreps

Table Op Msg_type Msg_text 
search.badfirmspreps check status OK 

and for the faulty one the output is

Check Table Search.PunishmentTypes

Table Op Msg_type Msg_text 
search.PunishmentTypes check warning Found 4 parts Should be: 0 parts 
search.punishmenttypes check status OK 

In both cases the line that says OK uses lowercase for everything. This works on Windows
Me with MySQL 4.0.18

I tested your tables and seem to me that the Check Table command
reports adequately the warning messages because after a Repair
Table the warning goes away:

mysql> repair Table Search.PunishmentTypes;
+------------------------+--------+----------+----------+
| Table                  | Op     | Msg_type | Msg_text |
+------------------------+--------+----------+----------+
| search.punishmenttypes | repair | status   | OK       |
+------------------------+--------+----------+----------+
1 row in set (0.03 sec)

mysql> Check Table Search.PunishmentTypes;
+------------------------+-------+----------+----------+
| Table                  | Op    | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| search.punishmenttypes | check | status   | OK       |
+------------------------+-------+----------+----------+
1 row in set (0.00 sec)

anyway it printed the table's name as lowercase.

Thank you for the feedback.

Thanks for verifying, I don't care about the warnings, I just demonstrated where wrong
lettercase is used (even if some table is broken, its report should use correct database
name). Now I can add another case like that, it is now an output of Show Processlist: 

Id User Host db Command Time State Info 
37 ODBC localhost:1099   Sleep 70     
40 ODBC localhost:1102   Sleep 133     
41 ODBC localhost:1103 search Sleep 85     
42 root localhost:1106 logs Query 0

The problem here again is that the databases' names are "Search" and "Logs". Show
Databases prints them correct.

Sorry, but fixing this in 4.0 is not an option as this can easily introduce any number of
new bugs.

I have added this to the know bugs section in the manual for now.

Similar issue is bug#21317 "SHOW CREATE DATABASE does not obey to lower_case_table_names"

Problem still exists in current version.