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
|
#
# WL#6737: InnoDB: Enabling InnoDB temp-tables as default internal SE for
# MySQL Optimizer
#
--source include/no_valgrind_without_big.inc
# There are expected differences in explain output when run with and without
# binary logging. Thus, test is updated to run with binary logging ON.
# Testcase is skipped for binlog_format=MIXED and STATEMENT, since it mismatches
# result file for used_columns output (Bug#22472365).
--source include/have_binlog_format_row.inc
# Optimizer plan are printed as part of the test-case and changing page
# size will affect the optimizer plan numbers. so enable it only for 16K
################################################################################
#
# Workload will run following scenarios
# 1. Execute different kind of queries using InnoDB as temp-tables
#
################################################################################
#-----------------------------------------------------------------------------
#
# create test bed.
#
let $wl6737_auto_inc = auto_increment;
--source suite/innodb/include/create_workload_itt.inc
#-----------------------------------------------------------------------------
#
# Workload in non-read-only mode.
#
let $restart_parameters = restart: --big-tables=1;
--source include/restart_mysqld.inc
--source suite/innodb/include/query_workload_itt.inc
#-----------------------------------------------------------------------------
#
# Workload in read-only mode.
#
let $extra_restart_parameters =--big-tables=1;
--source include/restart_innodb_read_only.inc
--source suite/innodb/include/query_workload_itt.inc
let $restart_parameters = restart;
--source include/restart_mysqld.inc
#-----------------------------------------------------------------------------
#
# Specialized used-cases.
# Intrinsic table being used as part of trx workload.
#
select @@big_tables;
let $big_tables = `select @@big_tables`;
create table t0 (m int, n int, key(m)) engine=innodb;
begin;
select count(*) from t0;
set @@session.sql_mode = ANSI;
set @@session.tmp_table_size = 0;
set @@session.big_tables = 1;
select @@big_tables;
--disable_result_log
select event_name, e.min_timer_wait, min(t.min_timer_wait) from
performance_schema.events_waits_summary_global_by_event_name as e JOIN
performance_schema.events_waits_summary_by_thread_by_event_name as t USING
(event_name) group by event_name;
--enable_result_log
select * from t0;
commit;
drop table t0;
eval set session big_tables = $big_tables;
select @@big_tables;
#-----------------------------------------------------------------------------
#
# remove test bed.
#
--source suite/innodb/include/drop_workload_itt.inc
--echo #
--echo # BUG#20762059 - innodb_thread_concurrency=1 and queries using intrinsic
--echo # temp tables, causes hang
--echo #
--echo # Scenario 1: Without timezone tables
--echo #
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1);
--echo # On default connection
SET @@session.max_heap_table_size=0;
SET @@global.innodb_thread_concurrency=1;
--echo # Executing I_S query which will create and do index read on
--echo # intrinsic temporary tables
let $DISCARD_OUTPUT=`SELECT COUNT(*) FROM information_schema.innodb_tables`;
--echo # Switch to connection con1
connect (con1, localhost, root);
SELECT * FROM t1;
--echo # Switch to connection default
connection default;
DROP TABLE t1;
# Cleanup
disconnect con1;
SET @@session.max_heap_table_size = default;
SET @@global.innodb_thread_concurrency = default;
SET @@global.innodb_thread_sleep_delay = default;
--echo #
--echo # Scenario 2: with I_S query and timezone table(uses attachable
--echo # transaction)
--echo #
SET @@session.max_heap_table_size=0;
SET @@global.innodb_thread_concurrency=1;
--echo # Executing I_S query which will create and do index read on
--echo # intrinsic temporary tables
let $DISCARD_OUTPUT=`SELECT COUNT(*) FROM information_schema.innodb_tables`;
select convert_tz(0,'a','a');
# Cleanup
SET @@session.max_heap_table_size = default;
SET @@global.innodb_thread_concurrency = default;
SET @@global.innodb_thread_sleep_delay = default;
--echo #
--echo # Scenario 3: with I_S query and help table(uses attachable
--echo # transaction)
--echo #
SET @@global.innodb_thread_concurrency=1;
--echo # Executing I_S query which will create and do index read on
--echo # intrinsic temporary tables
let $DISCARD_OUTPUT = `SELECT table_catalog, table_schema, table_name, column_name FROM information_schema.columns WHERE table_catalog IS NULL OR table_catalog <> 'def'`;
HELP '%function_2';
# Cleanup
SET @@global.innodb_thread_concurrency = default;
SET @@global.innodb_thread_sleep_delay = default;
--echo #
--echo # Bug#21871451: INNODB: FAILING ASSERTION: !(&BUF_POOL->MUTEX)
--echo #
SELECT 'SUCCESS_INNODB_CMPMEM' FROM DUAL WHERE EXISTS
(SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM);
SELECT 'SUCCESS_INNODB_CMP' FROM DUAL WHERE EXISTS
(SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET);
|
