1set @save_query_cache_size=@@query_cache_size; 2# 3# MDEV-12485: foreign key on delete cascade stale entries with 4# query cache enabled 5# 6SET NAMES utf8; 7set global query_cache_type=1; 8set global query_cache_size=1024*1024; 9set query_cache_type=1; 10create table t1 ( id int unsigned auto_increment, primary key(id) ) engine=innodb; 11create table t2 ( t2id int unsigned, id int unsigned, primary key(t2id, id), foreign key (`id`) references t1(`id`) on delete cascade ) engine=innodb; 12insert into t1 values (1); 13insert into t2 values (1,1); 14select * from t2; 15t2id id 161 1 17show status like "Qcache_queries_in_cache"; 18Variable_name Value 19Qcache_queries_in_cache 1 20delete from t1; 21show status like "Qcache_queries_in_cache"; 22Variable_name Value 23Qcache_queries_in_cache 0 24select * from t2; 25t2id id 26optimize table t2; 27Table Op Msg_type Msg_text 28test.t2 optimize note Table does not support optimize, doing recreate + analyze instead 29test.t2 optimize status OK 30select * from t2; 31t2id id 32drop table t2; 33drop table t1; 34create database `testdatabase$ї`; 35use `testdatabase$ї`; 36create table `t1$ї` ( id int unsigned auto_increment, primary key(id) ) engine=innodb; 37create table `t2$ї` ( t2id int unsigned, id int unsigned, primary key(t2id, id), foreign key (`id`) references `t1$ї`(`id`) on delete cascade ) engine=innodb; 38insert into `t1$ї` values (1); 39insert into `t2$ї`values (1,1); 40select * from `t2$ї`; 41t2id id 421 1 43show status like "Qcache_queries_in_cache"; 44Variable_name Value 45Qcache_queries_in_cache 1 46delete from `t1$ї`; 47show status like "Qcache_queries_in_cache"; 48Variable_name Value 49Qcache_queries_in_cache 0 50select * from `t2$ї`; 51t2id id 52optimize table `t2$ї`; 53Table Op Msg_type Msg_text 54testdatabase$ї.t2$ї optimize note Table does not support optimize, doing recreate + analyze instead 55testdatabase$ї.t2$ї optimize status OK 56select * from `t2$ї`; 57t2id id 58use test; 59drop database `testdatabase$ї`; 60SET NAMES default; 61create database `#mysql50#-`; 62use `#mysql50#-`; 63create table `#mysql50#t-1` ( id int unsigned auto_increment, primary key(id) ) engine=innodb; 64create table `#mysql50#t-2` ( t2id int unsigned, id int unsigned, primary key(t2id, id), foreign key (`id`) references `#mysql50#t-1`(`id`) on delete cascade ) engine=innodb; 65insert into `#mysql50#t-1` values (1); 66insert into `#mysql50#t-2`values (1,1); 67select * from `#mysql50#t-2`; 68t2id id 691 1 70show status like "Qcache_queries_in_cache"; 71Variable_name Value 72Qcache_queries_in_cache 1 73delete from `#mysql50#t-1`; 74show status like "Qcache_queries_in_cache"; 75Variable_name Value 76Qcache_queries_in_cache 0 77select * from `#mysql50#t-2`; 78t2id id 79optimize table `#mysql50#t-2`; 80Table Op Msg_type Msg_text 81#mysql50#-.#mysql50#t-2 optimize note Table does not support optimize, doing recreate + analyze instead 82#mysql50#-.#mysql50#t-2 optimize status OK 83select * from `#mysql50#t-2`; 84t2id id 85use test; 86drop database `#mysql50#-`; 87SET NAMES default; 88FOUND 8 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err 89set global query_cache_type=DEFAULT; 90set global query_cache_size=@save_query_cache_size; 91End of 10.2 tests 92