MySQL Bugs: #60773: REFERENCES part of SHOW CREATE TABLE is always in lower case

Bug #60773	REFERENCES part of SHOW CREATE TABLE is always in lower case
Submitted:	6 Apr 2011 10:36	Modified:	11 Apr 2011 15:09
Reporter:	Sunil John	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.56, 5.1.46	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Triage:	Needs Triage: D2 (Serious)

Description:
Hi,
The getExportedKeys() and getImportedKeys() returns PKTABLE_NAME in lowercase  always when we set lower_case_table_names=0 or lower_case_table_names=2, eventhough tables are created in UPPER CASE

How to repeat:
code snippet to reproduce:

1. Execute the following scripts
CRAETE DATABASE TESTDB;
USE TESTDB;

CREATE TABLE `TESTPARENT`(`COL1` int primary key, `COL2` int, `COL3` dateTime, `COL4` varchar(100) );

CREATE TABLE `TESTCHILD`(`COL1` int primary key, `COL2` int, `COL3` dateTime, `COL4` varchar(100) );

CREATE TABLE `TESTGRANDCHILD`(`COL1` int primary key, `COL2` int, `COL3` dateTime, `COL4` varchar(100) );

ALTER TABLE `TESTGRANDCHILD` ADD CONSTRAINT `FK_TESTGRANDCHILD_TESTCHILD` FOREIGN KEY(`COL2`) REFERENCES `TESTCHILD`(`COL1`);

ALTER TABLE `TESTCHILD` ADD CONSTRAINT `FK_TESTCHILD_TESTPARENT` FOREIGN KEY(`COL2`) REFERENCES `TESTPARENT`(`COL1`);

2. Execute the below code snippet

String driverName = "com.mysql.jdbc.Driver"; 
String url = "jdbc:mysql://cin0389:3307/TESTDB?useInformationSchema=true"; 
String username = "root";
String password = "c0rdys";
String dbName = "TESTDB";
String tableName="TESTCHILD";
Connection 	connection = null;
Class.forName(driverName);
	        
connection = DriverManager.getConnection(url, username, password);
connection.setReadOnly(true);
connection.setAutoCommit(true);
connection.setCatalog(dbName);
	        
DatabaseMetaData jMetadata =  connection.getMetaData();
rsResultSet = jMetadata.getImportedKeys(dbName, null, tableName);
System.out.println("*********Imported Keys***********");
while(rsResultSet.next()){
	String pktableName = rsResultSet.getString("PKTABLE_NAME");
	String pkColumnName = rsResultSet.getString("PKCOLUMN_NAME");
	String fktableName = rsResultSet.getString("FKTABLE_NAME");
	String fkColumnName = rsResultSet.getString("FKCOLUMN_NAME");
	String fkName = rsResultSet.getString("FK_NAME");
	String pkName = rsResultSet.getString("PK_NAME");
	System.out.println("Primary Key Table:"+pktableName+" Column name : "+pkColumnName);
	System.out.println("Foreign Key Table:"+fktableName+" Column name : "+fkColumnName);
        	}

3. Check the result
*********Imported Keys***********
Primary Key Table:testparent Column name : COL1
Foreign Key Table:TESTCHILD Column name : COL2

The same is the case with getExportedKeys().

But for getIndexInfo(),getPrimaryKeys() etc TABLE_NAME is coming in UPPER CASE

Suggested fix:

The table names are craeted in UPPER CASE during CREATE TABLE and ALTER for foreign key creation.So it should behave consistently for all

Java program

Attachment: TestJDBCForMySQL.java (text/java), 6.47 KiB.

SQL file

Attachment: createscript.sql (application/octet-stream, text), 559 bytes.

This is not a Connector/J bug. The MySQL server changes the case of the table names when lower_case_table_names=2, for example, see the following output of SHOW CREATE TABLE (which the driver uses historically to determine foreign key metadata, but the same is true with information_schema as well):

