MySQL Bugs: #20356: Names containing UPPERCASES get LOWERCASE'd

Bug #20356	Names containing UPPERCASES get LOWERCASE'd
Submitted:	9 Jun 2006 11:55	Modified:	20 Jun 2007 13:03
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.22 + 5.1.11	OS:	Microsoft Windows (Windows, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	qc
Triage:	Triaged: D3 (Medium) / R3 (Medium) / E2 (Low)

Description:
On Windows when creating a VIEW having UPPERCASE(s) it its name and based on tables also having UPPERCASE(s) in their names, *SHOW CREATE TABLE* returns only LOWERCASES for the VIEW itself and the TABLES used for its construction.

This makes it difficult to use a Windows machine with MySQL as a developer machine when using VIEWs (that can be very complex with several (hundreds) UNIONs and JOINs etc.) and then port it to a production machine on *nix where UPERCASES are used for TABLE and VIEW names.

IMHO it also is both an (un-necessary ?) over-simplification and a misunderstanding of how the 'case-insensivity' of Windows should be understood.

How to repeat:
CREATE TABLE `test`.`TableName1` (`id` bigint NOT NULL ,`t` varchar (20) NULL);
CREATE VIEW `test`.`ViewName1` AS ( SELECT * FROM TableName1);
SHOW CREATE TABLE TableName1;
/*
CREATE TABLE `TableName1` ( `id` bigint(20) NOT NULL,`t` varchar(20) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1
*/
SHOW CREATE TABLE ViewName1;
/*
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `viewname1` AS (select `tablename1`.`id` AS `id`,`tablename1`.`t` AS `t` from `tablename1`)
==> note that `ViewName1` becomes`viewname1` and `TableName1` becomes `tablename1`
*/

Suggested fix:
Let 'SHOW CREATE TABLE' return the LETTERCASE used when creating the VIEW.

If this in 'not a bug' according to the docs, then change the docs, so it will become a bug!  :-)  It is really annoying!

Thank you for a problem report. Sorry, but table names are not case-sensitive on Windows (as filesystem is not case-sensitive):

mysql> CREATE TABLE `test`.`TableName1` (`id` bigint NOT NULL ,`t` varchar (20)
NULL);
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE VIEW `test`.`ViewName1` AS ( SELECT * FROM TableName1);
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE TableName1;
+------------+------------------------------------------------------------------
-----------------------------------------------------------+
| Table      | Create Table
                                                           |
+------------+------------------------------------------------------------------
-----------------------------------------------------------+
| TableName1 | CREATE TABLE `tablename1` (
  `id` bigint(20) NOT NULL,
  `t` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------
-----------------------------------------------------------+
1 row in set (0.00 sec)

This is the reason for the behaviour you described.

I perfectly know that tablenames a case-insensitive on Windows, but that was not the point!

Windows default for 'lower_case_table_names' is '2' what means 'Store as Created, Case-insensitive'.  That is default because that is how the file system is and when not explicitly set 'lower_case_table_names' takes its value from 'lower_case_file_system'. 

However they are STORED AS CREATED.  
Why can't the then be RETURNED AS CREATED?

I really doubt that you tried to understand the point!

Also see attached picture for 'MySQL Administrator' that shows the meaning of the various settings for 'lower_case_table_names'.  Default on Windows is not '1' - it is '2'

lower_case_table_names settings

Attachment: case.jpg (image/jpeg, text), 6.66 KiB.

This:

"Windows default for 'lower_case_table_names' is '2' what means 'Store as
Created, Case-insensitive'."

is not true. Neither according to the manual (http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html):

"On Windows the default value is 1."

nor according to the real defaults I had in my test presented previously:

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

So, please, send the results of

show variables like '%case%';

from the mysql command line client (or whatever tool used to perform that CREATE TABLE and CREATE VIEW). If lower_case_table_names=2 there, it is surely a bug.

-- on my system

show variables like '%case%';

/* returns

Variable_name           Value 
----------------------  ------
lower_case_file_system  ON    
lower_case_table_names  2 
*/

-- but you are right I myself have explicitly set it '2' it the my.ini. I had totally forgotten.  Was long ago I did this. My apology!

Now see this (4 examples):

Show create table tablename1;
/*returns
CREATE TABLE `tablename1` (`id` bigint(20) NOT NULL,`t` varchar(20) default NULL  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 
*/

Show create table TableName1;
/*returns
CREATE TABLE `TableName1` (`id` bigint(20) NOT NULL,`t` varchar(20) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1
*/

Show create table viewname1;
/*returns
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `viewname1` AS (select `tablename1`.`id`
*/

Show create table ViewName1;
/*returns
viewname1  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `viewname1` AS (select `tablename1`.`id` AS `id`,`tablename1`.`t` AS `t` from `tablename1`)
*/

Table was created as 'TableName1' and View is created as 'ViewName1'.

Is that expected behaviour?  I have those objections:

1)Show create table for a TABLE returns the CASE of the show create statement itself, not how it is (or should be) stored.
2)Show create table for a VIEW always returns LOWERCASE .. for the View itself and for the Tables(s) used for its construction.

