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