Bug #91110 Foreign key reference option RESTRICT ignored after mysqldump and reload.
Submitted: 2 Jun 2018 8:07 Modified: 21 Jul 2018 14:25
Reporter: Gopal Shankar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.11 OS:Any
Assigned to: CPU Architecture:Any

[2 Jun 2018 8:07] Gopal Shankar
Description:
Foreign key reference option RESTRICT ignored after mysqldump and reload.

The problem is that the SHOW CREATE TABLE does not show foreign key option
NO ACTION and even RESTRICT. Hence mysqldump tool endups recording CREATE TABLE
without RESTRICT option. And then reloading the table makes the foreign key
reference option as NO ACTION.

How to repeat:
TEST:
# create table and views in db2
create database db2;
use db2;

CREATE TABLE t1(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;
CREATE TABLE t2(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES t1(cat_id) ON UPDATE RESTRICT
)ENGINE=InnoDB;

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SELECT *  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME='t1' OR TABLE_NAME='t2';

# dump tables and view from db2
--exec $MYSQL_DUMP db2 > $MYSQLTEST_VARDIR/tmp/testing.sql

# drop the db, tables and views
drop table t1, t2;
drop database db2;
use test;

# create db1 and reload dump
create database db1;
use db1;
--exec $MYSQL db1 < $MYSQLTEST_VARDIR/tmp/testing.sql

SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SELECT *  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME='t1' OR TABLE_NAME='t2';

# check that all tables and views could be created
show tables;
select * from t2 order by a;

drop table t1, t2;
drop database db1;
use test;
--remove_file $MYSQLTEST_VARDIR/tmp/testing.sql

RESULT:
```````
create database db2;
use db2;
CREATE TABLE t1(
cat_id int not null auto_increment primary key,
cat_name varchar(255) not null,
cat_description text
) ENGINE=InnoDB;
CREATE TABLE t2(
prd_id int not null auto_increment primary key,
prd_name varchar(355) not null,
prd_price decimal,
cat_id int not null,
FOREIGN KEY fk_cat(cat_id)
REFERENCES t1(cat_id) ON UPDATE RESTRICT
)ENGINE=InnoDB;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(255) NOT NULL,
  `cat_description` text,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `prd_id` int(11) NOT NULL AUTO_INCREMENT,
  `prd_name` varchar(355) NOT NULL,
  `prd_price` decimal(10,0) DEFAULT NULL,
  `cat_id` int(11) NOT NULL,
  PRIMARY KEY (`prd_id`),
  KEY `fk_cat` (`cat_id`),
  CONSTRAINT `fk_cat` FOREIGN KEY (`cat_id`) REFERENCES `t1` (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT *  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME='t1' OR TABLE_NAME='t2';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	db2	fk_cat	def	db2	PRIMARY	NONE	RESTRICT	NO ACTION	t2	t1
drop table t1, t2;
drop database db2;
use test;
create database db1;
use db1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(255) NOT NULL,
  `cat_description` text,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `prd_id` int(11) NOT NULL AUTO_INCREMENT,
  `prd_name` varchar(355) NOT NULL,
  `prd_price` decimal(10,0) DEFAULT NULL,
  `cat_id` int(11) NOT NULL,
  PRIMARY KEY (`prd_id`),
  KEY `fk_cat` (`cat_id`),
  CONSTRAINT `fk_cat` FOREIGN KEY (`cat_id`) REFERENCES `t1` (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT *  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE TABLE_NAME='t1' OR TABLE_NAME='t2';
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
def	db1	fk_cat	def	db1	PRIMARY	NONE	NO ACTION	NO ACTION	t2	t1

Suggested fix:
a) Discuss with innodb if they would prefer to revert the default back.

b) If answer to a) is NO, then update documentation about new default and
   show RESTRICT clause in mysqldump output.
[21 Jul 2018 14:25] Paul Dubois
Posted by developer:
 
Fixed in 8.0.13.

SHOW CREATE TABLE could omit foreign key RESTRICT options. This in
turn could cause foreign key RESTRICT options to be lost from tables
dumped with mysqldump and restored from the dump file.