Bug #52054 Case (non-)sensitivity for SHOW DATABASES does not work correctly on Windows

• Submitted: 15 Mar 2010 6:54
• Modified: 15 Mar 2010 6:56
• Reporter: Roel Van de Paar
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.1.42
• OS: Windows
• CPU Architecture: Any

[15 Mar 2010 6:54] Roel Van de Paar

Description:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases like 'test';
+-----------------+
| Database (test) |
+-----------------+
| test            |
+-----------------+
1 row in set (0.03 sec)

mysql> show databases like 'tesT';
Empty set (0.00 sec)

mysql> show databases like 'TesT';
Empty set (0.00 sec)

mysql> show databases like 'TEST';
Empty set (0.00 sec)

mysql> show global variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

How to repeat:
create database test;
show databases like 'test';
show databases like 'TEST';

Suggested fix:
Fix how SHOW DATABASES works. SHOW TABLES works correctly:

mysql> create table test (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> show tables like 'test';
+-------------------------+
| Tables_in_test12 (test) |
+-------------------------+
| test                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> show tables like 'tesT';
+-------------------------+
| Tables_in_test12 (tesT) |
+-------------------------+
| test                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> show tables like 'TEST';
+-------------------------+
| Tables_in_test12 (TEST) |
+-------------------------+
| test                    |
+-------------------------+
1 row in set (0.00 sec)

[15 Mar 2010 7:03] Roel Van de Paar

Maybe we need a lower_case_database_names variable, or lower_case_table_names should include databases.

[15 Mar 2010 8:44] Peter Laursen

I think that lower_case_tables names does (or should) include database names.

Refer:\\http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

If set to 1, table names are stored in lowercase on disk and table name comparisons are not case sensitive. If set to 2 table names are stored as given but compared in lowercase. This option also applies to database names and table aliases." (but not to TRIGGERS what I consider a bug).