Description:
DatabaseMetaData.getImportedKeys doesn't return the correct foreign key under InnoDB tables. The error is at com.mysql.jdbc.DatabaseMetaData.java
at method extractForeignKeyForTable(ArrayList, ResultSet, String)
The summary of getting foreign key is basically as follows:
* Invoke query "SHOW CREATE TABLE TABLE_NAME".
* Fetch the second column, which is basically the clause that create the table
* Parse it and look for the line that starts with "FOREIGN KEY".
The problem is:
* With InnoDB, "FOREIGN KEY" clause may not be the first two words. Rather, the declaration would look something like this:
CONSTRAINT '0_33' FOREIGN KEY ('COL_NAME') REFERENCE OTHER_TABLE ('OTHER_COL')
So, the parser in extractForeignKeyForTable failed to detect this situation.
How to repeat:
See above
Suggested fix:
Build a more robust parser.
Change the:
if (line.startsWith("FOREIGN KEY")) {
line with:
something that will detect CONSTRAINT stuff.
For me, since I'm only interested in reference key name, other table name, and other table's primary key, I did it this way:
--------------------------------------------------------
StringTokenizer tok = new StringTokenizer(createTableString,"\n");
while (tok.hasMoreTokens()) {
String lineToken = tok.nextToken().trim();
StringTokenizer lineTok = new StringTokenizer(lineToken, " ");
int state = 0;
String[] triplet = new String[3];
while (lineTok.hasMoreTokens()) {
String token = lineTok.nextToken();
switch (state) {
case 0:
if (token.equals("CONSTRAINT")) state = 1;
else if (token.equals("FOREIGN")) state = 3;
else state = -1; // INVALID
break;
case 1:
state = 2; // Skip constraint name, we're not interested
break;
case 2:
if (token.equals("FOREIGN")) state = 3;
else state = -1; // INVALID
break;
case 3:
if (token.equals("KEY")) state = 4;
else state = -1; // INVALID
break;
case 4:
// This is the foreign key name
token = token.substring(2, token.length()-2);
triplet[0] = token;
state = 5;
break;
case 5:
if (token.equals("REFERENCES")) state = 6;
else state = -1; // INVALID
break;
case 6:
// This is the primary referencing table
token = token.substring(1, token.length()-1);
triplet[1] = token;
state = 7;
break;
case 7:
// This is the primary referencing column
// Cut of trailling commas, if present
if (token.endsWith(","))
token = token.substring(0, token.length()-1);
token = token.substring(2, token.length()-2);
triplet[2] = token;
state = 8;
break;
case 8:
default:
state = -1; break;
} // end switch
if (state == -1) break;
} // end while (lineTok.hasMoreTokens())
if (state != -1) { // If no errors, then take care of the triplet
rows.add(triplet)
}
} // end while
--------------------------------------------------------
Note that: I'm not familiar with variations of Create table clauses. My suggestion is to build a custom parser like above to take care of all combinations.
Good luck.
Description: DatabaseMetaData.getImportedKeys doesn't return the correct foreign key under InnoDB tables. The error is at com.mysql.jdbc.DatabaseMetaData.java at method extractForeignKeyForTable(ArrayList, ResultSet, String) The summary of getting foreign key is basically as follows: * Invoke query "SHOW CREATE TABLE TABLE_NAME". * Fetch the second column, which is basically the clause that create the table * Parse it and look for the line that starts with "FOREIGN KEY". The problem is: * With InnoDB, "FOREIGN KEY" clause may not be the first two words. Rather, the declaration would look something like this: CONSTRAINT '0_33' FOREIGN KEY ('COL_NAME') REFERENCE OTHER_TABLE ('OTHER_COL') So, the parser in extractForeignKeyForTable failed to detect this situation. How to repeat: See above Suggested fix: Build a more robust parser. Change the: if (line.startsWith("FOREIGN KEY")) { line with: something that will detect CONSTRAINT stuff. For me, since I'm only interested in reference key name, other table name, and other table's primary key, I did it this way: -------------------------------------------------------- StringTokenizer tok = new StringTokenizer(createTableString,"\n"); while (tok.hasMoreTokens()) { String lineToken = tok.nextToken().trim(); StringTokenizer lineTok = new StringTokenizer(lineToken, " "); int state = 0; String[] triplet = new String[3]; while (lineTok.hasMoreTokens()) { String token = lineTok.nextToken(); switch (state) { case 0: if (token.equals("CONSTRAINT")) state = 1; else if (token.equals("FOREIGN")) state = 3; else state = -1; // INVALID break; case 1: state = 2; // Skip constraint name, we're not interested break; case 2: if (token.equals("FOREIGN")) state = 3; else state = -1; // INVALID break; case 3: if (token.equals("KEY")) state = 4; else state = -1; // INVALID break; case 4: // This is the foreign key name token = token.substring(2, token.length()-2); triplet[0] = token; state = 5; break; case 5: if (token.equals("REFERENCES")) state = 6; else state = -1; // INVALID break; case 6: // This is the primary referencing table token = token.substring(1, token.length()-1); triplet[1] = token; state = 7; break; case 7: // This is the primary referencing column // Cut of trailling commas, if present if (token.endsWith(",")) token = token.substring(0, token.length()-1); token = token.substring(2, token.length()-2); triplet[2] = token; state = 8; break; case 8: default: state = -1; break; } // end switch if (state == -1) break; } // end while (lineTok.hasMoreTokens()) if (state != -1) { // If no errors, then take care of the triplet rows.add(triplet) } } // end while -------------------------------------------------------- Note that: I'm not familiar with variations of Create table clauses. My suggestion is to build a custom parser like above to take care of all combinations. Good luck.