1############# mysql-test\t\Query_cache_limit_func.test ########################
2#                                                                             #
3# Variable Name: Query_cache_limit                                            #
4# Scope: SESSION                                                              #
5# Access Type: Dynamic                                                        #
6# Data Type: NUMERIC                                                          #
7# Default Value: 1048576                                                      #
8# Min Value:       0                                                          #
9#                                                                             #
10#                                                                             #
11# Creation Date: 2008-03-02                                                   #
12# Author:  Sharique Abdullah                                                  #
13#                                                                             #
14# Description: Test Cases of Dynamic System Variable "Query_cache_limit"      #
15#              that checks behavior of this variable in the following ways    #
16#              * Functionality based on different values                      #
17#                                                                             #
18# Reference:                                                                  #
19#   http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html       #
20#                                                                             #
21###############################################################################
22
23
24--echo ** Setup **
25--echo
26#
27# Setup
28#
29
30SET @global_query_cache_limit = @@global.query_cache_limit;
31SET @global_query_cache_size = @@global.query_cache_size;
32SET @global_query_cache_type = @@global.query_cache_type;
33
34--echo ** warnings **
35--disable_warnings
36DROP TABLE IF EXISTS t;
37
38--enable_warnings
39#creating table#
40
41--echo ** creating table **
42
43CREATE TABLE t
44(
45id INT AUTO_INCREMENT PRIMARY KEY,
46c TEXT(30)
47);
48
49#inserting value#
50
51--echo **inserting value **
52
53INSERT INTO t set c = repeat('x',29);
54INSERT INTO t set c = concat(repeat('x',28),'r','x');
55INSERT INTO t set c = concat(repeat('x',28),'s','y');
56INSERT INTO t set c = concat(repeat('x',28),'g','w');
57
58# Reset cache & flush status
59--echo ** Reset cache values **
60FLUSH STATUS;
61RESET QUERY CACHE;
62
63# set query cache type value to on and allocating cache size
64--echo ** On query_cache_type **
65SET GLOBAL query_cache_type = ON;
66
67--echo ** Allocating cache size **
68SET GLOBAL query_cache_size = 131072;
69
70# reset values
71--echo ** Reset values
72SET GLOBAL query_cache_size = 0;
73SET GLOBAL query_cache_size = 131072;
74SET GLOBAL query_cache_type = ON;
75
76--echo '#---------------------FN_DYNVARS_132_01----------------------#'
77#
78#Check if results are cacheing on default value #
79#
80
81# Reset cache & flush status
82--echo ** Reset cache values **
83FLUSH STATUS;
84RESET QUERY CACHE;
85
86
87#fetching results#
88--echo ** fetching results **
89SELECT * FROM t;
90
91# Check status
92--echo ** check status on not setting query_cache_limit value **
93
94SHOW STATUS LIKE 'Qcache_not_cached';
95SHOW STATUS LIKE 'Qcache_queries_in_cache';
96
97
98--echo '#---------------------FN_DYNVARS_132_02----------------------#'
99#
100#Check if results are cacheing on setting value to 0 i.e. no caching#
101#
102
103# Reset cache & flush status
104--echo ** Reset cache values **
105FLUSH STATUS;
106RESET QUERY CACHE;
107
108#set cache limit
109--echo ** set cache limit **
110SET @@GLOBAL.query_cache_limit = 0;
111
112#fetching results#
113--echo ** fetching results **
114SELECT * FROM t;
115
116# Check status after setting value#
117--echo ** Check status after setting value **
118SHOW STATUS LIKE 'Qcache_not_cached';
119--echo 1 Expected
120SHOW STATUS LIKE 'Qcache_queries_in_cache';
121--echo 0 Expected
122
123
124--echo '#---------------------FN_DYNVARS_132_03----------------------#'
125#
126# Check if setting to 0 makes any difference to the cache or not #
127#
128
129#set cache limit to default
130--echo ** set cache limit **
131SET @@GLOBAL.query_cache_limit = DEFAULT;
132
133# Reset cache & flush status
134--echo ** Reset cache values **
135FLUSH STATUS;
136RESET QUERY CACHE;
137
138#fetching results#
139--echo ** fetching results **
140SELECT * FROM t;
141
142SHOW STATUS LIKE 'Qcache_not_cached';
143--echo 0 Expected
144SHOW STATUS LIKE 'Qcache_queries_in_cache';
145--echo 1 Expected
146
147SET @@GLOBAL.query_cache_limit = 0;
148
149SHOW STATUS LIKE 'Qcache_not_cached';
150--echo 0 Expected
151SHOW STATUS LIKE 'Qcache_queries_in_cache';
152--echo 1 Expected
153
154#fetching results#
155--echo ** fetching results **
156SELECT * FROM t;
157
158# Check status after setting value#
159--echo ** Check status after setting value **
160
161SHOW STATUS LIKE 'Qcache_not_cached';
162--echo 0 Expected
163SHOW STATUS LIKE 'Qcache_queries_in_cache';
164--echo 1 Expected
165
166#
167# Cleanup
168#
169
170SET @@GLOBAL.query_cache_limit = @global_query_cache_limit;
171SET GLOBAL query_cache_size = @global_query_cache_size;
172SET GLOBAL query_cache_type = @global_query_cache_type;
173
174--disable_warnings
175DROP TABLE IF EXISTS t;
176--enable_warnings
177
178