MySQL Bugs: #23109: getExportedKeys doesn't cascade across multiple databases

Bug #23109	getExportedKeys doesn't cascade across multiple databases
Submitted:	9 Oct 2006 15:16	Modified:	31 Mar 2014 12:14
Reporter:	Cory Twibell	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Solaris (Solaris 8)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
I have two databases where one key is a foreign key in the other. 

The function getImportedKeys( "DB1", null, "TBL1" ) shows me that db2.tb2.column1 is a key. 

The function getExportedKeys( "DB2", null, "TBL1" ) shows me no exported keys, even though that is clearly the case from the imported function. 

How to repeat:
Create 2 databases with one table containg a foreign key from a different database.

create database DB1;
create table tbl1 (id int not null auto_increment, primary key (id) ) engine = INNODB;

create database DB2;
create table tbl1 (id int not null, foreign key (id) references db1.tbl1(id) ) engine = INNODB;

call function getExportedKeys( "db1", null, "tbl1" )
returns no resultset

Suggested fix:
Implement a cascade feature of getExportedKeys()

Hi Cory and thanks for your problem report.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation: Are you using MySQL server version 5.x? If so, are you using useInformationSchema=true in your connection string? Please post full test case exhibiting this behavior and info needed for testing if you feel this is stil a bug.

My environment and results:
  - MySQL server 5.0.24BK on WinXP Pro SP2 localhost
  - JDK 1.5.07
  - connector/J 5 SVN latest sources

Connected to 5.0.24-log
Entering: Exported db1.tbl1
PK col name: id_parent
FK col name: id_child
---
Entering: Imported db2.tbl1
PK col name: id_parent
FK col name: id_child

Time: 3,812

OK (1 test)

step 1) create database TestDb1

step 2) create table TestDb1.TestTbl1 ( id int not null auto_increment, name tinytext not null, primary key (id) ) Engine = InnoDB;

step 3) create database TestDb2

step 4) create table TestDb2.TestTbl2 ( id int not null auto_increment, id_sk int not null, primary key (id), foreign key (id_sk) references TestDb1.TestTbl1(id)on delete cascade on update cascade ) Engine = InnoDB;

step 5) create a java program that calls getExportedKeys()
   Connection con;
   DatabaseMetaData dmd = con.getMetaData();
   ResultSet rs = dmd.getExportedKeys( "TestDb1", null, "TestTbl1" );
   ResultSetMetaData rsmd = rs.getMetaData();

   Vector columnTitles = new Vector();
   for ( int  i =0; i < rsmd.getColumnCount(); i++ )
      columnTitles.add( rsmd.getColumnLabel( i + 1 ) );

   Vector rows = new Vector();
   while ( rs.next() )
   {
      Vector col = new Vector();
      for ( int i = 1; i <= columnTitles.size(); i++ )
         col.add( rs.getObject(i) );
      rows.add( col );
   }

   System.out.println( columnTitles );
   for ( int i = 0; i < rows.size(); i++ )
   {
      Vector v = (Vector)rows.get(i);
      System.out.println( v );
   }
   System.out.println();

You can clearly see that the function getExportedKeys() doesn't cascade across multiple databases.

I am using MySQL5.0.17-pro with JDBC 5.0.3-commercial on a Solaris 8 box.

Cory, have you tried with "useInformationSchema=true"? We know this method won't cascade databases when not using the information schema (and I wrote about the reasons why we decided not to support it in the related forum post at http://forums.mysql.com/read.php?39,119537,119557#msg-119557). 

We'll look at adding an option when _not_ using the information schema for users who really want to do this (but the method might then take a very long time to run), but for now using the information schema configuration is a workaround.

Mark,

I tried adding useInformationSchema=true to my properties, but then I started to get ParseExceptions on rs.getObject(i)

Test case

Attachment: TestBug23109.java (text/x-java), 3.10 KiB.

Hi Cory.
I have attached my test case. It was made to run inside Eclipse against SVN repository so it might look a bit odd. Can you try it with "useInformationSchema=true" and report the result?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. Please, feel free to reopen it if the problem still exists in current driver.