Bug #87270 Altering character set with set foreign_key_checks=0 breaks innodb table
Submitted: 1 Aug 2017 9:26 Modified: 1 Aug 2017 11:28
Reporter: Nikolai Ikhalainen Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6, 5.7, 5.7.19 OS:Any
Assigned to: CPU Architecture:Any

[1 Aug 2017 9:26] Nikolai Ikhalainen
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)
[1 Aug 2017 11:28] MySQL Verification Team
Hello Nikolai,

Thank you for the report.
Verified as described using 5.7.19 build.