Debian Codegraph @

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

raw
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
30 SET @global_query_cache_limit = @@global.query_cache_limit;
31 SET @global_query_cache_size = @@global.query_cache_size;
32 SET @global_query_cache_type = @@global.query_cache_type;
33
34 SET GLOBAL query_cache_type= ON;
35 SET LOCAL query_cache_type= ON;
36
37 --echo ** warnings **
38 --disable_warnings
39 DROP TABLE IF EXISTS t;
40
41 --enable_warnings
42 #creating table#
43
44 --echo ** creating table **
45
46 CREATE TABLE t
47 (
48 id INT AUTO_INCREMENT PRIMARY KEY,
49 c TEXT(30)
50 );
51
52 #inserting value#
53
54 --echo **inserting value **
55
56 INSERT INTO t set c = repeat('x',29);
57 INSERT INTO t set c = concat(repeat('x',28),'r','x');
58 INSERT INTO t set c = concat(repeat('x',28),'s','y');
59 INSERT INTO t set c = concat(repeat('x',28),'g','w');
60
61 # Reset cache & flush status
62 --echo ** Reset cache values **
63 FLUSH GLOBAL STATUS;
64 RESET QUERY CACHE;
65
66 # set query cache type value to on and allocating cache size
67 --echo ** On query_cache_type **
68 SET GLOBAL query_cache_type = ON;
69
70 --echo ** Allocating cache size **
71 SET GLOBAL query_cache_size = 131072;
72
73 # reset values
74 --echo ** Reset values
75 SET GLOBAL query_cache_size = 0;
76 SET GLOBAL query_cache_size = 131072;
77 SET GLOBAL query_cache_type = ON;
78
79 --disable_cursor_protocol
80
81 --echo '#---------------------FN_DYNVARS_132_01----------------------#'
82 #
83 #Check if results are cacheing on default value #
84 #
85
86 # Reset cache & flush status
87 --echo ** Reset cache values **
88 FLUSH GLOBAL STATUS;
89 RESET QUERY CACHE;
90
91
92 #fetching results#
93 --echo ** fetching results **
94 SELECT * FROM t;
95
96 # Check status
97 --echo ** check status on not setting query_cache_limit value **
98
99 SHOW STATUS LIKE 'Qcache_not_cached';
100 SHOW STATUS LIKE 'Qcache_queries_in_cache';
101
102
103 --echo '#---------------------FN_DYNVARS_132_02----------------------#'
104 #
105 #Check if results are cacheing on setting value to 0 i.e. no caching#
106 #
107
108 # Reset cache & flush status
109 --echo ** Reset cache values **
110 FLUSH GLOBAL STATUS;
111 RESET QUERY CACHE;
112
113 #set cache limit
114 --echo ** set cache limit **
115 SET @@GLOBAL.query_cache_limit = 0;
116
117 #fetching results#
118 --echo ** fetching results **
119 --disable_ps2_protocol
120 SELECT * FROM t;
121 --enable_ps2_protocol
122
123 # Check status after setting value#
124 --echo ** Check status after setting value **
125 SHOW STATUS LIKE 'Qcache_not_cached';
126 --echo 1 Expected
127 SHOW STATUS LIKE 'Qcache_queries_in_cache';
128 --echo 0 Expected
129
130
131 --echo '#---------------------FN_DYNVARS_132_03----------------------#'
132 #
133 # Check if setting to 0 makes any difference to the cache or not #
134 #
135
136 #set cache limit to default
137 --echo ** set cache limit **
138 SET @@GLOBAL.query_cache_limit = DEFAULT;
139
140 # Reset cache & flush status
141 --echo ** Reset cache values **
142 FLUSH GLOBAL STATUS;
143 RESET QUERY CACHE;
144
145 #fetching results#
146 --echo ** fetching results **
147 SELECT * FROM t;
148
149 SHOW STATUS LIKE 'Qcache_not_cached';
150 --echo 0 Expected
151 SHOW STATUS LIKE 'Qcache_queries_in_cache';
152 --echo 1 Expected
153
154 SET @@GLOBAL.query_cache_limit = 0;
155
156 SHOW STATUS LIKE 'Qcache_not_cached';
157 --echo 0 Expected
158 SHOW STATUS LIKE 'Qcache_queries_in_cache';
159 --echo 1 Expected
160
161 #fetching results#
162 --echo ** fetching results **
163 SELECT * FROM t;
164
165 # Check status after setting value#
166 --echo ** Check status after setting value **
167
168 SHOW STATUS LIKE 'Qcache_not_cached';
169 --echo 0 Expected
170 SHOW STATUS LIKE 'Qcache_queries_in_cache';
171 --echo 1 Expected
172
173 --enable_cursor_protocol
174
175 #
176 # Cleanup
177 #
178
179 SET @@GLOBAL.query_cache_limit = @global_query_cache_limit;
180 SET GLOBAL query_cache_size = @global_query_cache_size;
181 SET GLOBAL query_cache_type = @global_query_cache_type;
182
183 --disable_warnings
184 DROP TABLE IF EXISTS t;
185 --enable_warnings
186