Bug #1925 DatabaseMetaData.getCrossReference always returns 0 rows

Description:

The following patch fixes two problems with DataBaseMetaData.java. It also fixes a problem with the corresponding MetaDataTest. 

The problems were:
1. DataBasemetaData.getCrossReference() never returned any rows ( against mysqld  Ver 4.0.15-max-debug for Win95/Win98 on i32)
2. DataBasemetaData.extractForeignKeyFromCreateTable() was looping over tables, and creating a statement each iteration, but only closing the last statement.

The latter error caused MetadataTest.testForeignKeys() to fail with a concurrency exception. Fixing that error enabled the test to pass, even though bug 1 was still present. So this patch adds a check to the test to ensure that getCrossReference() (as well as getImportedKeys() and getExportedKeys()) return the expected number of rows.

The problem with getCrossReference() was the way it looks for the FOREIGN KEY declaration in the result from SHOW CREATE TABLE. It was searching for a line beginning with the string "FOREIGN KEY". But MySQL 4.0.15 is returning a line like this for the FK:
    CONSTRAINT `0_17254` FOREIGN KEY (`r`) REFERENCES `resource` (`locator`)

So I changed the detection to call indexOf("FOREIGN KEY"). This strategy ought to be compatible with any version of MySQL that does return the line beginning with "FOREIGN KEY". There is a vulnerability if other possible lines can constain the substring "FOREIGN KEY"; I don't know if that can ever be the case.

How to repeat:
The patch in "suggested fix" patches the MetadataTest to demonstrate the bug. You would have to apply only that one patch and not the other one in that patchfile, that fixes the bug.

Suggested fix:

diff -x '*.class' -Naur mysql-connector-java-3.1.0-alpha/com/mysql/jdbc/DatabaseMetaData.java mysql-connector-java-3.1.0-alpha-FIX/com/mysql/jdbc/DatabaseMetaData.java
--- mysql-connector-java-3.1.0-alpha/com/mysql/jdbc/DatabaseMetaData.java 2003-02-17 11:28:00.000000000 -0600
+++ mysql-connector-java-3.1.0-alpha-FIX/com/mysql/jdbc/DatabaseMetaData.java 2003-11-23 12:17:12.921777600 -0600
@@ -4926,7 +4926,11 @@
         while (lineTokenizer.hasMoreTokens()) {
             String line = lineTokenizer.nextToken().trim();
 
-            if (line.startsWith("FOREIGN KEY")) {
+            //if (line.startsWith("FOREIGN KEY")) {
+            int position = line.indexOf("FOREIGN KEY") ;
+            if ( position>= 0) {
+                // Position start of string at "FOREIGN KEY" 
+                line = line.substring(position); 
                 if (line.endsWith(",")) {
                     line = line.substring(0, line.length() - 1);
                 }
@@ -5063,6 +5067,9 @@
                 while (rs.next()) {
                     extractForeignKeyForTable(rows, rs, catalog);
                 }
+                
+                stmt.close(); 
+                stmt = null;
             }
         } finally {
             if (rs != null) {
diff -x '*.class' -Naur mysql-connector-java-3.1.0-alpha/testsuite/simple/MetadataTest.java mysql-connector-java-3.1.0-alpha-FIX/testsuite/simple/MetadataTest.java
--- mysql-connector-java-3.1.0-alpha/testsuite/simple/MetadataTest.java 2003-02-17 11:28:00.000000000 -0600
+++ mysql-connector-java-3.1.0-alpha-FIX/testsuite/simple/MetadataTest.java 2003-11-23 12:17:15.034816000 -0600
@@ -73,6 +73,7 @@
         DatabaseMetaData dbmd = conn.getMetaData();
         rs = dbmd.getImportedKeys(null, null, "child");
 
+        int count = 0;
         while (rs.next()) {
             String pkColumnName = rs.getString("PKCOLUMN_NAME");
             String fkColumnName = rs.getString("FKCOLUMN_NAME");
@@ -82,11 +83,14 @@
             assertTrue("Foreign Key not returned correctly ('" + fkColumnName
                 + "' != 'parent_id_fk')",
                 fkColumnName.equalsIgnoreCase("parent_id_fk"));
+            count++;
         }
-
+        assertEquals("getImportedKeys did not return 1 row", 1, count);
+        
         rs.close();
         rs = dbmd.getExportedKeys(null, null, "parent");
 
+        count = 0;
         while (rs.next()) {
             String pkColumnName = rs.getString("PKCOLUMN_NAME");
             String fkColumnName = rs.getString("FKCOLUMN_NAME");
@@ -102,13 +106,16 @@
                 "Foreign Key not returned correctly for getExportedKeys ('"
                 + fkColumnName + "' != 'parent_id_fk')",
                 fkColumnName.equalsIgnoreCase("parent_id_fk"));
+            count++;
         }
+        assertEquals("getExportedKeys did not return 1 row", 1, count);
 
         rs.close();
 
         rs = dbmd.getCrossReference(null, null, "cpd_foreign_3", null, null,
                 "cpd_foreign_4");
 
+        count = 0;
         while (rs.next()) {
             String pkColumnName = rs.getString("PKCOLUMN_NAME");
             String pkTableName = rs.getString("PKTABLE_NAME");
@@ -122,11 +129,15 @@
 
             System.out.println(pkTableName + "(" + pkColumnName + ") -> "
                 + fkTableName + "(" + fkColumnName + ")");
+            count++;
         }
+        assertEquals("getCrossReference did not return 2 rows", 2, count);
 
         rs.close();
 
         rs = dbmd.getImportedKeys(null, null, "fktable2");
+        
+
     }
 
     /**

The first part of your bug (the parsing error) has already been fixed in 3.0.9 and merged into 3.1.1 (see the nightly builds of 3.1.x from http://downloads.mysql.com/snapshots.php). The second part (closing statements) will be fixed ASAP!

Thanks for your excellent bug report!

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Please see a nightly snapshot build of 3.1 from http://downloads.mysql.com/snapshots.php for the fix (until 3.1.1 is released).