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:Microsoft Windows (Windos, Mac, Linux if FS is ci)
Assigned to: Sergei Glukhov CPU Architecture:Any

[27 Jul 2006 15:07] Andrey Hristov
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 ^^^^^
[27 Jul 2006 15:29] MySQL Verification Team
Thank you for the bug report. Verified as described.
[9 Aug 2006 15:08] Iggy Galarza
Also see bug#20356
[17 Dec 2007 6:38] Bugs System
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'
[24 Dec 2008 17:25] Bugs System
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'
[23 Mar 2009 1:55] Paul DuBois
Noted in 6.0.11 changelog.

SHOW CREATE DATABASE did not account for the value of the
lower_case_table_names system variable.
[12 May 2010 11:17] Bugs System
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
[16 Aug 2010 6:40] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:20] Bugs System
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)
[20 Nov 2010 22:42] Paul DuBois
Noted in 5.6.1 changelog.