1** Setup ** 2 3connect con0,localhost,root,,; 4connect con1, localhost, root,,; 5connection con0; 6SET @start_global_value = @@GLOBAL.query_cache_wlock_invalidate; 7SET @start_session_value = @@SESSION.query_cache_wlock_invalidate; 8SET @old_cache_size = @@GLOBAL.query_cache_size; 9SET @old_cache_type = @@GLOBAL.query_cache_type; 10SET GLOBAL query_cache_type= ON; 11SET LOCAL query_cache_type= ON; 12connection con1; 13SET LOCAL query_cache_type= ON; 14connection con0; 15CREATE TABLE t1(id int, value varchar(10)); 16INSERT INTO t1 VALUES(1, 'val1'); 17INSERT INTO t1 VALUES(2, 'val2'); 18INSERT INTO t1 VALUES(3, 'val3'); 19SET GLOBAL query_cache_size = 131072; 20FLUSHING CACHE 21SET GLOBAL query_cache_size = 0; 22SET GLOBAL query_cache_size = 131072; 23SET GLOBAL query_cache_type = ON; 24Testing for value ON 25connection con0; 26SET SESSION query_cache_wlock_invalidate = ON; 27connection con1; 28SET SESSION query_cache_wlock_invalidate = ON; 29'#----------------------------FN_DYNVARS_136_01--------------#' 30Testing for cache invalidation 31SELECT * FROM t1; 32id value 331 val1 342 val2 353 val3 36SHOW STATUS LIKE 'Qcache_queries_in_cache'; 37Variable_name Value 38Qcache_queries_in_cache 1 391 Expected 40LOCK TABLE t1 WRITE; 41UNLOCK TABLES; 42SHOW STATUS LIKE 'Qcache_queries_in_cache'; 43Variable_name Value 44Qcache_queries_in_cache 0 450 Expected 46'#----------------------------FN_DYNVARS_136_02-----------------------#' 47SELECT * FROM t1; 48SHOW STATUS LIKE 'Qcache_queries_in_cache'; 49connection con1; 50SELECT * FROM t1; 51id value 521 val1 532 val2 543 val3 55connection con0; 56LOCK TABLE t1 WRITE; 57connection con1; 58** Asynchronous Execution ** 59SELECT * FROM t1; 60connection con0; 61wait until table is locked 62UNLOCK TABLES; 63connection con1; 64** Asynchronous Result ** 65id value 661 val1 672 val2 683 val3 69Testing for value OFF 70connection con0; 71SET SESSION query_cache_wlock_invalidate = OFF; 72connection con1; 73SET SESSION query_cache_wlock_invalidate = OFF; 74'#----------------------------FN_DYNVARS_136_03------------------#' 75Testing for cache invalidation 76SELECT * FROM t1; 77id value 781 val1 792 val2 803 val3 81SHOW STATUS LIKE 'Qcache_queries_in_cache'; 82Variable_name Value 83Qcache_queries_in_cache 1 841 Expected 85LOCK TABLE t1 WRITE; 86UNLOCK TABLES; 87SHOW STATUS LIKE 'Qcache_queries_in_cache'; 88Variable_name Value 89Qcache_queries_in_cache 0 900 Expected 91'#----------------------------FN_DYNVARS_136_04---------------------#' 92SELECT * FROM t1; 93id value 941 val1 952 val2 963 val3 97SHOW STATUS LIKE 'Qcache_queries_in_cache'; 98Variable_name Value 99Qcache_queries_in_cache 1 1001 Expected 101connection con1; 102SELECT * FROM t1; 103id value 1041 val1 1052 val2 1063 val3 107connection con0; 108LOCK TABLE t1 WRITE; 109connection con1; 110** Should not be blocked ** 111SELECT * FROM t1; 112id value 1131 val1 1142 val2 1153 val3 116SELECT * FROM t1; 117id value 1181 val1 1192 val2 1203 val3 121SELECT * FROM t1; 122id value 1231 val1 1242 val2 1253 val3 126connection con0; 127UNLOCK TABLES; 128connection con1; 129'#----------------------------FN_DYNVARS_136_05------------------------#' 130SET GLOBAL query_cache_wlock_invalidate = OFF; 131connect con_int1,localhost,root,,; 132connection con_int1; 133SELECT @@SESSION.query_cache_wlock_invalidate; 134@@SESSION.query_cache_wlock_invalidate 1350 136Expected Value : 0 / OFF; 137SET SESSION query_cache_wlock_invalidate = ON; 138connect con_int2,localhost,root,,; 139connection con_int2; 140SELECT @@SESSION.query_cache_wlock_invalidate; 141@@SESSION.query_cache_wlock_invalidate 1420 143Expected Value : 0 / OFF; 144SET SESSION query_cache_wlock_invalidate = OFF; 145connection con_int1; 146SELECT @@SESSION.query_cache_wlock_invalidate; 147@@SESSION.query_cache_wlock_invalidate 1481 149Expected Value : 1 / ON; 150connection con_int2; 151SELECT @@SESSION.query_cache_wlock_invalidate; 152@@SESSION.query_cache_wlock_invalidate 1530 154Expected Value : 0 / OFF; 155SELECT @@GLOBAL.query_cache_wlock_invalidate; 156@@GLOBAL.query_cache_wlock_invalidate 1570 158Expected Value : 0 / OFF; 159connection default; 160disconnect con_int1; 161disconnect con_int2; 162 163CLEANUP 164connection con0; 165SET @@GLOBAL.query_cache_wlock_invalidate = @start_global_value; 166SET @@SESSION.query_cache_wlock_invalidate = @start_session_value ; 167SET @@GLOBAL.query_cache_size = @old_cache_size; 168SET @@GLOBAL.query_cache_type = @old_cache_type; 169connection default; 170disconnect con0; 171disconnect con1; 172DROP TABLE t1; 173