1** Setup ** 2 3connect con0,localhost,root,,; 4connection con0; 5SET @start_global_value = @@GLOBAL.query_cache_type; 6SET @start_session_value = @@SESSION.query_cache_type; 7SET @start_query_cache_size= @@global.query_cache_size; 8CREATE TABLE t1(id int, value varchar(10)); 9INSERT INTO t1 VALUES(1, 'val1'); 10INSERT INTO t1 VALUES(2, 'val2'); 11INSERT INTO t1 VALUES(3, 'val3'); 12SET @@GLOBAL.query_cache_size = 65536; 13FLUSHING CACHE 14SET @@GLOBAL.query_cache_size = 0; 15SET @@GLOBAL.query_cache_size = 65536; 16** TESTING SESSION SCOPE ** 17'#--------------------FN_DYNVARS_135_01-------------------------#' 18SET @@query_cache_type = DEFAULT; 19SELECT @@query_cache_type; 20@@query_cache_type 21OFF 22OFF Expected 23SET @@query_cache_type = ON; 24ERROR HY000: Query cache is globally disabled and you can't enable it only for this session 25'#--------------------FN_DYNVARS_135_02-------------------------#' 26SET GLOBAL query_cache_type= ON; 27SET @@query_cache_type = NULL; 28ERROR 42000: Variable 'query_cache_type' can't be set to the value of 'NULL' 29Expected error: Wrong value for variable 30SET @@query_cache_type = ON; 31SELECT @@query_cache_type; 32@@query_cache_type 33ON 34ON Expected 35SET @@query_cache_type = OFF; 36SELECT @@query_cache_type; 37@@query_cache_type 38OFF 39OFF Expected 40SET @@query_cache_type = DEMAND; 41SELECT @@query_cache_type; 42@@query_cache_type 43DEMAND 44DEMAND Expected 45SET @@query_cache_type = XYZ; 46ERROR 42000: Variable 'query_cache_type' can't be set to the value of 'XYZ' 47Expected error: Wrong value for variable 48** Turning cache to ON ** 49SET @@query_cache_type = ON; 50'#--------------------FN_DYNVARS_135_03-------------------------#' 51** CACHE ADDITION ** 52 53SHOW STATUS LIKE 'Qcache_queries_in_cache'; 54Variable_name Value 55Qcache_queries_in_cache 0 560 Expected 57SELECT * FROM t1; 58id value 591 val1 602 val2 613 val3 62SHOW STATUS LIKE 'Qcache_queries_in_cache'; 63Variable_name Value 64Qcache_queries_in_cache 1 651 Expected 66SELECT value FROM t1; 67value 68val1 69val2 70val3 71SHOW STATUS LIKE 'Qcache_queries_in_cache'; 72Variable_name Value 73Qcache_queries_in_cache 2 742 Expected 75'#--------------------FN_DYNVARS_135_04-------------------------#' 76** CACHE HIT ** 77SELECT * FROM t1; 78id value 791 val1 802 val2 813 val3 82SHOW STATUS LIKE 'Qcache_queries_in_cache'; 83Variable_name Value 84Qcache_queries_in_cache 2 852 Expected 86SELECT SQL_CACHE value FROM t1; 87value 88val1 89val2 90val3 91SHOW STATUS LIKE 'Qcache_queries_in_cache'; 92Variable_name Value 93Qcache_queries_in_cache 3 943 Expected 95SELECT SQL_NO_CACHE value FROM t1; 96value 97val1 98val2 99val3 100SHOW STATUS LIKE 'Qcache_queries_in_cache'; 101Variable_name Value 102Qcache_queries_in_cache 3 1033 Expected 104'#--------------------FN_DYNVARS_135_05-------------------------#' 105Testing with store procedure 106CREATE PROCEDURE testProcHit() 107BEGIN 108SELECT * FROM t1; 109SELECT SQL_CACHE value FROM t1; 110END| 111CALL testProcHit(); 112id value 1131 val1 1142 val2 1153 val3 116value 117val1 118val2 119val3 120SHOW STATUS LIKE 'Qcache_queries_in_cache'; 121Variable_name Value 122Qcache_queries_in_cache 5 123Expected value : 3 124================== 125Bug#35388: Stored procedure execution causes improper behavior of query cache. 126'#--------------------FN_DYNVARS_135_06-------------------------#' 127FLUSHING CACHE 128SET @@GLOBAL.query_cache_size = 0; 129SET @@GLOBAL.query_cache_size = 65536; 130** Turning cache to OFF ** 131SET @@query_cache_type = OFF; 132SELECT @@query_cache_type; 133@@query_cache_type 134OFF 135SELECT id FROM t1; 136id 1371 1382 1393 140SHOW STATUS LIKE 'Qcache_queries_in_cache'; 141Variable_name Value 142Qcache_queries_in_cache 0 1430 Expected 144'#--------------------FN_DYNVARS_135_07-------------------------#' 145SELECT SQL_CACHE id FROM t1; 146id 1471 1482 1493 150SHOW STATUS LIKE 'Qcache_queries_in_cache'; 151Variable_name Value 152Qcache_queries_in_cache 0 1530 Expected 154SELECT SQL_CACHE * FROM t1; 155id value 1561 val1 1572 val2 1583 val3 159SHOW STATUS LIKE 'Qcache_queries_in_cache'; 160Variable_name Value 161Qcache_queries_in_cache 0 1620 Expected 163'#--------------------FN_DYNVARS_135_08-------------------------#' 164FLUSHING CACHE 165SET @@GLOBAL.query_cache_size = 0; 166SET @@GLOBAL.query_cache_size = 65536; 167** Turning cache to DEMAND ** 168SET @@query_cache_type = DEMAND; 169SELECT @@query_cache_type; 170@@query_cache_type 171DEMAND 172'#--------------------FN_DYNVARS_135_09-------------------------#' 173SELECT value,id FROM t1; 174value id 175val1 1 176val2 2 177val3 3 178SHOW STATUS LIKE 'Qcache_queries_in_cache'; 179Variable_name Value 180Qcache_queries_in_cache 0 1810 Expected 182SELECT SQL_CACHE * FROM t1; 183id value 1841 val1 1852 val2 1863 val3 187SHOW STATUS LIKE 'Qcache_queries_in_cache'; 188Variable_name Value 189Qcache_queries_in_cache 1 1900 Expected 191** TESTING GLOBAL SCOPE ** 192'#--------------------FN_DYNVARS_135_10-------------------------#' 193SET @@GLOBAL.query_cache_type = OFF; 194connect con1,localhost,root,,; 195connection con1; 196SELECT @@query_cache_type; 197@@query_cache_type 198OFF 199OFF Expected 200'#--------------------FN_DYNVARS_135_11-------------------------#' 201SET @@GLOBAL.query_cache_type = DEMAND; 202connect con2,localhost,root,,; 203connection con2; 204SELECT @@query_cache_type; 205@@query_cache_type 206DEMAND 207DEMAND Expected 208'#--------------------FN_DYNVARS_135_12-------------------------#' 209SET @@GLOBAL.query_cache_type = ON; 210connect con3,localhost,root,,; 211connection con3; 212SELECT @@query_cache_type; 213@@query_cache_type 214ON 215ON Expected 216 217** Cleanup ** 218 219disconnect con1; 220disconnect con2; 221disconnect con3; 222connection con0; 223SET @@GLOBAL.query_cache_type = @start_global_value; 224SET @@SESSION.query_cache_type = @start_session_value; 225SET GLOBAL query_cache_size = @start_query_cache_size; 226DROP TABLE t1; 227DROP PROCEDURE testProcHit; 228disconnect con0; 229