Debian Codegraph @

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

raw
1 #
2 # InnoDB temporary table test case, including
3 # WL#6560: InnoDB: separate tablespace for innodb-temp-tables.
4 # WL#7899: InnoDB: Map compressed temporary tables to uncompressed
5 #
6
7 --source include/no_valgrind_without_big.inc
8
9 --disable_query_log
10 call mtr.add_suppression("Tablespace innodb_temporary ran out of space. Please add another file or use 'autoextend' for the last file in setting innodb_temp_data_file_path.");
11 call mtr.add_suppression("The table 't1' is full");
12 --enable_query_log
13
14 ################################################################################
15 #
16 # Will test following scenarios:
17 # 1. creation of shared temp-tablespace.
18 # 2. ddl + dml operation involving temp-tablespace.
19 # insert/delete/update/select
20 # create/drop/alter/truncate/import-discard (though blocked).
21 # 3. ddl + dml operation on row_format=dynamic table
22 # (table would still reside in shared temp-tablespace).
23 # 4. Test bulk-loading that result in auto-extension of temp-tablespace.
24 # 5. re-creation of temp-tablespace on re-start.
25 # also to ensure non-existence of existing temp-table.
26 # 6. restart server in innodb-read-only mode. this will also
27 # block creation of temp-tables.
28 # 7. try starting server with shared and temp-tablespace filename same.
29 # 8. try re-starting server with param so that temp-tablespace can't be
30 # expanded and insert enough data to make it full.
31 # 9. tests for different row format types and key block sizes for
32 # compressed tables, which would be ignored when innodb_strict_mode is OFF,
33 # but regarded as an error when innodb_strict_mode is ON.
34 # 10. try restarting server with raw device specified for temp-tablespace.
35 # 11. try restarting server with temp-tablespace less than min. threshold
36 # 12. no file specified for temp-tablespace.
37 # 13. Test the structure and info of I_S.innodb_temp_table_info
38 ################################################################################
39
40 #-----------------------------------------------------------------------------
41 #
42 # create test-bed
43 #
44 let $MYSQL_TMP_DIR = `select @@tmpdir`;
45 let $MYSQL_DATA_DIR = `select @@datadir`;
46 let SEARCH_FILE = $MYSQLTEST_VARDIR/log/my_restart.err;
47 let $args = --loose-console --core-file > $SEARCH_FILE 2>&1;
48 let nameconflicts = $args --innodb_data_file_path="ibdata1:12M:autoextend:max:134217728" --innodb_temp_data_file_path="ibdata1:12M:autoextend";
49 let rawdevice1 = $args --innodb_temp_data_file_path="/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw";
50 let rawdevice2 = $args --innodb_temp_data_file_path="/dev/hdd1:3Graw;/dev/hdd2:2Graw";
51 let sizeoftempfile1 = $args --innodb_temp_data_file_path="ibtmp1:2M:autoextend";
52 let sizeoftempfile2 = $args --innodb_data_file_path="ibdata1:2M:autoextend";
53 let notemptablespacefile = $args --innodb_temp_data_file_path="";
54
55 #-----------------------------------------------------------------------------
56 #
57 # 1. creation of shared temp-tablespace.
58 #
59 --echo # files in MYSQL_DATA_DIR
60 --list_files $MYSQL_DATA_DIR/ ibtmp*
61
62
63 #-----------------------------------------------------------------------------
64 #
65 # 2. ddl + dml operation involving temp-tablespace.
66 # insert/delete/update/select
67 # create/drop/alter/truncate/import-discard (though blocked).
68 #
69 create temporary table t1 (i int, f float, c char(100)) engine=innodb;
70 #
71 --source suite/innodb_zip/include/innodb_temp_table_dml.inc
72 #
73 # alter table
74 --error ER_CANNOT_DISCARD_TEMPORARY_TABLE
75 alter table t1 discard tablespace;
76 --error ER_CANNOT_DISCARD_TEMPORARY_TABLE
77 alter table t1 import tablespace;
78 #
79 # drop table
80 drop table t1;
81
82 #-----------------------------------------------------------------------------
83 #
84 # 3. ddl + dml operation on row_format=dynamic table
85 # (table would still reside in shared temp-tablespace).
86 #
87 create temporary table t1
88 (i int, f float, c char(100)) engine = innodb row_format=dynamic;
89 show create table t1;
90 #
91 --source suite/innodb_zip/include/innodb_temp_table_dml.inc
92 #
93 # alter table
94 --error ER_CANNOT_DISCARD_TEMPORARY_TABLE
95 alter table t1 discard tablespace;
96 #
97 # drop table
98 drop table t1;
99
100 #-----------------------------------------------------------------------------
101 #
102 # 4. Test bulk-loading that result in auto-extension of temp-tablespace.
103 #
104 create temporary table t1
105 (keyc int, c1 char(100), c2 char(100),
106 primary key(keyc)) engine = innodb;
107 delimiter |;
108 CREATE PROCEDURE populate_t1()
109 BEGIN
110 DECLARE i INT DEFAULT 1;
111 while (i <= 20000) DO
112 insert into t1 values (i, 'a', 'b');
113 SET i = i + 1;
114 END WHILE;
115 END|
116 delimiter ;|
117 set autocommit=0;
118 select count(*) from t1;
119 call populate_t1();
120 select count(*) from t1;
121 select * from t1 limit 10;
122 set autocommit=1;
123 truncate table t1;
124 select count(*) from t1;
125 #
126 drop procedure populate_t1;
127 drop table t1;
128
129 #-----------------------------------------------------------------------------
130 #
131 # 5. re-creation of temp-tablespace on re-start.
132 # also to ensure non-existence of existing temp-table.
133 #
134 create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;
135 insert into t1 values (1, 'c', 'b');
136 select * from t1;
137 #
138 --source include/restart_mysqld.inc
139 #
140 --echo # files in MYSQL_DATA_DIR
141 --list_files $MYSQL_DATA_DIR/ ibtmp*
142 use test;
143 --error ER_NO_SUCH_TABLE
144 select * from t1;
145
146 #-----------------------------------------------------------------------------
147 #
148 # 6. restart server in innodb-read-only mode. this will also
149 # block creation of temp-tables.
150 #
151 #
152 --echo "testing temp-table creation in --innodb_read_only mode"
153 --source include/restart_innodb_read_only.inc
154 #
155 use test;
156 show tables;
157 --error ER_INNODB_READ_ONLY
158 create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;
159
160 #-----------------------------------------------------------------------------
161 #
162 # 7. try starting server with shared and temp-tablespace filename same.
163 #
164 --source include/shutdown_mysqld.inc
165 --echo "testing system and temp tablespace name conflict"
166 # InnoDB aborts, so ASAN may report leaks
167 --error 1,42
168 --exec $MYSQLD_CMD $nameconflicts
169 let SEARCH_PATTERN = innodb_temporary and innodb_system file names seem to be the same;
170 --source ./include/search_pattern.inc
171 --remove_file $SEARCH_FILE
172 --echo "restarting server in normal mode"
173 --enable_reconnect
174 let $restart_parameters = restart;
175 --source include/start_mysqld.inc
176 #
177 show tables;
178 create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;
179 drop table t1;
180
181 #-----------------------------------------------------------------------------
182 #
183 # 8. try re-starting server with param so that temp-tablespace can't be expanded
184 # and insert enough data to make it full.
185 # Note that each rollback segment will take one full page
186 --echo # test condition of full-temp-tablespace
187 let $restart_parameters = restart: --innodb-rollback-segments=32 --innodb_temp_data_file_path=ibtmp1:64M;
188 --source include/restart_mysqld.inc
189 #
190 create temporary table t1
191 (keyc int, c1 char(250), c2 char(250), c3 char(250),
192 primary key(keyc, c1, c2, c3)) engine = innodb charset latin1;
193 delimiter |;
194 CREATE PROCEDURE populate_t1()
195 BEGIN
196 DECLARE i INT DEFAULT 1;
197 while (i <= 20000) DO
198 insert into t1 values (i, repeat('a', 250), repeat('b', 250), repeat('c', 250));
199 SET i = i + 1;
200 END WHILE;
201 END|
202 delimiter ;|
203 set autocommit=0;
204 select count(*) from t1;
205 # we now use session temporary tablespaces and they can
206 # expanded.
207 call populate_t1();
208 #
209 drop procedure populate_t1;
210 drop table t1;
211
212 #-----------------------------------------------------------------------------
213 #
214 # 9. tests for different row format types and key block sizes for
215 # compressed tables, which would be ignored when innodb_strict_mode is OFF,
216 # but regarded as an error when innodb_strict_mode is ON.
217 #
218 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
219 create temporary table t (
220 i int)
221 engine = innodb row_format = compressed;
222 #
223 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
224 create temporary table t (
225 i int)
226 engine = innodb row_format = compressed key_block_size = 8;
227 #
228 create temporary table t (
229 i int)
230 engine = innodb row_format = dynamic;
231 --replace_regex /[0-9]+/NUMBER/
232 show warnings;
233 drop table t;
234 #
235 set innodb_strict_mode = on;
236 create temporary table t (
237 i int)
238 engine = innodb row_format = dynamic;
239 --replace_regex /[0-9]+/NUMBER/
240 show warnings;
241 drop table t;
242 set innodb_strict_mode = off;
243 #
244 create temporary table t (
245 i int)
246 engine = innodb row_format = compressed key_block_size = 8;
247 show warnings;
248 set innodb_strict_mode = default;
249 #
250 drop table t;
251 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
252 create temporary table t (
253 i int)
254 engine = innodb row_format = compressed;
255 --echo #files in MYSQL_TMP_DIR, expecting only default temporary tablespace file
256 --list_files $MYSQL_TMP_DIR/ *.ibd
257 --list_files $MYSQL_DATA_DIR/ ibtmp*
258 #
259 create temporary table t (
260 i int)
261 engine = innodb row_format = dynamic;
262 --replace_regex /[0-9]+/NUMBER/
263 show warnings;
264 drop table t;
265 #
266 set innodb_strict_mode = on;
267 create temporary table t (
268 i int)
269 engine = innodb row_format = dynamic;
270 --replace_regex /[0-9]+/NUMBER/
271 show warnings;
272 drop table t;
273 set innodb_strict_mode = off;
274 #
275 create temporary table t (
276 i int)
277 engine = innodb row_format = dynamic key_block_size = 4;
278 --replace_regex /[0-9]+/NUMBER/
279 show warnings;
280 drop table t;
281 #
282 create temporary table t (
283 i int)
284 engine = innodb row_format = compact;
285 --replace_regex /[0-9]+/NUMBER/
286 show warnings;
287 drop table t;
288 #
289 create temporary table t (
290 i int)
291 engine = innodb key_block_size = 4;
292 --replace_regex /[0-9]+/NUMBER/
293 show warnings;
294 drop table t;
295 #
296
297 #-----------------------------------------------------------------------------
298 #
299 # Before doing following test, create a temp table,
300 # get its space no
301 #
302 CREATE TEMPORARY TABLE t1(
303 i INT
304 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
305
306 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
307
308 CREATE TABLE space_id(
309 id INT UNSIGNED
310 ) ENGINE = InnoDB;
311 INSERT INTO space_id SELECT space FROM information_schema.innodb_temp_table_info;
312 SELECT count(*) AS `Expect 1` FROM space_id;
313
314 DROP TABLE t1;
315
316 CREATE TEMPORARY TABLE t1 (
317 i INT NOT NULL PRIMARY KEY,
318 c CHAR(10) NOT NULL
319 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
320 --replace_regex /[0-9]+/NUMBER/
321 SHOW WARNINGS;
322 SHOW CREATE TABLE t1;
323 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
324 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
325 DROP TABLE t1;
326 #
327 CREATE TEMPORARY TABLE t1 (
328 i INT NOT NULL PRIMARY KEY,
329 c CHAR(10) NOT NULL
330 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
331 --replace_regex /[0-9]+/NUMBER/
332 SHOW WARNINGS;
333 SHOW CREATE TABLE t1;
334 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
335 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
336 DROP TABLE t1;
337 #
338 CREATE TEMPORARY TABLE t1 (
339 i INT NOT NULL PRIMARY KEY,
340 c CHAR(10) NOT NULL
341 ) ENGINE = InnoDB ROW_FORMAT = COMPACT;
342 --replace_regex /[0-9]+/NUMBER/
343 SHOW WARNINGS;
344 SHOW CREATE TABLE t1;
345 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
346 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
347 DROP TABLE t1;
348 #
349
350 CREATE TEMPORARY TABLE t1 (
351 i INT NOT NULL PRIMARY KEY,
352 c CHAR(10) NOT NULL
353 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
354 --replace_regex /[0-9]+/NUMBER/
355 SHOW WARNINGS;
356 SHOW CREATE TABLE t1;
357 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
358 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
359 DROP TABLE t1;
360 #
361 CREATE TEMPORARY TABLE t1 (
362 i INT NOT NULL PRIMARY KEY,
363 c CHAR(10) NOT NULL
364 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
365 --replace_regex /[0-9]+/NUMBER/
366 SHOW WARNINGS;
367 SHOW CREATE TABLE t1;
368 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
369 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
370 DROP TABLE t1;
371 #
372 CREATE TEMPORARY TABLE t1 (
373 i INT NOT NULL PRIMARY KEY,
374 c CHAR(10) NOT NULL
375 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;
376 --replace_regex /[0-9]+/NUMBER/
377 SHOW WARNINGS;
378 SHOW CREATE TABLE t1;
379 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
380 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
381 DROP TABLE t1;
382 #
383 CREATE TEMPORARY TABLE t1 (
384 i INT NOT NULL PRIMARY KEY,
385 c CHAR(10) NOT NULL
386 ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 8;
387 --replace_regex /[0-9]+/NUMBER/
388 SHOW WARNINGS;
389 SHOW CREATE TABLE t1;
390 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
391 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
392 DROP TABLE t1;
393 #
394 CREATE TEMPORARY TABLE t1 (
395 i INT NOT NULL PRIMARY KEY,
396 c CHAR(10) NOT NULL
397 ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
398 --replace_regex /[0-9]+/NUMBER/
399 SHOW WARNINGS;
400 SHOW CREATE TABLE t1;
401 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
402 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
403 DROP TABLE t1;
404 #
405
406 # Test alter table for temporary tables with row format = compressed or
407 # key_block_size
408 CREATE TEMPORARY TABLE t1 (
409 i INT NOT NULL PRIMARY KEY
410 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
411 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
412 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
413
414 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
415 --replace_regex /[0-9]+/NUMBER/
416 SHOW WARNINGS;
417 SHOW CREATE TABLE t1;
418 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
419 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
420 ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
421 --replace_regex /[0-9]+/NUMBER/
422 SHOW WARNINGS;
423 SHOW CREATE TABLE t1;
424 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
425 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
426 ALTER TABLE t1 KEY_BLOCK_SIZE = 4 ROW_FORMAT = COMPRESSED;
427 --replace_regex /[0-9]+/NUMBER/
428 SHOW WARNINGS;
429 SHOW CREATE TABLE t1;
430 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
431 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
432
433 ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
434 --replace_regex /[0-9]+/NUMBER/
435 SHOW WARNINGS;
436 SHOW CREATE TABLE t1;
437 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
438 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
439 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
440 --replace_regex /[0-9]+/NUMBER/
441 SHOW WARNINGS;
442 SHOW CREATE TABLE t1;
443 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
444 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
445
446 DROP TABLE t1;
447
448 # Set innodb_strict_mode=ON and test compressed temporary tables again.
449 set innodb_strict_mode = ON;
450
451 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
452 CREATE TEMPORARY TABLE t1 (
453 i INT NOT NULL PRIMARY KEY,
454 c CHAR(10) NOT NULL
455 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
456 --replace_regex /[0-9][0-9]+/NUMBER/
457 SHOW WARNINGS;
458 #
459
460 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
461 CREATE TEMPORARY TABLE t1 (
462 i INT NOT NULL PRIMARY KEY,
463 c CHAR(10) NOT NULL
464 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4, ROW_FORMAT = COMPACT;
465 --replace_regex /[0-9][0-9]+/NUMBER/
466 SHOW WARNINGS;
467 #
468
469 CREATE TEMPORARY TABLE t1 (
470 i INT NOT NULL PRIMARY KEY,
471 c CHAR(10) NOT NULL
472 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
473 --replace_regex /[0-9]+/NUMBER/
474 SHOW WARNINGS;
475 SHOW CREATE TABLE t1;
476 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
477 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
478 DROP TABLE t1;
479 #
480
481 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
482 CREATE TEMPORARY TABLE t1 (
483 i INT NOT NULL PRIMARY KEY,
484 c CHAR(10) NOT NULL
485 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
486 --replace_regex /[0-9][0-9]+/NUMBER/
487 SHOW WARNINGS;
488 #
489
490 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
491 CREATE TEMPORARY TABLE t1 (
492 i INT NOT NULL PRIMARY KEY,
493 c CHAR(10) NOT NULL
494 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
495 --replace_regex /[0-9][0-9]+/NUMBER/
496 SHOW WARNINGS;
497 #
498
499 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
500 CREATE TEMPORARY TABLE t1 (
501 i INT NOT NULL PRIMARY KEY,
502 c CHAR(10) NOT NULL
503 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;
504 --replace_regex /[0-9][0-9]+/NUMBER/
505 SHOW WARNINGS;
506 #
507
508 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
509 CREATE TEMPORARY TABLE t1 (
510 i INT NOT NULL PRIMARY KEY,
511 c CHAR(10) NOT NULL
512 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 7;
513 --replace_regex /[0-9][0-9]+/NUMBER/
514 SHOW WARNINGS;
515 #
516
517 CREATE TEMPORARY TABLE t1 (
518 i INT NOT NULL PRIMARY KEY,
519 c CHAR(10) NOT NULL
520 ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
521 --replace_regex /[0-9]+/NUMBER/
522 SHOW WARNINGS;
523 SHOW CREATE TABLE t1;
524 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
525 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
526 DROP TABLE t1;
527 #
528
529 CREATE TEMPORARY TABLE t1 (
530 i INT NOT NULL PRIMARY KEY
531 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
532 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_temp_table_info;
533 SELECT count(*) AS `Expect 1` FROM space_id, information_schema.innodb_temp_table_info WHERE space_id.id = information_schema.innodb_temp_table_info.space;
534
535 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
536 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
537 --replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
538 SHOW WARNINGS;
539
540 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
541 ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
542 --replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
543 SHOW WARNINGS;
544
545 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
546 ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
547 --replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
548 SHOW WARNINGS;
549
550 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
551
552 #
553 # Some checking for turning innodb_strict_mode ON and OFF.
554 set innodb_strict_mode = OFF;
555
556 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
557 --replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
558 SHOW WARNINGS;
559
560 set innodb_strict_mode = ON;
561
562 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
563 SHOW CREATE TABLE t1;
564
565 set innodb_strict_mode = OFF;
566
567 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
568 --replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
569 SHOW WARNINGS;
570
571 ALTER TABLE t1 KEY_BLOCK_SIZE = 8;
572 --replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
573 SHOW WARNINGS;
574
575 set innodb_strict_mode = ON;
576
577 --error ER_UNSUPPORT_COMPRESSED_TEMPORARY_TABLE
578 ALTER TABLE t1 ADD COLUMN j INT;
579 --replace_regex /#sql-[0-9a-f_0-9a-f]*/#sql<temporary>/ /[0-9]+/NUMBER/
580 SHOW WARNINGS;
581 SHOW CREATE TABLE t1;
582
583 set innodb_strict_mode = OFF;
584
585 ALTER TABLE t1 KEY_BLOCK_SIZE = 0;
586 SHOW CREATE TABLE t1;
587
588 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
589
590 set innodb_strict_mode = ON;
591 ALTER TABLE t1 ADD COLUMN j INT;
592 SHOW CREATE TABLE t1;
593
594 DROP TABLE t1, space_id;
595
596 set innodb_strict_mode = OFF;
597
598 #-----------------------------------------------------------------------------
599 #
600 # 10. try restarting server with raw device specified for temp-tablespace.
601 #
602 --source include/shutdown_mysqld.inc
603 --echo "testing temp tablespace non-support for raw device"
604 # InnoDB aborts, so ASAN may report leaks
605 --error 1,42
606 --exec $MYSQLD_CMD $rawdevice1
607 let SEARCH_PATTERN = support raw device;
608 --source include/search_pattern.inc
609 --remove_file $SEARCH_FILE
610 --echo "testing temp tablespace non-support for raw device"
611 # InnoDB aborts, so ASAN may report leaks
612 --error 1,42
613 --exec $MYSQLD_CMD $rawdevice2
614 let SEARCH_PATTERN = support raw device;
615 --source include/search_pattern.inc
616 --remove_file $SEARCH_FILE
617
618 let $restart_parameters = restart;
619 --source include/start_mysqld.inc
620
621 show tables;
622 create temporary table t1 (
623 keyc int, c1 char(100), c2 char(100)
624 ) engine = innodb;
625 drop table t1;
626
627 #-----------------------------------------------------------------------------
628 #
629 # 11. try restarting server with temp-tablespace less than min. threshold
630 #
631 --source include/shutdown_mysqld.inc
632 --echo "try starting server with temp-tablespace size < min. threshold"
633 --error 1,42
634 --exec $MYSQLD_CMD $sizeoftempfile1
635 let SEARCH_PATTERN = Tablespace size must be at least;
636 --source ./include/search_pattern.inc
637 --remove_file $SEARCH_FILE
638 --echo "try starting server with sys-tablespace size < min. threshold"
639 --error 1
640 --exec $MYSQLD_CMD $sizeoftempfile2
641 let SEARCH_PATTERN = Tablespace size must be at least;
642 --source ./include/search_pattern.inc
643 --remove_file $SEARCH_FILE
644
645 --source include/start_mysqld.inc
646
647 show tables;
648 create temporary table t1 (
649 keyc int, c1 char(100), c2 char(100)
650 ) engine = innodb;
651 drop table t1;
652
653 #-----------------------------------------------------------------------------
654 #
655 # 12. no file specified for temp-tablespace.
656 #
657 --source include/shutdown_mysqld.inc
658
659 --echo "try starting server with no file specified for temp-tablespace"
660 # InnoDB aborts, so ASAN may report leaks
661 --error 1,42
662 --exec $MYSQLD_CMD $notemptablespacefile
663 let SEARCH_PATTERN = init function returned error;
664 --source ./include/search_pattern.inc
665 --remove_file $SEARCH_FILE
666
667 --source include/start_mysqld.inc
668
669 show tables;
670 create temporary table t1 (
671 keyc int, c1 char(100), c2 char(100)
672 ) engine = innodb;
673 drop table t1;
674
675 #-----------------------------------------------------------------------------
676 #
677 # 13. Test the structure and info of I_S.innodb_temp_table_info
678 #
679 SHOW CREATE TABLE information_schema.innodb_temp_table_info;
680 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_temp_table_info;
681
682 CREATE TEMPORARY TABLE t1 (
683 i int,
684 f float
685 ) ENGINE = InnoDB;
686
687 CREATE TEMPORARY TABLE t2 (
688 i int,
689 f float
690 ) ENGINE = InnoDB;
691
692 SELECT count(*) AS `Expect 2` FROM information_schema.innodb_temp_table_info;
693
694 DROP TABLE t1, t2;