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

[6 Jul 2005 18:54] Chris Price
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
[18 Jul 2005 12:38] Aleksey Kishkin
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?
[18 Jul 2005 12:39] Aleksey Kishkin
first version of testcase

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

[18 Aug 2005 23:00] Bugs System
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".
[19 Aug 2005 14:23] Chris Price
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
[19 Aug 2005 14:25] Chris Price
Also, the test case looks good.

c
[23 Aug 2005 20:00] Mark Matthews
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
[14 Dec 2006 21:29] Bugs System
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