Bug #19923 MyODBC Foreign key retrieval broken in multiple ways
Submitted: 18 May 2006 23:33 Modified: 10 Jan 2008 9:34
Reporter: Richard Wesley Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51.12.0 OS:Microsoft Windows (Windows/Linux)
Assigned to: Jim Winstead CPU Architecture:Any
Tags: SQLForeignKeys
Triage: D3 (Medium)

[18 May 2006 23:33] Richard Wesley
Description:
The ODBC driver (as previously reported in case 8100)  insists on quoting the column names retrieved.  This problem was supposedly fixed, but I was encountering it today running against version 5.0.15-nt.  I was also finding that it was returning only single keys for tables with multiple foreign key constraints.  And thirdly, it has a nasty habit of filling in the schema names with empty strings, instead of reporting  them as nulls (although that may be an OLEDDB/ODBC issue).

On top of that, while reading the comments in case 8100, I was appalled to learn that that the driver was parsing the InnoDB comment field to get this information!  The same information appears in the information_schema.KEY_COLUMN_USAGE table in a much more easily digested form.

I have marked this as S1 because that is what 8100 was escalated to and it is a larger problem than that.

How to repeat:
CREATE TABLE `stores` (
  `stor_id` char(4) NOT NULL,
  `stor_name` varchar(40) default NULL,
  `stor_address` varchar(40) default NULL,
  `city` varchar(20) default NULL,
  `state` char(2) default NULL,
  `zip` char(5) default NULL,
  PRIMARY KEY  (`stor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `titles` (
  `title_id` varchar(6) NOT NULL,
  `title` varchar(80) NOT NULL,
  `type` char(12) NOT NULL,
  `pub_id` char(4) default NULL,
  `price` decimal(19,4) default NULL,
  `advance` decimal(19,4) default NULL,
  `royalty` int(11) default NULL,
  `ytd_sales` int(11) default NULL,
  `notes` varchar(200) default NULL,
  `pubdate` datetime NOT NULL,
  PRIMARY KEY  (`title_id`),
  KEY `titleind` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sales` (
  `stor_id` char(4) NOT NULL,
  `ord_num` varchar(20) NOT NULL,
  `ord_date` datetime NOT NULL,
  `qty` smallint(6) NOT NULL,
  `payterms` varchar(12) NOT NULL,
  `title_id` varchar(6) NOT NULL,
  PRIMARY KEY  (`stor_id`,`ord_num`,`title_id`),
  KEY `titleidind` (`title_id`),
  CONSTRAINT `sales_ibfk_3` FOREIGN KEY (`title_id`) REFERENCES `titles` (`title_id`),
  CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`stor_id`) REFERENCES `stores` (`stor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Try to use the driver to obtain

Suggested fix:
Do a select from information_schema.KEY_COLUMN_USAGE instead of trying to track endless syntax changes in the InnoDB comment field.
[19 May 2006 11:01] Tonci Grgin
Hi Richard. Thanks for your report.
Can you please include sales_ibfk_3 table structure and a small but complete code which shows this error?
[19 May 2006 17:55] Richard Wesley
sales_ibfk_3 is just the name of the constraint.  I rechecked my submission and all the tables needed are defined in the repro steps.  The end of my last sentence appears to have been chopped of and should have read:

"Try to use the driver to obtain the foreign key metadata for the `sales` table."

I'm afraid don't have a code snippet, but I expect the test code used to validate 8100 from your automated test suite should be easily adaptable.
[22 May 2006 10:53] Tonci Grgin
Hi Richard.
I was able to verify the bug you described (source attached) with standard MyODBC test. This addresses "insists on quoting" and "returning only single keys for tables with multiple foreign key" from your report.

Environment: 
 - MySQL server 5.0.22-log, bk build
 - WinXP SP2
 - MyODBC 3.51.12

Console output:
All FK referencing PK table "titles": titles ( `title_id` ) <-- sales ( `title_id` )
All FK of table "sales": sales ( `title_id` )--> titles ( `title_id` )

This one is not true anymore: "I was appalled to learn
that that the driver was parsing the InnoDB comment field to get this
information".
[22 May 2006 10:54] Tonci Grgin
Test case to reproduce the error reported

Attachment: test19923.zip (application/zip, text), 1.93 KiB.

[16 Mar 2007 16:46] Jim Winstead
Some of these problems are addressed by the patch for bug #4518, but test cases need to be written to make sure.

Adding an INFORMATION_SCHEMA-based implementation of SQLForeignKeys should also be done, but we did need to clean up the existing implementation as much as possible for 4.1 and earlier.
[18 May 2007 23:53] Jim Winstead
Implementation of SQLForeignKeys that uses INFORMATION_SCHEMA.

Attachment: bug19923.patch (text/plain), 17.69 KiB.

[30 Jul 2007 21:13] Jim Winstead
updated patch, still needs work

Attachment: bug19923.patch (text/plain), 17.69 KiB.

[31 Jul 2007 21:01] Jim Winstead
Bug #25772 has been marked as a duplicate of this bug.
[6 Aug 2007 17:26] Daniel Serodio
I'd like to try this fix, can someone please post a binary build of the patched driver?
[8 Aug 2007 10:21] [ name withheld ]
I second the INFORMATION_SCHEMA based solution.
This should encourage people to upgrade, which is good, and make your code simpler /more consistent.
[13 Nov 2007 1:21] eric s
I would appreciate an update on this Bug.  This has been open for some time, and is a critical bug to resolve.
[20 Dec 2007 20:45] Jim Winstead
This is too big of a change for 3.51 now, we'll try to fix this in 5.1.
[20 Dec 2007 20:45] Jim Winstead
Use INFORMATION_SCHEMA for SQLForeignKeys -- needs more tests

Attachment: bug19923.diff (text/plain), 18.26 KiB.

[20 Dec 2007 22:53] Jim Winstead
Fixed up queries for UPDATE_RULE and DELETE_RULE with 5.1, added test

Attachment: bug19923.diff (text/plain), 20.62 KiB.

[22 Dec 2007 17:29] Jess Balint
Should the data[11] and data[12] in the pre-5.0 version be NULL instead of "NULL"?
[3 Jan 2008 18:10] Jim Winstead
The fix for this has been committed, and will be in 5.1.2. It includes a full implementation of SQLForeignKeys based on the information available from INFORMATION_SCHEMA in 5.0 and later versions of the server.
[10 Jan 2008 9:34] MC Brown
A note has been added to the 5.1.2 changelog: 

SQLForeignKeys would return an empty string
for the schema columns instead of NULL.