1
############# mysql-test\t\Query_cache_limit_func.test ########################
3
# Variable Name: Query_cache_limit #
5
# Access Type: Dynamic #
7
# Default Value: 1048576 #
11
# Creation Date: 2008-03-02 #
12
# Author: Sharique Abdullah #
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 #
19
# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html #
21
###############################################################################
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;
36
DROP TABLE IF EXISTS t;
41
--echo ** creating table **
45
id INT AUTO_INCREMENT PRIMARY KEY,
51
--echo **inserting value **
53
INSERT INTO t set c = repeat('x',29);
54
INSERT INTO t set c = concat(repeat('x',28),'r','x');
55
INSERT INTO t set c = concat(repeat('x',28),'s','y');
56
INSERT INTO t set c = concat(repeat('x',28),'g','w');
58
# Reset cache & flush status
59
--echo ** Reset cache values **
63
# set query cache type value to on and allocating cache size
64
--echo ** On query_cache_type **
65
SET GLOBAL query_cache_type = ON;
67
--echo ** Allocating cache size **
68
SET GLOBAL query_cache_size = 131072;
71
--echo ** Reset values
72
SET GLOBAL query_cache_size = 0;
73
SET GLOBAL query_cache_size = 131072;
74
SET GLOBAL query_cache_type = ON;
76
--echo '#---------------------FN_DYNVARS_132_01----------------------#'
78
#Check if results are cacheing on default value #
81
# Reset cache & flush status
82
--echo ** Reset cache values **
88
--echo ** fetching results **
92
--echo ** check status on not setting query_cache_limit value **
94
SHOW STATUS LIKE 'Qcache_not_cached';
95
SHOW STATUS LIKE 'Qcache_queries_in_cache';
98
--echo '#---------------------FN_DYNVARS_132_02----------------------#'
100
#Check if results are cacheing on setting value to 0 i.e. no caching#
103
# Reset cache & flush status
104
--echo ** Reset cache values **
109
--echo ** set cache limit **
110
SET @@GLOBAL.query_cache_limit = 0;
113
--echo ** fetching results **
116
# Check status after setting value#
117
--echo ** Check status after setting value **
118
SHOW STATUS LIKE 'Qcache_not_cached';
120
SHOW STATUS LIKE 'Qcache_queries_in_cache';
124
--echo '#---------------------FN_DYNVARS_132_03----------------------#'
126
# Check if setting to 0 makes any difference to the cache or not #
129
#set cache limit to default
130
--echo ** set cache limit **
131
SET @@GLOBAL.query_cache_limit = DEFAULT;
133
# Reset cache & flush status
134
--echo ** Reset cache values **
139
--echo ** fetching results **
142
SHOW STATUS LIKE 'Qcache_not_cached';
144
SHOW STATUS LIKE 'Qcache_queries_in_cache';
147
SET @@GLOBAL.query_cache_limit = 0;
149
SHOW STATUS LIKE 'Qcache_not_cached';
151
SHOW STATUS LIKE 'Qcache_queries_in_cache';
155
--echo ** fetching results **
158
# Check status after setting value#
159
--echo ** Check status after setting value **
161
SHOW STATUS LIKE 'Qcache_not_cached';
163
SHOW STATUS LIKE 'Qcache_queries_in_cache';
170
SET @@GLOBAL.query_cache_limit = @global_query_cache_limit;
171
SET GLOBAL query_cache_size = @global_query_cache_size;
172
SET GLOBAL query_cache_type = @global_query_cache_type;
175
DROP TABLE IF EXISTS t;