Description:
The character set of the foreign key field is different, causing the table to be lost after restart.
error.log
2022-05-17T01:55:49.704838Z 2 [Warning] InnoDB: Load table `test`.`t2` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2022-05-17T01:55:49.711208Z 2 [Warning] InnoDB: Load table `test`.`t2` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
How to repeat:
MTR test
create table t1(id varchar(50) not null primary key, c1 int) DEFAULT CHARSET=utf8mb4;
create table t2(id int, c1 varchar(50), KEY `idx1` (`c1`), CONSTRAINT fk_idx1 FOREIGN KEY (`c1`) REFERENCES `t1` (`id`))DEFAULT CHARSET=utf8mb4;
set foreign_key_checks=off;
alter table t1 modify `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
--echo "Restarting MySQL server"
--source include/restart_mysqld.inc
--echo "MySQL restarted"
select * from test.t1;
Result:
mysqltest: At line 11: query 'select * from test.t2' failed: 1146: Table 'test.t2' doesn't exist
