MySQL Bugs: #91577: INFORMATION_SCHEMA.INNODB_FOREIGN does not return a correct TYPE

Bug #91577	INFORMATION_SCHEMA.INNODB_FOREIGN does not return a correct TYPE
Submitted:	9 Jul 2018 15:15	Modified:	11 Oct 2018 22:34
Reporter:	Angel Milanov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.11	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The new renamed INFORMATION_SCHEMA.INNODB_FOREIGN system view does not report the correct TYPE of any of the foreign keys. Instead, it display zeroes for every foreign key type.

The foreign key type specifies additional information in the form of bit flags: https://dev.mysql.com/doc/refman/8.0/en/innodb-foreign-table.html

How to repeat:
1) Create any kind of foreign key. Sample DDL statements:

CREATE TABLE `warehouses` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(64) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `warehouse_bins` (
	`id` INT(11) NOT NULL,
	`warehouse_id` INT(11) NOT NULL,
	`name` VARCHAR(64) NOT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`warehouse_id`, `id`),
	CONSTRAINT `FK_7070DD8C5080ECDE` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE CASCADE
);

CREATE TABLE `articles` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(256) NOT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `FK_127C874E5080ECDE` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`),
	CONSTRAINT `FK_127C874E5080ECDEC77159AD` FOREIGN KEY (`warehouse_id`, `warehouse_bin_id`) REFERENCES `warehouse_bins` (`warehouse_id`, `id`) ON DELETE RESTRICT
);

2) Execute:
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN
WHERE ID LIKE '%/FK_7070DD8C5080ECDE2' 
	OR ID LIKE '%/FK_127C874E5080ECD2E'
	OR ID LIKE '%/FK_127C874E5080ECDEC77159AD2';

3) Inspect the TYPE column from the result set.

Changes category.

To reproduce the problem I had to modify the create statement of the articles table like this:

CREATE TABLE `articles` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(256) NOT NULL,
	`warehouse_id` INT(11) NOT NULL,
	`warehouse_bin_id` INT(11) NOT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `FK_127C874E5080ECDE` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`),
	CONSTRAINT `FK_127C874E5080ECDEC77159AD` FOREIGN KEY (`warehouse_id`, `warehouse_bin_id`) REFERENCES `warehouse_bins` (`warehouse_id`, `id`) ON DELETE RESTRICT
);

and then I could see:

MySQL [mysql1+ ssl/bug] SQL> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN WHERE ID LIKE '%/FK_707%'  OR ID LIKE '%/FK_127C874E5080ECD%';
+---------------------------------+--------------------+--------------------+--------+------+
| ID                              | FOR_NAME           | REF_NAME           | N_COLS | TYPE |
+---------------------------------+--------------------+--------------------+--------+------+
| bug/FK_7070DD8C5080ECDE         | bug/warehouse_bins | bug/warehouses     |      1 |    0 |
| bug/FK_127C874E5080ECDE         | bug/articles       | bug/warehouses     |      1 |    0 |
| bug/FK_127C874E5080ECDEC77159AD | bug/articles       | bug/warehouse_bins |      2 |    0 |
+---------------------------------+--------------------+--------------------+--------+------+
3 rows in set (0.1960 sec)

Thank you fro the bug report.

Posted by developer:
 
Following fixed the bug, however it requires rebuilding the installation:

diff --git a/sql/dd/impl/system_views/innodb_foreign.cc b/sql/dd/impl/system_vie
index d1a22f3..5a55c03 100644
--- a/sql/dd/impl/system_views/innodb_foreign.cc
+++ b/sql/dd/impl/system_views/innodb_foreign.cc
@@ -42,7 +42,9 @@ Innodb_foreign::Innodb_foreign() {
       FIELD_REF_NAME, "REF_NAME",
       "CONCAT(fk.referenced_table_schema, '/', fk.referenced_table_name)");
   m_target_def.add_field(FIELD_N_COLS, "N_COLS", "COUNT(*)");
-  m_target_def.add_field(FIELD_TYPE, "TYPE", "0");
+  m_target_def.add_field(
+      FIELD_TYPE, "TYPE",
+      "CONCAT('ON DELETE ', fk.delete_rule, ' ON UPDATE ', fk.update_rule)");
 
   m_target_def.add_from("mysql.foreign_keys fk");
   m_target_def.add_from("JOIN mysql.tables tbl ON fk.table_id=tbl.id");

mysql> CREATE TABLE `warehouses` (
    -> `id` INT(11) NOT NULL AUTO_INCREMENT,
    -> `name` VARCHAR(64) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> );
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: test

Query OK, 0 rows affected (0.63 sec)

mysql> 
mysql> CREATE TABLE `warehouse_bins` (
    -> `id` INT(11) NOT NULL,
    -> `warehouse_id` INT(11) NOT NULL,
    -> `name` VARCHAR(64) NOT NULL COLLATE 'utf8_unicode_ci',
    -> PRIMARY KEY (`warehouse_id`, `id`),
    -> CONSTRAINT `FK_7070DD8C5080ECDE` FOREIGN KEY (`warehouse_id`) REFERENCES
    -> `warehouses` (`id`) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;                                   +--------------------------+---------------------+-----------------+--------+---------------------------------------+
| ID                       | FOR_NAME            | REF_NAME        | N_COLS | TYPE                                  |
+--------------------------+---------------------+-----------------+--------+---------------------------------------+
| test/FK_7070DD8C5080ECDE | test/warehouse_bins | test/warehouses |      1 | ON DELETE CASCADE ON UPDATE NO ACTION |
+--------------------------+---------------------+-----------------+--------+---------------------------------------+
1 row in set (0.03 sec)

Has the MySQL specification changed?

In section 24.36.11 of the documentation (https://dev.mysql.com/doc/refman/8.0/en/innodb-foreign-table.html), it is stated that the field "TYPE" yields only bit flags and not strings (like "ON DELETE" or "ON UPDATE").

You should not change the expected behavior, unless that is your intention. There are several ORMs that rely heavily on this mechanism.

Posted by developer:
 
Fixed as of the upcoming 8.0.15 release, and here's the changelog entry:

The INFORMATION_SCHEMA.INNODB_FOREIGN TYPE column did not report the
correct values.