1############################################################################### 2# # 3# Variable Name: query_cache_size # 4# Scope: Global # 5# Access Type: Dynamic # 6# Data Type: numeric # 7# # 8# # 9# Creation Date: 2012-09-6 # 10# Author : Tanjot Singh Uppal # 11# # 12# # 13# Description:Test Cases of Dynamic System Variable query_cache_size # 14# that checks the behavior of this variable in the following ways # 15# * Value Check # 16# * Scope Check # 17# * Functionality Check # 18# * Accessability Check # 19# # 20# This test does not perform the crash recovery on this variable # 21# For crash recovery test on default change please run the ibtest # 22############################################################################### 23 24-- source include/have_innodb.inc 25-- source include/not_embedded.inc 26-- source include/have_innodb_16k.inc 27 28echo '#________________________VAR_02_query_cache_size__________________#' 29echo '##' 30--echo '#---------------------WL6372_VAR_2_01----------------------#' 31#################################################################### 32# Checking default value # 33#################################################################### 34SELECT COUNT(@@GLOBAL.query_cache_size); 35--echo 1 Expected 36 37SELECT @@GLOBAL.query_cache_size; 38--echo 1048576 Expected 39 40--echo '#---------------------WL6372_VAR_2_02----------------------#' 41################################################################################# 42# Checking the Default value post starting the server with other value # 43################################################################################# 44--echo # Restart server with query_cache_size 51200; 45 46let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; 47--exec echo "wait" > $restart_file 48--shutdown_server 49--source include/wait_until_disconnected.inc 50-- exec echo "restart:--query_cache_size=51200 " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 51-- enable_reconnect 52-- source include/wait_until_connected_again.inc 53 54SELECT @@GLOBAL.query_cache_size; 55--echo 51200 Expected 56 57SET @@GLOBAL.query_cache_size=DEFAULT; 58SELECT @@GLOBAL.query_cache_size; 59--echo 1048576 Expected 60 61 62 63--echo '#---------------------WL6372_VAR_2_03----------------------#' 64#################################################################### 65# Checking Value can be set - Dynamic # 66#################################################################### 67 68--error ER_GLOBAL_VARIABLE 69SET @@local.query_cache_size=1; 70--echo Expected error 'Global variable' 71 72--error ER_GLOBAL_VARIABLE 73SET @@session.query_cache_size=1; 74--echo Expected error 'Global variable' 75 76--disable_warnings 77SET @@GLOBAL.query_cache_size=1; 78--enable_warnings 79SET @@GLOBAL.query_cache_size=DEFAULT; 80 81SELECT @@GLOBAL.query_cache_size; 82--echo 1048576 Expected 83 84 85--echo '#---------------------WL6372_VAR_2_04----------------------#' 86################################################################# 87# Check if the value in GLOBAL Table matches value in variable # 88################################################################# 89--disable_warnings 90SELECT @@GLOBAL.query_cache_size = VARIABLE_VALUE 91FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 92WHERE VARIABLE_NAME='query_cache_size'; 93--enable_warnings 94--echo 1 Expected 95 96SELECT COUNT(@@GLOBAL.query_cache_size); 97--echo 1 Expected 98 99--disable_warnings 100SELECT COUNT(VARIABLE_VALUE) 101FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 102WHERE VARIABLE_NAME='query_cache_size'; 103--enable_warnings 104--echo 1 Expected 105 106 107 108--echo '#---------------------WL6372_VAR_2_05----------------------#' 109################################################################################ 110# Checking Variable Scope # 111################################################################################ 112SELECT @@query_cache_size = @@GLOBAL.query_cache_size; 113--echo 1 Expected 114 115--Error ER_INCORRECT_GLOBAL_LOCAL_VAR 116SELECT COUNT(@@local.query_cache_size); 117--echo Expected error 'Variable is a GLOBAL variable' 118 119--Error ER_INCORRECT_GLOBAL_LOCAL_VAR 120SELECT COUNT(@@SESSION.query_cache_size); 121--echo Expected error 'Variable is a GLOBAL variable' 122 123SELECT COUNT(@@GLOBAL.query_cache_size); 124--echo 1 Expected 125 126--Error ER_BAD_FIELD_ERROR 127SELECT query_cache_size = @@SESSION.query_cache_size; 128--echo Expected error 'Unknown column query_cache_size in field list' 129 130 131 132--echo '#---------------------WL6372_VAR_2_06----------------------#' 133############################################################################### 134# Checking the /Var directory size # 135############################################################################### 136-- source include/vardir_size_check.inc 137--echo TRUE Expected 138 139--echo '#---------------------WL6372_VAR_2_07----------------------#' 140################################################################################# 141# Checking the size of query cache functionality # 142################################################################################# 143--echo # create 1 table and insert 3 rows each 144--disable_warnings 145DROP TABLE IF EXISTS tab1; 146--enable_warnings 147let $i = 3; 148 149let $table = tab1; 150--source include/create_table.inc 151--source include/Load_data.inc 152 153set @@GLOBAL.query_cache_size=DEFAULT; 154 155let $Qcache_hits = `select variable_value from information_schema.global_status where variable_name ='Qcache_hits';`; 156let $Qcache_inserts = `select variable_value from information_schema.global_status where variable_name ='Qcache_inserts';`; 157let $Qcache_queries_in_cache = `select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache';`; 158let $Qcache_not_cached = `select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached';`; 159 160select 1 from tab1 limit 1; 161--echo 1 Expected 162--disable_warnings 163select variable_value from information_schema.global_status where variable_name ='Qcache_hits'; 164--echo 0 Expected 165select variable_value from information_schema.global_status where variable_name ='Qcache_inserts'; 166--echo 0 Expected 167select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache'; 168--echo 0 Expected 169select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached'; 170--enable_warnings 171--echo 17 Expected 172 173select 1 from tab1 limit 1; 174--echo 1 Expected 175--disable_warnings 176select variable_value from information_schema.global_status where variable_name ='Qcache_hits'; 177--echo 0 Expected 178select variable_value from information_schema.global_status where variable_name ='Qcache_inserts'; 179--echo 0 Expected 180select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache'; 181--echo 0 Expected 182select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached'; 183--enable_warnings 184--echo 22 Expected 185 186select 1 from tab1 limit 2; 187--echo 1 Expected 188--disable_warnings 189select variable_value from information_schema.global_status where variable_name ='Qcache_hits'; 190--echo 0 Expected 191select variable_value from information_schema.global_status where variable_name ='Qcache_inserts'; 192--echo 0 Expected 193select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache'; 194--echo 0 Expected 195select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached'; 196--enable_warnings 197--echo 27 Expected 198 199--echo # Restart server with query_cache_type ON 200 201let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; 202--exec echo "wait" > $restart_file 203--shutdown_server 204--source include/wait_until_disconnected.inc 205-- exec echo "restart:--query_cache_type=1 " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 206-- enable_reconnect 207-- source include/wait_until_connected_again.inc 208 209--disable_warnings 210let $Qcache_hits = `select variable_value from information_schema.global_status where variable_name ='Qcache_hits';`; 211let $Qcache_inserts = `select variable_value from information_schema.global_status where variable_name ='Qcache_inserts';`; 212let $Qcache_queries_in_cache = `select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache';`; 213let $Qcache_not_cached = `select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached';`; 214--enable_warnings 215 216select 1 from tab1 limit 1; 217--echo 1 Expected 218 219--disable_warnings 220select variable_value from information_schema.global_status where variable_name ='Qcache_hits'; 221--echo 0 Expected 222 223select variable_value from information_schema.global_status where variable_name ='Qcache_inserts'; 224--echo 1 Expected 225 226select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache'; 227--echo 1 Expected 228 229select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached'; 230--echo 8 Expected 231 232select 1 from tab1 limit 2; 233--echo 1 Expected 234select variable_value from information_schema.global_status where variable_name ='Qcache_hits'; 235--echo 0 Expected 236 237select variable_value from information_schema.global_status where variable_name ='Qcache_inserts'; 238--echo 2 Expected 239 240select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache'; 241--echo 2 Expected 242 243select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached'; 244--echo 12 Expected 245 246select 1 from tab1 limit 1; 247--echo 1 Expected 248select variable_value from information_schema.global_status where variable_name ='Qcache_hits'; 249--echo 1 Expected 250 251select variable_value from information_schema.global_status where variable_name ='Qcache_inserts'; 252--echo 2 Expected 253 254select variable_value from information_schema.global_status where variable_name ='Qcache_queries_in_cache'; 255--echo 2 Expected 256 257select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached'; 258--echo 16 Expected 259 260--enable_warnings 261 262--echo # opening another client session 263connect (con1,localhost,root,,); 264 265--disable_warnings 266select variable_value from information_schema.global_status where variable_name ='Qcache_not_cached'; 267--enable_warnings 268--echo 17 Expected 269 270disconnect con1; 271 272--echo #cleanup 273connection default; 274 275DROP TABLE IF EXISTS tab1; 276 277