Skip to content

J
joohanhong
Switch branch/tag
Find file
Normal viewHistoryPermalink
table_definition_cache_functionality.test 8.56 KB
Newer Older
JooHan Hong's avatar
SWARM, Docker 소스파일 init
a1dce81a
 
JooHan Hong committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 
###############################################################################
#                                                                             #
# Variable Name: table_definition_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_definition_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

echo '#________________________VAR_05_table_definition_cache__________________#'
echo '##'
--echo '#---------------------WL6372_VAR_5_01----------------------#'
####################################################################
#   Checking default value                                         #
####################################################################
SELECT COUNT(@@GLOBAL.table_definition_cache);
--echo 1 Expected

SET @@GLOBAL.table_definition_cache=DEFAULT;
SELECT IF (@@open_files_limit < 5000, 1400, @@GLOBAL.table_definition_cache);
--echo 1400 Expected


--echo '#---------------------WL6372_VAR_5_02----------------------#'
#################################################################################
# Checking the Default value post starting the server with other value          #
#################################################################################
--echo # Restart server with table_definition_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_definition_cache=2000  " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
-- enable_reconnect
-- source include/wait_until_connected_again.inc

SELECT @@GLOBAL.table_definition_cache;
--echo 2000 Expected

SET @@GLOBAL.table_definition_cache=DEFAULT;
SELECT IF (@@open_files_limit < 5000, 1400, @@GLOBAL.table_definition_cache);
--echo 1400 Expected


--echo '#---------------------WL6372_VAR_5_03----------------------#'
####################################################################
#   Checking Value can be set - Dynamic                            #
####################################################################

--error ER_GLOBAL_VARIABLE
SET @@local.table_definition_cache=1;
--echo Expected error 'Global variable'

--error ER_GLOBAL_VARIABLE
SET @@session.table_definition_cache=1;
--echo Expected error 'Global variable'

--disable_warnings
SET @@GLOBAL.table_definition_cache=1;
--enable_warnings

SET @@GLOBAL.table_definition_cache=DEFAULT;


SELECT IF (@@open_files_limit < 5000, 1400, @@GLOBAL.table_definition_cache);
--echo 1400 Expected

--echo '#---------------------WL6372_VAR_5_04----------------------#'
#################################################################
# Check if the value in GLOBAL Table matches value in variable  #
#################################################################
SELECT @@GLOBAL.table_definition_cache = VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
WHERE VARIABLE_NAME='table_definition_cache';
--echo 1 Expected

SELECT COUNT(@@GLOBAL.table_definition_cache);
--echo 1 Expected

SELECT COUNT(VARIABLE_VALUE)
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 
WHERE VARIABLE_NAME='table_definition_cache';
--echo 1 Expected



--echo '#---------------------WL6372_VAR_5_05----------------------#'
################################################################################
#  Checking Variable Scope                                                     #
################################################################################
SELECT @@table_definition_cache = @@GLOBAL.table_definition_cache;
--echo 1 Expected

--Error ER_INCORRECT_GLOBAL_LOCAL_VAR
SELECT COUNT(@@local.table_definition_cache);
--echo Expected error 'Variable is a GLOBAL variable'

--Error ER_INCORRECT_GLOBAL_LOCAL_VAR
SELECT COUNT(@@SESSION.table_definition_cache);
--echo Expected error 'Variable is a GLOBAL variable'

SELECT COUNT(@@GLOBAL.table_definition_cache);
--echo 1 Expected

--Error ER_BAD_FIELD_ERROR
SELECT table_definition_cache = @@SESSION.table_definition_cache;
--echo Expected error 'Unknown column table_definition_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 definition cache functionality                     #
#################################################################################
flush tables;
flush status;

set @Open_table_definitions = (select variable_value from information_schema.session_status where variable_name ='Open_table_definitions');
set @Opened_table_definitions = (select variable_value from information_schema.session_status where variable_name ='Opened_table_definitions');

--echo # create 2 tables 

--disable_warnings
DROP TABLE IF EXISTS tab1;
DROP TABLE IF EXISTS tab2;
--enable_warnings

let $table = tab1;
--source include/create_table.inc

let $table = tab2;
--source include/create_table.inc

select (select variable_value from information_schema.session_status where variable_name ='Open_table_definitions') = @Open_table_definitions;
--echo 1 Expected

select (select variable_value from information_schema.session_status where variable_name ='Opened_table_definitions') = @Opened_table_definitions + 2;
--echo 1 Expected

--echo # open two tables

select 1 from tab1;
--echo 1 Expected

select (select variable_value from information_schema.session_status where variable_name ='Open_table_definitions') = @Open_table_definitions + 1;
--echo 1 Expected

select (select variable_value from information_schema.session_status where variable_name ='Opened_table_definitions') = @Opened_table_definitions + 3;
--echo 1 Expected

select 1 from tab2;
--echo 1 Expected

select (select variable_value from information_schema.session_status where variable_name ='Open_table_definitions') = @Open_table_definitions + 2;
--echo 1 Expected

select (select variable_value from information_schema.session_status where variable_name ='Opened_table_definitions') = @Opened_table_definitions + 4;
--echo 1 Expected

DROP TABLE IF EXISTS tab1;

select (select variable_value from information_schema.session_status where variable_name ='Open_table_definitions') = @Open_table_definitions + 1;
--echo 1 Expected

select (select variable_value from information_schema.session_status where variable_name ='Opened_table_definitions') = @Opened_table_definitions + 4;
--echo 1 Expected


DROP TABLE IF EXISTS tab2;

select (select variable_value from information_schema.session_status where variable_name ='Open_table_definitions') = @Open_table_definitions;
--echo 1 Expected

select (select variable_value from information_schema.session_status where variable_name ='Opened_table_definitions') = @Opened_table_definitions + 4;
--echo 1 Expected




--echo #cleanup
set @@GLOBAL.table_definition_cache=DEFAULT;