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