Edit File: table_open_cache_functionality.result
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."); '#________________________VAR_05_table_open_cache__________________#' echo '##' --echo '#---------------------WL6372_VAR_5_01----------------------#' #################################################################### # Checking default value # #################################################################### SELECT COUNT(@@GLOBAL.table_open_cache) 1 Expected SELECT IF(@@open_files_limit < 5000, 2000, @@GLOBAL.table_open_cache); IF(@@open_files_limit < 5000, 2000, @@GLOBAL.table_open_cache) 2000 2000 Expected '#---------------------WL6372_VAR_5_02----------------------#' # Restart server with table_open_cache 1 SELECT @@GLOBAL.table_open_cache; @@GLOBAL.table_open_cache 1 1 Expected SET @@GLOBAL.table_open_cache=DEFAULT; SELECT @@GLOBAL.table_open_cache; @@GLOBAL.table_open_cache 2000 2000 Expected '#---------------------WL6372_VAR_5_03----------------------#' SET @@local.table_open_cache=1; ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable and should be set with SET GLOBAL Expected error 'Global variable' SET @@session.table_open_cache=1; ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable and should be set with SET GLOBAL Expected error 'Global variable' SET @@GLOBAL.table_open_cache=1; SET @@GLOBAL.table_open_cache=DEFAULT; SELECT @@GLOBAL.table_open_cache; @@GLOBAL.table_open_cache 2000 2000 Expected '#---------------------WL6372_VAR_5_04----------------------#' SELECT @@GLOBAL.table_open_cache = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='table_open_cache'; @@GLOBAL.table_open_cache = VARIABLE_VALUE 1 1 Expected SELECT COUNT(@@GLOBAL.table_open_cache); COUNT(@@GLOBAL.table_open_cache) 1 1 Expected SELECT COUNT(VARIABLE_VALUE) FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='table_open_cache'; COUNT(VARIABLE_VALUE) 1 1 Expected '#---------------------WL6372_VAR_5_05----------------------#' SELECT @@table_open_cache = @@GLOBAL.table_open_cache; @@table_open_cache = @@GLOBAL.table_open_cache 1 1 Expected SELECT COUNT(@@local.table_open_cache); ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable Expected error 'Variable is a GLOBAL variable' SELECT COUNT(@@SESSION.table_open_cache); ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable Expected error 'Variable is a GLOBAL variable' SELECT COUNT(@@GLOBAL.table_open_cache); COUNT(@@GLOBAL.table_open_cache) 1 1 Expected SELECT table_open_cache = @@SESSION.table_open_cache; ERROR 42S22: Unknown column 'table_open_cache' in 'field list' Expected error 'Unknown column table_open_cache in field list' '#---------------------WL6372_VAR_5_06----------------------#' Check the size of the vardir The output size is in unit blocks TRUE TRUE Expected '#---------------------WL6372_VAR_5_07----------------------#' # create 3 tables and insert 1 row each DROP TABLE IF EXISTS tab1; DROP TABLE IF EXISTS tab2; DROP TABLE IF EXISTS tab3; =============== create table & Index =============== CREATE TABLE tab1 (col_1 text(10)) ENGINE=INNODB ; =============== Load the data =============== SET @col_1 = repeat('a', 10); INSERT INTO tab1 VALUES (@col_1); commit; =============== create table & Index =============== CREATE TABLE tab2 (col_1 text(10)) ENGINE=INNODB ; =============== Load the data =============== SET @col_1 = repeat('a', 10); commit; =============== create table & Index =============== CREATE TABLE tab3 (col_1 text(10)) ENGINE=INNODB ; =============== Load the data =============== SET @col_1 = repeat('a', 10); commit; flush tables; flush status; set @@GLOBAL.table_open_cache=2; # open two tables select 1 from tab1; 1 1 1 Expected select 1 from tab2; 1 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'); # table_open_cache hit 1 select 1 from tab1; 1 1 1 Expected # table_open_cache hit 2 select 1 from tab2; 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables; (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow 1 1 Expected # open third table select 1 from tab3; 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1; (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1 1 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'); # opening table 2 from another client session 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; 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables; (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 1; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 1 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow 1 1 Expected flush status; select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables; (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables 1 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1; (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1 1 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss; (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss 1 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow; (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow 1 1 Expected select 1 from tab1; 1 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1; (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1 1 1 Expected select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1; (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1 1 flush status; select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables + 1; (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables + 1 1 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1; (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1 1 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss + 1; (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss + 1 1 1 Expected select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow + 1; (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow + 1 1 1 Expected #cleanup DROP TABLE IF EXISTS tab1; DROP TABLE IF EXISTS tab2; DROP TABLE IF EXISTS tab3; set @@GLOBAL.table_open_cache=DEFAULT;