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:
Tags: affects_connectors
Triage: Triaged: D3 (Medium)

[4 Jan 2006 13:14] Markus Fuchs
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.
[4 Jan 2006 19:15] Mark Matthews
Tablenames are always forced to all lowercase on windows by default by the server, irregardless of the case in the DDL for the table.
[5 Jan 2006 9:42] Markus Fuchs
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.
[5 Jan 2006 9:44] Markus Fuchs
Test case

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

[5 Jan 2006 20:33] Mark Matthews
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.
[21 May 2009 10:19] Sveta Smirnova
Problem still exists in current versions.
[2 Aug 2010 12:44] Sunil John
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