Debian Codegraph @

mysql-test/suite/innodb/r/session_temp_tablespaces.result

raw
1 # restart: --innodb_temp_tablespaces_dir=MYSQLTEST_VARDIR/tmp/wl11613_dir
2 SELECT @@innodb_temp_tablespaces_dir;
3 @@innodb_temp_tablespaces_dir
4 MYSQLTEST_VARDIR/tmp/wl11613_dir
5 # Check the initial number of .ibt files and their size
6 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
7 PATH SIZE STATE PURPOSE
8 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_1.ibt 81920 INACTIVE NONE
9 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_2.ibt 81920 INACTIVE NONE
10 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_3.ibt 81920 INACTIVE NONE
11 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_4.ibt 81920 INACTIVE NONE
12 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_5.ibt 81920 INACTIVE NONE
13 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_6.ibt 81920 INACTIVE NONE
14 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_7.ibt 81920 INACTIVE NONE
15 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_8.ibt 81920 INACTIVE NONE
16 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_9.ibt 81920 INACTIVE NONE
17 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_10.ibt 98304 ACTIVE INTRINSIC
18 # Check connection_id() of session is populated correctly in
19 # ID column of INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES
20 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES WHERE ID = connection_id();
21 PATH SIZE STATE PURPOSE
22 MYSQLTEST_VARDIR/tmp/wl11613_dir/temp_10.ibt 98304 ACTIVE INTRINSIC
23 # Check the default location of innodb_temp_tablespaces_dir
24 # restart
25 SELECT @@innodb_temp_tablespaces_dir;
26 @@innodb_temp_tablespaces_dir
27 #innodb_temp
28 SET GLOBAL big_tables=ON;
29 # Create a new connection.
30 # A session tablespace will be allocated to connection 1
31 # for explicit temporary tables and state will be active
32 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
33 CREATE TEMPORARY TABLE test.t2(a INT, b BLOB);
34 INSERT INTO t1 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
35 INSERT INTO t2 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
36 INSERT INTO t1 SELECT * FROM t2;
37 INSERT INTO t2 SELECT * FROM t1;
38 INSERT INTO t1 SELECT * FROM t2;
39 INSERT INTO t2 SELECT * FROM t1;
40 INSERT INTO t1 SELECT * FROM t2;
41 INSERT INTO t2 SELECT * FROM t1;
42 INSERT INTO t1 SELECT * FROM t2;
43 INSERT INTO t2 SELECT * FROM t1;
44 INSERT INTO t1 SELECT * FROM t2;
45 INSERT INTO t2 SELECT * FROM t1;
46 INSERT INTO t1 SELECT * FROM t2;
47 INSERT INTO t2 SELECT * FROM t1;
48 INSERT INTO t1 SELECT * FROM t2;
49 INSERT INTO t2 SELECT * FROM t1;
50 INSERT INTO t1 SELECT * FROM t2;
51 INSERT INTO t2 SELECT * FROM t1;
52 # Create a new connection.
53 # A session tablespace will be allocated to connection 2
54 # for explicit temporary tables
55 # One more session tablespace will be allocated for intrinsic
56 # temporary tables on executing select query from
57 # INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES
58 # State for both tablespaces would be active
59 # The purpose for internal tablespace would be intrinsic
60 # The purpose for tablespace for user defined temporary tables
61 # would be USER
62 # The purpose of inactive(unallocated) tablespaces would be none
63 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
64 CREATE TEMPORARY TABLE test.t2(a INT, b BLOB);
65 INSERT INTO t1 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
66 INSERT INTO t2 values (1, 'hello'), (2, 'hi'), (3, 'wl11613'), (4, 'temp'), (5, 'tablespace');
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 INSERT INTO t1 SELECT * FROM t2;
78 INSERT INTO t2 SELECT * FROM t1;
79 INSERT INTO t1 SELECT * FROM t2;
80 INSERT INTO t2 SELECT * FROM t1;
81 INSERT INTO t1 SELECT * FROM t2;
82 INSERT INTO t2 SELECT * FROM t1;
83 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
84 PATH SIZE STATE PURPOSE
85 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
86 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
87 ./#innodb_temp/temp_3.ibt 81920 INACTIVE NONE
88 ./#innodb_temp/temp_4.ibt 81920 INACTIVE NONE
89 ./#innodb_temp/temp_5.ibt 81920 INACTIVE NONE
90 ./#innodb_temp/temp_6.ibt 81920 INACTIVE NONE
91 ./#innodb_temp/temp_7.ibt 98304 ACTIVE INTRINSIC
92 ./#innodb_temp/temp_8.ibt 950272 ACTIVE USER
93 ./#innodb_temp/temp_9.ibt 950272 ACTIVE USER
94 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
95 # Ensure entries in information_schema.processlist and
96 # information_schema.innodb_session_temp_tablespaces are
97 # in sync
98 SELECT USER, b.STATE, INFO, SIZE, a.STATE, PURPOSE FROM
99 INFORMATION_SCHEMA.PROCESSLIST a, INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES b
100 WHERE a.ID=b.ID ORDER BY SPACE;
101 USER STATE INFO SIZE STATE PURPOSE
102 root ACTIVE SELECT USER, b.STATE, INFO, SIZE, a.STATE, PURPOSE FROM
103 INFORMATION_SCHEMA.PROCESSLIST a, INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES b
104 WHERE a.ID=b.ID ORDER BY SPACE 131072 executing INTRINSIC
105 root ACTIVE SELECT USER, b.STATE, INFO, SIZE, a.STATE, PURPOSE FROM
106 INFORMATION_SCHEMA.PROCESSLIST a, INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES b
107 WHERE a.ID=b.ID ORDER BY SPACE 950272 executing USER
108 root ACTIVE NULL 950272 USER
109 root ACTIVE NULL 98304 INTRINSIC
110 Warnings:
111 Warning 1287 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead
112 # Create a new connection.
113 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
114 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
115 PATH SIZE STATE PURPOSE
116 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
117 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
118 ./#innodb_temp/temp_3.ibt 81920 INACTIVE NONE
119 ./#innodb_temp/temp_4.ibt 81920 INACTIVE NONE
120 ./#innodb_temp/temp_5.ibt 98304 ACTIVE INTRINSIC
121 ./#innodb_temp/temp_6.ibt 98304 ACTIVE USER
122 ./#innodb_temp/temp_7.ibt 131072 ACTIVE INTRINSIC
123 ./#innodb_temp/temp_8.ibt 950272 ACTIVE USER
124 ./#innodb_temp/temp_9.ibt 950272 ACTIVE USER
125 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
126 # Create a new connection.
127 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
128 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
129 PATH SIZE STATE PURPOSE
130 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
131 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
132 ./#innodb_temp/temp_3.ibt 98304 ACTIVE INTRINSIC
133 ./#innodb_temp/temp_4.ibt 98304 ACTIVE USER
134 ./#innodb_temp/temp_5.ibt 98304 ACTIVE INTRINSIC
135 ./#innodb_temp/temp_6.ibt 98304 ACTIVE USER
136 ./#innodb_temp/temp_7.ibt 131072 ACTIVE INTRINSIC
137 ./#innodb_temp/temp_8.ibt 950272 ACTIVE USER
138 ./#innodb_temp/temp_9.ibt 950272 ACTIVE USER
139 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
140 # Create a new connection.
141 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
142 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
143 PATH SIZE STATE PURPOSE
144 ./#innodb_temp/temp_1.ibt 98304 ACTIVE INTRINSIC
145 ./#innodb_temp/temp_2.ibt 98304 ACTIVE USER
146 ./#innodb_temp/temp_3.ibt 98304 ACTIVE INTRINSIC
147 ./#innodb_temp/temp_4.ibt 98304 ACTIVE USER
148 ./#innodb_temp/temp_5.ibt 98304 ACTIVE INTRINSIC
149 ./#innodb_temp/temp_6.ibt 98304 ACTIVE USER
150 ./#innodb_temp/temp_7.ibt 131072 ACTIVE INTRINSIC
151 ./#innodb_temp/temp_8.ibt 950272 ACTIVE USER
152 ./#innodb_temp/temp_9.ibt 950272 ACTIVE USER
153 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
154 # Create a new connection.
155 # This CREATE will cause 10 more session temporary tablespaces to be
156 # created. Use a debug flag to simulate a failure in fallocate on Linux
157 # so the code will fall back to setting the file size by writing zeros.
158 SET SESSION DEBUG = "+d,fil_create_temp_tablespace_fail_fallocate";
159 CREATE TEMPORARY TABLE test.t1(a INT, b BLOB);
160 SET SESSION DEBUG = "-d,fil_create_temp_tablespace_fail_fallocate";
161 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
162 PATH SIZE STATE PURPOSE
163 ./#innodb_temp/temp_1.ibt 98304 ACTIVE INTRINSIC
164 ./#innodb_temp/temp_2.ibt 98304 ACTIVE USER
165 ./#innodb_temp/temp_3.ibt 98304 ACTIVE INTRINSIC
166 ./#innodb_temp/temp_4.ibt 98304 ACTIVE USER
167 ./#innodb_temp/temp_5.ibt 98304 ACTIVE INTRINSIC
168 ./#innodb_temp/temp_6.ibt 98304 ACTIVE USER
169 ./#innodb_temp/temp_7.ibt 131072 ACTIVE INTRINSIC
170 ./#innodb_temp/temp_8.ibt 950272 ACTIVE USER
171 ./#innodb_temp/temp_9.ibt 950272 ACTIVE USER
172 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
173 ./#innodb_temp/temp_11.ibt 81920 INACTIVE NONE
174 ./#innodb_temp/temp_12.ibt 81920 INACTIVE NONE
175 ./#innodb_temp/temp_13.ibt 81920 INACTIVE NONE
176 ./#innodb_temp/temp_14.ibt 81920 INACTIVE NONE
177 ./#innodb_temp/temp_15.ibt 81920 INACTIVE NONE
178 ./#innodb_temp/temp_16.ibt 81920 INACTIVE NONE
179 ./#innodb_temp/temp_17.ibt 81920 INACTIVE NONE
180 ./#innodb_temp/temp_18.ibt 81920 INACTIVE NONE
181 ./#innodb_temp/temp_19.ibt 98304 ACTIVE INTRINSIC
182 ./#innodb_temp/temp_20.ibt 98304 ACTIVE USER
183 # Space would now be reclaimed and state for all sessions would be inactive
184 # in INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES except for one
185 # intrinsic tablespace for default connection when select query from
186 # INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACE is executed from
187 # default connection
188 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
189 PATH SIZE STATE PURPOSE
190 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
191 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
192 ./#innodb_temp/temp_3.ibt 81920 INACTIVE NONE
193 ./#innodb_temp/temp_4.ibt 81920 INACTIVE NONE
194 ./#innodb_temp/temp_5.ibt 81920 INACTIVE NONE
195 ./#innodb_temp/temp_6.ibt 81920 INACTIVE NONE
196 ./#innodb_temp/temp_7.ibt 81920 INACTIVE NONE
197 ./#innodb_temp/temp_8.ibt 81920 INACTIVE NONE
198 ./#innodb_temp/temp_9.ibt 81920 INACTIVE NONE
199 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
200 ./#innodb_temp/temp_11.ibt 81920 INACTIVE NONE
201 ./#innodb_temp/temp_12.ibt 81920 INACTIVE NONE
202 ./#innodb_temp/temp_13.ibt 81920 INACTIVE NONE
203 ./#innodb_temp/temp_14.ibt 81920 INACTIVE NONE
204 ./#innodb_temp/temp_15.ibt 81920 INACTIVE NONE
205 ./#innodb_temp/temp_16.ibt 81920 INACTIVE NONE
206 ./#innodb_temp/temp_17.ibt 81920 INACTIVE NONE
207 ./#innodb_temp/temp_18.ibt 81920 INACTIVE NONE
208 ./#innodb_temp/temp_19.ibt 81920 INACTIVE NONE
209 ./#innodb_temp/temp_20.ibt 81920 INACTIVE NONE
210 "List files in #innodb_temp. It should be empty"
211 "Check if ibtmp1 exists in datadir. There should be no ibtmp1"
212 "Test with invalid values of innodb_temp_tablespaces_dir"
213 "invalid abc dir"
214 "invalid ./sys/ dir"
215 "invalid /wl11613-do-not-exists/ dir"
216 "invalid empty "" "
217 "invalid #innodb_temp "
218 # restart
219 "Check if ibtmp1 exists in datadir. It should be recreated on restart"
220 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
221 PATH SIZE STATE PURPOSE
222 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
223 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
224 ./#innodb_temp/temp_3.ibt 81920 INACTIVE NONE
225 ./#innodb_temp/temp_4.ibt 81920 INACTIVE NONE
226 ./#innodb_temp/temp_5.ibt 81920 INACTIVE NONE
227 ./#innodb_temp/temp_6.ibt 81920 INACTIVE NONE
228 ./#innodb_temp/temp_7.ibt 81920 INACTIVE NONE
229 ./#innodb_temp/temp_8.ibt 81920 INACTIVE NONE
230 ./#innodb_temp/temp_9.ibt 81920 INACTIVE NONE
231 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
232 # Simulating disk space full scenario
233 SET GLOBAL big_tables=ON;
234 SELECT @@innodb_temp_tablespaces_dir;
235 @@innodb_temp_tablespaces_dir
236 #innodb_temp
237 # Create a new connection.
238 SET SESSION DEBUG='+d,ibt_pool_exhausted';
239 CREATE TEMPORARY TABLE t1 (a INT);
240 ERROR HY000: Storage engine could not allocate temporary tablespace for this session.
241 SET SESSION DEBUG='-d,ibt_pool_exhausted';
242 CREATE TABLE t3(a INT, b BLOB);
243 INSERT INTO t3 VALUES (1, 'hi'), (2, 'hello');
244 SET SESSION DEBUG='+d,ibt_pool_exhausted';
245 INSERT INTO t3 SELECT * FROM t3;
246 ERROR HY000: Storage engine could not allocate temporary tablespace for this session.
247 SET SESSION DEBUG='-d,ibt_pool_exhausted';
248 # Create Workload and execute queries involving intrinsic tables.
249 explain format = json select a from t1 group by b order by sum(a);
250 select a from t1 group by b order by sum(a);
251 explain format = json select a from t7 group by b order by sum(a);
252 select a from t7 group by b order by sum(a);
253 explain format = json select sql_big_result distinct * from t1, t2;
254 select sql_big_result distinct * from t1, t2;
255 explain format = json select sql_big_result t1.b from t1, t2 group by t1.b;
256 select sql_big_result t1.b from t1, t2 group by t1.b;
257 explain format = json select sql_big_result distinct t1.a from t1, t2 group by t1.a, t1.b;
258 select sql_big_result distinct t1.a from t1, t2 group by t1.a, t1.b;
259 explain format = json select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
260 select t1.* from t1 inner join t2 on t1.a = t2.a order by t2.a;
261 explain format = json select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
262 select t1.* from t1, t2 where t1.a = t2.a order by t1.a;
263 explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
264 select t1.* from t1 inner join t2 where t1.a = t2.a group by t1.a;
265 explain format = json select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
266 select t1.* from t1 inner join t2 where t1.a = t2.a group by t2.a;
267 explain format = json select a from t1 group by a order by a;
268 select a from t1 group by a order by a;
269 explain format = json select b from t1 group by b order by a;
270 select b from t1 group by b order by a;
271 explain format = json select distinct t1.a from t1, t2 where t1.b = t2.b;
272 select distinct t1.a from t1, t2 where t1.b = t2.b;
273 explain format = json select distinct a from t1 group by b,a;
274 select distinct a from t1 group by b,a;
275 set @old_sort_buffer_size = @@sort_buffer_size;
276 set @@sort_buffer_size = 32804;
277 explain format = json select distinct b from t3 group by a having count(*) > 1;
278 select distinct b from t3 group by a having count(*) > 1;
279 select distinct b from t3 group by a;
280 set @@sort_buffer_size = @old_sort_buffer_size;
281 explain format = json select distinct t4.a,e from t4, t1 order by t4.b;
282 select distinct t4.a,e from t4, t1 order by t4.b;
283 explain format = json select * from t1 union all select * from t2;
284 select * from t1 union all select * from t2;
285 explain format = json select * from t1 union select * from t2 order by b;
286 select * from t1 union select * from t2 order by b;
287 explain format = json select tt.* from (select * from t1) as tt;
288 select tt.* from (select * from t1) as tt;
289 select * from t5;
290 explain format = json select b from (select b from t5 where b > 5) tt;
291 select b from (select b from t5 where b > 5) tt;
292 explain format = json select * from (select b from t1) as t1, (select b from t2) as t2;
293 select * from (select b from t1) as t1, (select b from t2) as t2;
294 explain format = json select * from t1, (select * from t6) as t where t.a =5;
295 select * from t1, (select * from t6) as t where t.a =5;
296 explain format = json select * from t1 where t1.a in (select a from t2) ;
297 select * from t1 where t1.a in (select a from t2) ;
298 set optimizer_switch='materialization=off';
299 set optimizer_switch='firstmatch=off';
300 set optimizer_switch='loosescan=off';
301 explain format = json select * from t1 where t1.a in (select a from t2) ;
302 select * from t1 where t1.a in (select a from t2) ;
303 set optimizer_switch='default,derived_merge=off';
304 explain format = json update t1, t2 set t1.a = 9, t2.a = 10 where t1.b = 'a';
305 update t1, t2 set t1.a = 9, t2.a = 10 where t1.b = 'a';
306 explain format = json select count(distinct a) from t1;
307 select count(distinct a) from t1;
308 explain format = json select b, group_concat(a) from t1;
309 select b, group_concat(a) from t1;
310 select * from t1;
311 explain format = json select b, count(a), sum(a), avg(a), std(a),
312 min(a), max(a), bit_and(a), bit_or(a) from t1 group by b;
313 select b, count(a), sum(a), avg(a), std(a), min(a),
314 max(a), bit_and(a), bit_or(a) from t1 group by b;
315 select sql_big_result * from performance_schema.global_variables where variable_name like 'internal_tmp_mem_storage_engine';
316 select 1 union distinct select 1 union all select 1;
317 select alias1.`col_int_key` AS field1 from (t8 as alias1) where (5) in
318 (select 7 union select 1) order by alias1.`col_varchar_key` asc;
319 select count(table2.col_date_key) as field1 from
320 (t10 as table1 inner join
321 ((t10 as table2 right join
322 t9 as table3 on
323 ((table3.pk = table2.pk) or
324 (table3.pk = table2.col_int_key))))
325 on ((table3.pk < table2.col_int_nokey)))
326 order by field1 ASC;
327 select distinct table1.col_varchar_1024_utf8_key as field1
328 from t11 as table1 left join t11 as table2 on
329 table1.col_varchar_1024_utf8 = table2.col_varchar_1024_latin1_key
330 order by field1;
331 select t from t12 group by c;
332 select count(outr.col_varchar_nokey) AS x
333 from t13 as outr2 left join
334 (select * from t13) as outr
335 on (outr2.col_time_key > outr.col_date_key)
336 where outr.pk in
337 (select innr.col_int_nokey AS y from
338 (select * from t14) as innr2 left join
339 (select * from t14) AS innr
340 ON (innr2.col_int_nokey = innr.col_int_key)
341 where innr.col_varchar_nokey > innr.col_varchar_key
342 or not outr.pk > 9)
343 or outr.col_varchar_key is null;
344 SET @@optimizer_switch=@optimizer_switch_saved;
345 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
346 PATH SIZE STATE PURPOSE
347 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
348 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
349 ./#innodb_temp/temp_3.ibt 81920 INACTIVE NONE
350 ./#innodb_temp/temp_4.ibt 81920 INACTIVE NONE
351 ./#innodb_temp/temp_5.ibt 81920 INACTIVE NONE
352 ./#innodb_temp/temp_6.ibt 81920 INACTIVE NONE
353 ./#innodb_temp/temp_7.ibt 81920 INACTIVE NONE
354 ./#innodb_temp/temp_8.ibt 81920 INACTIVE NONE
355 ./#innodb_temp/temp_9.ibt 163840 ACTIVE INTRINSIC
356 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
357 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
358 PATH SIZE STATE PURPOSE
359 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
360 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
361 ./#innodb_temp/temp_3.ibt 81920 INACTIVE NONE
362 ./#innodb_temp/temp_4.ibt 81920 INACTIVE NONE
363 ./#innodb_temp/temp_5.ibt 81920 INACTIVE NONE
364 ./#innodb_temp/temp_6.ibt 81920 INACTIVE NONE
365 ./#innodb_temp/temp_7.ibt 81920 INACTIVE NONE
366 ./#innodb_temp/temp_8.ibt 81920 INACTIVE NONE
367 ./#innodb_temp/temp_9.ibt 81920 INACTIVE NONE
368 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
369 drop function func1;
370 drop table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14;
371 SET GLOBAL big_tables=default;
372 set @@innodb_strict_mode=0;
373 CREATE TEMPORARY table t1(a INT) TABLESPACE=innodb_temporary;
374 Warnings:
375 Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release.
376 set @@innodb_strict_mode=1;
377 CREATE TEMPORARY table t2(a INT) TABLESPACE=innodb_temporary;
378 Warnings:
379 Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release.
380 SHOW WARNINGS;
381 Level Code Message
382 Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release.
383 CREATE TEMPORARY TABLE t3 (a INT);
384 ALTER TABLE t3 TABLESPACE innodb_temporary;
385 Warnings:
386 Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release.
387 SHOW WARNINGS;
388 Level Code Message
389 Warning 1478 InnoDB: TABLESPACE=innodb_temporary option is ignored. All temporary tables are created in a session temporary tablespace. This option is deprecated and will be removed in a future release.
390 CREATE TEMPORARY TABLE t4 (a INT) TABLESPACE=innodb_system;
391 ERROR HY000: InnoDB: Tablespace `innodb_system` cannot contain TEMPORARY tables.
392 CREATE TEMPORARY TABLE t4 (a INT) TABLESPACE=mysql;
393 ERROR HY000: The table 't4' may not be created in the reserved tablespace 'mysql'.
394 CREATE TABLESPACE tbs ADD DATAFILE 'tbs.ibd' engine=INNODB;
395 CREATE TEMPORARY TABLE t4 (a INT) TABLESPACE=tbs;
396 ERROR HY000: InnoDB: Tablespace `tbs` cannot contain TEMPORARY tables.
397 SELECT PATH, SIZE, STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES ORDER BY SPACE;
398 PATH SIZE STATE PURPOSE
399 ./#innodb_temp/temp_1.ibt 81920 INACTIVE NONE
400 ./#innodb_temp/temp_2.ibt 81920 INACTIVE NONE
401 ./#innodb_temp/temp_3.ibt 81920 INACTIVE NONE
402 ./#innodb_temp/temp_4.ibt 81920 INACTIVE NONE
403 ./#innodb_temp/temp_5.ibt 81920 INACTIVE NONE
404 ./#innodb_temp/temp_6.ibt 81920 INACTIVE NONE
405 ./#innodb_temp/temp_7.ibt 81920 INACTIVE NONE
406 ./#innodb_temp/temp_8.ibt 81920 INACTIVE NONE
407 ./#innodb_temp/temp_9.ibt 147456 ACTIVE USER
408 ./#innodb_temp/temp_10.ibt 98304 ACTIVE INTRINSIC
409 #########################################################################
410 # Cleanup
411 #########################################################################
412 DROP TABLESPACE tbs;