Debian Codegraph @

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

raw
1 ################################################################################
2 # InnoDB: Reclaim disk space occupied by temporary tables online.
3 # This test case will test
4 # - Starting server with innodb_temp_tablespaces_dir system variable
5 # - Initial number of .ibt files and their size
6 # - Info population in INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES
7 # - New set of .ibt files getting allocated once initial set is exausted
8 # - Session tablespaces getting allocated for explicit temporary tables
9 # - Session tablespaces getting allocated for intrinsic temporary tables
10 # - Purpose/state of .ibt files getting displayed
11 # - Space getting reclaimed on disconnection
12 # - Test with invalid values of innodb_temp_tablespaces_dir
13 # - Disk full scenario
14 # - Creating workload and executing queries involving intrinsic tables
15 ################################################################################
16
17 --source include/have_binlog_format_row.inc
18 --source include/have_debug.inc
19 --source include/have_innodb_16k.inc
20
21 --mkdir $MYSQLTEST_VARDIR/tmp/wl11613_dir/
22 let $restart_parameters = "restart: --innodb_temp_tablespaces_dir=$MYSQLTEST_VARDIR/tmp/wl11613_dir";
23 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
24 --source include/restart_mysqld.inc
25 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
26 SELECT @@innodb_temp_tablespaces_dir;
27
28 --echo # Check the initial number of .ibt files and their size
29 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
30 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
31
32 --echo # Check connection_id() of session is populated correctly in
33 --echo # ID column of INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES
34 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
35 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES WHERE ID = connection_id();
36
37 --echo # Check the default location of innodb_temp_tablespaces_dir
38 let $restart_parameters=;
39 --source include/restart_mysqld.inc
40 let $MYSQLD_DATADIR=`SELECT @@datadir`;
41
42 --replace_regex /\.[\\\/]#innodb_temp[\\\/]/#innodb_temp/
43 SELECT @@innodb_temp_tablespaces_dir;
44
45 # Using on disk tablespace for intrinsic optimiser tables
46 SET GLOBAL big_tables=ON;
47
48 --echo # Create a new connection.
49 connect (con1, localhost, root,,);
50
51 connection con1;
52
53 --echo # A session tablespace will be allocated to connection 1
54 --echo # for explicit temporary tables and state will be active
55
56 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
57 CREATE TEMPORARY TABLE test.t2(a INT, b BLOB);
58 INSERT INTO t1 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
59 INSERT INTO t2 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
60
61 INSERT INTO t1 SELECT * FROM t2;
62 INSERT INTO t2 SELECT * FROM t1;
63 INSERT INTO t1 SELECT * FROM t2;
64 INSERT INTO t2 SELECT * FROM t1;
65 INSERT INTO t1 SELECT * FROM t2;
66 INSERT INTO t2 SELECT * FROM t1;
67 INSERT INTO t1 SELECT * FROM t2;
68 INSERT INTO t2 SELECT * FROM t1;
69 INSERT INTO t1 SELECT * FROM t2;
70 INSERT INTO t2 SELECT * FROM t1;
71 INSERT INTO t1 SELECT * FROM t2;
72 INSERT INTO t2 SELECT * FROM t1;
73 INSERT INTO t1 SELECT * FROM t2;
74 INSERT INTO t2 SELECT * FROM t1;
75 INSERT INTO t1 SELECT * FROM t2;
76 INSERT INTO t2 SELECT * FROM t1;
77
78 --echo # Create a new connection.
79 connect (con2, localhost, root,,);
80 connection con2;
81
82 --echo # A session tablespace will be allocated to connection 2
83 --echo # for explicit temporary tables
84 --echo # One more session tablespace will be allocated for intrinsic
85 --echo # temporary tables on executing select query from
86 --echo # INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES
87 --echo # State for both tablespaces would be active
88 --echo # The purpose for internal tablespace would be intrinsic
89 --echo # The purpose for tablespace for user defined temporary tables
90 --echo # would be USER
91 --echo # The purpose of inactive(unallocated) tablespaces would be none
92
93 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
94 CREATE TEMPORARY TABLE test.t2(a INT, b BLOB);
95 INSERT INTO t1 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
96 INSERT INTO t2 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
97 INSERT INTO t1 SELECT * FROM t2;
98 INSERT INTO t2 SELECT * FROM t1;
99 INSERT INTO t1 SELECT * FROM t2;
100 INSERT INTO t2 SELECT * FROM t1;
101 INSERT INTO t1 SELECT * FROM t2;
102 INSERT INTO t2 SELECT * FROM t1;
103 INSERT INTO t1 SELECT * FROM t2;
104 INSERT INTO t2 SELECT * FROM t1;
105 INSERT INTO t1 SELECT * FROM t2;
106 INSERT INTO t2 SELECT * FROM t1;
107 INSERT INTO t1 SELECT * FROM t2;
108 INSERT INTO t2 SELECT * FROM t1;
109 INSERT INTO t1 SELECT * FROM t2;
110 INSERT INTO t2 SELECT * FROM t1;
111 INSERT INTO t1 SELECT * FROM t2;
112 INSERT INTO t2 SELECT * FROM t1;
113
114 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
115 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
116
117 --echo # Ensure entries in information_schema.processlist and
118 --echo # information_schema.innodb_session_temp_tablespaces are
119 --echo # in sync
120
121 SELECT USER, b.STATE, INFO, SIZE, a.STATE, PURPOSE FROM
122 INFORMATION_SCHEMA.PROCESSLIST a, INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES b
123 WHERE a.ID=b.ID ORDER BY SPACE;
124
125 # Creating more connections to see if a new set of session tablespaces
126 # get created once initial set of 10 ibt files get allocated.
127
128 --echo # Create a new connection.
129 connect (con3, localhost, root,,);
130 connection con3;
131
132 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
133 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
134 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
135
136 --echo # Create a new connection.
137 connect (con4, localhost, root,,);
138 connection con4;
139
140 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
141 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
142 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
143
144 --echo # Create a new connection.
145 connect (con5, localhost, root,,);
146 connection con5;
147
148 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
149 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
150 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
151
152 --echo # Create a new connection.
153 connect (con6, localhost, root,,);
154 connection con6;
155
156 --echo # This CREATE will cause 10 more session temporary tablespaces to be
157 --echo # created. Use a debug flag to simulate a failure in fallocate on Linux
158 --echo # so the code will fall back to setting the file size by writing zeros.
159 SET SESSION DEBUG = "+d,fil_create_temp_tablespace_fail_fallocate";
160 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
161 SET SESSION DEBUG = "-d,fil_create_temp_tablespace_fail_fallocate";
162
163 # At this point, all 10 tablespaces have been allocated.
164 # On executing select query from INNODB_SESSION_TEMP_TABLESPACES,
165 # new set of 10 ibt files would be created
166
167 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
168 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
169
170 disconnect con1;
171 disconnect con2;
172 disconnect con3;
173 disconnect con4;
174 disconnect con5;
175 disconnect con6;
176
177 --echo # Space would now be reclaimed and state for all sessions would be inactive
178 --echo # in INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES except for one
179 --echo # intrinsic tablespace for default connection when select query from
180 --echo # INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACE is executed from
181 --echo # default connection
182 connection default;
183 # wait for all the connections to go away and release the temporary tablespaces
184 let $wait_timeout= 60;
185 let $wait_condition= SELECT COUNT(*) = 1 from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES WHERE STATE = 'ACTIVE';
186 --source include/wait_condition.inc
187 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
188 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
189
190 --source include/shutdown_mysqld.inc
191 --echo "List files in #innodb_temp. It should be empty"
192 --list_files $MYSQLD_DATADIR/#innodb_temp/
193 --echo "Check if ibtmp1 exists in datadir. There should be no ibtmp1"
194 --error 1
195 --file_exists $MYSQLD_DATADIR/ibtmp1
196
197 # Note: Relative path for innodb_temp_tablespaces_dir is with respect to datadir.
198 # Any directory inside datadir is not allowed for innodb_temp_tablespaces_dir
199
200 --echo "Test with invalid values of innodb_temp_tablespaces_dir"
201 --echo "invalid abc dir"
202 --error 1,42
203 --exec $MYSQLD --datadir=$MYSQLD_DATADIR --innodb_temp_tablespaces_dir="abc"
204
205 --echo "invalid ./sys/ dir"
206 --error 1,42
207 --exec $MYSQLD --datadir=$MYSQLD_DATADIR --innodb_temp_tablespaces_dir="./sys/"
208
209 --echo "invalid /wl11613-do-not-exists/ dir"
210 --error 1,42
211 --exec $MYSQLD --datadir=$MYSQLD_DATADIR --innodb_temp_tablespaces_dir="/wl11613-do-not-exist/"
212
213 --echo "invalid empty \"\" "
214 --error 1,42
215 --exec $MYSQLD --datadir=$MYSQLD_DATADIR --innodb_temp_tablespaces_dir=""
216
217 --echo "invalid #innodb_temp "
218 --error 1,42
219 --exec $MYSQLD --datadir=$MYSQLD_DATADIR --innodb_temp_tablespaces_dir="#innodb_temp"
220
221 # Remove #innodb_temp directory manually and check if on next restart the
222 # directory is getting created or not. It should be created and server
223 # should successfully start
224
225 --rmdir $MYSQLD_DATADIR/#innodb_temp
226 let $restart_parameters=;
227 --source include/start_mysqld.inc
228
229 --echo "Check if ibtmp1 exists in datadir. It should be recreated on restart"
230 --file_exists $MYSQLD_DATADIR/ibtmp1
231
232 # Create a dummy file in #innodb_temp directory manually and see if
233 # this is shown in INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES
234
235 --write_file $MYSQLD_DATADIR/#innodb_temp/temp_11.ibt
236 test contents
237 EOF
238
239 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
240 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
241
242 --echo # Simulating disk space full scenario
243 SET GLOBAL big_tables=ON;
244
245 --replace_regex /\.[\\\/]#innodb_temp[\\\/]/#innodb_temp/
246 SELECT @@innodb_temp_tablespaces_dir;
247
248 --echo # Create a new connection.
249 connect (con1, localhost, root,,);
250
251 connection con1;
252 SET SESSION DEBUG='+d,ibt_pool_exhausted';
253 --error ER_NO_SESSION_TEMP
254 CREATE TEMPORARY TABLE t1 (a INT);
255 SET SESSION DEBUG='-d,ibt_pool_exhausted';
256
257 CREATE TABLE t3(a INT, b BLOB);
258 INSERT INTO t3 VALUES (1, 'hi'), (2, 'hello');
259
260 SET SESSION DEBUG='+d,ibt_pool_exhausted';
261 --replace_regex /tablespace.*failed/tablespace failed/
262 --error ER_NO_SESSION_TEMP
263 INSERT INTO t3 SELECT * FROM t3;
264 SET SESSION DEBUG='-d,ibt_pool_exhausted';
265
266 --echo # Create Workload and execute queries involving intrinsic tables.
267
268 let $wl6737_auto_inc = auto_increment;
269 --disable_result_log
270 --disable_query_log
271
272 --source suite/innodb/include/create_workload_itt.inc
273 --source suite/innodb/include/query_workload_itt.inc
274
275 --enable_query_log
276 --enable_result_log
277
278 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
279 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
280
281 disconnect con1;
282 connection default;
283 --sleep 2
284 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
285 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
286
287 --source suite/innodb/include/drop_workload_itt.inc
288 SET GLOBAL big_tables=default;
289 --force-rmdir $MYSQLTEST_VARDIR/tmp/wl11613_dir/
290
291 # tablespace identifier for temporary tablespace is allowed
292 # with INNODB_STRICT_MODE ON as well as OFF
293 set @@innodb_strict_mode=0;
294 CREATE TEMPORARY table t1(a INT) TABLESPACE=innodb_temporary;
295 set @@innodb_strict_mode=1;
296 CREATE TEMPORARY table t2(a INT) TABLESPACE=innodb_temporary;
297 SHOW WARNINGS;
298
299 CREATE TEMPORARY TABLE t3 (a INT);
300 ALTER TABLE t3 TABLESPACE innodb_temporary;
301 SHOW WARNINGS;
302
303 --error ER_ILLEGAL_HA_CREATE_OPTION
304 CREATE TEMPORARY TABLE t4 (a INT) TABLESPACE=innodb_system;
305
306 --error ER_RESERVED_TABLESPACE_NAME
307 CREATE TEMPORARY TABLE t4 (a INT) TABLESPACE=mysql;
308
309 CREATE TABLESPACE tbs ADD DATAFILE 'tbs.ibd' engine=INNODB;
310 --error ER_ILLEGAL_HA_CREATE_OPTION
311 CREATE TEMPORARY TABLE t4 (a INT) TABLESPACE=tbs;
312
313
314 --replace_regex /\\#innodb_temp\\temp/\/#innodb_temp\/temp/
315 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
316
317 --echo #########################################################################
318 --echo # Cleanup
319 --echo #########################################################################
320 DROP TABLESPACE tbs;
321
322 --disable_query_log
323 call mtr.add_suppression("Unable to allocate temporary tablespace for this session");
324 call mtr.add_suppression("Failed to preallocate data for file");
325 --enable_query_log