I did not try with 'lower_case_table_names' = '0';  Maybe I'll get the time tonight.

BTW: I think that you know perfectly well that I think that life is too short for MySQL command-line client :-)

-- with those settings for '%case%'
show variables like '%case%';

/*
Variable_name           Value 
----------------------  ------
lower_case_file_system  ON    
lower_case_table_names  0 
*/ 

create table `TableName1` (`id` bigint NULL ,`t` varchar(20) NULL);
Show create table tablename1;
/* returns
CREATE TABLE `tablename1` (`id` bigint(20) default NULL,`t` varchar(20) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 
*/

Show create table TableName1;
/* returns
CREATE TABLE `TableName1` (`id` bigint(20) default NULL,`t` varchar(20) default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1
*/

Let me add that all tables in above examples were MyISAM.

-- but here is then an exercise with InnoDB

Show variables like '%case%';
/*returns

Variable_name Value
---------------------- ------
lower_case_file_system ON
lower_case_table_names 2
*/

create database `DDDD`;
use DDDD;
create table `TTTT` (`id` bigint NULL);
alter table `TTTT` Engine = INNODB;
show tables;
/*returns

Tables_in_dddd
--------------
TTTTT
*/

show databases:
/* returns

Database
------------------
information_schema
DDDD
etc ...
*/

And both of those:
show create table `tttt`;
show create table `TTTT`;
.. do exactly as MyISAM tables do,

Now docs (your link) says on this
InnoDB table names are stored in lowercase, as for lower_case_table_names=1"
To me it looks as if it should read 'InnoDB database names are stored in lowercase'. But not consisstent either!

This is going to be funny when we have 20 storage engines more! :-)

From the docs:
"To avoid data transfer problems stemming from lettercase of database or table names ... Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect." == > This is basically what I request should work so that systems can be developed on Windows abd be ported to *nix with very minimal change!

Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-community-nt-log

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

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

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

mysql> create view `ViewNameCS1` as select * from `TableNameCS1`;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table TableNameCS1;
+--------------+----------------------------------------------------------------
--------------------------------+
| Table        | Create Table
                                |
