MySQL Bugs: #19923: MyODBC Foreign key retrieval broken in multiple ways

Bug #19923	MyODBC Foreign key retrieval broken in multiple ways
Submitted:	19 May 2006 1:33	Modified:	10 Jan 2008 10:34
Reporter:	Richard Wesley
Status:	Closed
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	3.51.12.0	OS:	Microsoft Windows (Windows/Linux)
Assigned to:	Jim Winstead	Target Version:	5.1.2
Tags:	SQLForeignKeys
Triage:	D3 (Medium)

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.

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?

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.

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".

Test case to reproduce the error reported

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

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.

Implementation of SQLForeignKeys that uses INFORMATION_SCHEMA.

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

updated patch, still needs work

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

Bug #25772 has been marked as a duplicate of this bug.

I'd like to try this fix, can someone please post a binary build of the patched driver?

I second the INFORMATION_SCHEMA based solution.
This should encourage people to upgrade, which is good, and make your code simpler /more
consistent.

I would appreciate an update on this Bug.  This has been open for some time, and is a
critical bug to resolve.

This is too big of a change for 3.51 now, we'll try to fix this in 5.1.

Use INFORMATION_SCHEMA for SQLForeignKeys -- needs more tests

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

Fixed up queries for UPDATE_RULE and DELETE_RULE with 5.1, added test

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

Should the data[11] and data[12] in the pre-5.0 version be NULL instead of "NULL"?

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.

A note has been added to the 5.1.2 changelog: 

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