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.