mysql> show create table TESTCHILD;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                   |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
CREATE TABLE `TESTCHILD` (
  `COL1` int(11) NOT NULL,
  `COL2` int(11) DEFAULT NULL,
  `COL3` datetime DEFAULT NULL,
  `COL4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`COL1`),
  KEY `FK_TESTCHILD_TESTPARENT` (`COL2`),
  CONSTRAINT `FK_TESTCHILD_TESTPARENT` FOREIGN KEY (`COL2`) REFERENCES `testparent` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

This is not specific to value 2 for lower_case_table_name. But for value 0 also, this is the behavior. Whether you put the table names in `` or not , it is converted to lowercase. That laso only for foreign key. Not for anything else.

It may not be a bug in JDBC driver, but its a bug. So do I need to log this to MySQL server seperately or this report can be forwarded

The bug is moved to MySQL server. As per MySQL doc(http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html) for value 0, "Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive." . But foreign key referencing table is stored in lower case

How we can workaround this?

Does http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lower_case_tabl... help:
  If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

Yes. We went through that page, thats how 0 was set.  Ours is a framework which works with multiple database servers and our requirement is that we need all object names in UPPER CASE(the case we used in our CREATE scripts).

We tested this in MySQL server on Linux earlier and it is working as expected. No setting required in Linux as default value for lower_case_table_names is 0. But in windows this behavior changed. 

So after reading through that page we set 0 in Windows with the hope that everything will be craeted ith same case as in CREATE. But thats not happening.

If we set 1 all table names will be in lower case nad that will create problems in our framework

Sunil,

> Yes. We went through that page, thats how 0 was set.  Ours is a framework which works with multiple database servers and our requirement is that we need all object names in UPPER CASE(the case we used in our CREATE scripts).

So you set the rules and want me to obey by them? This is not how things work in general.

> We tested this in MySQL server on Linux earlier and it is working as expected. No setting required in Linux as default value for lower_case_table_names is 0. But in windows this behavior changed. 

So you're saying there is a problem with how MySQL server works on Windows now? Did you checked the changelog of the server?

> So after reading through that page we set 0 in Windows with the hope that everything will be craeted ith same case as in CREATE. But thats not happening.

Basically, you are expecting Windows to be case specific? This will not happen.

Now, after all this discussion I gather this:
  o The problem itself has no relation to c/J.
  o Problem is in that you require case sensitivity from Windows.

Am I right?

Tonci,
No... 
When you set 0 for lower_case_table_name on windows, all object names created in the same case as in CREATE. DB name, TABLE NMAE, COLUMN NAME, PRIMARY KEY NAME etc.. All created in UPPER CASE for me except the REFERENCES part.
If you execute SHOW
mysql> show create table TESTCHILD;

| Table     | Create Table                                                               
    

CREATE TABLE `TESTCHILD` (
  `COL1` int(11) NOT NULL,
  `COL2` int(11) DEFAULT NULL,
  `COL3` datetime DEFAULT NULL,
  `COL4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`COL1`),
  KEY `FK_TESTCHILD_TESTPARENT` (`COL2`),
  CONSTRAINT `FK_TESTCHILD_TESTPARENT` FOREIGN KEY (`COL2`) REFERENCES `testparent`
(`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Why only REFERENCES part is mentioned in lower case(even though that table is created with upper case, if you issue SHOW) when all others are in UPPER CASE?
Is this not a bug?

SHOW RESULT for TESTPARENT(which is mentioned in REFERENCES in lower case).

CREATE TABLE `TESTPARENT` (
  `COL1` int(11) NOT NULL,
  `COL2` int(11) DEFAULT NULL,
  `COL3` datetime DEFAULT NULL,
  `COL4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I don't want Windows to be case sensitive, but store in one case. Table name in UPPER CASE and references in LOWER CASE defies logic.

Sunil, I think you should do the following:
  o Change the synopsis to reflect the actual problem. It is not with getExportedKeys() but with the fact that *only* REFERENCES part of SHOW CREATE is returned in lowercase on Windows regardless of settings

The the colleagues will pick up on this discussion.

Changed the synopsis to reflect the actual problem.
Thanks Tonci.

On Mac OS X with current 5.1.5.7 I see the results you expect:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE `TESTPARENT`(`COL1` int primary key, `COL2` int, `COL3` dateTime, `COL4` varchar(100) ) engine=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE `TESTCHILD`(`COL1` int primary key, `COL2` int, `COL3` dateTime, `COL4` varchar(100) ) engine=InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> ALTER TABLE `TESTCHILD` ADD CONSTRAINT `FK_TESTCHILD_TESTPARENT` FOREIGN KEY(`COL2`) REFERENCES `TESTPARENT`(`COL1`);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `TESTCHILD`
    -> \G
*************************** 1. row ***************************
       Table: TESTCHILD
Create Table: CREATE TABLE `TESTCHILD` (
  `COL1` int(11) NOT NULL,
  `COL2` int(11) DEFAULT NULL,
  `COL3` datetime DEFAULT NULL,
  `COL4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`COL1`),
  KEY `FK_TESTCHILD_TESTPARENT` (`COL2`),
  CONSTRAINT `FK_TESTCHILD_TESTPARENT` FOREIGN KEY (`COL2`) REFERENCES `TESTPARENT` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

The same test on Windows, with the same settings, produce different results:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.56-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

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

mysql>  CREATE TABLE `TESTPARENT`(`COL1` int primary key, `COL2` int, `COL3` dat
eTime,
    -> `COL4` varchar(100) ) engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `TESTCHILD`(`COL1` int primary key, `COL2` int, `COL3` dateT
ime,
    -> `COL4` varchar(100) ) engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE `TESTCHILD` ADD CONSTRAINT `FK_TESTCHILD_TESTPARENT` FOREIGN
    -> KEY(`COL2`) REFERENCES `TESTPARENT`(`COL1`);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table `TESTCHILD`\G
*************************** 1. row ***************************
       Table: TESTCHILD
Create Table: CREATE TABLE `TESTCHILD` (
  `COL1` int(11) NOT NULL,
  `COL2` int(11) DEFAULT NULL,
  `COL3` datetime DEFAULT NULL,
  `COL4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`COL1`),
  KEY `FK_TESTCHILD_TESTPARENT` (`COL2`),
  CONSTRAINT `FK_TESTCHILD_TESTPARENT` FOREIGN KEY (`COL2`) REFERENCES `testparent` (`COL1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

So, I'd say we have a Windows-specific bug here.