Bug #3329 Incomplete lower_case_table_names=2 implementation
Submitted: 29 Mar 2004 22:23 Modified: 20 May 2009 7:26
Reporter: Sergei Kulakov (Candidate Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL 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: CPU Architecture:Any
Tags: qc
Triage: Triaged: D3 (Medium)

[29 Mar 2004 22:23] Sergei Kulakov
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.
[31 Mar 2004 10:49] Miguel Solorzano
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.
[1 Apr 2004 23:27] Sergei Kulakov
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
[4 Apr 2004 15:23] Miguel Solorzano
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.
[4 Apr 2004 23:12] Sergei Kulakov
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.
[27 Apr 2004 8:19] Michael Widenius
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.
[27 Jul 2006 15:08] Andrey Hristov
Similar issue is bug#21317 "SHOW CREATE DATABASE does not obey to lower_case_table_names"
[20 May 2009 7:26] Sveta Smirnova
Problem still exists in current version.