Edit File: table_open_cache_functionality.test
############################################################################### # # # Variable Name: table_open_cache # # Scope: Global # # Access Type: Dynamic # # Data Type: numeric # # # # # # Creation Date: 2012-08-31 # # Author : Tanjot Singh Uppal # # # # # # Description:Test Cases of Dynamic System Variable table_open_cache # # that checks the behavior of this variable in the following ways # # * Value Check # # * Scope Check # # * Functionality Check # # * Accessability Check # # # # This test does not perform the crash recovery on this variable # # For crash recovery test on default change please run the ibtest # ############################################################################### -- source include/have_innodb.inc -- source include/not_embedded.inc -- source include/have_innodb_16k.inc CALL mtr.add_suppression("innodb_open_files should not be greater than the open_files_limit."); CALL mtr.add_suppression("Warning: you must raise the value of "); CALL mtr.add_suppression(" InnoDB: Warning: too many (.*) files stay open"); CALL mtr.add_suppression(" while the maximum"); CALL mtr.add_suppression("InnoDB: allowed value would be 1."); CALL mtr.add_suppression("InnoDB: You may need to raise the value of"); CALL mtr.add_suppression(" innodb_open_files in"); CALL mtr.add_suppression("InnoDB: my.cnf."); if (`select $PS_PROTOCOL != 0`) { --skip Test requires: ps-protocol disabled } echo '#________________________VAR_05_table_open_cache__________________#' echo '##' --echo '#---------------------WL6372_VAR_5_01----------------------#' #################################################################### # Checking default value # #################################################################### SELECT COUNT(@@GLOBAL.table_open_cache); --echo 1 Expected SELECT IF(@@open_files_limit < 5000, 2000, @@GLOBAL.table_open_cache); --echo 2000 Expected --echo '#---------------------WL6372_VAR_5_02----------------------#' ################################################################################# # Checking the Default value post starting the server with other value # ################################################################################# --echo # Restart server with table_open_cache 1 let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; --exec echo "wait" > $restart_file --shutdown_server 10 --source include/wait_until_disconnected.inc -- exec echo "restart:--table_open_cache=1 " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect -- enable_reconnect -- source include/wait_until_connected_again.inc SELECT @@GLOBAL.table_open_cache; --echo 1 Expected SET @@GLOBAL.table_open_cache=DEFAULT; SELECT @@GLOBAL.table_open_cache; --echo 2000 Expected --echo '#---------------------WL6372_VAR_5_03----------------------#' #################################################################### # Checking Value can be set - Dynamic # #################################################################### --error ER_GLOBAL_VARIABLE SET @@local.table_open_cache=1; --echo Expected error 'Global variable' --error ER_GLOBAL_VARIABLE SET @@session.table_open_cache=1; --echo Expected error 'Global variable' SET @@GLOBAL.table_open_cache=1; SET @@GLOBAL.table_open_cache=DEFAULT; SELECT @@GLOBAL.table_open_cache; --echo 2000 Expected --echo '#---------------------WL6372_VAR_5_04----------------------#' ################################################################# # Check if the value in GLOBAL Table matches value in variable # ################################################################# SELECT @@GLOBAL.table_open_cache = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='table_open_cache'; --echo 1 Expected SELECT COUNT(@@GLOBAL.table_open_cache); --echo 1 Expected SELECT COUNT(VARIABLE_VALUE) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='table_open_cache'; --echo 1 Expected --echo '#---------------------WL6372_VAR_5_05----------------------#' ################################################################################ # Checking Variable Scope # ################################################################################ SELECT @@table_open_cache = @@GLOBAL.table_open_cache; --echo 1 Expected --Error ER_INCORRECT_GLOBAL_LOCAL_VAR SELECT COUNT(@@local.table_open_cache); --echo Expected error 'Variable is a GLOBAL variable' --Error ER_INCORRECT_GLOBAL_LOCAL_VAR SELECT COUNT(@@SESSION.table_open_cache); --echo Expected error 'Variable is a GLOBAL variable' SELECT COUNT(@@GLOBAL.table_open_cache); --echo 1 Expected --Error ER_BAD_FIELD_ERROR SELECT table_open_cache = @@SESSION.table_open_cache; --echo Expected error 'Unknown column table_open_cache in field list' --echo '#---------------------WL6372_VAR_5_06----------------------#' ############################################################################### # Checking the /Var directory size # ############################################################################### -- source include/vardir_size_check.inc --echo TRUE Expected --echo '#---------------------WL6372_VAR_5_07----------------------#' ################################################################################# # Checking the size of table cache functionality # ################################################################################# --echo # create 3 tables and insert 1 row each --disable_warnings DROP TABLE IF EXISTS tab1; DROP TABLE IF EXISTS tab2; DROP TABLE IF EXISTS tab3; --enable_warnings let $i = 1; let $table = tab1; --source include/create_table.inc --source include/Load_data.inc let $table = tab2; --source include/create_table.inc --source include/Load_data.inc let $table = tab3; --source include/create_table.inc --source include/Load_data.inc flush tables; flush status; set @@GLOBAL.table_open_cache=2; --echo # open two tables select 1 from tab1; --echo 1 Expected select 1 from tab2; --echo 1 Expected set @opened_tables = (select variable_value from information_schema.session_status where variable_name ='Opened_tables'); set @open_cache_hits = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits'); set @open_cache_miss = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses'); set @open_cache_overflow = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows'); --echo # table_open_cache hit 1 select 1 from tab1; --echo 1 Expected --echo # table_open_cache hit 2 select 1 from tab2; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow; --echo 1 Expected --echo # open third table select 1 from tab3; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1; --echo 1 Expected flush status; set @global_opened_tables = (select variable_value from information_schema.global_status where variable_name ='Opened_tables'); set @global_open_cache_hits = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits'); set @global_open_cache_miss = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses'); set @global_open_cache_overflow = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows'); --echo # opening table 2 from another client session connect (con1,localhost,root,,); set @opened_tables = (select variable_value from information_schema.session_status where variable_name ='Opened_tables'); set @open_cache_hits = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits'); set @open_cache_miss = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses'); set @open_cache_overflow = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows'); select 1 from tab2; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 1; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow; --echo 1 Expected flush status; connection default; select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables; --echo 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1; --echo 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss; --echo 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow; --echo 1 Expected connection con1; select 1 from tab1; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1; --echo 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1; flush status; connection default; select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables + 1; --echo 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1; --echo 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss + 1; --echo 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow + 1; --echo 1 Expected disconnect con1; --echo #cleanup connection default; DROP TABLE IF EXISTS tab1; DROP TABLE IF EXISTS tab2; DROP TABLE IF EXISTS tab3; set @@GLOBAL.table_open_cache=DEFAULT;