| 2017-07-19 11:07:09 |
Arturas Moskvinas |
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_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
```
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_key_checks=0;
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_key_checks=1;
test> insert into b values('1', 'b');
service mysql restart
test> select * from a;
ERROR 1146 (42S02): Table 'test.a' doesn't exist
``` |
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_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
```
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_key_checks=0;
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_key_checks=1;
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. |
|
