Debian Codegraph @

mysql-test/suite/innodb_zip/t/transportable_tbsp_zip.test

raw
1 -- source include/have_innodb_max_16k.inc
2
3 # compressed table in tests are with sizes KEY_BLOCK_SIZE 1,2,4,8,16
4 # Table creatation fails if KEY_BLOCK_SIZE > innodb-page-size,so
5 # allow test to run only when innodb-page-size=16
6
7 --disable_query_log
8 call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* cannot calculate statistics for table `test`\.`t1` because the \.ibd file is missing");
9 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Unable to import tablespace `test`\.`t1` because it already exists. Please DISCARD the tablespace before IMPORT.");
10 --enable_query_log
11
12 --disable_warnings
13 DROP TABLE IF EXISTS t1;
14 --enable_warnings
15
16 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
17
18 SET GLOBAL innodb_file_per_table = 1;
19 SELECT @@innodb_file_per_table;
20
21 SET SESSION innodb_strict_mode=1;
22 SELECT @@SESSION.innodb_strict_mode;
23
24 let $MYSQLD_TMPDIR = `SELECT @@tmpdir`;
25 let $MYSQLD_DATADIR = `SELECT @@datadir`;
26
27 CREATE TABLE t1
28 (a INT AUTO_INCREMENT PRIMARY KEY,
29 b char(22),
30 c varchar(255),
31 KEY (b))
32 ENGINE = InnoDB ROW_FORMAT=COMPRESSED ;
33
34 insert into t1 (b, c) values ('Apa', 'Filler........'),
35 ('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'),
36 ('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg');
37
38 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
39 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
40 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
41 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
42 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
43 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
44 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
45 SELECT COUNT(*) FROM t1;
46 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
47 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
48 --list_files $MYSQLD_DATADIR/test
49 --echo # Restarting server
50 -- source include/restart_mysqld.inc
51 --echo # Done restarting server
52 FLUSH TABLE t1 FOR EXPORT;
53 --echo # List before copying files
54 --list_files $MYSQLD_DATADIR/test
55 --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_TMPDIR/t1.cfg
56 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_TMPDIR/t1.ibd
57 UNLOCK TABLES;
58 INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
59 SELECT COUNT(*) FROM t1;
60 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
61 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
62 --echo # Restarting server
63 -- source include/restart_mysqld.inc
64 --echo # Done restarting server
65 --echo # List before t1 DISCARD
66 --list_files $MYSQLD_DATADIR/test
67 ALTER TABLE t1 DISCARD TABLESPACE;
68 --echo # List after t1 DISCARD
69 --list_files $MYSQLD_DATADIR/test
70 --copy_file $MYSQLD_TMPDIR/t1.cfg $MYSQLD_DATADIR/test/t1.cfg
71 --copy_file $MYSQLD_TMPDIR/t1.ibd $MYSQLD_DATADIR/test/t1.ibd
72 ALTER TABLE t1 IMPORT TABLESPACE;
73 SET innodb_strict_mode=off;
74 ALTER TABLE t1 ENGINE InnoDB;
75 SET innodb_strict_mode=default;
76 SELECT COUNT(*) FROM t1;
77 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
78 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
79 --list_files $MYSQLD_DATADIR/test
80 SELECT COUNT(*) FROM t1;
81 SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
82 SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
83 DROP TABLE t1;
84 --remove_file $MYSQLD_TMPDIR/t1.cfg
85 --remove_file $MYSQLD_TMPDIR/t1.ibd
86
87 SET GLOBAL innodb_file_per_table = 1;
88 SELECT @@innodb_file_per_table;
89
90 # restore session variable
91 SET SESSION innodb_strict_mode=1;
92 SELECT @@SESSION.innodb_strict_mode;
93
94 let MYSQLD_DATADIR =`SELECT @@datadir`;
95
96 # Try importing when tablespace already exists
97 CREATE TABLE t1(
98 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
99 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
100
101 INSERT INTO t1(c2) VALUES(1);
102 --error ER_TABLESPACE_EXISTS
103 ALTER TABLE t1 IMPORT TABLESPACE;
104 SELECT * FROM t1;
105 DROP TABLE t1;
106
107 # Export/import on the same instance, with --innodb-file-per-table=1
108 CREATE TABLE t1(
109 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
110 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
111
112 INSERT INTO t1(c2) VALUES(1);
113 INSERT INTO t1(c2) SELECT c2 FROM t1;
114 INSERT INTO t1(c2) SELECT c2 FROM t1;
115 INSERT INTO t1(c2) SELECT c2 FROM t1;
116 INSERT INTO t1(c2) SELECT c2 FROM t1;
117
118 --list_files $MYSQLD_DATADIR/test
119
120 FLUSH TABLES t1 FOR EXPORT;
121 SELECT COUNT(*) FROM t1;
122 perl;
123 require 'include/innodb-util.inc';
124 ib_backup_tablespaces("test", "t1");
125 EOF
126
127 --list_files $MYSQLD_DATADIR/test
128
129 UNLOCK TABLES;
130
131 DROP TABLE t1;
132
133 CREATE TABLE t1(
134 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
135 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
136
137 ALTER TABLE t1 DISCARD TABLESPACE;
138 --error ER_TABLESPACE_DISCARDED
139 SELECT * FROM t1;
140
141 perl;
142 require 'include/innodb-util.inc';
143 ib_discard_tablespaces("test", "t1");
144 ib_restore_tablespaces("test", "t1");
145 EOF
146
147 --list_files $MYSQLD_DATADIR/test
148
149 ALTER TABLE t1 IMPORT TABLESPACE;
150 CHECK TABLE t1;
151
152 SELECT COUNT(*) FROM t1;
153
154 DROP TABLE t1;
155
156 # Export/import on the same instance, with --innodb-file-per-table=1
157 # Insert some more records to move the LSN forward and then drop the
158 # table and restore
159 CREATE TABLE t1(
160 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
161 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
162
163 INSERT INTO t1(c2) VALUES(1);
164 INSERT INTO t1(c2) SELECT c2 FROM t1;
165 INSERT INTO t1(c2) SELECT c2 FROM t1;
166 INSERT INTO t1(c2) SELECT c2 FROM t1;
167 INSERT INTO t1(c2) SELECT c2 FROM t1;
168
169 --list_files $MYSQLD_DATADIR/test
170 FLUSH TABLES t1 FOR EXPORT;
171 SELECT COUNT(*) FROM t1;
172 perl;
173 require 'include/innodb-util.inc';
174 ib_backup_tablespaces("test", "t1");
175 EOF
176 --list_files $MYSQLD_DATADIR/test
177 UNLOCK TABLES;
178
179 --list_files $MYSQLD_DATADIR/test
180
181 # Move the LSN forward
182 INSERT INTO t1(c2) SELECT c2 FROM t1;
183 INSERT INTO t1(c2) SELECT c2 FROM t1;
184
185 DROP TABLE t1;
186
187 CREATE TABLE t1(
188 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
189 c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
190
191 ALTER TABLE t1 DISCARD TABLESPACE;
192 --error ER_TABLESPACE_DISCARDED
193 SELECT * FROM t1;
194
195 perl;
196 require 'include/innodb-util.inc';
197 ib_discard_tablespaces("test", "t1");
198 ib_restore_tablespaces("test", "t1");
199 EOF
200
201 --list_files $MYSQLD_DATADIR/test
202
203 ALTER TABLE t1 IMPORT TABLESPACE;
204 CHECK TABLE t1;
205
206 SELECT COUNT(*) FROM t1;
207
208 DROP TABLE t1;
209
210 # Export/import on the same instance, with --innodb-file-per-table=1
211 # Insert some more records to move the LSN forward and then drop the
212 # table and restore, this time the table has a secondary index too.
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
218 INSERT INTO t1(c2) VALUES(1);
219 INSERT INTO t1(c2) SELECT c2 FROM t1;
220 INSERT INTO t1(c2) SELECT c2 FROM t1;
221 INSERT INTO t1(c2) SELECT c2 FROM t1;
222 INSERT INTO t1(c2) SELECT c2 FROM t1;
223
224 FLUSH TABLES t1 FOR EXPORT;
225 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
226 perl;
227 require 'include/innodb-util.inc';
228 ib_backup_tablespaces("test", "t1");
229 EOF
230 --list_files $MYSQLD_DATADIR/test
231 UNLOCK TABLES;
232
233 # Move the LSN forward
234 INSERT INTO t1(c2) SELECT c2 FROM t1;
235 INSERT INTO t1(c2) SELECT c2 FROM t1;
236
237 DROP TABLE t1;
238
239 CREATE TABLE t1(
240 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
241 c2 INT, INDEX(c2)) ENGINE=InnoDB
242 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
243
244 ALTER TABLE t1 DISCARD TABLESPACE;
245 --error ER_TABLESPACE_DISCARDED
246 SELECT * FROM t1;
247
248 perl;
249 require 'include/innodb-util.inc';
250 ib_discard_tablespaces("test", "t1");
251 ib_restore_tablespaces("test", "t1");
252 EOF
253
254 ALTER TABLE t1 IMPORT TABLESPACE;
255 CHECK TABLE t1;
256
257 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
258
259 DROP TABLE t1;
260
261 # Export/import on the same instance, with --innodb-file-per-table=1
262 # Insert some more records to move the LSN forward and then drop the
263 # table and restore, this time the table has a secondary index too.
264 # Rename the index on the create so that the IMPORT fails, drop index
265 # Create with proper name and then do an IMPORT.
266 CREATE TABLE t1(
267 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
268 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
269 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
270
271 INSERT INTO t1(c2) VALUES(1);
272 INSERT INTO t1(c2) SELECT c2 FROM t1;
273 INSERT INTO t1(c2) SELECT c2 FROM t1;
274 INSERT INTO t1(c2) SELECT c2 FROM t1;
275 INSERT INTO t1(c2) SELECT c2 FROM t1;
276
277 FLUSH TABLES t1 FOR EXPORT;
278 SELECT COUNT(*) FROM t1 WHERE c2 = 1;
279 perl;
280 require 'include/innodb-util.inc';
281 ib_backup_tablespaces("test", "t1");
282 EOF
283 UNLOCK TABLES;
284
285 # Move the LSN forward
286 INSERT INTO t1(c2) SELECT c2 FROM t1;
287 INSERT INTO t1(c2) SELECT c2 FROM t1;
288
289 DROP TABLE t1;
290
291 CREATE TABLE t1(
292 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
293 c2 INT, INDEX x(c2)) ENGINE=InnoDB
294 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
295
296 ALTER TABLE t1 DISCARD TABLESPACE;
297 --error ER_TABLESPACE_DISCARDED
298 SELECT * FROM t1;
299
300 perl;
301 require 'include/innodb-util.inc';
302 ib_discard_tablespaces("test", "t1");
303 ib_restore_tablespaces("test", "t1");
304 EOF
305
306 # This is really a name mismatch error, need better error codes.
307 -- error ER_TABLE_SCHEMA_MISMATCH
308 ALTER TABLE t1 IMPORT TABLESPACE;
309
310 ALTER TABLE t1 DROP INDEX x;
311 ALTER TABLE t1 ADD INDEX idx(c2);
312
313 perl;
314 require 'include/innodb-util.inc';
315 ib_restore_tablespaces("test", "t1");
316 EOF
317
318 ALTER TABLE t1 IMPORT TABLESPACE;
319 CHECK TABLE t1;
320
321 SELECT * FROM t1;
322
323 perl;
324 require 'include/innodb-util.inc';
325 ib_cleanup("test", "t1");
326 EOF
327
328 DROP TABLE t1;
329
330 #
331 # Export/import on the same instance, with --innodb-file-per-table=0
332 # This should fail because it is not supported
333 SET GLOBAL innodb_file_per_table = 0;
334
335 CREATE TABLE t1(
336 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
337 c2 INT) ENGINE=InnoDB;
338
339 INSERT INTO t1(c2) VALUES(1);
340 INSERT INTO t1(c2) SELECT c2 FROM t1;
341 INSERT INTO t1(c2) SELECT c2 FROM t1;
342 INSERT INTO t1(c2) SELECT c2 FROM t1;
343 INSERT INTO t1(c2) SELECT c2 FROM t1;
344
345 SELECT COUNT(*) FROM t1;
346
347 SHOW CREATE TABLE t1;
348
349 # This should fail, InnoDB should return a warning
350 FLUSH TABLES t1 FOR EXPORT;
351
352 UNLOCK TABLES;
353
354 DROP TABLE t1;
355
356 #
357 # Tests that check for schema mismatch during IMPORT
358 #
359
360 SET GLOBAL innodb_file_per_table = 1;
361
362 CREATE TABLE t1(
363 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
364 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
365 ROW_FORMAT=COMPRESSED;
366
367 INSERT INTO t1(c2) VALUES(1);
368 INSERT INTO t1(c2) SELECT c2 FROM t1;
369 INSERT INTO t1(c2) SELECT c2 FROM t1;
370 INSERT INTO t1(c2) SELECT c2 FROM t1;
371 INSERT INTO t1(c2) SELECT c2 FROM t1;
372 INSERT INTO t1(c2) SELECT c2 FROM t1;
373
374 SHOW CREATE TABLE t1;
375 SELECT * FROM t1;
376
377 FLUSH TABLES t1 FOR EXPORT;
378
379 perl;
380 require 'include/innodb-util.inc';
381 ib_backup_tablespaces("test", "t1");
382 EOF
383
384 UNLOCK TABLES;
385
386 DROP TABLE t1;
387
388 # Table without the secondary index
389 CREATE TABLE t1(
390 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
391 c2 INT) ENGINE=InnoDB
392 ROW_FORMAT=COMPRESSED;
393
394 ALTER TABLE t1 DISCARD TABLESPACE;
395 --error ER_TABLESPACE_DISCARDED
396 SELECT * FROM t1;
397
398 perl;
399 require 'include/innodb-util.inc';
400 ib_discard_tablespaces("test", "t1");
401 ib_restore_tablespaces("test", "t1");
402 EOF
403
404 # This should fail because of a missing secondary index
405 -- error ER_TABLE_SCHEMA_MISMATCH
406 ALTER TABLE t1 IMPORT TABLESPACE;
407
408 perl;
409 require 'include/innodb-util.inc';
410 ib_unlink_tablespace("test", "t1");
411 EOF
412
413 DROP TABLE t1;
414
415 # Table with an additional column
416 CREATE TABLE t1(
417 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
418 c2 INT,
419 c3 INT, INDEX idx(c2)) ENGINE=InnoDB
420 ROW_FORMAT=COMPRESSED;
421
422 ALTER TABLE t1 DISCARD TABLESPACE;
423 --error ER_TABLESPACE_DISCARDED
424 SELECT * FROM t1;
425
426 perl;
427 require 'include/innodb-util.inc';
428 ib_discard_tablespaces("test", "t1");
429 ib_restore_tablespaces("test", "t1");
430 EOF
431
432 # This should fail because the table has an additional column
433 -- error ER_TABLE_SCHEMA_MISMATCH
434 ALTER TABLE t1 IMPORT TABLESPACE;
435
436 perl;
437 require 'include/innodb-util.inc';
438 ib_unlink_tablespace("test", "t1");
439 EOF
440
441 DROP TABLE t1;
442
443 # Change the column type of c2
444 CREATE TABLE t1(
445 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
446 c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB
447 ROW_FORMAT=COMPRESSED;
448
449 ALTER TABLE t1 DISCARD TABLESPACE;
450 --error ER_TABLESPACE_DISCARDED
451 SELECT * FROM t1;
452
453 perl;
454 require 'include/innodb-util.inc';
455 ib_discard_tablespaces("test", "t1");
456 ib_restore_tablespaces("test", "t1");
457 EOF
458
459 # This should fail because c2 is now a BIGINT and not INT
460 -- error ER_TABLE_SCHEMA_MISMATCH
461 ALTER TABLE t1 IMPORT TABLESPACE;
462
463 perl;
464 require 'include/innodb-util.inc';
465 ib_unlink_tablespace("test", "t1");
466 EOF
467
468 DROP TABLE t1;
469
470 # This should fail because KEY_BLOCK_SIZE is different
471 CREATE TABLE t1(
472 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
473 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
474 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
475
476 ALTER TABLE t1 DISCARD TABLESPACE;
477 --error ER_TABLESPACE_DISCARDED
478 SELECT * FROM t1;
479
480 perl;
481 require 'include/innodb-util.inc';
482 ib_discard_tablespaces("test", "t1");
483 ib_restore_tablespaces("test", "t1");
484 EOF
485
486 # This should fail because is KEY_BLOCK_SIZE=4
487 # but KEY_BLOCK_SIZE=8 is exported table
488 # Need better error message for following
489 --replace_regex /match, .*\)/match)/
490 -- error ER_TABLE_SCHEMA_MISMATCH
491 ALTER TABLE t1 IMPORT TABLESPACE;
492
493 perl;
494 require 'include/innodb-util.inc';
495 ib_unlink_tablespace("test", "t1");
496 EOF
497
498 DROP TABLE t1;
499
500
501 # This should be OK.
502 CREATE TABLE t1(
503 c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
504 c2 INT, INDEX idx(c2)) ENGINE=InnoDB
505 ROW_FORMAT=COMPRESSED;
506
507 ALTER TABLE t1 DISCARD TABLESPACE;
508 --error ER_TABLESPACE_DISCARDED
509 SELECT * FROM t1;
510
511 perl;
512 require 'include/innodb-util.inc';
513 ib_discard_tablespaces("test", "t1");
514 ib_restore_tablespaces("test", "t1");
515 EOF
516
517 ALTER TABLE t1 IMPORT TABLESPACE;
518 CHECK TABLE t1;
519
520 perl;
521 require 'include/innodb-util.inc';
522 ib_cleanup("test", "t1");
523 EOF
524
525 SHOW CREATE TABLE t1;
526 SELECT * FROM t1;
527
528 DROP TABLE t1;
529
530 # cleanup
531 --remove_file $MYSQLTEST_VARDIR/tmp/t1.cfg
532 --remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd
533
534 eval SET GLOBAL INNODB_FILE_PER_TABLE=$innodb_file_per_table;