1############################################################################### 2# # 3# Variable Name: table_open_cache # 4# Scope: Global # 5# Access Type: Dynamic # 6# Data Type: numeric # 7# # 8# # 9# Creation Date: 2012-08-31 # 10# Author : Tanjot Singh Uppal # 11# # 12# # 13# Description:Test Cases of Dynamic System Variable table_open_cache # 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 28CALL mtr.add_suppression("innodb_open_files should not be greater than the open_files_limit."); 29CALL mtr.add_suppression("You must raise the value of innodb_open_files in my.cnf! Remember that InnoDB keeps all"); 30CALL mtr.add_suppression("log files and all system tablespace files open for the whole time mysqld is running, and"); 31CALL mtr.add_suppression("needs to open also some .ibd files if the file-per-table storage model is used. Current open files .*, max allowed open files 1."); 32CALL mtr.add_suppression("Too many (.*) files stay open while the maximum allowed value would be 1. You may need to raise the value of innodb_open_files in my.cnf."); 33 34if (`select $PS_PROTOCOL != 0`) 35{ 36 --skip Test requires: ps-protocol disabled 37} 38 39 40 41echo '#________________________VAR_05_table_open_cache__________________#' 42echo '##' 43--echo '#---------------------WL6372_VAR_5_01----------------------#' 44#################################################################### 45# Checking default value # 46#################################################################### 47SELECT COUNT(@@GLOBAL.table_open_cache); 48--echo 1 Expected 49 50SELECT IF(@@open_files_limit < 5000, 2000, @@GLOBAL.table_open_cache); 51--echo 2000 Expected 52 53 54--echo '#---------------------WL6372_VAR_5_02----------------------#' 55################################################################################# 56# Checking the Default value post starting the server with other value # 57################################################################################# 58--echo # Restart server with table_open_cache 1 59 60let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; 61--exec echo "wait" > $restart_file 62--shutdown_server 63--source include/wait_until_disconnected.inc 64-- exec echo "restart:--table_open_cache=1 " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 65-- enable_reconnect 66-- source include/wait_until_connected_again.inc 67 68SELECT @@GLOBAL.table_open_cache; 69--echo 1 Expected 70 71SET @@GLOBAL.table_open_cache=DEFAULT; 72SELECT @@GLOBAL.table_open_cache; 73--echo 2000 Expected 74 75 76--echo '#---------------------WL6372_VAR_5_03----------------------#' 77#################################################################### 78# Checking Value can be set - Dynamic # 79#################################################################### 80 81--error ER_GLOBAL_VARIABLE 82SET @@local.table_open_cache=1; 83--echo Expected error 'Global variable' 84 85--error ER_GLOBAL_VARIABLE 86SET @@session.table_open_cache=1; 87--echo Expected error 'Global variable' 88 89SET @@GLOBAL.table_open_cache=1; 90SET @@GLOBAL.table_open_cache=DEFAULT; 91 92 93SELECT @@GLOBAL.table_open_cache; 94--echo 2000 Expected 95 96--echo '#---------------------WL6372_VAR_5_04----------------------#' 97################################################################# 98# Check if the value in GLOBAL Table matches value in variable # 99################################################################# 100--disable_warnings 101SELECT @@GLOBAL.table_open_cache = VARIABLE_VALUE 102FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 103WHERE VARIABLE_NAME='table_open_cache'; 104--echo 1 Expected 105 106SELECT COUNT(@@GLOBAL.table_open_cache); 107--echo 1 Expected 108 109SELECT COUNT(VARIABLE_VALUE) 110FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 111WHERE VARIABLE_NAME='table_open_cache'; 112--enable_warnings 113--echo 1 Expected 114 115 116 117--echo '#---------------------WL6372_VAR_5_05----------------------#' 118################################################################################ 119# Checking Variable Scope # 120################################################################################ 121SELECT @@table_open_cache = @@GLOBAL.table_open_cache; 122--echo 1 Expected 123 124--Error ER_INCORRECT_GLOBAL_LOCAL_VAR 125SELECT COUNT(@@local.table_open_cache); 126--echo Expected error 'Variable is a GLOBAL variable' 127 128--Error ER_INCORRECT_GLOBAL_LOCAL_VAR 129SELECT COUNT(@@SESSION.table_open_cache); 130--echo Expected error 'Variable is a GLOBAL variable' 131 132SELECT COUNT(@@GLOBAL.table_open_cache); 133--echo 1 Expected 134 135--Error ER_BAD_FIELD_ERROR 136SELECT table_open_cache = @@SESSION.table_open_cache; 137--echo Expected error 'Unknown column table_open_cache in field list' 138 139 140 141--echo '#---------------------WL6372_VAR_5_06----------------------#' 142############################################################################### 143# Checking the /Var directory size # 144############################################################################### 145-- source include/vardir_size_check.inc 146--echo TRUE Expected 147 148--echo '#---------------------WL6372_VAR_5_07----------------------#' 149################################################################################# 150# Checking the size of table cache functionality # 151################################################################################# 152--echo # create 3 tables and insert 1 row each 153--disable_warnings 154DROP TABLE IF EXISTS tab1; 155DROP TABLE IF EXISTS tab2; 156DROP TABLE IF EXISTS tab3; 157--enable_warnings 158 159let $i = 1; 160 161let $table = tab1; 162--source include/create_table.inc 163--source include/Load_data.inc 164 165let $table = tab2; 166--source include/create_table.inc 167--source include/Load_data.inc 168 169let $table = tab3; 170--source include/create_table.inc 171--source include/Load_data.inc 172flush tables; 173flush status; 174set @@GLOBAL.table_open_cache=2; 175--echo # open two tables 176 177select 1 from tab1; 178--echo 1 Expected 179 180select 1 from tab2; 181--echo 1 Expected 182 183--disable_warnings 184set @opened_tables = (select variable_value from information_schema.session_status where variable_name ='Opened_tables'); 185set @open_cache_hits = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits'); 186set @open_cache_miss = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses'); 187set @open_cache_overflow = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows'); 188--enable_warnings 189 190--echo # table_open_cache hit 1 191select 1 from tab1; 192--echo 1 Expected 193 194--echo # table_open_cache hit 2 195select 1 from tab2; 196--echo 1 Expected 197 198--disable_warnings 199select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables; 200--echo 1 Expected 201select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2; 202--echo 1 Expected 203select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss; 204--echo 1 Expected 205select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow; 206--enable_warnings 207--echo 1 Expected 208 209--echo # open third table 210select 1 from tab3; 211--echo 1 Expected 212 213--disable_warnings 214select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1; 215--echo 1 Expected 216select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2; 217--echo 1 Expected 218select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1; 219--echo 1 Expected 220select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1; 221--enable_warnings 222--echo 1 Expected 223 224flush status; 225 226--disable_warnings 227set @global_opened_tables = (select variable_value from information_schema.global_status where variable_name ='Opened_tables'); 228set @global_open_cache_hits = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits'); 229set @global_open_cache_miss = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses'); 230set @global_open_cache_overflow = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows'); 231--enable_warnings 232 233 234--echo # opening table 2 from another client session 235connect (con1,localhost,root,,); 236 237--disable_warnings 238set @opened_tables = (select variable_value from information_schema.session_status where variable_name ='Opened_tables'); 239set @open_cache_hits = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits'); 240set @open_cache_miss = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses'); 241set @open_cache_overflow = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows'); 242--enable_warnings 243 244select 1 from tab2; 245--echo 1 Expected 246 247--disable_warnings 248select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables; 249--echo 1 Expected 250 251select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 1; 252--echo 1 Expected 253 254select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss; 255--echo 1 Expected 256 257select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow; 258--enable_warnings 259--echo 1 Expected 260 261flush status; 262 263connection default; 264 265--disable_warnings 266select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables; 267--echo 1 Expected 268 269select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1; 270--echo 1 Expected 271 272select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss; 273--echo 1 Expected 274 275select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow; 276--enable_warnings 277--echo 1 Expected 278 279 280connection con1; 281 282select 1 from tab1; 283--echo 1 Expected 284 285--disable_warnings 286select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1; 287--echo 1 Expected 288 289select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits; 290--echo 1 Expected 291 292select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1; 293--echo 1 Expected 294 295select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1; 296--enable_warnings 297 298flush status; 299connection default; 300 301--disable_warnings 302select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables + 1; 303--echo 1 Expected 304 305select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1; 306--echo 1 Expected 307 308select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss + 1; 309--echo 1 Expected 310 311select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow + 1; 312--enable_warnings 313--echo 1 Expected 314 315disconnect con1; 316 317--echo #cleanup 318connection default; 319 320DROP TABLE IF EXISTS tab1; 321DROP TABLE IF EXISTS tab2; 322DROP TABLE IF EXISTS tab3; 323set @@GLOBAL.table_open_cache=DEFAULT; 324 325