MySQL Bugs: #64211: 'CREATE TABLE ... LIKE ...' fails to keep case

Bug #64211	'CREATE TABLE ... LIKE ...' fails to keep case
Submitted:	2 Feb 2012 16:26	Modified:	9 Apr 2012 16:08
Reporter:	Josh Trow	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5.18, 5.5.20	OS:	Any (7 Pro x64, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	case, create, like

Description:
When creating tables LIKE another, the table name case is ignored.  I understand that Windows is a case-insensitive file system, but lower_case_table_names = 2 and regular CREATE TABLE statements work fine (as shown in the repro step below).

How to repeat:
mysql> CREATE TABLE TestTable1 ( field1 CHAR(8) NOT NULL, id BIGINT(20) NOT NULL ) ENGINE MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| TestTable1     |
+----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE TestTable2 LIKE TestTable1;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| TestTable1     |
| testtable2     |
+----------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE TestTable1;
+------------+------------------------------------------+
| Table      | Create Table                                                                                                             |
+------------+------------------------------------------+
| TestTable1 | CREATE TABLE `TestTable1` (
  `field1` char(8) NOT NULL,
  `id` bigint(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+------------+------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Properly keep the case of the table name as requested in the CREATE LIKE statement.

Old bug! Same with MySQL 5.0 and 5.1 with lower_case_table_names = 2. Annoying!

Peter
(not a MySQL person)

Verified with 5.5.20 on Mac OS X also:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table `TestTable1` (c1 int);
Query OK, 0 rows affected (1.19 sec)

mysql> show tables like 'test%';
+------------------------+
| Tables_in_test (test%) |
+------------------------+
| TestTable1             |
| test                   |
+------------------------+
2 rows in set (0.02 sec)

mysql> create table `TestTable2` like `TestTable1`;
Query OK, 0 rows affected (0.25 sec)

mysql> show tables like 'test%';
+------------------------+
| Tables_in_test (test%) |
+------------------------+
| TestTable1             |
| test                   |
| testtable2             |
+------------------------+
3 rows in set (0.03 sec)

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

Does not look consistent or documented.

Noted in 5.6.6 changelog.

With lower_case_table_names=2 on systems with case-insensitive file
systems such as Windows or Mac OS X, CREATE TABLE ... LIKE did not
preserve lettercase of the destination table name as given in the
statement.