Debian Codegraph @

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

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