Bug #57904 Missing constraint from information schema REFERENTIAL_CONSTRAINTS table

• Submitted: 1 Nov 2010 19:22
• Modified: 21 Dec 2010 18:34
• Reporter: Erica Moss
• Status: Closed
• Category: MySQL Server: Information schema
• Severity: S1 (Critical)
• Version: 5.5.7
• OS: Any (windows / linux)
• Assigned to: Kevin Lewis
• CPU Architecture: Any
• Tags: Foreign key constraint, regression

[1 Nov 2010 19:22] Erica Moss

Description:
After creating a table with two foreign key constraints in the 5.5.7 server candidate, only one of those constraints was found present in the information schema.  This is the output from 5.5.7 followed by the output from 5.1.52.  

Note this test was discovered when running the connector .Net test, GetSchemaTests.MultiSingleForeignKey() and should be verified fixed there.

5.5.7 Output:
*************************** 1. row ***************************
     constraint_catalog: def
      constraint_schema: db616-a
        constraint_name: product_order_ibfk_1
          table_catalog: def
           table_schema: db616-a
             table_name: product_order
           match_option: NONE
            update_rule: CASCADE
            delete_rule: RESTRICT
referenced_table_schema: db616-a
  referenced_table_name: product
1 row in set (0.19 sec)

5.1.52 Output:
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: NULL
        CONSTRAINT_SCHEMA: test
          CONSTRAINT_NAME: product_order_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: NULL
 UNIQUE_CONSTRAINT_SCHEMA: test
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: CASCADE
              DELETE_RULE: RESTRICT
               TABLE_NAME: product_order
    REFERENCED_TABLE_NAME: product
*************************** 2. row ***************************
       CONSTRAINT_CATALOG: NULL
        CONSTRAINT_SCHEMA: test
          CONSTRAINT_NAME: product_order_ibfk_2
UNIQUE_CONSTRAINT_CATALOG: NULL
 UNIQUE_CONSTRAINT_SCHEMA: test
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: RESTRICT
              DELETE_RULE: RESTRICT
               TABLE_NAME: product_order
    REFERENCED_TABLE_NAME: customer
2 rows in set (0.02 sec)

How to repeat:
Execute the following SQL from the client
_________________________________________

CREATE DATABASE `db616-a`;
USE `db616-a`;
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB;

CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=INNODB;

SELECT rc.constraint_catalog, rc.constraint_schema, rc.constraint_name, kcu.table_catalog, kcu.table_schema, rc.table_name, rc.match_option, rc.update_rule, rc.delete_rule, kcu.referenced_table_schema, rc.referenced_table_name 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc 
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
ON kcu.constraint_catalog <=> rc.constraint_catalog AND kcu.constraint_schema <=> rc.constraint_schema AND kcu.constraint_name <=> rc.constraint_name AND  kcu .ORDINAL_POSITION=1 
WHERE 1=1 AND rc.constraint_schema LIKE 'db616-a' AND rc.table_name LIKE 'product_order'\G

[2 Nov 2010 13:42] Kevin Lewis

The problem seems to be in ha_innobase::get_foreign_key_list().  It starts looking through a list of foreign key references but then uses the referenced keys field when looking for the next link.  This is what it aught to do;

    for (foreign = UT_LIST_GET_FIRST(prebuilt->table->foreign_list);
         foreign != NULL;
-         foreign = UT_LIST_GET_NEXT(referenced_list, foreign)) {
+         foreign = UT_LIST_GET_NEXT(foreign_list, foreign)) {
        pf_key_info = get_foreign_key_info(thd, foreign);
        if (pf_key_info) {
            f_key_list->push_back(pf_key_info);
        }
    }

So this function, which provides data for INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS, does not report more than one foreign key per table

[2 Nov 2010 15:24] Kevin Lewis

The attached patch contains a simple testcase that now works to display both foreign keys in the same table.

Pushed to mysql-5.5.7-rc-release and mysql-5.5-innodb

[2 Nov 2010 18:09] Kevin Lewis

Pushed to Pushed to mysql-5.5.7-rc-release,
mysql-5.5-innodb, mysql-trunk-innodb and mysql-next-mr-innodb

[5 Dec 2010 12:41] Bugs System

Pushed into mysql-trunk 5.6.1 (revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (version source revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)

[14 Dec 2010 23:17] Paul DuBois

Noted in 5.5.8 changelog.

After creating a table with two foreign key constraints, the
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table displayed only one
of them.

[16 Dec 2010 22:30] Bugs System

Pushed into mysql-5.5 5.5.9 (revid:[e-mail]-20101216101358-fyzr1epq95a3yett) (version source revid:[e-mail]-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)