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

[9 Jul 2018 15:15] Angel Milanov
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.
[9 Jul 2018 15:16] Angel Milanov
Changes category.
[9 Jul 2018 18:39] Frederic Descamps
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)
[9 Jul 2018 23:38] MySQL Verification Team
Thank you fro the bug report.
[21 Aug 2018 22:12] Jimmy Yang
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)
[22 Aug 2018 8:01] Angel Milanov
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.
[11 Oct 2018 22:34] Daniel Price
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.