Description:
When the corresponding FK columns are inconsistent, after db restart, subsequent table access would report "table not exist" which is misleading and causing panic. The error log does state the FK issue and suggest to turn on foreign_key_checks.
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)
Records: 0 Duplicates: 0 Warnings: 0
-- Turn on foreign_key_checks
mysql> SET foreign_key_checks = 1;
-- table access operates normally despite the mismatching charset
mysql> select * from child;
Empty set (0.01 sec)
mysql> select * from parent;
+------+
| name |
+------+
| aa |
+------+
Now restart the database
mysql> use test;
mysql> show create table parent;
ERROR 1146 (42S02): Table 'test.parent' doesn't exist
mysql> select * from parent;
ERROR 1146 (42S02): Table 'test.parent' doesn't exist
With 8.0.18, the behavior is different. After db restart, it accesses the tables normally despite the foreign key column mismatch. This is not expected either.
Suggested fix:
The ALTER TABLE shouldn't be allowed at the first place, which is reported in bug #106077.
The "SET foreign_key_checks = 1" should check referential constraint and fail if mismatch was detected.
On db restart, since foreign_key_checks is on, table access should report FK issue as what is stated in the error log, rather than reporting table doesn't exist.
Description: When the corresponding FK columns are inconsistent, after db restart, subsequent table access would report "table not exist" which is misleading and causing panic. The error log does state the FK issue and suggest to turn on foreign_key_checks. 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) Records: 0 Duplicates: 0 Warnings: 0 -- Turn on foreign_key_checks mysql> SET foreign_key_checks = 1; -- table access operates normally despite the mismatching charset mysql> select * from child; Empty set (0.01 sec) mysql> select * from parent; +------+ | name | +------+ | aa | +------+ Now restart the database mysql> use test; mysql> show create table parent; ERROR 1146 (42S02): Table 'test.parent' doesn't exist mysql> select * from parent; ERROR 1146 (42S02): Table 'test.parent' doesn't exist With 8.0.18, the behavior is different. After db restart, it accesses the tables normally despite the foreign key column mismatch. This is not expected either. Suggested fix: The ALTER TABLE shouldn't be allowed at the first place, which is reported in bug #106077. The "SET foreign_key_checks = 1" should check referential constraint and fail if mismatch was detected. On db restart, since foreign_key_checks is on, table access should report FK issue as what is stated in the error log, rather than reporting table doesn't exist.