Debian Codegraph @

mysql-test/suite/innodb/t/optimizer_temporary_table.test

raw
1 #
2 # WL#6737: InnoDB: Enabling InnoDB temp-tables as default internal SE for
3 # MySQL Optimizer
4 #
5
6 --source include/no_valgrind_without_big.inc
7
8 # There are expected differences in explain output when run with and without
9 # binary logging. Thus, test is updated to run with binary logging ON.
10 # Testcase is skipped for binlog_format=MIXED and STATEMENT, since it mismatches
11 # result file for used_columns output (Bug#22472365).
12 --source include/have_binlog_format_row.inc
13
14 # Optimizer plan are printed as part of the test-case and changing page
15 # size will affect the optimizer plan numbers. so enable it only for 16K
16
17
18 ################################################################################
19 #
20 # Workload will run following scenarios
21 # 1. Execute different kind of queries using InnoDB as temp-tables
22 #
23 ################################################################################
24
25 #-----------------------------------------------------------------------------
26 #
27 # create test bed.
28 #
29 let $wl6737_auto_inc = auto_increment;
30 --source suite/innodb/include/create_workload_itt.inc
31
32 #-----------------------------------------------------------------------------
33 #
34 # Workload in non-read-only mode.
35 #
36 let $restart_parameters = restart: --big-tables=1;
37 --source include/restart_mysqld.inc
38
39 --source suite/innodb/include/query_workload_itt.inc
40
41 #-----------------------------------------------------------------------------
42 #
43 # Workload in read-only mode.
44 #
45
46 let $extra_restart_parameters =--big-tables=1;
47 --source include/restart_innodb_read_only.inc
48
49 --source suite/innodb/include/query_workload_itt.inc
50
51 let $restart_parameters = restart;
52 --source include/restart_mysqld.inc
53
54 #-----------------------------------------------------------------------------
55 #
56 # Specialized used-cases.
57 # Intrinsic table being used as part of trx workload.
58 #
59 select @@big_tables;
60 let $big_tables = `select @@big_tables`;
61 create table t0 (m int, n int, key(m)) engine=innodb;
62 begin;
63 select count(*) from t0;
64 set @@session.sql_mode = ANSI;
65 set @@session.tmp_table_size = 0;
66 set @@session.big_tables = 1;
67 select @@big_tables;
68 --disable_result_log
69 select event_name, e.min_timer_wait, min(t.min_timer_wait) from
70 performance_schema.events_waits_summary_global_by_event_name as e JOIN
71 performance_schema.events_waits_summary_by_thread_by_event_name as t USING
72 (event_name) group by event_name;
73 --enable_result_log
74 select * from t0;
75 commit;
76 drop table t0;
77 eval set session big_tables = $big_tables;
78 select @@big_tables;
79
80 #-----------------------------------------------------------------------------
81 #
82 # remove test bed.
83 #
84
85 --source suite/innodb/include/drop_workload_itt.inc
86 --echo #
87 --echo # BUG#20762059 - innodb_thread_concurrency=1 and queries using intrinsic
88 --echo # temp tables, causes hang
89 --echo #
90
91 --echo # Scenario 1: Without timezone tables
92 --echo #
93 CREATE TABLE t1(a INT);
94 INSERT INTO t1 VALUES(1);
95
96 --echo # On default connection
97 SET @@session.max_heap_table_size=0;
98 SET @@global.innodb_thread_concurrency=1;
99 --echo # Executing I_S query which will create and do index read on
100 --echo # intrinsic temporary tables
101 let $DISCARD_OUTPUT=`SELECT COUNT(*) FROM information_schema.innodb_tables`;
102
103 --echo # Switch to connection con1
104 connect (con1, localhost, root);
105 SELECT * FROM t1;
106
107 --echo # Switch to connection default
108 connection default;
109 DROP TABLE t1;
110
111 # Cleanup
112 disconnect con1;
113 SET @@session.max_heap_table_size = default;
114 SET @@global.innodb_thread_concurrency = default;
115 SET @@global.innodb_thread_sleep_delay = default;
116
117 --echo #
118 --echo # Scenario 2: with I_S query and timezone table(uses attachable
119 --echo # transaction)
120 --echo #
121 SET @@session.max_heap_table_size=0;
122 SET @@global.innodb_thread_concurrency=1;
123 --echo # Executing I_S query which will create and do index read on
124 --echo # intrinsic temporary tables
125 let $DISCARD_OUTPUT=`SELECT COUNT(*) FROM information_schema.innodb_tables`;
126 select convert_tz(0,'a','a');
127 # Cleanup
128 SET @@session.max_heap_table_size = default;
129 SET @@global.innodb_thread_concurrency = default;
130 SET @@global.innodb_thread_sleep_delay = default;
131
132 --echo #
133 --echo # Scenario 3: with I_S query and help table(uses attachable
134 --echo # transaction)
135 --echo #
136 SET @@global.innodb_thread_concurrency=1;
137 --echo # Executing I_S query which will create and do index read on
138 --echo # intrinsic temporary tables
139 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'`;
140 HELP '%function_2';
141 # Cleanup
142 SET @@global.innodb_thread_concurrency = default;
143 SET @@global.innodb_thread_sleep_delay = default;
144
145 --echo #
146 --echo # Bug#21871451: INNODB: FAILING ASSERTION: !(&BUF_POOL->MUTEX)
147 --echo #
148
149 SELECT 'SUCCESS_INNODB_CMPMEM' FROM DUAL WHERE EXISTS
150 (SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM);
151
152 SELECT 'SUCCESS_INNODB_CMP' FROM DUAL WHERE EXISTS
153 (SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET);