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

Bug #11781	DatabaseMetaData.extractForeignKeyFromCreateTable does not work properly
Submitted:	6 Jul 2005 18:54	Modified:	17 Oct 2005 19:42
Reporter:	Chris Price	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.7	OS:	Windows (Windows ME)
Assigned to:	Mark Matthews	CPU Architecture:	Any

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