+--------------+----------------------------------------------------------------
--------------------------------+
| TableNameCS1 | CREATE TABLE `TableNameCS1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+----------------------------------------------------------------
--------------------------------+
1 row in set (0.00 sec)

mysql> show create table ViewNameCS1;
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| View        | Create View

        |
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| viewnamecs1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURI
TY DEFINER VIEW `viewnamecs1` AS select `tablenamecs1`.`c1` AS `c1` from `tablen
amecs1` |
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
1 row in set (0.01 sec)

mysql> show create table tablenamecs1;
+--------------+----------------------------------------------------------------
--------------------------------+
| Table        | Create Table
                                |
+--------------+----------------------------------------------------------------
--------------------------------+
| tablenamecs1 | CREATE TABLE `tablenamecs1` (
  `c1` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+----------------------------------------------------------------
--------------------------------+
1 row in set (0.00 sec)

mysql> show create table viewnamecs1;
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| View        | Create View

        |
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| viewnamecs1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURI
TY DEFINER VIEW `viewnamecs1` AS select `tablenamecs1`.`c1` AS `c1` from `tablen
amecs1` |
+-------------+-----------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
1 row in set (0.00 sec)

This is inconsistent and, thus, a bug.

Also see bug#21317

Same on Mac OS X: look Bug #25991
Linux version is not affected.

The fix seems to take very long time!

and actually with 5.0.41 it has become even worse!
Now SHOW CREATE TABLE always returns lowercase!

select version();
/*
version()          
-------------------
5.0.41-community-nt
*/

show variables like 'lower_case_table_names';
/*
Variable_name           Value 
----------------------  ------
lower_case_table_names  2    
*/

show tables like 'parenttable';
/*
Tables_in_t4 (Parenttable)
--------------------------
Parenttable 
*/

/*alternatively*/
show create table childtable;
/*
CREATE TABLE `childtable` ( 
`id` bigint(20) NOT NULL, 
`keyrow` bigint(20) default NULL,  
PRIMARY KEY  (`id`), 
KEY `FK_Childtable` (`keyrow`),  CONSTRAINT `FK_Childtable` 
FOREIGN KEY (`keyrow`) REFERENCES `parenttable` (`id`) ) 
ENGINE=InnoDB DEFAULT CHARSET=latin1  
*/

Yuck!
It's still here in version 5.0.45 (5.0.45-community-nt).

Tripped over this one. No variables' changes seem to help - we have to re-edit all "show create" statements by hand (!?).

on Mac X, there is a issure almost same as this one:

when table name include uppercase letters, 
SELECT database( ) return all lowercase,
show databases     return correct name

didn't try on windows, maybe same results. 

It's fine on linux.

I've just joined the world of MySQL, having landed a new job where our enterprise is 80% SQL Server and 20% MySQL 5.x

I too am having trouble with this lower_case_table_names issue. In my humble opinion, nothing should ever be 'forced', especially something like this.

Case in point: I backed up a database residing on a Linux box and restored it to a Windows MySQL server. Now all my table names are lower-case and it appears I need to perform some sort of demon-ritual sacrifice to set my variables properly (Still haven't figured that one out)

I've seen the following "Solution" thrown around frequently on this issue as well... "You should just use standard naming conventions". While this may be theoreticallly correct it is perfectly useless in the real world. 

In my opinion, nothing at all should be changing schema. If my tablenames are in camelback notation on a linux server, they should be camelback notation when I port that database to a Windows box. Period. 

Maybe that's not considered 'best practices', but that's not for MySQL to decide.

I have similar problem when using CREATE TABLE TableName LIKE OtherDatabase.TableName on OS X. MySQL version 5.0.88.

If I do this:
USE DB1;
CREATE TABLE DomainComponentLOP LIKE DB2.DomainComponentLOP;

and then SHOW TABLES, I get:
domaincomponentlop

if I do SHOW CREATE TABLE, I get:

*************************** 1. row ***************************
       Table: domaincomponent
Create Table: CREATE TABLE `domaincomponent` (
  `displayId` varchar(10) NOT NULL,
  `displaySequence` int(11) default NULL,
  `id` int(11) NOT NULL,
  `idDomain` int(11) NOT NULL,
  `title` varchar(255) default NULL,
  `rationaleExplanation` longtext,
  `idSpecialistType` int(11) default NULL,
  `status` int(11) default '1',
  `idRubric` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `DomainComponent_domain_FK` (`idDomain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Basically this completely messes us up since our apps are developed on OS X and deployed on Linux.

My settings are:

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

oh well, off to figure out a workaround......

Here is a Workaround for OS X lowercasing of table names when using CREATE TABLE LIKE statement:

CREATE TABLE _Table1 LIKE OtherDatabase.Table1;
RENAME TABLE _Table1 TO Table1;

This will work on OS X and Linux and give the same end result on both platforms.

This still seems to be a problem.

Unfortunately, on the Windows config that it relates to, 

RENAME TABLE `mixedcase` TO `MixedCase`;

Will usually just give the error "Error Code: 1050. Table 'mixedcase' already exists".

The obvious workaround is to do this in 2 steps:

RENAME TABLE `mixedcase` TO `MixedCase$`;
RENAME TABLE `MixedCase$` TO `MixedCase`;

Or if you are just creating the view from scratch,

CREATE OR REPLACE VIEW MixedCase$ ...;
RENAME TABLE `MixedCase$` TO `MixedCase`;

Not elegant ... but it works ... for me.

I'm writing ORM for Node.js and views being forced to lower case makes it impossible to read the correct case from MySQL to create appropriately named DAO's.

Example:

show variables like 'vers%';

    version,5.6.17
    version_comment,"MySQL Community Server (GPL)"
    version_compile_machine,x86_64
    version_compile_os,Win64

show variables like '%case%';

    lower_case_file_system,ON
    lower_case_table_names,2

create view MyView as select 1;

    [2014-10-06 00:24:38] completed in 19 ms

select * from information_schema.views;

    myview

show tables;

    myview

Same issue for me, ten years no fix?