Bug #35084 Discrepancy in displayed TABLES.TABLE_SCHEMA
Submitted: 5 Mar 2008 16:04 Modified: 6 Mar 2008 10:34
Reporter: Marc Delisle Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.23/6.0 OS:Windows
Assigned to: Assigned Account CPU Architecture:Any

[5 Mar 2008 16:04] Marc Delisle
Description:
The database name for a database whose name starts with an uppercase character is sometimes returned in all lowercase, sometimes with the leading uppercase.

How to repeat:
(This is Windows-specific).
Server is running with
lower_case_file_system  ON
lower_case_table_names 	1

Create a database Shop and inside it, create a table "backup" and a table "buyers".

The table schema name is returned differently, depending on which of the following queries is used in the command-line mysql client:

Bug:
SELECT `TABLE_SCHEMA` , `TABLE_NAME` ,  `CHECKSUM` AS `Checksum`, `CREATE_OPTIONS` AS `Create_options`, `TABLE_COMMENT` AS `Comment` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` IN ('Shop') LIMIT 250 OFFSET 0 

Correct:
SELECT `TABLE_SCHEMA` , `TABLE_NAME` ,  `CREATE_OPTIONS` AS `Create_options`, `TABLE_COMMENT` AS `Comment` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` IN ('Shop') LIMIT 250 OFFSET 0
[6 Mar 2008 3:07] MySQL Verification Team
c:\dbs>5.0\bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.24-rc-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database Shop;
Query OK, 1 row affected (0.03 sec)

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

mysql> use Shop;
Database changed

mysql> create table `backup` (id serial);
Query OK, 0 rows affected (0.09 sec)

mysql> create table `buyers` (id serial);
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT `TABLE_SCHEMA` , `TABLE_NAME` ,  `CHECKSUM` AS `Checksum`, `CREATE_OPTIONS` AS
    -> `Create_options`, `TABLE_COMMENT` AS `Comment` FROM `information_schema`.`TABLES` WHERE
    -> `TABLE_SCHEMA` IN ('Shop') LIMIT 250 OFFSET 0 ;
+--------------+------------+----------+----------------+---------+
| TABLE_SCHEMA | TABLE_NAME | Checksum | Create_options | Comment |
+--------------+------------+----------+----------------+---------+
| Shop         | backup     |     NULL |                |         |
| shop         | buyers     |     NULL |                |         |
+--------------+------------+----------+----------------+---------+
2 rows in set (0.05 sec)

mysql> SELECT `TABLE_SCHEMA` , `TABLE_NAME` ,  `CREATE_OPTIONS` AS `Create_options`,
    -> `TABLE_COMMENT` AS `Comment` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` IN
    -> ('Shop') LIMIT 250 OFFSET 0;
+--------------+------------+----------------+---------+
| TABLE_SCHEMA | TABLE_NAME | Create_options | Comment |
+--------------+------------+----------------+---------+
| Shop         | backup     |                |         |
| Shop         | buyers     |                |         |
+--------------+------------+----------------+---------+
2 rows in set (0.00 sec)

mysql>
[6 Mar 2008 10:34] MySQL Verification Team
Thank you for the bug report.