MySQL Bugs: #21317: SHOW CREATE DATABASE does not obey to lower_case_table

Bug #21317	SHOW CREATE DATABASE does not obey to lower_case_table_names
Submitted:	27 Jul 2006 15:07	Modified:	20 Nov 2010 22:42
Reporter:	Andrey Hristov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0+, 4.1	OS:	Windows (Windos, Mac, Linux if FS is ci)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
 Hi,
according to the documentation "9.2.2. Identifier Case Sensitivity"
lower_case_table_names==2
"Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. Note: This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1."
The text above states that only lookups are lowercased, but SHOW CREATE DATABASE and probably others which use the C function check_db_name() have problems in this mode. Not only the lookup but presentation is still in lower case.

There is another bug reported similar to this one "#3329: Incomplete lower_case_table_names=2 implementation"

How to repeat:
mysql> show variables like "lower_case%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.41 sec)

mysql> create database mix_DB;
Query OK, 1 row affected (0.40 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| cluster            |
| db1                |
| db2                |
| dbxy               |
| many_tables        |
| mix_DB             |
| mixed              |
| mixed_DATABASE     |
| mysql              |
| slow_log           |
| test               |
| upper              |
| upper_case         |
+--------------------+
15 rows in set (0.42 sec)

mysql> show create database mix_DB;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| mix_db   | CREATE DATABASE `mix_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.42 sec)

See the name of the database ^^^^^^^^^

mysql> use mix_DB;
Database changed
mysql> create table mix_DB_table(a int);
Query OK, 0 rows affected (0.50 sec)

mysql> show create table mix_DB_table\G
*************************** 1. row ***************************
       Table: mix_DB_table
Create Table: CREATE TABLE `mix_DB_table` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.41 sec)

Here the name of the table is ok ^^^^^

Thank you for the bug report. Verified as described.

Also see bug#20356

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40082

ChangeSet@1.2583, 2007-12-17 10:33:43+04:00, gluh@mysql.com +6 -0
  Bug#21317 SHOW CREATE DATABASE does not obey to lower_case_table_names
  preserve lettercase as it specified in 'show create'

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62309

2811 Sergey Glukhov	2008-12-24
      Bug#21317 SHOW CREATE DATABASE does not obey to lower_case_table_names
      preserve lettercase for DB name as it specified in 'show create'

Noted in 6.0.11 changelog.

SHOW CREATE DATABASE did not account for the value of the
lower_case_table_names system variable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108109

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

Noted in 5.6.1 changelog.