1
############# mysql-test\t\query_cache_type_func.test #########################
3
# Variable Name: query_cache_type #
4
# Scope: GLOBAL & SESSION #
5
# Access Type: Dynamic #
6
# Data Type: enumeration #
8
# Values: ON, OFF, DEMAND #
11
# Creation Date: 2008-02-20 #
12
# Author: Sharique Abdullah #
14
# Description: Test Cases of Dynamic System Variable "query_cache_type" #
15
# that checks behavior of this variable in the following ways #
17
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18
# server-system-variables.html#option_mysqld_query_cache_type #
20
###############################################################################
32
--echo ** Connecting to con0 using root **
33
connect (con0,localhost,root,,);
34
--echo ** Connection con0 **
37
SET @start_global_value = @@GLOBAL.query_cache_type;
38
SET @start_session_value = @@SESSION.query_cache_type;
44
CREATE TABLE t1(id int, value varchar(10));
46
INSERT INTO t1 VALUES(1, 'val1');
47
INSERT INTO t1 VALUES(2, 'val2');
48
INSERT INTO t1 VALUES(3, 'val3');
51
# Clearing the query cache and setting up cache size
54
SET @@GLOBAL.query_cache_size = 65536;
57
SET @@GLOBAL.query_cache_size = 0;
58
SET @@GLOBAL.query_cache_size = 65536;
64
--echo ** TESTING SESSION SCOPE **
66
--echo '#--------------------FN_DYNVARS_135_01-------------------------#'
68
# Testing default value it should be ON by default
71
SET @@query_cache_type = DEFAULT;
72
SELECT @@query_cache_type;
75
--echo '#--------------------FN_DYNVARS_135_02-------------------------#'
77
# Testing valid possible values
80
--error ER_WRONG_VALUE_FOR_VAR
81
SET @@query_cache_type = NULL;
82
--echo Expected error: Wrong value for variable
84
SET @@query_cache_type = ON;
85
SELECT @@query_cache_type;
88
SET @@query_cache_type = OFF;
89
SELECT @@query_cache_type;
92
SET @@query_cache_type = DEMAND;
93
SELECT @@query_cache_type;
94
--echo DEMAND Expected
96
--error ER_WRONG_VALUE_FOR_VAR
97
SET @@query_cache_type = XYZ;
98
--echo Expected error: Wrong value for variable
102
# TESTING FOR VALUE ON or 1
105
--echo ** Turning cache to ON **
107
SET @@query_cache_type = ON;
109
--echo '#--------------------FN_DYNVARS_135_03-------------------------#'
111
# TESTING CACHE ADDITION
114
--echo ** CACHE ADDITION **
117
SHOW STATUS LIKE 'Qcache_queries_in_cache';
122
SHOW STATUS LIKE 'Qcache_queries_in_cache';
125
SELECT value FROM t1;
127
SHOW STATUS LIKE 'Qcache_queries_in_cache';
130
--echo '#--------------------FN_DYNVARS_135_04-------------------------#'
135
--echo ** CACHE HIT **
139
SHOW STATUS LIKE 'Qcache_queries_in_cache';
142
SELECT SQL_CACHE value FROM t1;
144
SHOW STATUS LIKE 'Qcache_queries_in_cache';
147
SELECT SQL_NO_CACHE value FROM t1;
149
SHOW STATUS LIKE 'Qcache_queries_in_cache';
152
--echo '#--------------------FN_DYNVARS_135_05-------------------------#'
154
# Testing with store procedure
157
--echo Testing with store procedure
161
CREATE PROCEDURE testProcHit()
164
SELECT SQL_CACHE value FROM t1;
171
SHOW STATUS LIKE 'Qcache_queries_in_cache';
172
--echo Expected value : 3
173
--echo ==================
174
--echo Bug#35388: Stored procedure execution causes improper behavior of query cache.
177
--echo '#--------------------FN_DYNVARS_135_06-------------------------#'
179
# TESTING FOR VALUE OFF or 0
182
--echo FLUSHING CACHE
183
SET @@GLOBAL.query_cache_size = 0;
184
SET @@GLOBAL.query_cache_size = 65536;
186
--echo ** Turning cache to OFF **
188
SET @@query_cache_type = OFF;
189
SELECT @@query_cache_type;
193
SHOW STATUS LIKE 'Qcache_queries_in_cache';
196
--echo '#--------------------FN_DYNVARS_135_07-------------------------#'
198
# TESTING QUERY WITH SQL_CACHE
201
SELECT SQL_CACHE id FROM t1;
203
SHOW STATUS LIKE 'Qcache_queries_in_cache';
206
SELECT SQL_CACHE * FROM t1;
208
SHOW STATUS LIKE 'Qcache_queries_in_cache';
211
--echo '#--------------------FN_DYNVARS_135_08-------------------------#'
213
# TESTING FOR VALUE DEMAND or 2
216
--echo FLUSHING CACHE
217
SET @@GLOBAL.query_cache_size = 0;
218
SET @@GLOBAL.query_cache_size = 65536;
220
--echo ** Turning cache to DEMAND **
222
SET @@query_cache_type = DEMAND;
223
SELECT @@query_cache_type;
225
--echo '#--------------------FN_DYNVARS_135_09-------------------------#'
227
# TESTING NORMAL QUERY
230
SELECT value,id FROM t1;
232
SHOW STATUS LIKE 'Qcache_queries_in_cache';
235
SELECT SQL_CACHE * FROM t1;
237
SHOW STATUS LIKE 'Qcache_queries_in_cache';
244
--echo ** TESTING GLOBAL SCOPE **
246
--echo '#--------------------FN_DYNVARS_135_10-------------------------#'
248
# Turning global cache to OFF
251
SET @@GLOBAL.query_cache_type = OFF;
253
--echo ** Connecting con1 using root **
254
connect (con1,localhost,root,,);
256
--echo ** Connection con1 **
259
SELECT @@query_cache_type;
262
--echo '#--------------------FN_DYNVARS_135_11-------------------------#'
264
# Turning global cache to DEMAND
266
SET @@GLOBAL.query_cache_type = DEMAND;
268
--echo ** Connecting con2 using root **
269
connect (con2,localhost,root,,);
271
--echo ** Connection con2 **
274
SELECT @@query_cache_type;
275
--echo DEMAND Expected
277
--echo '#--------------------FN_DYNVARS_135_12-------------------------#'
279
# Turning global cache to ON
281
SET @@GLOBAL.query_cache_type = ON;
283
--echo ** Connecting con3 using root **
284
connect (con3,localhost,root,,);
285
--echo ** Connection con3 **
288
SELECT @@query_cache_type;
298
--echo Disconnecting con1,con2,con3
303
--echo ** Connection con0 **
306
SET @@GLOBAL.query_cache_type = @start_global_value;
307
SET @@SESSION.query_cache_type = @start_session_value;
311
DROP PROCEDURE testProcHit;
313
--echo Disconnecting con0