Debian Codegraph @

mysql-test/suite/innodb_zip/r/transportable_tbsp_zip.result

raw
1 DROP TABLE IF EXISTS t1;
2 SET GLOBAL innodb_file_per_table = 1;
3 SELECT @@innodb_file_per_table;
4 @@innodb_file_per_table
5 1
6 SET SESSION innodb_strict_mode=1;
7 SELECT @@SESSION.innodb_strict_mode;
8 @@SESSION.innodb_strict_mode
9 1
10 CREATE TABLE t1
11 (a INT AUTO_INCREMENT PRIMARY KEY,
12 b char(22),
13 c varchar(255),
14 KEY (b))
15 ENGINE = InnoDB ROW_FORMAT=COMPRESSED ;
16 insert into t1 (b, c) values ('Apa', 'Filler........'),
17 ('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'),
18 ('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg');
19 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
20 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
21 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
22 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
23 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
24 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
25 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
26 SELECT COUNT(*) FROM t1;
27 COUNT(*)
28 640
29 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
30 a b c
31 819 Apa Filler........
32 814 Apa Filler........
33 809 Apa Filler........
34 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
35 a b c
36 823 Evolution lsjndofiabsoibeg
37 822 Devotion asdfuihknaskdf
38 821 Cavalry ..asdasdfaeraf
39 t1.ibd
40 # Restarting server
41 # restart
42 # Done restarting server
43 FLUSH TABLE t1 FOR EXPORT;
44 # List before copying files
45 t1.cfg
46 t1.ibd
47 UNLOCK TABLES;
48 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
49 SELECT COUNT(*) FROM t1;
50 COUNT(*)
51 1280
52 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
53 a b c
54 1459 Apa Filler........
55 1454 Apa Filler........
56 1449 Apa Filler........
57 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
58 a b c
59 1463 Evolution lsjndofiabsoibeg
60 1462 Devotion asdfuihknaskdf
61 1461 Cavalry ..asdasdfaeraf
62 # Restarting server
63 # restart
64 # Done restarting server
65 # List before t1 DISCARD
66 t1.ibd
67 ALTER TABLE t1 DISCARD TABLESPACE;
68 # List after t1 DISCARD
69 ALTER TABLE t1 IMPORT TABLESPACE;
70 SET innodb_strict_mode=off;
71 ALTER TABLE t1 ENGINE InnoDB;
72 SET innodb_strict_mode=default;
73 SELECT COUNT(*) FROM t1;
74 COUNT(*)
75 640
76 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
77 a b c
78 819 Apa Filler........
79 814 Apa Filler........
80 809 Apa Filler........
81 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
82 a b c
83 823 Evolution lsjndofiabsoibeg
84 822 Devotion asdfuihknaskdf
85 821 Cavalry ..asdasdfaeraf
86 t1.cfg
87 t1.ibd
88 SELECT COUNT(*) FROM t1;
89 COUNT(*)
90 640
91 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
92 a b c
93 819 Apa Filler........
94 814 Apa Filler........
95 809 Apa Filler........
96 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
97 a b c
98 823 Evolution lsjndofiabsoibeg
99 822 Devotion asdfuihknaskdf
100 821 Cavalry ..asdasdfaeraf
101 DROP TABLE t1;
102 SET GLOBAL innodb_file_per_table = 1;
103 SELECT @@innodb_file_per_table;
104 @@innodb_file_per_table
105 1
106 SET SESSION innodb_strict_mode=1;
107 SELECT @@SESSION.innodb_strict_mode;
108 @@SESSION.innodb_strict_mode
109 1
110 CREATE TABLE t1(
111 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
112 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
113 INSERT INTO t1(c2) VALUES(1);
114 ALTER TABLE t1 IMPORT TABLESPACE;
115 ERROR HY000: Tablespace 'test/t1' exists.
116 SELECT * FROM t1;
117 c1 c2
118 1 1
119 DROP TABLE t1;
120 CREATE TABLE t1(
121 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
122 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
123 INSERT INTO t1(c2) VALUES(1);
124 INSERT INTO t1(c2) SELECT c2 FROM t1;
125 INSERT INTO t1(c2) SELECT c2 FROM t1;
126 INSERT INTO t1(c2) SELECT c2 FROM t1;
127 INSERT INTO t1(c2) SELECT c2 FROM t1;
128 t1.ibd
129 FLUSH TABLES t1 FOR EXPORT;
130 SELECT COUNT(*) FROM t1;
131 COUNT(*)
132 16
133 backup: t1
134 t1.cfg
135 t1.ibd
136 UNLOCK TABLES;
137 DROP TABLE t1;
138 CREATE TABLE t1(
139 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
140 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
141 ALTER TABLE t1 DISCARD TABLESPACE;
142 SELECT * FROM t1;
143 ERROR HY000: Tablespace has been discarded for table 't1'
144 restore: t1 .ibd and .cfg files
145 t1.cfg
146 t1.ibd
147 ALTER TABLE t1 IMPORT TABLESPACE;
148 CHECK TABLE t1;
149 Table Op Msg_type Msg_text
150 test.t1 check status OK
151 SELECT COUNT(*) FROM t1;
152 COUNT(*)
153 16
154 DROP TABLE t1;
155 CREATE TABLE t1(
156 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
157 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
158 INSERT INTO t1(c2) VALUES(1);
159 INSERT INTO t1(c2) SELECT c2 FROM t1;
160 INSERT INTO t1(c2) SELECT c2 FROM t1;
161 INSERT INTO t1(c2) SELECT c2 FROM t1;
162 INSERT INTO t1(c2) SELECT c2 FROM t1;
163 t1.ibd
164 FLUSH TABLES t1 FOR EXPORT;
165 SELECT COUNT(*) FROM t1;
166 COUNT(*)
167 16
168 backup: t1
169 t1.cfg
170 t1.ibd
171 UNLOCK TABLES;
172 t1.ibd
173 INSERT INTO t1(c2) SELECT c2 FROM t1;
174 INSERT INTO t1(c2) SELECT c2 FROM t1;
175 DROP TABLE t1;
176 CREATE TABLE t1(
177 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
178 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
179 ALTER TABLE t1 DISCARD TABLESPACE;
180 SELECT * FROM t1;
181 ERROR HY000: Tablespace has been discarded for table 't1'
182 restore: t1 .ibd and .cfg files
183 t1.cfg
184 t1.ibd
185 ALTER TABLE t1 IMPORT TABLESPACE;
186 CHECK TABLE t1;
187 Table Op Msg_type Msg_text
188 test.t1 check status OK
189 SELECT COUNT(*) FROM t1;
190 COUNT(*)
191 16
192 DROP TABLE t1;
193 CREATE TABLE t1(
194 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
195 c2 INT, INDEX(c2)) ENGINE=InnoDB
196 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
197 INSERT INTO t1(c2) VALUES(1);
198 INSERT INTO t1(c2) SELECT c2 FROM t1;
199 INSERT INTO t1(c2) SELECT c2 FROM t1;
200 INSERT INTO t1(c2) SELECT c2 FROM t1;
201 INSERT INTO t1(c2) SELECT c2 FROM t1;
202 FLUSH TABLES t1 FOR EXPORT;
203 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
204 COUNT(*)
205 16
206 backup: t1
207 t1.cfg
208 t1.ibd
209 UNLOCK TABLES;
210 INSERT INTO t1(c2) SELECT c2 FROM t1;
211 INSERT INTO t1(c2) SELECT c2 FROM t1;
212 DROP TABLE t1;
213 CREATE TABLE t1(
214 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
215 c2 INT, INDEX(c2)) ENGINE=InnoDB
216 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
217 ALTER TABLE t1 DISCARD TABLESPACE;
218 SELECT * FROM t1;
219 ERROR HY000: Tablespace has been discarded for table 't1'
220 restore: t1 .ibd and .cfg files
221 ALTER TABLE t1 IMPORT TABLESPACE;
222 CHECK TABLE t1;
223 Table Op Msg_type Msg_text
224 test.t1 check status OK
225 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
226 COUNT(*)
227 16
228 DROP TABLE t1;
229 CREATE TABLE t1(
230 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
231 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
232 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
233 INSERT INTO t1(c2) VALUES(1);
234 INSERT INTO t1(c2) SELECT c2 FROM t1;
235 INSERT INTO t1(c2) SELECT c2 FROM t1;
236 INSERT INTO t1(c2) SELECT c2 FROM t1;
237 INSERT INTO t1(c2) SELECT c2 FROM t1;
238 FLUSH TABLES t1 FOR EXPORT;
239 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
240 COUNT(*)
241 16
242 backup: t1
243 UNLOCK TABLES;
244 INSERT INTO t1(c2) SELECT c2 FROM t1;
245 INSERT INTO t1(c2) SELECT c2 FROM t1;
246 DROP TABLE t1;
247 CREATE TABLE t1(
248 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
249 c2 INT, INDEX x(c2)) ENGINE=InnoDB
250 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
251 ALTER TABLE t1 DISCARD TABLESPACE;
252 SELECT * FROM t1;
253 ERROR HY000: Tablespace has been discarded for table 't1'
254 restore: t1 .ibd and .cfg files
255 ALTER TABLE t1 IMPORT TABLESPACE;
256 ERROR HY000: Schema mismatch (Index x not found in tablespace meta-data file.)
257 ALTER TABLE t1 DROP INDEX x;
258 Warnings:
259 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
260 ALTER TABLE t1 ADD INDEX idx(c2);
261 Warnings:
262 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
263 restore: t1 .ibd and .cfg files
264 ALTER TABLE t1 IMPORT TABLESPACE;
265 CHECK TABLE t1;
266 Table Op Msg_type Msg_text
267 test.t1 check status OK
268 SELECT * FROM t1;
269 c1 c2
270 1 1
271 2 1
272 3 1
273 4 1
274 6 1
275 7 1
276 8 1
277 9 1
278 13 1
279 14 1
280 15 1
281 16 1
282 17 1
283 18 1
284 19 1
285 20 1
286 unlink: t1.cfg
287 DROP TABLE t1;
288 SET GLOBAL innodb_file_per_table = 0;
289 CREATE TABLE t1(
290 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
291 c2 INT) ENGINE=InnoDB;
292 INSERT INTO t1(c2) VALUES(1);
293 INSERT INTO t1(c2) SELECT c2 FROM t1;
294 INSERT INTO t1(c2) SELECT c2 FROM t1;
295 INSERT INTO t1(c2) SELECT c2 FROM t1;
296 INSERT INTO t1(c2) SELECT c2 FROM t1;
297 SELECT COUNT(*) FROM t1;
298 COUNT(*)
299 16
300 SHOW CREATE TABLE t1;
301 Table Create Table
302 t1 CREATE TABLE `t1` (
303 `c1` int NOT NULL AUTO_INCREMENT,
304 `c2` int DEFAULT NULL,
305 PRIMARY KEY (`c1`)
306 ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
307 FLUSH TABLES t1 FOR EXPORT;
308 Warnings:
309 Warning 1809 InnoDB: Table '`test`.`t1`' in system tablespace
310 UNLOCK TABLES;
311 DROP TABLE t1;
312 SET GLOBAL innodb_file_per_table = 1;
313 CREATE TABLE t1(
314 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
315 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
316 ROW_FORMAT=COMPRESSED;
317 INSERT INTO t1(c2) VALUES(1);
318 INSERT INTO t1(c2) SELECT c2 FROM t1;
319 INSERT INTO t1(c2) SELECT c2 FROM t1;
320 INSERT INTO t1(c2) SELECT c2 FROM t1;
321 INSERT INTO t1(c2) SELECT c2 FROM t1;
322 INSERT INTO t1(c2) SELECT c2 FROM t1;
323 SHOW CREATE TABLE t1;
324 Table Create Table
325 t1 CREATE TABLE `t1` (
326 `c1` int NOT NULL AUTO_INCREMENT,
327 `c2` int DEFAULT NULL,
328 PRIMARY KEY (`c1`),
329 KEY `idx` (`c2`)
330 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
331 SELECT * FROM t1;
332 c1 c2
333 1 1
334 2 1
335 3 1
336 4 1
337 6 1
338 7 1
339 8 1
340 9 1
341 13 1
342 14 1
343 15 1
344 16 1
345 17 1
346 18 1
347 19 1
348 20 1
349 28 1
350 29 1
351 30 1
352 31 1
353 32 1
354 33 1
355 34 1
356 35 1
357 36 1
358 37 1
359 38 1
360 39 1
361 40 1
362 41 1
363 42 1
364 43 1
365 FLUSH TABLES t1 FOR EXPORT;
366 backup: t1
367 UNLOCK TABLES;
368 DROP TABLE t1;
369 CREATE TABLE t1(
370 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
371 c2 INT) ENGINE=InnoDB
372 ROW_FORMAT=COMPRESSED;
373 ALTER TABLE t1 DISCARD TABLESPACE;
374 SELECT * FROM t1;
375 ERROR HY000: Tablespace has been discarded for table 't1'
376 restore: t1 .ibd and .cfg files
377 ALTER TABLE t1 IMPORT TABLESPACE;
378 ERROR HY000: Schema mismatch (Number of indexes don't match, table has 1 indexes but the tablespace meta-data file has 3 indexes)
379 unlink: t1.ibd
380 unlink: t1.cfg
381 DROP TABLE t1;
382 CREATE TABLE t1(
383 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
384 c2 INT,
385 c3 INT, INDEX idx(c2)) ENGINE=InnoDB
386 ROW_FORMAT=COMPRESSED;
387 ALTER TABLE t1 DISCARD TABLESPACE;
388 SELECT * FROM t1;
389 ERROR HY000: Tablespace has been discarded for table 't1'
390 restore: t1 .ibd and .cfg files
391 ALTER TABLE t1 IMPORT TABLESPACE;
392 ERROR HY000: Schema mismatch (Number of columns don't match, table has 6 columns but the tablespace meta-data file has 5 columns)
393 unlink: t1.ibd
394 unlink: t1.cfg
395 DROP TABLE t1;
396 CREATE TABLE t1(
397 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
398 c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB
399 ROW_FORMAT=COMPRESSED;
400 ALTER TABLE t1 DISCARD TABLESPACE;
401 SELECT * FROM t1;
402 ERROR HY000: Tablespace has been discarded for table 't1'
403 restore: t1 .ibd and .cfg files
404 ALTER TABLE t1 IMPORT TABLESPACE;
405 ERROR HY000: Schema mismatch (Column c2 precise type mismatch.)
406 unlink: t1.ibd
407 unlink: t1.cfg
408 DROP TABLE t1;
409 CREATE TABLE t1(
410 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
411 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
412 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
413 ALTER TABLE t1 DISCARD TABLESPACE;
414 SELECT * FROM t1;
415 ERROR HY000: Tablespace has been discarded for table 't1'
416 restore: t1 .ibd and .cfg files
417 ALTER TABLE t1 IMPORT TABLESPACE;
418 ERROR HY000: Schema mismatch (Table flags don't match)
419 unlink: t1.ibd
420 unlink: t1.cfg
421 DROP TABLE t1;
422 CREATE TABLE t1(
423 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
424 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
425 ROW_FORMAT=COMPRESSED;
426 ALTER TABLE t1 DISCARD TABLESPACE;
427 SELECT * FROM t1;
428 ERROR HY000: Tablespace has been discarded for table 't1'
429 restore: t1 .ibd and .cfg files
430 ALTER TABLE t1 IMPORT TABLESPACE;
431 CHECK TABLE t1;
432 Table Op Msg_type Msg_text
433 test.t1 check status OK
434 unlink: t1.cfg
435 SHOW CREATE TABLE t1;
436 Table Create Table
437 t1 CREATE TABLE `t1` (
438 `c1` int NOT NULL AUTO_INCREMENT,
439 `c2` int DEFAULT NULL,
440 PRIMARY KEY (`c1`),
441 KEY `idx` (`c2`)
442 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED
443 SELECT * FROM t1;
444 c1 c2
445 1 1
446 2 1
447 3 1
448 4 1
449 6 1
450 7 1
451 8 1
452 9 1
453 13 1
454 14 1
455 15 1
456 16 1
457 17 1
458 18 1
459 19 1
460 20 1
461 28 1
462 29 1
463 30 1
464 31 1
465 32 1
466 33 1
467 34 1
468 35 1
469 36 1
470 37 1
471 38 1
472 39 1
473 40 1
474 41 1
475 42 1
476 43 1
477 DROP TABLE t1;
478 SET GLOBAL INNODB_FILE_PER_TABLE=1;