MySQL Bugs: #16184: DatabaseMetaData.getImportedKeys returns lower case tablename

Bug #16184	DatabaseMetaData.getImportedKeys returns lower case tablename
Submitted:	4 Jan 2006 13:14	Modified:	21 May 2009 10:19
Reporter:	Markus Fuchs	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S1 (Critical)
Version:	5.0.18, 5.0, 5.1, 6.0 bzr	OS:	Any (Windows, MacOSX)
Assigned to:		CPU Architecture:	Any
Tags:	affects_connectors

Description:
The JDBC call

dmd.getImportedKeys(con.getCatalog(), schema, table);

The third column of the result set denotes PKTABLE_NAME String => primary key table name being imported.

This information is returned differently btw. the solaris and windows MySQL instance.

Solaris:
Foreign key info:
test # null # B # ROOT # dbsmpl1 # null # A # FK_FOR_B # 1 # 3 # 3 # FK_B # null # 7 #

Windows:
Foreign key info:
test # null # b # ROOT # test # null # A # FK_FOR_B # 1 # 3 # 3 # FK_B # null # 7 #

The returned table name doesn't match the actual database table. I used 

mysqld --lower_case_table_names=2 --sql-mode="PIPES_AS_CONCAT"

to start the MySql server, the DDL contains only upper case names. Tried w/ mysql-connector-java-3.1.12-bin.jar

How to repeat:
See description.

Suggested fix:
Fix the driver/database.

Tablenames are always forced to all lowercase on windows by default by the server, irregardless of the case in the DDL for the table.

Matthew,

thanks for looking into this. *But:*

1) Why does getPrimaryKeys(con.getCatalog(), schema, table)
return the table name in upper case on the same database?

Primary keys:
test # null # B # ROOT # 1 # PRIMARY #

2) How should any (java) programm work, if JDBC getImportedKeys(con.getCatalog(),...) returns different values depending on the platform, the database instance is installed? 

The return of getImportedKeys(con.getCatalog(), ...) *must* be the same, independent from the platforms, or shouldn't it?

The DDL was the on both platforms.

==> in the end, MySQL should return either all upper or all lower case names, as the DDL is one case. Also keep in mind, that I start the server w/ --lower_case_table_names=2 to prevent problems w/ mixed case names.

I still think this is a bug.

Thanks,

-- markus.

Test case

Attachment: mysql_fk.zip (application/x-zip-compressed, text), 1.04 KiB.

It's a bug with the metadata in the server. Notice that the server metadata commands used by these two functions return information in different cases depending on how they're called:

mysql> show keys from Bar\G
***************************
       Table: Bar
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: field2
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
***************************
       Table: Bar
  Non_unique: 1
    Key_name: Foo_Fk_1
Seq_in_index: 1
 Column_name: Foo_Fk_1
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)

mysql> show keys from bar\G
***************************
       Table: bar
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: field2
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
***************************
       Table: bar
  Non_unique: 1
    Key_name: Foo_Fk_1
Seq_in_index: 1
 Column_name: Foo_Fk_1
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)

Notice above that the "table name" was returned as it was specified, irregardless of what the actual case is (this is on Windows, by the way).

mysql> show create table Bar\G
*************************** 1. row ***************************
       Table: Bar
Create Table: CREATE TABLE `bar` (
  `field2` int(11) NOT NULL,
  `Foo_Fk_1` int(11) NOT NULL,
  PRIMARY KEY  (`field2`),
  KEY `Foo_Fk_1` (`Foo_Fk_1`),
  CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`Foo_Fk_1`) REFERENCES `foo` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

In this example, "SHOW CREATE TABLE" (which is used for DatabaseMetaData.getImportedKeys/getExportedKeys/getCrossReference()), the table name for the "references" clause is reported in lower-case, even though the table name in the DDL was specified as "Foo", and using "SHOW KEYS FROM Foo" returns "Foo" as the table name.

I'm adjusting the category accordingly, as this really needs to be solved in the server.

Problem still exists in current versions.

Hi All,
Is this bug fixed? I am facing this in 5.1.46 with 5.1.12 JDBC driver.
I created tables  and columns and relations in UpperCase(eg:TESTTABLE).
But when I get metadata vai JDBC column details are coming in upper case but importedKeys() table name in lower acse.

Any workaround/suugestion/settings?

Regards
Zunil