Debian Codegraph @

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

raw
1 # files in MYSQL_DATA_DIR
2 ibtmp1
3 create temporary table t1 (i int, f float, c char(100)) engine=innodb;
4 insert into t1 values (100, 1.1, 'pune');
5 insert into t1 values (99, 1.2, 'mumbai');
6 insert into t1 values (98, 1.3, 'jaipur');
7 insert into t1 values (97, 1.4, 'delhi');
8 insert into t1 values (96, 1.5, 'ahmedabad');
9 select * from t1;
10 i f c
11 100 1.1 pune
12 99 1.2 mumbai
13 98 1.3 jaipur
14 97 1.4 delhi
15 96 1.5 ahmedabad
16 select * from t1 where i = 98;
17 i f c
18 98 1.3 jaipur
19 select * from t1 where i < 100;
20 i f c
21 99 1.2 mumbai
22 98 1.3 jaipur
23 97 1.4 delhi
24 96 1.5 ahmedabad
25 explain select * from t1 where f > 1.29999;
26 id select_type table type possible_keys key key_len ref rows Extra
27 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
28 alter table t1 add index sec_index(f);
29 explain select * from t1 where f > 1.29999;
30 id select_type table type possible_keys key key_len ref rows Extra
31 1 SIMPLE t1 range sec_index sec_index 5 NULL 3 Using index condition
32 select * from t1 where f > 1.29999;
33 i f c
34 98 1.3 jaipur
35 97 1.4 delhi
36 96 1.5 ahmedabad
37 explain select * from t1 where i = 100;
38 id select_type table type possible_keys key key_len ref rows Extra
39 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
40 alter table t1 add unique index pri_index(i);
41 explain select * from t1 where i = 100;
42 id select_type table type possible_keys key key_len ref rows Extra
43 1 SIMPLE t1 const pri_index pri_index 5 const 1
44 select * from t1 where i = 100;
45 i f c
46 100 1.1 pune
47 delete from t1 where i < 97;
48 select * from t1;
49 i f c
50 100 1.1 pune
51 99 1.2 mumbai
52 98 1.3 jaipur
53 97 1.4 delhi
54 insert into t1 values (96, 1.5, 'kolkata');
55 select * from t1;
56 i f c
57 100 1.1 pune
58 99 1.2 mumbai
59 98 1.3 jaipur
60 97 1.4 delhi
61 96 1.5 kolkata
62 update t1 set f = 1.44 where c = 'delhi';
63 select * from t1;
64 i f c
65 100 1.1 pune
66 99 1.2 mumbai
67 98 1.3 jaipur
68 97 1.44 delhi
69 96 1.5 kolkata
70 truncate table t1;
71 insert into t1 values (100, 1.1, 'pune');
72 insert into t1 values (99, 1.2, 'mumbai');
73 insert into t1 values (98, 1.3, 'jaipur');
74 insert into t1 values (97, 1.4, 'delhi');
75 insert into t1 values (96, 1.5, 'ahmedabad');
76 select * from t1;
77 i f c
78 100 1.1 pune
79 99 1.2 mumbai
80 98 1.3 jaipur
81 97 1.4 delhi
82 96 1.5 ahmedabad
83 alter table t1 discard tablespace;
84 ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
85 alter table t1 import tablespace;
86 ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
87 drop temporary table t1;
88 create temporary table t1
89 (keyc int, c1 char(100), c2 char(100),
90 primary key(keyc)) engine = innodb;
91 CREATE PROCEDURE populate_t1()
92 BEGIN
93 DECLARE i INT DEFAULT 1;
94 while (i <= 20000) DO
95 insert into t1 values (i, 'a', 'b');
96 SET i = i + 1;
97 END WHILE;
98 END|
99 set autocommit=0;
100 select count(*) from t1;
101 count(*)
102 0
103 call populate_t1();
104 select count(*) from t1;
105 count(*)
106 20000
107 select * from t1 limit 10;
108 keyc c1 c2
109 1 a b
110 2 a b
111 3 a b
112 4 a b
113 5 a b
114 6 a b
115 7 a b
116 8 a b
117 9 a b
118 10 a b
119 set autocommit=1;
120 truncate table t1;
121 select * from t1;
122 keyc c1 c2
123 # test condition of full-temp-tablespace
124 # restart: --innodb_temp_data_file_path=ibtmp1:12M
125 create temporary table t1
126 (keyc int, c1 char(100), c2 char(100),
127 primary key(keyc)) engine = innodb;
128 begin;
129 call populate_t1();
130 ERROR HY000: The table 't1' is full
131 drop procedure populate_t1;
132 # test read-only mode
133 # restart: --innodb-read-only
134 # files in MYSQL_DATA_DIR
135 select * from t1;
136 ERROR 42S02: Table 'test.t1' doesn't exist
137 show tables;
138 Tables_in_test
139 create temporary table t1 (keyc int, c1 char(100), c2 char(100)) engine = innodb;
140 ERROR HY000: Can't create table `test`.`t1` (errno: 165 "Table is read only")
141 SET GLOBAL innodb_encrypt_tables=DEFAULT;
142 # test various bad start-up parameters
143 FOUND 2 /InnoDB: Unable to create temporary file/ in mysqld.1.err
144 # restart: --innodb_data_file_path=ibdata1:12M:autoextend --innodb_temp_data_file_path=ibdata1:12M:autoextend
145 FOUND 1 /innodb_temporary and innodb_system file names seem to be the same/ in mysqld.1.err
146 SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb'
147 AND support IN ('YES', 'DEFAULT', 'ENABLED');
148 ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
149 # restart: --innodb_temp_data_file_path=foobar:3Gnewraw
150 FOUND 1 /support raw device/ in mysqld.1.err
151 SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb'
152 AND support IN ('YES', 'DEFAULT', 'ENABLED');
153 ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
154 # restart: --innodb_temp_data_file_path=barbar:3Graw
155 FOUND 2 /support raw device/ in mysqld.1.err
156 SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb'
157 AND support IN ('YES', 'DEFAULT', 'ENABLED');
158 ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
159 # restart: --innodb_temp_data_file_path=
160 FOUND 1 /InnoDB: syntax error in file path/ in mysqld.1.err
161 SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb'
162 AND support IN ('YES', 'DEFAULT', 'ENABLED');
163 ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
164 # restart
165 create temporary table t (
166 i int)
167 engine = innodb row_format = compressed;
168 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
169 create temporary table t (
170 i int)
171 engine = innodb row_format = compressed key_block_size = 8;
172 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
173 create temporary table t (
174 i int)
175 engine = innodb row_format = dynamic;
176 show warnings;
177 Level Code Message
178 drop table t;
179 create temporary table t (
180 i int)
181 engine = innodb row_format = dynamic;
182 show warnings;
183 Level Code Message
184 drop table t;
185 set innodb_strict_mode = off;
186 create temporary table t (
187 i int)
188 engine = innodb row_format = compressed key_block_size = 8;
189 Warnings:
190 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
191 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
192 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
193 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
194 show warnings;
195 Level Code Message
196 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
197 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
198 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
199 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
200 set innodb_strict_mode = default;
201 drop table t;
202 create temporary table t (
203 i int)
204 engine = innodb row_format = compressed;
205 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
206 #files in MYSQL_TMP_DIR, expecting only default temporary tablespace file
207 ibtmp1
208 create temporary table t (
209 i int)
210 engine = innodb row_format = dynamic;
211 show warnings;
212 Level Code Message
213 drop table t;
214 create temporary table t (
215 i int)
216 engine = innodb row_format = dynamic;
217 show warnings;
218 Level Code Message
219 drop table t;
220 set innodb_strict_mode = off;
221 create temporary table t (
222 i int)
223 engine = innodb row_format = dynamic key_block_size = 4;
224 Warnings:
225 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
226 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
227 show warnings;
228 Level Code Message
229 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
230 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
231 drop table t;
232 create temporary table t (
233 i int)
234 engine = innodb row_format = compact;
235 show warnings;
236 Level Code Message
237 drop table t;
238 create temporary table t (
239 i int)
240 engine = innodb key_block_size = 4;
241 Warnings:
242 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
243 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
244 show warnings;
245 Level Code Message
246 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
247 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
248 drop table t;
249 CREATE TEMPORARY TABLE t1 (
250 i INT NOT NULL PRIMARY KEY,
251 c CHAR(10) NOT NULL
252 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
253 Warnings:
254 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
255 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
256 SHOW WARNINGS;
257 Level Code Message
258 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
259 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
260 SHOW CREATE TABLE t1;
261 Table Create Table
262 t1 CREATE TEMPORARY TABLE `t1` (
263 `i` int(11) NOT NULL,
264 `c` char(10) NOT NULL,
265 PRIMARY KEY (`i`)
266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci KEY_BLOCK_SIZE=4
267 DROP TABLE t1;
268 CREATE TEMPORARY TABLE t1 (
269 i INT NOT NULL PRIMARY KEY,
270 c CHAR(10) NOT NULL
271 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
272 SHOW WARNINGS;
273 Level Code Message
274 SHOW CREATE TABLE t1;
275 Table Create Table
276 t1 CREATE TEMPORARY TABLE `t1` (
277 `i` int(11) NOT NULL,
278 `c` char(10) NOT NULL,
279 PRIMARY KEY (`i`)
280 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=REDUNDANT
281 DROP TABLE t1;
282 CREATE TEMPORARY TABLE t1 (
283 i INT NOT NULL PRIMARY KEY,
284 c CHAR(10) NOT NULL
285 ) ENGINE = InnoDB ROW_FORMAT = COMPACT;
286 SHOW WARNINGS;
287 Level Code Message
288 SHOW CREATE TABLE t1;
289 Table Create Table
290 t1 CREATE TEMPORARY TABLE `t1` (
291 `i` int(11) NOT NULL,
292 `c` char(10) NOT NULL,
293 PRIMARY KEY (`i`)
294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPACT
295 DROP TABLE t1;
296 CREATE TEMPORARY TABLE t1 (
297 i INT NOT NULL PRIMARY KEY,
298 c CHAR(10) NOT NULL
299 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
300 Warnings:
301 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
302 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
303 SHOW WARNINGS;
304 Level Code Message
305 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
306 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
307 SHOW CREATE TABLE t1;
308 Table Create Table
309 t1 CREATE TEMPORARY TABLE `t1` (
310 `i` int(11) NOT NULL,
311 `c` char(10) NOT NULL,
312 PRIMARY KEY (`i`)
313 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci KEY_BLOCK_SIZE=4
314 DROP TABLE t1;
315 CREATE TEMPORARY TABLE t1 (
316 i INT NOT NULL PRIMARY KEY,
317 c CHAR(10) NOT NULL
318 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
319 Warnings:
320 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
321 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
322 SHOW WARNINGS;
323 Level Code Message
324 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
325 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
326 SHOW CREATE TABLE t1;
327 Table Create Table
328 t1 CREATE TEMPORARY TABLE `t1` (
329 `i` int(11) NOT NULL,
330 `c` char(10) NOT NULL,
331 PRIMARY KEY (`i`)
332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED
333 DROP TABLE t1;
334 CREATE TEMPORARY TABLE t1 (
335 i INT NOT NULL PRIMARY KEY,
336 c CHAR(10) NOT NULL
337 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;
338 Warnings:
339 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
340 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
341 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
342 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
343 SHOW WARNINGS;
344 Level Code Message
345 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
346 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
347 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
348 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
349 SHOW CREATE TABLE t1;
350 Table Create Table
351 t1 CREATE TEMPORARY TABLE `t1` (
352 `i` int(11) NOT NULL,
353 `c` char(10) NOT NULL,
354 PRIMARY KEY (`i`)
355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
356 DROP TABLE t1;
357 CREATE TEMPORARY TABLE t1 (
358 i INT NOT NULL PRIMARY KEY,
359 c CHAR(10) NOT NULL
360 ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 8;
361 Warnings:
362 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
363 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
364 SHOW WARNINGS;
365 Level Code Message
366 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
367 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
368 SHOW CREATE TABLE t1;
369 Table Create Table
370 t1 CREATE TEMPORARY TABLE `t1` (
371 `i` int(11) NOT NULL,
372 `c` char(10) NOT NULL,
373 PRIMARY KEY (`i`)
374 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8
375 DROP TABLE t1;
376 CREATE TEMPORARY TABLE t1 (
377 i INT NOT NULL PRIMARY KEY,
378 c CHAR(10) NOT NULL
379 ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
380 SHOW WARNINGS;
381 Level Code Message
382 SHOW CREATE TABLE t1;
383 Table Create Table
384 t1 CREATE TEMPORARY TABLE `t1` (
385 `i` int(11) NOT NULL,
386 `c` char(10) NOT NULL,
387 PRIMARY KEY (`i`)
388 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
389 DROP TABLE t1;
390 CREATE TEMPORARY TABLE t1 (
391 i INT NOT NULL PRIMARY KEY
392 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
393 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
394 Warnings:
395 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
396 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
397 SHOW WARNINGS;
398 Level Code Message
399 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
400 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
401 SHOW CREATE TABLE t1;
402 Table Create Table
403 t1 CREATE TEMPORARY TABLE `t1` (
404 `i` int(11) NOT NULL,
405 PRIMARY KEY (`i`)
406 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED
407 ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
408 Warnings:
409 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
410 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
411 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
412 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
413 SHOW WARNINGS;
414 Level Code Message
415 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
416 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
417 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
418 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
419 SHOW CREATE TABLE t1;
420 Table Create Table
421 t1 CREATE TEMPORARY TABLE `t1` (
422 `i` int(11) NOT NULL,
423 PRIMARY KEY (`i`)
424 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
425 ALTER TABLE t1 KEY_BLOCK_SIZE = 4 ROW_FORMAT = COMPRESSED;
426 Warnings:
427 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
428 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
429 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
430 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
431 SHOW WARNINGS;
432 Level Code Message
433 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
434 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
435 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
436 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
437 SHOW CREATE TABLE t1;
438 Table Create Table
439 t1 CREATE TEMPORARY TABLE `t1` (
440 `i` int(11) NOT NULL,
441 PRIMARY KEY (`i`)
442 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
443 ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
444 Warnings:
445 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
446 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
447 SHOW WARNINGS;
448 Level Code Message
449 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
450 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
451 SHOW CREATE TABLE t1;
452 Table Create Table
453 t1 CREATE TEMPORARY TABLE `t1` (
454 `i` int(11) NOT NULL,
455 PRIMARY KEY (`i`)
456 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4
457 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
458 Warnings:
459 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
460 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
461 SHOW WARNINGS;
462 Level Code Message
463 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
464 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4.
465 SHOW CREATE TABLE t1;
466 Table Create Table
467 t1 CREATE TEMPORARY TABLE `t1` (
468 `i` int(11) NOT NULL,
469 PRIMARY KEY (`i`)
470 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=4
471 DROP TABLE t1;
472 set innodb_strict_mode = ON;
473 CREATE TEMPORARY TABLE t1 (
474 i INT NOT NULL PRIMARY KEY,
475 c CHAR(10) NOT NULL
476 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
477 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
478 SHOW WARNINGS;
479 Level Code Message
480 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
481 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
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, ROW_FORMAT = COMPACT;
486 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
487 SHOW WARNINGS;
488 Level Code Message
489 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
490 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
491 CREATE TEMPORARY TABLE t1 (
492 i INT NOT NULL PRIMARY KEY,
493 c CHAR(10) NOT NULL
494 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
495 SHOW WARNINGS;
496 Level Code Message
497 SHOW CREATE TABLE t1;
498 Table Create Table
499 t1 CREATE TEMPORARY TABLE `t1` (
500 `i` int(11) NOT NULL,
501 `c` char(10) NOT NULL,
502 PRIMARY KEY (`i`)
503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=REDUNDANT
504 DROP TABLE t1;
505 CREATE TEMPORARY TABLE t1 (
506 i INT NOT NULL PRIMARY KEY,
507 c CHAR(10) NOT NULL
508 ) ENGINE = InnoDB KEY_BLOCK_SIZE = 4;
509 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
510 SHOW WARNINGS;
511 Level Code Message
512 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
513 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
514 CREATE TEMPORARY TABLE t1 (
515 i INT NOT NULL PRIMARY KEY,
516 c CHAR(10) NOT NULL
517 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED;
518 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
519 SHOW WARNINGS;
520 Level Code Message
521 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
522 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
523 CREATE TEMPORARY TABLE t1 (
524 i INT NOT NULL PRIMARY KEY,
525 c CHAR(10) NOT NULL
526 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 8;
527 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
528 SHOW WARNINGS;
529 Level Code Message
530 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
531 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
532 CREATE TEMPORARY TABLE t1 (
533 i INT NOT NULL PRIMARY KEY,
534 c CHAR(10) NOT NULL
535 ) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE = 7;
536 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
537 SHOW WARNINGS;
538 Level Code Message
539 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
540 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
541 CREATE TEMPORARY TABLE t1 (
542 i INT NOT NULL PRIMARY KEY,
543 c CHAR(10) NOT NULL
544 ) ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
545 SHOW WARNINGS;
546 Level Code Message
547 SHOW CREATE TABLE t1;
548 Table Create Table
549 t1 CREATE TEMPORARY TABLE `t1` (
550 `i` int(11) NOT NULL,
551 `c` char(10) NOT NULL,
552 PRIMARY KEY (`i`)
553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
554 DROP TABLE t1;
555 CREATE TEMPORARY TABLE t1 (
556 i INT NOT NULL PRIMARY KEY
557 ) ENGINE = InnoDB ROW_FORMAT = REDUNDANT;
558 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
559 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
560 SHOW WARNINGS;
561 Level Code Message
562 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
563 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
564 ALTER TABLE t1 KEY_BLOCK_SIZE = 4;
565 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
566 SHOW WARNINGS;
567 Level Code Message
568 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
569 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
570 ALTER TABLE t1 ROW_FORMAT = DYNAMIC KEY_BLOCK_SIZE = 4;
571 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
572 SHOW WARNINGS;
573 Level Code Message
574 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
575 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
576 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
577 set innodb_strict_mode = OFF;
578 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
579 Warnings:
580 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
581 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
582 SHOW WARNINGS;
583 Level Code Message
584 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
585 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
586 set innodb_strict_mode = ON;
587 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
588 SHOW CREATE TABLE t1;
589 Table Create Table
590 t1 CREATE TEMPORARY TABLE `t1` (
591 `i` int(11) NOT NULL,
592 PRIMARY KEY (`i`)
593 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
594 set innodb_strict_mode = OFF;
595 ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
596 Warnings:
597 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
598 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
599 SHOW WARNINGS;
600 Level Code Message
601 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
602 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
603 ALTER TABLE t1 KEY_BLOCK_SIZE = 8;
604 Warnings:
605 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
606 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
607 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
608 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
609 SHOW WARNINGS;
610 Level Code Message
611 Warning 1478 InnoDB: KEY_BLOCK_SIZE is ignored for TEMPORARY TABLE.
612 Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=8.
613 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
614 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
615 set innodb_strict_mode = ON;
616 ALTER TABLE t1 ADD COLUMN j INT;
617 ERROR HY000: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
618 SHOW WARNINGS;
619 Level Code Message
620 Error 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
621 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
622 SHOW CREATE TABLE t1;
623 Table Create Table
624 t1 CREATE TEMPORARY TABLE `t1` (
625 `i` int(11) NOT NULL,
626 PRIMARY KEY (`i`)
627 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
628 set innodb_strict_mode = OFF;
629 ALTER TABLE t1 KEY_BLOCK_SIZE = 0;
630 Warnings:
631 Warning 1478 InnoDB: ROW_FORMAT=COMPRESSED is ignored for TEMPORARY TABLE.
632 Warning 1478 InnoDB: assuming ROW_FORMAT=DYNAMIC.
633 SHOW CREATE TABLE t1;
634 Table Create Table
635 t1 CREATE TEMPORARY TABLE `t1` (
636 `i` int(11) NOT NULL,
637 PRIMARY KEY (`i`) KEY_BLOCK_SIZE=8
638 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED
639 ALTER TABLE t1 ROW_FORMAT = DYNAMIC;
640 set innodb_strict_mode = ON;
641 ALTER TABLE t1 ADD COLUMN j INT;
642 SHOW CREATE TABLE t1;
643 Table Create Table
644 t1 CREATE TEMPORARY TABLE `t1` (
645 `i` int(11) NOT NULL,
646 `j` int(11) DEFAULT NULL,
647 PRIMARY KEY (`i`) KEY_BLOCK_SIZE=8
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
649 DROP TABLE t1;
650 CREATE TEMPORARY TABLE t1(f1 INT, KEY(f1)) ENGINE=InnoDB;
651 INSERT INTO t1 VALUES(NULL);
652 UPDATE t1 SET f1 = 0;
653 START TRANSACTION;
654 UPDATE t1 SET f1 = 4;
655 UPDATE t1 SET f1 = 0;
656 ROLLBACK;
657 SELECT * FROM t1;
658 f1
659 0
660 DROP TABLE t1;
661 create procedure t1_proc()
662 begin
663 DECLARE var INT UNSIGNED;
664 CREATE TEMPORARY TABLE t1(f1 INT UNSIGNED, f2 INT UNSIGNED, KEY( f1, f2 ) )engine=innodb;
665 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
666 START TRANSACTION;
667 INSERT INTO t1 SET f1 = 1, f2 = 1;
668 UPDATE t1 SET f2 = 2;
669 SET var = ( SELECT 1 FROM t1 );
670 DROP TABLE t1;
671 END//
672 call t1_proc;
673 drop procedure t1_proc;
674 #
675 # MDEV-15874 CREATE TABLE creates extra transaction
676 #
677 call mtr.add_suppression("Warning 150 Create table `mysqld.1`.`t1` with foreign key constraint failed. Temporary tables can't have foreign key constraints.*");
678 SET FOREIGN_KEY_CHECKS = 0;
679 CREATE TEMPORARY TABLE t1(f1 INT NOT NULL,
680 FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB;
681 ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
682 CREATE TABLE t (c INT) ENGINE=InnoDB;
683 INSERT INTO t VALUES(0);
684 CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB;
685 START TRANSACTION READ ONLY;
686 INSERT INTO t2 SELECT * FROM t;
687 COMMIT;
688 DROP TABLE t, t2;
689 CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB;
690 START TRANSACTION READ ONLY;
691 INSERT INTO t VALUES(0);
692 ERROR 21S01: Column count doesn't match value count at row 1
693 SAVEPOINT s;
694 INSERT INTO t VALUES(0,0);
695 COMMIT;
696 DROP TABLE t;
697 CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB;
698 START TRANSACTION READ ONLY;
699 INSERT INTO t VALUES(0);
700 ERROR 21S01: Column count doesn't match value count at row 1
701 SAVEPOINT s;
702 INSERT INTO t VALUES(0,0);
703 ROLLBACK;
704 DROP TABLE t;
705 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
706 INSERT INTO t1 VALUES (1);
707 START TRANSACTION READ ONLY;
708 UPDATE t1 SET a= 2;
709 COMMIT;
710 DROP TABLE t1;
711 CREATE TEMPORARY TABLE t(c INT) ENGINE=InnoDB;
712 SET SESSION TRANSACTION_READ_ONLY=TRUE;
713 LOCK TABLE test.t READ;
714 SELECT * FROM t;
715 c
716 INSERT INTO t VALUES(0xADC3);
717 SET SESSION TRANSACTION_READ_ONLY=FALSE;
718 DROP TABLE t;
719 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
720 INSERT INTO t1 VALUES (1);
721 START TRANSACTION READ ONLY;
722 UPDATE t1 SET a= 2;
723 COMMIT;
724 DROP TABLE t1;
725 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int) ENGINE=InnoDB;
726 INSERT INTO t1 VALUES (1, 1);
727 START TRANSACTION READ ONLY;
728 UPDATE t1 SET b= 2;
729 COMMIT;
730 DROP TABLE t1;
731 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int, c varchar(255)) ENGINE=InnoDB;
732 INSERT INTO t1 VALUES (1, 1, repeat('a', 200));
733 START TRANSACTION READ ONLY;
734 UPDATE t1 SET b= 2, c=repeat('a', 250);
735 COMMIT;
736 DROP TABLE t1;
737 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
738 INSERT INTO t1 VALUES (1);
739 START TRANSACTION READ ONLY;
740 UPDATE t1 SET a= 2;
741 ROLLBACK;
742 DROP TABLE t1;
743 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
744 INSERT INTO t1 VALUES (1);
745 START TRANSACTION READ ONLY;
746 DELETE FROM t1 WHERE a= 2;
747 COMMIT;
748 DROP TABLE t1;
749 CREATE TEMPORARY TABLE tmp (a INT) ENGINE=InnoDB;
750 INSERT INTO tmp () VALUES (),();
751 SET TRANSACTION_READ_ONLY= 1;
752 INSERT INTO tmp SELECT * FROM tmp;
753 SET TRANSACTION_READ_ONLY= 0;
754 DROP TABLE tmp;
755 SET sql_mode='';
756 SET GLOBAL TRANSACTION_READ_ONLY=TRUE;
757 CREATE TEMPORARY TABLE t (c INT);
758 SET SESSION TRANSACTION_READ_ONLY=DEFAULT;
759 INSERT INTO t VALUES(1);
760 INSERT INTO t SELECT * FROM t;
761 SET SESSION TRANSACTION_READ_ONLY=FALSE;
762 SET GLOBAL TRANSACTION_READ_ONLY=OFF;
763 DROP TABLE t;
764 CREATE TEMPORARY TABLE t(a INT);
765 SET SESSION TRANSACTION_READ_ONLY=ON;
766 LOCK TABLE t READ;
767 SELECT COUNT(*)FROM t;
768 COUNT(*)
769 0
770 INSERT INTO t VALUES (0);
771 SET SESSION TRANSACTION_READ_ONLY=OFF;
772 DROP TABLE t;
773 CREATE TEMPORARY TABLE t (a INT) ENGINE=InnoDB;
774 INSERT INTO t VALUES (1);
775 START TRANSACTION READ ONLY;
776 UPDATE t SET a = NULL;
777 ROLLBACK;
778 DROP TABLE t;
779 #
780 # MDEV-29886 Assertion !index->table->is_temporary() failed
781 # in trx_undo_prev_version_build upon CHECK
782 #
783 CREATE TEMPORARY TABLE t (a INT, KEY(a)) ENGINE=InnoDB SELECT 1;
784 UPDATE t SET a=2;
785 CHECK TABLE t;
786 Table Op Msg_type Msg_text
787 test.t check status OK
788 CHECK TABLE t EXTENDED;
789 Table Op Msg_type Msg_text
790 test.t check status OK
791 DROP TEMPORARY TABLE t;
792 #
793 # MDEV-29978 Corruption errors upon CHECK on temporary InnoDB table
794 #
795 CREATE TEMPORARY TABLE t (f INT UNIQUE) ENGINE=InnoDB;
796 INSERT INTO t (f) VALUES (1),(2);
797 CHECK TABLE t;
798 Table Op Msg_type Msg_text
799 test.t check status OK
800 CHECK TABLE t EXTENDED;
801 Table Op Msg_type Msg_text
802 test.t check status OK
803 DROP TEMPORARY TABLE t;
804 #
805 # MDEV-34118 fsp_alloc_free_extent() fails to flag DB_OUT_OF_FILE_SPACE
806 #
807 SET @save_increment = @@GLOBAL.innodb_autoextend_increment;
808 SET GLOBAL innodb_autoextend_increment=1;
809 CREATE TEMPORARY TABLE t (c LONGTEXT) ENGINE=INNODB;
810 INSERT INTO t VALUES (REPEAT ('1',16777216));
811 ERROR HY000: The table 't' is full
812 DROP TEMPORARY TABLE t;
813 SET GLOBAL innodb_autoextend_increment=@save_increment;
814 # End of 10.6 tests