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