Mysql allows modifying referenced table with incompatible charset and after mysql restart referencing(or referenced depending on order of commands) table disappears from catalog
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
High
|
Unassigned | |||
5.6 |
Triaged
|
High
|
Unassigned | |||
5.7 |
Triaged
|
High
|
Unassigned | |||
8.0 |
Fix Committed
|
High
|
Unassigned |
Bug Description
Percona servers allows changing referenced table key with incompatible charset and after mysql restart referenced (or referencing depends on steps) table is missing in catalog and can only be recovered by disabling FK check and rebuilding table.
Reproduction (Percona server 5.6.36), referencing table missing in catalog (b):
```
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_
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
```
Reproduction (Percona server 5.6.36), referenced table missing in catalog (a):
```
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_
test> alter table a modify column id varchar(1) character set utf8mb4;
test> insert into b values('1', 'b');
test> delete from b;
test> set foreign_
test> insert into b values('1', 'b');
service mysql restart
test> select * from a;
ERROR 1146 (42S02): Table 'test.a' doesn't exist
```
Expected behavior:
Mysql after restart should behave the same like it was behaving before restart itself or mysql should block such incompatible change in the first place.
Probably related to this bug is that after mysql is restarted and you get error with table missing and actually try dropping table - mysql removes metadata (*.frm file) but leaves innodb (*.ibd) behind and from then on you cannot create table with missing table name anymore or do anything with it without risking mysql crash: key_checks= 0; indb03- sjc1 test5> drop table b;
```
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_
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
test> root@perconatra
ERROR 1051 (42S02): Unknown table 'test.b'
/var/lib/ mysql/test# ls -la
total 232
drwx------ 2 mysql mysql 4096 Jul 19 11:22 .
drwx------ 31 mysql mysql 4096 Jul 19 11:10 ..
-rw-rw---- 1 mysql mysql 8556 Jul 19 11:10 a.frm
-rw-rw---- 1 mysql mysql 98304 Jul 19 11:10 a.ibd
-rw-rw---- 1 mysql mysql 114688 Jul 19 11:10 b.ibd
-rw-rw---- 1 mysql mysql 67 Jul 19 10:56 db.opt
test> create table b(a varchar(1));
ERROR 1813 (HY000): Tablespace for table '`test`.`b`' exists. Please DISCARD the tablespace before IMPORT.
```
This leaves table in limbo state