Debian Codegraph @

mysql-test/suite/sys_vars/t/query_cache_wlock_invalidate_func.test

raw
1 ######################################################################################
2 # #
3 # Variable Name: query_cache_wlock_invalidate #
4 # Scope: GLOBAL & SESSION #
5 # Access Type: Dynamic #
6 # Data Type: boolean #
7 # Default Value: FALSE #
8 # Values: TRUE / 1, FALSE / 0 #
9 # #
10 # #
11 # Creation Date: 2008-02-21 #
12 # Author: Sharique Abdullah #
13 # Modified: HHunger 2009-02-27 Replaced sleeps, beautifications #
14 # #
15 # Description: Test Cases of Dynamic System Variable "query_cache_wlock_invalidate" #
16 # that checks behavior of this variable in the following ways #
17 # * Default Value #
18 # * Valid & Invalid values #
19 # * Scope & Access method #
20 # * Cache behaviors #
21 # #
22 # Reference: #
23 # http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
24 # #
25 ######################################################################################
26
27 --echo ** Setup **
28 --echo
29 #
30 # Setup
31 #
32
33 --source include/not_embedded.inc
34
35 # disabled due to differences in the result
36 --disable_ps_protocol
37 #
38 # Save initial value
39 #
40 connect (con0,localhost,root,,);
41 connect (con1, localhost, root,,);
42 connection con0;
43
44 SET @start_global_value = @@GLOBAL.query_cache_wlock_invalidate;
45 SET @start_session_value = @@SESSION.query_cache_wlock_invalidate;
46
47 SET @old_cache_size = @@GLOBAL.query_cache_size;
48 SET @old_cache_type = @@GLOBAL.query_cache_type;
49 SET GLOBAL query_cache_type= ON;
50 SET LOCAL query_cache_type= ON;
51 connection con1;
52 SET LOCAL query_cache_type= ON;
53 connection con0;
54
55 #
56 # Creating test table
57 #
58
59 CREATE TABLE t1(id int, value varchar(10));
60
61 INSERT INTO t1 VALUES(1, 'val1');
62 INSERT INTO t1 VALUES(2, 'val2');
63 INSERT INTO t1 VALUES(3, 'val3');
64
65 #
66 # Clearing the query cache and setting up cache size
67 #
68
69 SET GLOBAL query_cache_size = 131072;
70
71 --echo FLUSHING CACHE
72 SET GLOBAL query_cache_size = 0;
73 SET GLOBAL query_cache_size = 131072;
74
75 SET GLOBAL query_cache_type = ON;
76
77 #
78 # Testing for value ON
79 #
80 --echo Testing for value ON
81
82 connection con0;
83
84 SET SESSION query_cache_wlock_invalidate = ON;
85
86 connection con1;
87
88 SET SESSION query_cache_wlock_invalidate = ON;
89
90 --echo '#----------------------------FN_DYNVARS_136_01--------------#'
91 #
92 # Testing for cache invalidation
93 #
94
95 --echo Testing for cache invalidation
96
97 SELECT * FROM t1;
98
99 SHOW STATUS LIKE 'Qcache_queries_in_cache';
100 --echo 1 Expected
101
102 LOCK TABLE t1 WRITE;
103
104 UNLOCK TABLES;
105
106 SHOW STATUS LIKE 'Qcache_queries_in_cache';
107 --echo 0 Expected
108
109 --echo '#----------------------------FN_DYNVARS_136_02-----------------------#'
110 #
111 # Testing for blockage of access
112 #
113
114 disable_result_log;
115
116 SELECT * FROM t1;
117
118 SHOW STATUS LIKE 'Qcache_queries_in_cache';
119
120 enable_result_log;
121
122 connection con1;
123
124 SELECT * FROM t1;
125
126 connection con0;
127
128 LOCK TABLE t1 WRITE;
129
130 connection con1;
131
132 --echo ** Asynchronous Execution **
133 send SELECT * FROM t1;
134
135 connection con0;
136
137 --echo wait until table is locked
138 let $wait_condition= SELECT count(*) > 0 FROM information_schema.processlist
139 WHERE state= 'Waiting for table metadata lock';
140 --source include/wait_condition.inc
141 UNLOCK TABLES;
142
143 connection con1;
144 --echo ** Asynchronous Result **
145 reap;
146
147 #
148 # Testing for value OFF
149 #
150 --echo Testing for value OFF
151
152 connection con0;
153
154 SET SESSION query_cache_wlock_invalidate = OFF;
155
156 connection con1;
157
158 SET SESSION query_cache_wlock_invalidate = OFF;
159
160 --echo '#----------------------------FN_DYNVARS_136_03------------------#'
161 #
162 # Testing for cache invalidation
163 #
164
165 --echo Testing for cache invalidation
166
167 SELECT * FROM t1;
168
169 SHOW STATUS LIKE 'Qcache_queries_in_cache';
170 --echo 1 Expected
171
172 LOCK TABLE t1 WRITE;
173
174 UNLOCK TABLES;
175
176 SHOW STATUS LIKE 'Qcache_queries_in_cache';
177 --echo 0 Expected
178
179 --echo '#----------------------------FN_DYNVARS_136_04---------------------#'
180 #
181 # Testing for blockage of access
182 #
183
184 SELECT * FROM t1;
185
186 SHOW STATUS LIKE 'Qcache_queries_in_cache';
187 --echo 1 Expected
188
189 connection con1;
190
191 SELECT * FROM t1;
192
193 connection con0;
194 LOCK TABLE t1 WRITE;
195
196 connection con1;
197 --echo ** Should not be blocked **
198 SELECT * FROM t1;
199 SELECT * FROM t1;
200 SELECT * FROM t1;
201
202 connection con0;
203 UNLOCK TABLES;
204
205 connection con1;
206
207 --echo '#----------------------------FN_DYNVARS_136_05------------------------#'
208 #
209 # Session data integrity check & GLOBAL Value check
210 #
211
212 SET GLOBAL query_cache_wlock_invalidate = OFF;
213
214 connect (con_int1,localhost,root,,);
215 connection con_int1;
216
217 SELECT @@SESSION.query_cache_wlock_invalidate;
218 --echo Expected Value : 0 / OFF;
219 SET SESSION query_cache_wlock_invalidate = ON;
220
221 connect (con_int2,localhost,root,,);
222 connection con_int2;
223
224 SELECT @@SESSION.query_cache_wlock_invalidate;
225 --echo Expected Value : 0 / OFF;
226
227 SET SESSION query_cache_wlock_invalidate = OFF;
228
229 connection con_int1;
230 SELECT @@SESSION.query_cache_wlock_invalidate;
231 --echo Expected Value : 1 / ON;
232
233 connection con_int2;
234 SELECT @@SESSION.query_cache_wlock_invalidate;
235 --echo Expected Value : 0 / OFF;
236
237 SELECT @@GLOBAL.query_cache_wlock_invalidate;
238 --echo Expected Value : 0 / OFF;
239
240 connection default;
241 disconnect con_int1;
242 disconnect con_int2;
243
244 #
245 # Cleanup
246 #
247 --echo
248 --echo CLEANUP
249
250 connection con0;
251
252 SET @@GLOBAL.query_cache_wlock_invalidate = @start_global_value;
253 SET @@SESSION.query_cache_wlock_invalidate = @start_session_value ;
254 SET @@GLOBAL.query_cache_size = @old_cache_size;
255 SET @@GLOBAL.query_cache_type = @old_cache_type;
256
257 connection default;
258 disconnect con0;
259 disconnect con1;
260
261 DROP TABLE t1;
262
263 --enable_ps_protocol