1CALL mtr.add_suppression("innodb_open_files should not be greater than the open_files_limit.");
2CALL mtr.add_suppression("You must raise the value of innodb_open_files in my.cnf! Remember that InnoDB keeps all");
3CALL mtr.add_suppression("log files and all system tablespace files open for the whole time mysqld is running, and");
4CALL 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.");
5CALL 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.");
6'#________________________VAR_05_table_open_cache__________________#'
7echo '##'
8--echo '#---------------------WL6372_VAR_5_01----------------------#'
9####################################################################
10#   Checking default value                                         #
11####################################################################
12SELECT COUNT(@@GLOBAL.table_open_cache)
131 Expected
14SELECT IF(@@open_files_limit < 5000, 2000, @@GLOBAL.table_open_cache);
15IF(@@open_files_limit < 5000, 2000, @@GLOBAL.table_open_cache)
162000
172000 Expected
18'#---------------------WL6372_VAR_5_02----------------------#'
19# Restart server with table_open_cache 1
20SELECT @@GLOBAL.table_open_cache;
21@@GLOBAL.table_open_cache
221
231 Expected
24SET @@GLOBAL.table_open_cache=DEFAULT;
25SELECT @@GLOBAL.table_open_cache;
26@@GLOBAL.table_open_cache
272000
282000 Expected
29'#---------------------WL6372_VAR_5_03----------------------#'
30SET @@local.table_open_cache=1;
31ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable and should be set with SET GLOBAL
32Expected error 'Global variable'
33SET @@session.table_open_cache=1;
34ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable and should be set with SET GLOBAL
35Expected error 'Global variable'
36SET @@GLOBAL.table_open_cache=1;
37SET @@GLOBAL.table_open_cache=DEFAULT;
38SELECT @@GLOBAL.table_open_cache;
39@@GLOBAL.table_open_cache
402000
412000 Expected
42'#---------------------WL6372_VAR_5_04----------------------#'
43SELECT @@GLOBAL.table_open_cache = VARIABLE_VALUE
44FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
45WHERE VARIABLE_NAME='table_open_cache';
46@@GLOBAL.table_open_cache = VARIABLE_VALUE
471
481 Expected
49SELECT COUNT(@@GLOBAL.table_open_cache);
50COUNT(@@GLOBAL.table_open_cache)
511
521 Expected
53SELECT COUNT(VARIABLE_VALUE)
54FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
55WHERE VARIABLE_NAME='table_open_cache';
56COUNT(VARIABLE_VALUE)
571
581 Expected
59'#---------------------WL6372_VAR_5_05----------------------#'
60SELECT @@table_open_cache = @@GLOBAL.table_open_cache;
61@@table_open_cache = @@GLOBAL.table_open_cache
621
631 Expected
64SELECT COUNT(@@local.table_open_cache);
65ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable
66Expected error 'Variable is a GLOBAL variable'
67SELECT COUNT(@@SESSION.table_open_cache);
68ERROR HY000: Variable 'table_open_cache' is a GLOBAL variable
69Expected error 'Variable is a GLOBAL variable'
70SELECT COUNT(@@GLOBAL.table_open_cache);
71COUNT(@@GLOBAL.table_open_cache)
721
731 Expected
74SELECT table_open_cache = @@SESSION.table_open_cache;
75ERROR 42S22: Unknown column 'table_open_cache' in 'field list'
76Expected error 'Unknown column table_open_cache in field list'
77'#---------------------WL6372_VAR_5_06----------------------#'
78Check the size of the vardir
79The output size is in unit blocks
80TRUE
81TRUE Expected
82'#---------------------WL6372_VAR_5_07----------------------#'
83# create 3 tables and insert 1 row each
84DROP TABLE IF EXISTS tab1;
85DROP TABLE IF EXISTS tab2;
86DROP TABLE IF EXISTS tab3;
87===============
88create table & Index
89===============
90CREATE TABLE tab1 (col_1 text(10))
91ENGINE=INNODB ;
92===============
93Load the data
94===============
95SET @col_1 = repeat('a', 10);
96INSERT INTO tab1
97VALUES (@col_1);
98commit;
99===============
100create table & Index
101===============
102CREATE TABLE tab2 (col_1 text(10))
103ENGINE=INNODB ;
104===============
105Load the data
106===============
107SET @col_1 = repeat('a', 10);
108commit;
109===============
110create table & Index
111===============
112CREATE TABLE tab3 (col_1 text(10))
113ENGINE=INNODB ;
114===============
115Load the data
116===============
117SET @col_1 = repeat('a', 10);
118commit;
119flush tables;
120flush status;
121set @@GLOBAL.table_open_cache=2;
122# open two tables
123select 1 from tab1;
1241
1251
1261 Expected
127select 1 from tab2;
1281
1291 Expected
130set @opened_tables = (select variable_value from information_schema.session_status where variable_name ='Opened_tables');
131set @open_cache_hits = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits');
132set @open_cache_miss = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses');
133set @open_cache_overflow = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows');
134# table_open_cache hit 1
135select 1 from tab1;
1361
1371
1381 Expected
139# table_open_cache hit 2
140select 1 from tab2;
1411
1421 Expected
143select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables;
144(select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables
1451
1461 Expected
147select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2;
148(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2
1491
1501 Expected
151select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss;
152(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss
1531
1541 Expected
155select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow;
156(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow
1571
1581 Expected
159# open third table
160select 1 from tab3;
1611
1621 Expected
163select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1;
164(select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1
1651
1661 Expected
167select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2;
168(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 2
1691
1701 Expected
171select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1;
172(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1
1731
1741 Expected
175select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1;
176(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1
1771
1781 Expected
179flush status;
180set @global_opened_tables = (select variable_value from information_schema.global_status where variable_name ='Opened_tables');
181set @global_open_cache_hits = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits');
182set @global_open_cache_miss = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses');
183set @global_open_cache_overflow = (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows');
184# opening table 2 from another client session
185set @opened_tables = (select variable_value from information_schema.session_status where variable_name ='Opened_tables');
186set @open_cache_hits = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits');
187set @open_cache_miss = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses');
188set @open_cache_overflow = (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows');
189select 1 from tab2;
1901
1911 Expected
192select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables;
193(select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables
1941
1951 Expected
196select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 1;
197(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits + 1
1981
1991 Expected
200select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss;
201(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss
2021
2031 Expected
204select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow;
205(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow
2061
2071 Expected
208flush status;
209select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables;
210(select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables
2111
2121 Expected
213select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1;
214(select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1
2151
2161 Expected
217select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss;
218(select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss
2191
2201 Expected
221select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow;
222(select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow
2231
2241 Expected
225select 1 from tab1;
2261
2271
2281 Expected
229select (select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1;
230(select variable_value from information_schema.session_status where variable_name ='Opened_tables') = @opened_tables + 1
2311
2321 Expected
233select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits;
234(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_hits') = @open_cache_hits
2351
2361 Expected
237select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1;
238(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_misses') = @open_cache_miss + 1
2391
2401 Expected
241select (select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1;
242(select variable_value from information_schema.session_status where variable_name ='Table_open_cache_overflows') = @open_cache_overflow + 1
2431
244flush status;
245select (select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables + 1;
246(select variable_value from information_schema.global_status where variable_name ='Opened_tables') = @global_opened_tables + 1
2471
2481 Expected
249select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1;
250(select variable_value from information_schema.global_status where variable_name ='Table_open_cache_hits') = @global_open_cache_hits + 1
2511
2521 Expected
253select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss + 1;
254(select variable_value from information_schema.global_status where variable_name ='Table_open_cache_misses') = @global_open_cache_miss + 1
2551
2561 Expected
257select (select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow + 1;
258(select variable_value from information_schema.global_status where variable_name ='Table_open_cache_overflows') = @global_open_cache_overflow + 1
2591
2601 Expected
261#cleanup
262DROP TABLE IF EXISTS tab1;
263DROP TABLE IF EXISTS tab2;
264DROP TABLE IF EXISTS tab3;
265set @@GLOBAL.table_open_cache=DEFAULT;
266