Description:
Original bug report https://bugs.launchpad.net/percona-server/+bug/1705239:
Mysql allows modifying referenced table with incompatible charset and after mysql restart referencing(or referenced depending on order of commands) table disappears from catalog:
Reproduced with 5.6 and 5.7, there is no error messages with 8.0
MySQL 5.6.37
MySQL 5.7.19-1.1.0
MySQL 8.0.2-dmr-1.1.0 no affected
How to repeat:
test> create table a(id varchar(1) character set utf8, primary key(id));
test> create table b(id varchar(1) character set utf8, b_id varchar(1) character set utf8, primary key(id), CONSTRAINT a_fkey FOREIGN KEY (b_id) REFERENCES a (id));
test> alter table a modify column id varchar(1) character set utf8mb4;
ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'a_fkey' of table 'test.b'
test> set foreign_key_checks=0;
test> alter table a modify column id varchar(1) character set utf8mb4;
service mysql restart
test> select * from b;
ERROR 1146 (42S02): Table 'test.b' doesn't exist
Drop table attempt for this "invisible" table removing frm file but not ibd.
The table became visible again with:
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from b;
Empty set (0.00 sec)
