Description:
When parent/child FK columns have mismatches, after db restart, db would first complain table missing (bug #106078). After disable foreign_key_checks, the table reappears. Now you can enable foreign_key_checks and still access the two tables with mismatching FK columns. What is worse, it allows new rows that violates referential constraint.
How to repeat:
mysql> CREATE TABLE `parent` (
-> `name` varchar(20) NOT NULL,
-> PRIMARY KEY (`name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> CREATE TABLE `child` (
-> `id` int(11) NOT NULL,
-> `p_name` varchar(20) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `p_name_idx` (`p_name`),
-> CONSTRAINT `fk_pname` FOREIGN KEY (`p_name`) REFERENCES `parent` (`name`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> insert into parent ('aa');
-- Turn off foreign_key_checks
mysql> SET foreign_key_checks = 0 ;
Query OK, 0 rows affected (0.00 sec)
-- Change column definition to mismatching charset
mysql> alter table child modify p_name varchar(20) character set latin1 DEFAULT NULL;
Query OK, 0 rows affected (0.08 sec)
-- Restart db
mysql> select * from test.parent;
+------+
| name |
+------+
| aa |
+------+
mysql> use test;
mysql> select * from child;
ERROR 1146 (42S02): Table 'test.child' doesn't exist
mysql> set foreign_key_checks = 0;
mysql> insert into child values (6, 'random');
Query OK, 1 row affected (0.00 sec)
mysql> set foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)
-- With foreign_key_checks enforced, it allows values which violate referential constraint
mysql> insert into child values (7, 'random');
Query OK, 1 row affected (0.00 sec)
-- Create a new connection
mysql> use test;
-- With foreign_key_checks being the default, it allows values which violate referential constraint
mysql> insert into child values (8, 'random');
Suggested fix:
When foreign_key_checks is enforced, it shouldn't allow values that violate referential constraint.
With 8.0.18, after db restart, it accepts the mismatch between FK columns while it doesn't allow row values that violates referential constraint.
