Debian Codegraph @

mysql-test/suite/sys_vars/r/query_cache_wlock_invalidate_func.result

raw
1 ** Setup **
2
3 connect con0,localhost,root,,;
4 connect con1, localhost, root,,;
5 connection con0;
6 SET @start_global_value = @@GLOBAL.query_cache_wlock_invalidate;
7 SET @start_session_value = @@SESSION.query_cache_wlock_invalidate;
8 SET @old_cache_size = @@GLOBAL.query_cache_size;
9 SET @old_cache_type = @@GLOBAL.query_cache_type;
10 SET GLOBAL query_cache_type= ON;
11 SET LOCAL query_cache_type= ON;
12 connection con1;
13 SET LOCAL query_cache_type= ON;
14 connection con0;
15 CREATE TABLE t1(id int, value varchar(10));
16 INSERT INTO t1 VALUES(1, 'val1');
17 INSERT INTO t1 VALUES(2, 'val2');
18 INSERT INTO t1 VALUES(3, 'val3');
19 SET GLOBAL query_cache_size = 131072;
20 FLUSHING CACHE
21 SET GLOBAL query_cache_size = 0;
22 SET GLOBAL query_cache_size = 131072;
23 SET GLOBAL query_cache_type = ON;
24 Testing for value ON
25 connection con0;
26 SET SESSION query_cache_wlock_invalidate = ON;
27 connection con1;
28 SET SESSION query_cache_wlock_invalidate = ON;
29 '#----------------------------FN_DYNVARS_136_01--------------#'
30 Testing for cache invalidation
31 SELECT * FROM t1;
32 id value
33 1 val1
34 2 val2
35 3 val3
36 SHOW STATUS LIKE 'Qcache_queries_in_cache';
37 Variable_name Value
38 Qcache_queries_in_cache 1
39 1 Expected
40 LOCK TABLE t1 WRITE;
41 UNLOCK TABLES;
42 SHOW STATUS LIKE 'Qcache_queries_in_cache';
43 Variable_name Value
44 Qcache_queries_in_cache 0
45 0 Expected
46 '#----------------------------FN_DYNVARS_136_02-----------------------#'
47 SELECT * FROM t1;
48 SHOW STATUS LIKE 'Qcache_queries_in_cache';
49 connection con1;
50 SELECT * FROM t1;
51 id value
52 1 val1
53 2 val2
54 3 val3
55 connection con0;
56 LOCK TABLE t1 WRITE;
57 connection con1;
58 ** Asynchronous Execution **
59 SELECT * FROM t1;
60 connection con0;
61 wait until table is locked
62 UNLOCK TABLES;
63 connection con1;
64 ** Asynchronous Result **
65 id value
66 1 val1
67 2 val2
68 3 val3
69 Testing for value OFF
70 connection con0;
71 SET SESSION query_cache_wlock_invalidate = OFF;
72 connection con1;
73 SET SESSION query_cache_wlock_invalidate = OFF;
74 '#----------------------------FN_DYNVARS_136_03------------------#'
75 Testing for cache invalidation
76 SELECT * FROM t1;
77 id value
78 1 val1
79 2 val2
80 3 val3
81 SHOW STATUS LIKE 'Qcache_queries_in_cache';
82 Variable_name Value
83 Qcache_queries_in_cache 1
84 1 Expected
85 LOCK TABLE t1 WRITE;
86 UNLOCK TABLES;
87 SHOW STATUS LIKE 'Qcache_queries_in_cache';
88 Variable_name Value
89 Qcache_queries_in_cache 0
90 0 Expected
91 '#----------------------------FN_DYNVARS_136_04---------------------#'
92 SELECT * FROM t1;
93 id value
94 1 val1
95 2 val2
96 3 val3
97 SHOW STATUS LIKE 'Qcache_queries_in_cache';
98 Variable_name Value
99 Qcache_queries_in_cache 1
100 1 Expected
101 connection con1;
102 SELECT * FROM t1;
103 id value
104 1 val1
105 2 val2
106 3 val3
107 connection con0;
108 LOCK TABLE t1 WRITE;
109 connection con1;
110 ** Should not be blocked **
111 SELECT * FROM t1;
112 id value
113 1 val1
114 2 val2
115 3 val3
116 SELECT * FROM t1;
117 id value
118 1 val1
119 2 val2
120 3 val3
121 SELECT * FROM t1;
122 id value
123 1 val1
124 2 val2
125 3 val3
126 connection con0;
127 UNLOCK TABLES;
128 connection con1;
129 '#----------------------------FN_DYNVARS_136_05------------------------#'
130 SET GLOBAL query_cache_wlock_invalidate = OFF;
131 connect con_int1,localhost,root,,;
132 connection con_int1;
133 SELECT @@SESSION.query_cache_wlock_invalidate;
134 @@SESSION.query_cache_wlock_invalidate
135 0
136 Expected Value : 0 / OFF;
137 SET SESSION query_cache_wlock_invalidate = ON;
138 connect con_int2,localhost,root,,;
139 connection con_int2;
140 SELECT @@SESSION.query_cache_wlock_invalidate;
141 @@SESSION.query_cache_wlock_invalidate
142 0
143 Expected Value : 0 / OFF;
144 SET SESSION query_cache_wlock_invalidate = OFF;
145 connection con_int1;
146 SELECT @@SESSION.query_cache_wlock_invalidate;
147 @@SESSION.query_cache_wlock_invalidate
148 1
149 Expected Value : 1 / ON;
150 connection con_int2;
151 SELECT @@SESSION.query_cache_wlock_invalidate;
152 @@SESSION.query_cache_wlock_invalidate
153 0
154 Expected Value : 0 / OFF;
155 SELECT @@GLOBAL.query_cache_wlock_invalidate;
156 @@GLOBAL.query_cache_wlock_invalidate
157 0
158 Expected Value : 0 / OFF;
159 connection default;
160 disconnect con_int1;
161 disconnect con_int2;
162
163 CLEANUP
164 connection con0;
165 SET @@GLOBAL.query_cache_wlock_invalidate = @start_global_value;
166 SET @@SESSION.query_cache_wlock_invalidate = @start_session_value ;
167 SET @@GLOBAL.query_cache_size = @old_cache_size;
168 SET @@GLOBAL.query_cache_type = @old_cache_type;
169 connection default;
170 disconnect con0;
171 disconnect con1;
172 DROP TABLE t1;