MySQL Bugs: #11781: DatabaseMetaData.extractForeignKeyFromCreateTable does not work properly

Bug #11781	DatabaseMetaData.extractForeignKeyFromCreateTable does not work properly
Submitted:	6 Jul 2005 20:54	Modified:	17 Oct 2005 21:42
Reporter:	Chris Price
Status:	Closed
Category:	Connector/J	Severity:	S2 (Serious)
Version:	3.1.7	OS:	Microsoft Windows (Windows ME)
Assigned to:	Mark Matthews	Target Version:

Description:
DatabaseMetaData.extractForeignKeyFromCreateTable does not work properly when the
referenced table has spaces in the name.

How to repeat:
1) Create a table with the following script:

CREATE TABLE `app tab`
(
    C1 int(11) NULL,
    INDEX NEWINX (C1),
    INDEX NEWINX2 (C1)
) ENGINE = InnoDB CHECKSUM = 0 COMMENT = 'InnoDB free: 3072 kB; (`C1`) REFER `test/app
tab`(`C1`)' PACK_KEYS = 0
;
ALTER TABLE `app tab`
    ADD CONSTRAINT APPFK
    FOREIGN KEY (C1)
    REFERENCES `app tab` (C1)
;

2) Call DatabaseMetaData.extractForeignKeyFromCreateTable on this table. The result set
returned tells us that the table has a foreign key referencing a table on catalog "app"
with name "tab". This is incorrect. In this particular case, of course, the catalog
should be the same as the table, and the referenced table should be "app tab".

Suggested fix:
The problem is in lines 5225 - 5299 of DatabaseMetaData. 

The code attempts to parse the line: "FOREIGN KEY (`C1`) REFERENCES `app tab` (`C1`)"

In the first step of the algorithm, the code strips out all of the single quotes. This
immediately makes it impossible (or at least, much more difficult) to determine that the
table name is "app tab", not "app" or "tab".

The error is then compounded by parsing the remaining text (with quotes removed) using a
StringTokenizer object which is constructed with the space character - ie ' ' - as one of
it's delimiters. The code then proceeds to set referCatalog as "app" and referTable as
"tab" - which is wrong!!!! 

I don't think there's an easy fix for this problem. I think that the code should be
written to NOT strip out the quotes; to tokenize based on spaces; and then to process
each token - ('C1'), REFERENCES, 'app lab' - on it's own.

In essence the main problem boils down to treating the space in "app lab" the same as you
would treat a period -> "app.lab". This is incorrect.

Please contact me if you need any assistance reproducing or fixing this bug.

cheers,
chris

Chris, i wrote some test case (attached). It shows:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.7-beta
app tab
SUPPORTS_FK
comment; APPFK(C1) REFER app/tab(C1)

Time: 0,704

OK (1 test)

Please check if I understand properly: You meant that line "comment; APPFK(C1) REFER
app/tab(C1)" is wrong. Right?

first version of testcase

Attachment: Bug11781.java (text/x-java), 1.29 KiB.

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 added feedback, but it seems to have disappeared.

To reiterate:

"Please check if I understand properly: You meant that line "comment;
APPFK(C1) REFER app/tab(C1)" is wrong. Right?"

Yes, that is correct. That's exactly what I mean.

chris

Also, the test case looks good.

c

Patch is available at http://lists.mysql.com/internals/28696

The fix for testing should be available in the nightly builds after 00:00 GMT, Aug. 24 at
http://downloads.mysql.com/snapshots.php#connector-j

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16995