Bug #664 JDBC Driver Connector/J DatabaseMetaData.getImportedKeys Error

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.

This is a duplicate of #517