Bug #883 DatabaseMetaData getImportedKeys() getting foreign keys
Submitted: 21 Jul 2003 18:39 Modified: 22 Jul 2003 5:56
Reporter: Sam Price Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.0.8 OS:Microsoft Windows (Windows 2000)
Assigned to: Mark Matthews CPU Architecture:Any

[21 Jul 2003 18:39] Sam Price
Description:
I am using mysql version 4.0.13 and have a database using InnoDB tables with foreign keys referencing them. I am using the MySQL Connector/J 3.0.8 JDBC driver to access the database with java version 1.4.1_03. I have implemented foreign keys correctly because I can see them when I use: SHOW CREATE TABLE table_name;

When I use the method DatabaseMetaData.getImportedKeys(null, null, tablename) it returns an empty RowSet.

After a bit of mucking around I noticed a problem with the code in DatabaseMetaData class in the mysql-connector-java-3.0.8-stable-bin jar.

This is the problem:

If I use DatabaseMetaData.getImportedKeys(null, null, tablename), it queries the database like so: SHOW CREATE TABLE tablename;
(okay so far)

Now this returns a RowSet where there is a line like so:
  CONSTRAINT `0_44` FOREIGN KEY (`Local`) REFERENCES `Afar` (`Local_ID`) ON DELETE SET NULL

The java code first trims this String (removes whitespaces either side) and then calls:
if (line.startsWith("FOREIGN KEY")) { //...get foreign keys

This is a problem because as you can see above the line starts with "CONSTRAINT `0_44`" not "FOREIGN KEY"

for this line of code see method: DatabaseMetaData.extractForeignKeyForTable() and it's about 10 lines into the method.

See suggested fix.

How to repeat:
To repeat use the same versions as I have used above and try and retrieve foreign key information via a java app.

Suggested fix:
Original code in method DatabaseMetaData.extractForeignKeyForTable():

...
if (line.startsWith("FOREIGN KEY")) {
   ...

Replacement code:

...
int index;
if ((index = line.indexOf("FOREIGN KEY")) != -1) {
   line = line.substring(index, line.length());
   ...

This replacement code works fine for me and my application, but I haven't tested it with different versions etc though.
[22 Jul 2003 5:56] Mark Matthews
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

This has been fixed for quite some time in the development tree. You can test a nightly snapshot build from http://mmmysql.sourceforge.net/snapshots/stable/ to see the fix.