Debian Codegraph @

mysql-test/suite/innodb/include/innodb_import.inc

raw
1 let MYSQLD_DATADIR =`SELECT @@datadir`;
2 SET @old_innodb_file_per_table = @@innodb_file_per_table;
3
4 if ($partitioning)
5 {
6 let $c1_part=PARTITION BY HASH(c1) PARTITIONS 3;
7 let $c18_part=PARTITION BY RANGE (col18)
8 SUBPARTITION BY HASH (col18) SUBPARTITIONS 3
9 (PARTITION `First_partition` VALUES LESS THAN (10),
10 PARTITION `Last_partition` VALUES LESS THAN MAXVALUE);
11 let $col7_part=PARTITION BY KEY (col_7_binary) PARTITIONS 3;
12 let $col1_part=PARTITION BY HASH (col_1_int) PARTITIONS 3;
13 let $col2_part=PARTITION BY KEY (col_2_varchar) PARTITIONS 3;
14 let $i_part=PARTITION BY HASH (i) PARTITIONS 3;
15 let $col1_key_part=PARTITION BY KEY (col_1) PARTITIONS 3;
16 let $col18_part=PARTITION BY HASH (col18) PARTITIONS 3;
17 # Set environment variable for perl snippets
18 let PARTITIONS=1;
19 }
20 SET GLOBAL innodb_file_per_table = 1;
21 SELECT @@innodb_file_per_table;
22
23 --echo # Following testcases are created from JET cases (where import
24 --echo # export instance are different servers)
25 --echo # Here test will be run on same import and export instance.
26
27 --echo #
28 --echo # case 1 Simple export, discard and import.
29 --echo #
30 eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
31 $c1_part;
32 INSERT INTO t1 VALUES (1),(123),(331);
33 SELECT c1 FROM t1;
34 FLUSH TABLES t1 FOR EXPORT;
35 SELECT * FROM t1 ORDER BY c1;
36
37 perl;
38 require 'include/innodb-util.inc';
39 if ($ENV{'PARTITIONS'})
40 {
41 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
42 } else {
43 ib_backup_tablespaces("test", "t1");
44 }
45 EOF
46
47 UNLOCK TABLES;
48
49 DROP TABLE t1;
50 eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
51 $c1_part;
52 ALTER TABLE t1 DISCARD TABLESPACE;
53
54
55 perl;
56 require 'include/innodb-util.inc';
57 if ($ENV{'PARTITIONS'})
58 {
59 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
60 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
61 } else {
62 ib_discard_tablespaces("test", "t1");
63 ib_restore_tablespaces("test", "t1");
64 }
65 EOF
66
67 ALTER TABLE t1 IMPORT TABLESPACE;
68 SELECT * FROM t1 ORDER BY c1;
69 DROP TABLE t1;
70
71 --echo #
72 --echo # case 2 With indexes and more datatypes.
73 --echo #
74 eval CREATE TABLE t1 (
75 col1 BIT(1),
76 col2 BOOLEAN,
77 col3 TINYINT,
78 col4 SMALLINT,
79 col5 MEDIUMINT,
80 col6 INT,
81 col7 BIGINT,
82 col8 FLOAT (14,3),
83 col9 DOUBLE (14,3),
84 col10 VARCHAR(20),
85 col11 TEXT,
86 col12 ENUM('a','b','c'),
87 col13 TEXT,
88 col14 CHAR(20),
89 col15 VARBINARY (400),
90 col16 BINARY(40),
91 col17 BLOB (400),
92 col18 INT NOT NULL PRIMARY KEY,
93 col19 DATE,
94 col20 DATETIME,
95 col21 TIMESTAMP NULL,
96 col22 TIME,
97 col23 YEAR) ENGINE = InnoDB
98 $c18_part;
99
100 CREATE INDEX idx1 ON t1(col18);
101 CREATE INDEX prefix_idx ON t1(col14 (10));
102 if (!$partitioning)
103 {
104 CREATE UNIQUE INDEX idx2 ON t1(col12);
105 CREATE UNIQUE INDEX idx3 ON t1(col8);
106 }
107 INSERT INTO t1 VALUES
108 (1,1,-128,32767,-8388608,2147483647,-9223372036854775808, 92233720368.222,
109 -92233720368.222,'aaa', + 'aaaaaaaaaa','b','bbbbb','ccccc',
110 REPEAT('d',40),REPEAT('d',40),REPEAT('d',40),1,'1000-01-01',
111 '3000-12-31 23:59:59.99','1990-01-01 00:00:01.00',
112 '01:59:59.00','1901');
113 INSERT INTO t1 VALUES
114 (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
115 NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
116 --error ER_BAD_NULL_ERROR
117 INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
118 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
119 --error ER_DUP_ENTRY
120 INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
121 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
122
123 FLUSH TABLES t1 WITH READ LOCK;
124 SELECT * FROM t1;
125
126 perl;
127 require 'include/innodb-util.inc';
128 if ($ENV{'PARTITIONS'})
129 {
130 ib_backup_tablespaces("test",
131 "t1#p#first_partition#sp#first_partitionsp0",
132 "t1#p#first_partition#sp#first_partitionsp1",
133 "t1#p#first_partition#sp#first_partitionsp2",
134 "t1#p#last_partition#sp#last_partitionsp0",
135 "t1#p#last_partition#sp#last_partitionsp1",
136 "t1#p#last_partition#sp#last_partitionsp2");
137 } else {
138 ib_backup_tablespaces("test", "t1");
139 }
140 EOF
141 UNLOCK TABLES;
142
143 DROP TABLE t1;
144
145 eval CREATE TABLE t1 (
146 col1 BIT(1),
147 col2 BOOLEAN,
148 col3 TINYINT,
149 col4 SMALLINT,
150 col5 MEDIUMINT,
151 col6 INT,
152 col7 BIGINT,
153 col8 FLOAT (14,3),
154 col9 DOUBLE (14,3),
155 col10 VARCHAR(20),
156 col11 TEXT,
157 col12 ENUM('a','b','c'),
158 col13 TEXT,
159 col14 CHAR(20),
160 col15 VARBINARY (400),
161 col16 BINARY(40),
162 col17 BLOB (400),
163 col18 INT NOT NULL PRIMARY KEY,
164 col19 DATE,
165 col20 DATETIME,
166 col21 TIMESTAMP NULL,
167 col22 TIME,
168 col23 YEAR) ENGINE = InnoDB
169 $c18_part;
170
171 CREATE INDEX idx1 ON t1(col18);
172 CREATE INDEX prefix_idx ON t1(col14 (10));
173 if (!$partitioning)
174 {
175 CREATE UNIQUE INDEX idx2 ON t1(col12);
176 CREATE UNIQUE INDEX idx3 ON t1(col8);
177 }
178
179 ALTER TABLE t1 DISCARD TABLESPACE;
180
181 perl;
182 require 'include/innodb-util.inc';
183 if ($ENV{'PARTITIONS'})
184 {
185 ib_discard_tablespaces("test",
186 "t1#p#first_partition#sp#first_partitionsp0",
187 "t1#p#first_partition#sp#first_partitionsp1",
188 "t1#p#first_partition#sp#first_partitionsp2",
189 "t1#p#last_partition#sp#last_partitionsp0",
190 "t1#p#last_partition#sp#last_partitionsp1",
191 "t1#p#last_partition#sp#last_partitionsp2");
192 ib_restore_tablespaces("test",
193 "t1#p#first_partition#sp#first_partitionsp0",
194 "t1#p#first_partition#sp#first_partitionsp1",
195 "t1#p#first_partition#sp#first_partitionsp2",
196 "t1#p#last_partition#sp#last_partitionsp0",
197 "t1#p#last_partition#sp#last_partitionsp1",
198 "t1#p#last_partition#sp#last_partitionsp2");
199 } else {
200 ib_discard_tablespaces("test", "t1");
201 ib_restore_tablespaces("test", "t1");
202 }
203 EOF
204
205 ALTER TABLE t1 IMPORT TABLESPACE;
206 CHECK TABLE t1;
207
208 SELECT * FROM t1;
209
210 DROP TABLE t1;
211
212 --echo #
213 --echo # case 3 - with blob objects
214 --echo #
215
216 eval CREATE TABLE t1 (
217 col_1_varbinary VARBINARY (4000),
218 col_2_varchar VARCHAR (4000),
219 col_3_text TEXT (4000),
220 col_4_blob BLOB (4000),
221 col_5_text TEXT (4000),
222 col_6_varchar VARCHAR (4000),
223 col_7_binary BINARY (255)
224 ) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
225 $col7_part;
226
227 INSERT INTO t1 VALUES(
228 REPEAT('a', 4000),REPEAT('o', 4000),REPEAT('a', 4000), REPEAT('o', 4000),
229 REPEAT('a', 4000),REPEAT('a', 4000),REPEAT('a', 255));
230
231 SELECT col_1_varbinary = REPEAT("a", 4000),
232 col_2_varchar = REPEAT("o", 4000),
233 col_3_text = REPEAT("a", 4000),
234 col_4_blob = REPEAT("o", 4000),
235 col_5_text = REPEAT("a", 4000),
236 col_6_varchar = REPEAT("a", 4000),
237 col_7_binary = REPEAT("a", 255)
238 FROM t1;
239 FLUSH TABLES t1 FOR EXPORT;
240
241 perl;
242 require 'include/innodb-util.inc';
243 if ($ENV{'PARTITIONS'})
244 {
245 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
246 } else {
247 ib_backup_tablespaces("test", "t1");
248 }
249 EOF
250
251 UNLOCK TABLES;
252
253 DROP TABLE t1;
254
255 eval CREATE TABLE t1 (
256 col_1_varbinary VARBINARY (4000),
257 col_2_varchar VARCHAR (4000),
258 col_3_text TEXT (4000),
259 col_4_blob BLOB (4000),
260 col_5_text TEXT (4000),
261 col_6_varchar VARCHAR (4000),
262 col_7_binary BINARY (255)
263 ) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
264 $col7_part;
265
266 ALTER TABLE t1 DISCARD TABLESPACE;
267
268 perl;
269 require 'include/innodb-util.inc';
270 if ($ENV{'PARTITIONS'})
271 {
272 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
273 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
274 } else {
275 ib_discard_tablespaces("test", "t1");
276 ib_restore_tablespaces("test", "t1");
277 }
278 EOF
279
280 ALTER TABLE t1 IMPORT TABLESPACE;
281
282 SELECT col_1_varbinary = REPEAT("a", 4000),
283 col_2_varchar = REPEAT("o", 4000),
284 col_3_text = REPEAT("a", 4000),
285 col_4_blob = REPEAT("o", 4000),
286 col_5_text = REPEAT("a", 4000),
287 col_6_varchar = REPEAT("a", 4000),
288 col_7_binary = REPEAT("a", 255)
289 FROM t1;
290
291 DROP TABLE t1;
292
293 --echo #
294 --echo # case 4 - transportable tablespace with auto_increment
295 --echo #
296 eval CREATE TABLE t1 (
297 col_1_int INT AUTO_INCREMENT,
298 col_2_varchar VARCHAR (20),
299 PRIMARY KEY (col_1_int)) ENGINE = InnoDB
300 $col1_part;
301
302 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3');
303 INSERT INTO t1 (col_2_varchar) VALUES ('a4'),('a5'),('a6');
304
305 SELECT * FROM t1 ORDER BY col_1_int;
306 FLUSH TABLES t1 FOR EXPORT;
307
308 perl;
309 require 'include/innodb-util.inc';
310 if ($ENV{'PARTITIONS'})
311 {
312 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
313 } else {
314 ib_backup_tablespaces("test", "t1");
315 }
316 EOF
317
318 UNLOCK TABLES;
319
320 DROP TABLE t1;
321
322 eval CREATE TABLE t1 (
323 col_1_int INT AUTO_INCREMENT,
324 col_2_varchar VARCHAR (20),
325 PRIMARY KEY (col_1_int)) ENGINE = InnoDB
326 $col1_part;
327 ALTER TABLE t1 DISCARD TABLESPACE;
328
329
330 perl;
331 require 'include/innodb-util.inc';
332 if ($ENV{'PARTITIONS'})
333 {
334 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
335 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
336 } else {
337 ib_discard_tablespaces("test", "t1");
338 ib_restore_tablespaces("test", "t1");
339 }
340 EOF
341
342 ALTER TABLE t1 IMPORT TABLESPACE;
343 SELECT * FROM t1 ORDER BY col_1_int;
344
345 # error on inserting duplicate value
346 --error ER_DUP_ENTRY
347 INSERT INTO t1 VALUES (1,'a1');
348 # insert new values
349 INSERT INTO t1(col_2_varchar) VALUES ('a101'),('a102'),('a103');
350 SELECT * FROM t1 ORDER BY col_1_int;
351 # check table can be altered
352 ALTER TABLE t1 MODIFY col_1_int BIGINT;
353 SELECT * FROM t1 ORDER BY col_1_int;
354
355 DROP TABLE t1;
356
357 --echo #
358 --echo # case 5 - check with primary and foreign key
359 --echo #
360 if (!$partitioning)
361 {
362 CREATE TABLE t1 (
363 col_1_int INT,col_2_varchar VARCHAR (20),
364 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB;
365
366 CREATE TABLE t1_fk (
367 col_1_int INT,col_2_varchar VARCHAR (20),
368 PRIMARY KEY (col_1_int),
369 FOREIGN KEY (col_2_varchar) REFERENCES t1(col_2_varchar)
370 ) ENGINE = InnoDB;
371
372 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
373 INSERT INTO t1_fk VALUES (1,'a1'),(2,'a2'),(3,'a3');
374
375 SELECT * FROM t1;
376 SELECT * FROM t1_fk;
377 FLUSH TABLES t1,t1_fk FOR EXPORT;
378
379 perl;
380 require 'include/innodb-util.inc';
381 ib_backup_tablespaces("test", "t1");
382 ib_backup_tablespaces("test", "t1_fk");
383 EOF
384
385 UNLOCK TABLES;
386
387 DROP TABLE t1_fk,t1;
388
389 CREATE TABLE t1 (
390 col_1_int INT,col_2_varchar VARCHAR (20),
391 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB;
392
393 CREATE TABLE t1_fk (
394 col_1_int INT,col_2_varchar VARCHAR (20),
395 PRIMARY KEY (col_1_int),
396 FOREIGN KEY (col_2_varchar) REFERENCES t1(col_2_varchar)
397 ) ENGINE = InnoDB;
398
399 --echo # Alter table discard table is not allowed with foreign_key_checks = 1
400 SET foreign_key_checks = 0;
401 ALTER TABLE t1_fk DISCARD TABLESPACE;
402 ALTER TABLE t1 DISCARD TABLESPACE;
403 SET foreign_key_checks = 1;
404
405 perl;
406 require 'include/innodb-util.inc';
407 ib_discard_tablespaces("test", "t1");
408 ib_discard_tablespaces("test", "t1_fk");
409 ib_restore_tablespaces("test", "t1");
410 ib_restore_tablespaces("test", "t1_fk");
411 EOF
412
413 ALTER TABLE t1 IMPORT TABLESPACE;
414 ALTER TABLE t1_fk IMPORT TABLESPACE;
415 SELECT * FROM t1;
416 SELECT * FROM t1_fk;
417
418 --echo # Enter Invalid value: PK-FK relationship violation
419 --error ER_NO_REFERENCED_ROW_2
420 INSERT INTO t1_fk VALUES (100,'a100');
421
422 SET AUTOCOMMIT = 0;
423 INSERT INTO t1_fk VALUES (4,'a4'),(5,'a5');
424 ROLLBACK;
425 SELECT * FROM t1_fk;
426
427 DROP TABLE t1_fk,t1;
428 SET AUTOCOMMIT = 1;
429 --remove_file $MYSQLTEST_VARDIR/tmp/t1_fk.cfg
430 --remove_file $MYSQLTEST_VARDIR/tmp/t1_fk.ibd
431 }
432 if ($partitioning)
433 {
434 --echo # Foreign Keys not supported by partitioned tables.
435 }
436
437 --echo #
438 --echo # case 6 - transportable tablespace with transactions
439 --echo #
440 eval CREATE TABLE t1 (
441 col_1_int int,col_2_varchar VARCHAR (20),
442 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
443 $col2_part;
444 SET AUTOCOMMIT = 0;
445 INSERT INTO t1 VALUES (1,'a1'),(2,'a2');
446 SELECT * FROM t1;
447 COMMIT;
448 INSERT INTO t1 VALUES (3,'a3'),(4,'a4');
449 ROLLBACK;
450 INSERT INTO t1 VALUES (5,'a5'),(6,'a6');
451 COMMIT;
452 SELECT * FROM t1 ORDER BY col_1_int;
453 FLUSH TABLES t1 FOR EXPORT;
454
455 perl;
456 require 'include/innodb-util.inc';
457 if ($ENV{'PARTITIONS'})
458 {
459 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
460 } else {
461 ib_backup_tablespaces("test", "t1");
462 }
463 EOF
464
465 UNLOCK TABLES;
466
467 DROP TABLE t1;
468 eval CREATE TABLE t1 (
469 col_1_int int,col_2_varchar VARCHAR (20),
470 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
471 $col2_part;
472 ALTER TABLE t1 DISCARD TABLESPACE;
473 SET AUTOCOMMIT = 0;
474
475 perl;
476 require 'include/innodb-util.inc';
477 if ($ENV{'PARTITIONS'})
478 {
479 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
480 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
481 } else {
482 ib_discard_tablespaces("test", "t1");
483 ib_restore_tablespaces("test", "t1");
484 }
485 EOF
486
487 ALTER TABLE t1 IMPORT TABLESPACE;
488
489 INSERT INTO t1 VALUES (7,'a7'),(8,'a8');
490 COMMIT;
491 INSERT INTO t1 VALUES (9,'a9'),(10,'a10');
492 ROLLBACK;
493 INSERT INTO t1 VALUES (11,'a11'),(12,'a12');
494 COMMIT;
495 SELECT * FROM t1 ORDER BY col_1_int;
496 SET AUTOCOMMIT = 1;
497
498 DROP TABLE t1;
499
500 --echo #
501 --echo # case 7 - Transportable tablespace with transaction
502 --echo # (earlier failed with jet)
503 --echo #
504
505 eval CREATE TABLE t1 (i int) ENGINE = InnoDB
506 $i_part;
507 INSERT INTO t1 VALUES (100),(200),(300);
508 SELECT * FROM t1 ORDER BY i;
509 FLUSH TABLES t1 FOR EXPORT;
510
511 perl;
512 require 'include/innodb-util.inc';
513 if ($ENV{'PARTITIONS'})
514 {
515 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
516 } else {
517 ib_backup_tablespaces("test", "t1");
518 }
519 EOF
520
521 UNLOCK TABLES;
522
523 DROP TABLE t1;
524 eval CREATE TABLE t1 (i int) ENGINE = InnoDB
525 $i_part;
526 ALTER TABLE t1 DISCARD TABLESPACE;
527
528
529 perl;
530 require 'include/innodb-util.inc';
531 if ($ENV{'PARTITIONS'})
532 {
533 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
534 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
535 } else {
536 ib_discard_tablespaces("test", "t1");
537 ib_restore_tablespaces("test", "t1");
538 }
539 EOF
540
541 ALTER TABLE t1 IMPORT TABLESPACE;
542 SELECT * FROM t1 ORDER BY i;
543 SET AUTOCOMMIT = 0;
544 INSERT INTO t1 VALUES (101),(102),(103);
545 COMMIT;
546 SELECT * FROM t1 ORDER BY i;
547 SET AUTOCOMMIT = 1;
548 DROP TABLE t1;
549
550 --echo #
551 --echo # case 8 - negative cases
552 --echo #
553 eval CREATE TABLE t1 (i int) ENGINE = InnoDB
554 $i_part;
555 INSERT INTO t1 VALUES (100),(200),(300);
556 SELECT * FROM t1 ORDER BY i;
557 FLUSH TABLES t1 FOR EXPORT;
558
559 perl;
560 require 'include/innodb-util.inc';
561 if ($ENV{'PARTITIONS'})
562 {
563 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
564 } else {
565 ib_backup_tablespaces("test", "t1");
566 }
567 EOF
568
569 UNLOCK TABLES;
570
571 --echo # try if we can flush again
572 FLUSH TABLES t1 FOR EXPORT;
573 UNLOCK TABLES;
574
575 DROP TABLE t1;
576
577 --echo # create table with incorrect schema
578 eval CREATE TABLE t1 (i bigint) ENGINE = InnoDB
579 $i_part;
580 ALTER TABLE t1 DISCARD TABLESPACE;
581
582 perl;
583 require 'include/innodb-util.inc';
584 if ($ENV{'PARTITIONS'})
585 {
586 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
587 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
588 } else {
589 ib_discard_tablespaces("test", "t1");
590 ib_restore_tablespaces("test", "t1");
591 }
592 EOF
593
594 --echo # error as mismatch in column data type
595 --error ER_TABLE_SCHEMA_MISMATCH
596 ALTER TABLE t1 IMPORT TABLESPACE;
597
598 --echo # explicitly delete ibd file before creating table with correct schema
599 perl;
600 require 'include/innodb-util.inc';
601 if ($ENV{'PARTITIONS'})
602 {
603 ib_unlink_tablespace("test", "t1#p#p0");
604 ib_unlink_tablespace("test", "t1#p#p1");
605 ib_unlink_tablespace("test", "t1#p#p2");
606 } else {
607 ib_unlink_tablespace("test", "t1");
608 }
609 EOF
610
611 DROP TABLE t1;
612
613 eval CREATE TABLE t1 (i int) ENGINE = InnoDB
614 $i_part;
615 ALTER TABLE t1 DISCARD TABLESPACE;
616
617 perl;
618 require 'include/innodb-util.inc';
619 if ($ENV{'PARTITIONS'})
620 {
621 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
622 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
623 } else {
624 ib_discard_tablespaces("test", "t1");
625 ib_restore_tablespaces("test", "t1");
626 }
627 EOF
628
629 --echo # Import should succeed
630 ALTER TABLE t1 IMPORT TABLESPACE;
631 --echo # Try to import twice
632 --replace_regex /#p#/#P#/ /#sp#/#SP#/
633 --error ER_TABLESPACE_EXISTS
634 ALTER TABLE t1 IMPORT TABLESPACE;
635 SELECT * FROM t1 ORDER BY i;
636
637 SET AUTOCOMMIT = 0;
638 INSERT INTO t1 VALUES (101),(102),(103);
639 COMMIT;
640 SELECT * FROM t1 ORDER BY i;
641 DROP TABLE t1;
642 eval CREATE TABLE t1 (i int) ENGINE = InnoDB
643 $i_part;
644 ALTER TABLE t1 DISCARD TABLESPACE;
645 --echo # do not delete ibd file and try to import
646 perl;
647 require 'include/innodb-util.inc';
648 if ($ENV{'PARTITIONS'})
649 {
650 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
651 } else {
652 ib_restore_tablespaces("test", "t1");
653 }
654 EOF
655 ALTER TABLE t1 IMPORT TABLESPACE;
656 DROP TABLE t1;
657 SET AUTOCOMMIT = 1;
658
659 --echo #
660 --echo # case 9 - empty table import
661 --echo #
662 eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
663 $c1_part;
664 SET AUTOCOMMIT = 0;
665 INSERT INTO t1 VALUES (1),(123),(331);
666 SELECT c1 FROM t1;
667 ROLLBACK;
668 FLUSH TABLES t1 FOR EXPORT;
669
670 perl;
671 require 'include/innodb-util.inc';
672 if ($ENV{'PARTITIONS'})
673 {
674 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
675 } else {
676 ib_backup_tablespaces("test", "t1");
677 }
678 EOF
679
680 UNLOCK TABLES;
681
682 DROP TABLE t1;
683 eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
684 $c1_part;
685 ALTER TABLE t1 DISCARD TABLESPACE;
686
687
688 perl;
689 require 'include/innodb-util.inc';
690 if ($ENV{'PARTITIONS'})
691 {
692 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
693 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
694 } else {
695 ib_discard_tablespaces("test", "t1");
696 ib_restore_tablespaces("test", "t1");
697 }
698 EOF
699
700 ALTER TABLE t1 IMPORT TABLESPACE;
701 SELECT * FROM t1 ORDER BY c1;
702 DROP TABLE t1;
703 SET AUTOCOMMIT = 1;
704
705 --echo #
706 --echo # case 10 - Transportable tablespaces with prefix index
707 --echo #
708
709 eval CREATE TABLE t1 (col_1 CHAR (255),
710 col_2 VARCHAR (255), col_3 VARCHAR (255),
711 col_4 VARCHAR (255),col_5 VARCHAR (255),
712 col_6 text (255), col_7 text (255),
713 col_8 text (255),col_9 text (255),
714 col_10 BLOB (255),col_11 BLOB (255),
715 col_12 BLOB (255), col_13 BLOB (255),
716 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
717 $col1_key_part;
718
719 CREATE INDEX prefix_idx ON t1(
720 col_1 (50),col_2 (50),col_3 (50),
721 col_4 (50),col_5 (50),col_6 (50),
722 col_7 (50),col_8 (50),col_9 (50),
723 col_10 (50),col_11 (50),col_12 (50),
724 col_13(50));
725
726 INSERT INTO t1 VALUES (
727 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
728 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
729 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
730 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
731 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
732
733 SELECT
734 col_1 = REPEAT("col1_00001",10),
735 col_2 = REPEAT("col2_00001",10),
736 col_3 = REPEAT("col3_00001",10),
737 col_4 = REPEAT("col4_00001",10),
738 col_5 = REPEAT("col5_00001",10),
739 col_6 = REPEAT("col6_00001",10),
740 col_7 = REPEAT("col7_00001",10),
741 col_8 = REPEAT("col8_00001",10),
742 col_9 = REPEAT("col9_00001",10),
743 col_10 = REPEAT("col10_00001",10),
744 col_11 = REPEAT("col11_00001",10),
745 col_12 = REPEAT("col12_00001",10),
746 col_13 = REPEAT("col13_00001",10),
747 col_14 = REPEAT("col14_00001",10),
748 col_15
749 FROM t1;
750
751 FLUSH TABLES t1 FOR EXPORT;
752
753 perl;
754 require 'include/innodb-util.inc';
755 if ($ENV{'PARTITIONS'})
756 {
757 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
758 } else {
759 ib_backup_tablespaces("test", "t1");
760 }
761 EOF
762
763 UNLOCK TABLES;
764
765 DROP TABLE t1;
766
767 eval CREATE TABLE t1 (col_1 CHAR (255),
768 col_2 VARCHAR (255), col_3 VARCHAR (255),
769 col_4 VARCHAR (255),col_5 VARCHAR (255),
770 col_6 text (255), col_7 text (255),
771 col_8 text (255),col_9 text (255),
772 col_10 BLOB (255),col_11 BLOB (255),
773 col_12 BLOB (255), col_13 BLOB (255),
774 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
775 $col1_key_part;
776
777 CREATE INDEX prefix_idx ON t1(
778 col_1 (50),col_2 (50),col_3 (50),
779 col_4 (50),col_5 (50),col_6 (50),
780 col_7 (50),col_8 (50),col_9 (50),
781 col_10 (50),col_11 (50),col_12 (50),
782 col_13(50));
783
784 ALTER TABLE t1 DISCARD TABLESPACE;
785
786 perl;
787 require 'include/innodb-util.inc';
788 if ($ENV{'PARTITIONS'})
789 {
790 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
791 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
792 } else {
793 ib_discard_tablespaces("test", "t1");
794 ib_restore_tablespaces("test", "t1");
795 }
796 EOF
797
798 ALTER TABLE t1 IMPORT TABLESPACE;
799
800 SELECT
801 col_1 = REPEAT("col1_00001",10),
802 col_2 = REPEAT("col2_00001",10),
803 col_3 = REPEAT("col3_00001",10),
804 col_4 = REPEAT("col4_00001",10),
805 col_5 = REPEAT("col5_00001",10),
806 col_6 = REPEAT("col6_00001",10),
807 col_7 = REPEAT("col7_00001",10),
808 col_8 = REPEAT("col8_00001",10),
809 col_9 = REPEAT("col9_00001",10),
810 col_10 = REPEAT("col10_00001",10),
811 col_11 = REPEAT("col11_00001",10),
812 col_12 = REPEAT("col12_00001",10),
813 col_13 = REPEAT("col13_00001",10),
814 col_14 = REPEAT("col14_00001",10),
815 col_15
816 FROM t1;
817
818 DROP TABLE t1;
819
820 --echo #
821 --echo # case 11 - Transportable tablespaces with secondary index
822 --echo #
823
824 eval CREATE TABLE t1 (col_1 CHAR (255),
825 col_2 VARCHAR (255), col_3 VARCHAR (255),
826 col_4 VARCHAR (255),col_5 VARCHAR (255),
827 col_6 text (255), col_7 text (255),
828 col_8 text (255),col_9 text (255),
829 col_10 BLOB (255),col_11 BLOB (255),
830 col_12 BLOB (255), col_13 BLOB (255),
831 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
832 $col1_key_part;
833
834 CREATE INDEX idx1 ON t1(col_1);
835 CREATE INDEX idx2 ON t1(col_2);
836 CREATE INDEX idx3 ON t1(col_3);
837 CREATE INDEX idx4 ON t1(col_4);
838 CREATE INDEX idx5 ON t1(col_5);
839 CREATE INDEX idx6 ON t1(col_6(255));
840 CREATE INDEX idx7 ON t1(col_7(255));
841 CREATE INDEX idx8 ON t1(col_8(255));
842 CREATE INDEX idx9 ON t1(col_9(255));
843 CREATE INDEX idx10 ON t1(col_10(255));
844 CREATE INDEX idx11 ON t1(col_11(255));
845 CREATE INDEX idx12 ON t1(col_12(255));
846 CREATE INDEX idx13 ON t1(col_13(255));
847 CREATE INDEX idx14 ON t1(col_14(255));
848
849 INSERT INTO t1 VALUES (
850 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
851 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
852 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
853 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
854 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
855
856 SELECT
857 col_1 = REPEAT("col1_00001",10),
858 col_2 = REPEAT("col2_00001",10),
859 col_3 = REPEAT("col3_00001",10),
860 col_4 = REPEAT("col4_00001",10),
861 col_5 = REPEAT("col5_00001",10),
862 col_6 = REPEAT("col6_00001",10),
863 col_7 = REPEAT("col7_00001",10),
864 col_8 = REPEAT("col8_00001",10),
865 col_9 = REPEAT("col9_00001",10),
866 col_10 = REPEAT("col10_00001",10),
867 col_11 = REPEAT("col11_00001",10),
868 col_12 = REPEAT("col12_00001",10),
869 col_13 = REPEAT("col13_00001",10),
870 col_14 = REPEAT("col14_00001",10),
871 col_15
872 FROM t1;
873
874 FLUSH TABLES t1 FOR EXPORT;
875
876 perl;
877 require 'include/innodb-util.inc';
878 if ($ENV{'PARTITIONS'})
879 {
880 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
881 } else {
882 ib_backup_tablespaces("test", "t1");
883 }
884 EOF
885
886 UNLOCK TABLES;
887
888 DROP TABLE t1;
889
890 eval CREATE TABLE t1 (col_1 CHAR (255),
891 col_2 VARCHAR (255), col_3 VARCHAR (255),
892 col_4 VARCHAR (255),col_5 VARCHAR (255),
893 col_6 text (255), col_7 text (255),
894 col_8 text (255),col_9 text (255),
895 col_10 BLOB (255),col_11 BLOB (255),
896 col_12 BLOB (255), col_13 BLOB (255),
897 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
898 $col1_key_part;
899
900 CREATE INDEX idx1 ON t1(col_1);
901 CREATE INDEX idx2 ON t1(col_2);
902 CREATE INDEX idx3 ON t1(col_3);
903 CREATE INDEX idx4 ON t1(col_4);
904 CREATE INDEX idx5 ON t1(col_5);
905 CREATE INDEX idx6 ON t1(col_6(255));
906 CREATE INDEX idx7 ON t1(col_7(255));
907 CREATE INDEX idx8 ON t1(col_8(255));
908 CREATE INDEX idx9 ON t1(col_9(255));
909 CREATE INDEX idx10 ON t1(col_10(255));
910 CREATE INDEX idx11 ON t1(col_11(255));
911 CREATE INDEX idx12 ON t1(col_12(255));
912 CREATE INDEX idx13 ON t1(col_13(255));
913 CREATE INDEX idx14 ON t1(col_14(255));
914
915 ALTER TABLE t1 DISCARD TABLESPACE;
916
917
918 perl;
919 require 'include/innodb-util.inc';
920 if ($ENV{'PARTITIONS'})
921 {
922 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
923 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
924 } else {
925 ib_discard_tablespaces("test", "t1");
926 ib_restore_tablespaces("test", "t1");
927 }
928 EOF
929
930 ALTER TABLE t1 IMPORT TABLESPACE;
931
932 SELECT
933 col_1 = REPEAT("col1_00001",10),
934 col_2 = REPEAT("col2_00001",10),
935 col_3 = REPEAT("col3_00001",10),
936 col_4 = REPEAT("col4_00001",10),
937 col_5 = REPEAT("col5_00001",10),
938 col_6 = REPEAT("col6_00001",10),
939 col_7 = REPEAT("col7_00001",10),
940 col_8 = REPEAT("col8_00001",10),
941 col_9 = REPEAT("col9_00001",10),
942 col_10 = REPEAT("col10_00001",10),
943 col_11 = REPEAT("col11_00001",10),
944 col_12 = REPEAT("col12_00001",10),
945 col_13 = REPEAT("col13_00001",10),
946 col_14 = REPEAT("col14_00001",10),
947 col_15
948 FROM t1;
949
950 --echo # perform transaction on imported table
951 SET AUTOCOMMIT = 0;
952 INSERT INTO t1(col_15) VALUES (15000),(16000);
953 SELECT col_15 FROM t1 WHERE col_15 > 11000;
954 ROLLBACK;
955 SELECT col_15 FROM t1 WHERE col_15 > 11000;
956 INSERT INTO t1(col_15) VALUES (15000),(16000);
957 COMMIT;
958 SELECT col_15 FROM t1 WHERE col_15 > 11000;
959 --echo # Perform DDL operations on imported table.
960 ALTER TABLE t1 DROP INDEX idx1;
961 ALTER TABLE t1 DROP INDEX idx6;
962 ALTER TABLE t1 DROP INDEX idx10;
963
964 SELECT
965 col_1 = REPEAT("col1_00001",10),
966 col_2 = REPEAT("col2_00001",10),
967 col_3 = REPEAT("col3_00001",10),
968 col_4 = REPEAT("col4_00001",10),
969 col_5 = REPEAT("col5_00001",10),
970 col_6 = REPEAT("col6_00001",10),
971 col_7 = REPEAT("col7_00001",10),
972 col_8 = REPEAT("col8_00001",10),
973 col_9 = REPEAT("col9_00001",10),
974 col_10 = REPEAT("col10_00001",10),
975 col_11 = REPEAT("col11_00001",10),
976 col_12 = REPEAT("col12_00001",10),
977 col_13 = REPEAT("col13_00001",10),
978 col_14 = REPEAT("col14_00001",10),
979 col_15
980 FROM t1;
981
982 ALTER TABLE t1 ADD INDEX idx1 (col_1);
983 ALTER TABLE t1 ADD INDEX idx6 (col_1(255));
984 ALTER TABLE t1 ADD INDEX idx10 (col_10(255));
985
986
987 SELECT
988 col_1 = REPEAT("col1_00001",10),
989 col_2 = REPEAT("col2_00001",10),
990 col_3 = REPEAT("col3_00001",10),
991 col_4 = REPEAT("col4_00001",10),
992 col_5 = REPEAT("col5_00001",10),
993 col_6 = REPEAT("col6_00001",10),
994 col_7 = REPEAT("col7_00001",10),
995 col_8 = REPEAT("col8_00001",10),
996 col_9 = REPEAT("col9_00001",10),
997 col_10 = REPEAT("col10_00001",10),
998 col_11 = REPEAT("col11_00001",10),
999 col_12 = REPEAT("col12_00001",10),
1000 col_13 = REPEAT("col13_00001",10),
1001 col_14 = REPEAT("col14_00001",10),
1002 col_15
1003 FROM t1;
1004
1005 DROP TABLE t1;
1006 SET AUTOCOMMIT = 1;
1007
1008 --echo #
1009 --echo # case 12 - Transportable tablespaces with trigger / view
1010 --echo #
1011
1012 eval CREATE TABLE t1(col1 bit(1),
1013 col2 boolean,col3 tinyint, col4 smallint,
1014 col5 mediumint,col6 int, col7 bigint,
1015 col8 float (14,3),col9 double (14,3),
1016 col10 VARCHAR(20) CHARACTER SET utf8,
1017 col11 TEXT CHARACTER SET binary,
1018 col12 ENUM('a','b','c') CHARACTER SET binary,
1019 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
1020 col14 CHAR(20), col15 VARBINARY (400),
1021 col16 BINARY(40), col17 BLOB (400),
1022 col18 int not null primary key,
1023 col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
1024 col22 TIME, col23 YEAR) ENGINE = InnoDB
1025 $col18_part;
1026
1027 --echo # table for trigger action
1028 CREATE TABLE trigger_table (i int) ENGINE = InnoDB;
1029 # define trigger
1030 CREATE TRIGGER tri AFTER INSERT ON t1
1031 FOR EACH ROW INSERT INTO trigger_table VALUES(NEW.col18);
1032 # define view
1033 CREATE OR REPLACE VIEW VW1 AS SELECT * FROM t1;
1034
1035 CREATE INDEX idx1 ON t1(col18);
1036 CREATE INDEX prefix_idx ON t1(col14 (10));
1037 if (!$partitioning)
1038 {
1039 CREATE UNIQUE INDEX idx2 ON t1(col12);
1040 CREATE UNIQUE INDEX idx3 ON t1(col8);
1041 }
1042
1043
1044 INSERT INTO t1 VALUES (
1045 1,1,-128,32767,-8388608,2147483647,-9223372036854775808,92233720368.222,
1046 -92233720368.222,'aaa','aaaaaaaaaa','b','bbbbb','ccccc',REPEAT('d',40),
1047 REPEAT('d',40),REPEAT('d',40),1,'1000-01-01','3000-12-31 23:59:59.99',
1048 '1990-01-01 00:00:01.00','01:59:59.00','1901');
1049
1050 INSERT INTO t1 VALUES (
1051 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1052 NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
1053
1054 --error ER_BAD_NULL_ERROR
1055 INSERT INTO t1 VALUES (
1056 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1057 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
1058
1059 --error ER_DUP_ENTRY
1060 INSERT INTO t1 VALUES (
1061 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
1062 NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
1063
1064 SELECT * FROM t1;
1065 SELECT * FROM trigger_table;
1066 SELECT * FROM VW1;
1067 FLUSH TABLES t1 FOR EXPORT;
1068
1069 perl;
1070 require 'include/innodb-util.inc';
1071 if ($ENV{'PARTITIONS'})
1072 {
1073 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1074 } else {
1075 ib_backup_tablespaces("test", "t1");
1076 }
1077 EOF
1078
1079 UNLOCK TABLES;
1080
1081 --echo # trigger is also dropped when table is dropped
1082 DROP TABLE t1;
1083
1084 eval CREATE TABLE t1(col1 bit(1),
1085 col2 boolean,col3 tinyint, col4 smallint,
1086 col5 mediumint,col6 int, col7 bigint,
1087 col8 float (14,3),col9 double (14,3),
1088 col10 VARCHAR(20) CHARACTER SET utf8,
1089 col11 TEXT CHARACTER SET binary,
1090 col12 ENUM('a','b','c') CHARACTER SET binary,
1091 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
1092 col14 CHAR(20), col15 VARBINARY (400),
1093 col16 BINARY(40), col17 BLOB (400),
1094 col18 int not null primary key,
1095 col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
1096 col22 TIME, col23 YEAR) ENGINE = InnoDB
1097 $col18_part;
1098
1099 CREATE INDEX idx1 ON t1(col18);
1100 CREATE INDEX prefix_idx ON t1(col14 (10));
1101 if (!$partitioning)
1102 {
1103 CREATE UNIQUE INDEX idx2 ON t1(col12);
1104 CREATE UNIQUE INDEX idx3 ON t1(col8);
1105 }
1106
1107 ALTER TABLE t1 DISCARD TABLESPACE;
1108
1109 perl;
1110 require 'include/innodb-util.inc';
1111 if ($ENV{'PARTITIONS'})
1112 {
1113 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1114 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1115 } else {
1116 ib_discard_tablespaces("test", "t1");
1117 ib_restore_tablespaces("test", "t1");
1118 }
1119 EOF
1120
1121 ALTER TABLE t1 IMPORT TABLESPACE;
1122
1123 SELECT * FROM t1;
1124 SELECT * FROM trigger_table;
1125 SELECT * FROM VW1;
1126
1127 # trigger table is not updated as trigger got dropped
1128 INSERT INTO t1(col18) VALUES (5);
1129 # validate data in table not updated
1130 SELECT * FROM trigger_table;
1131
1132 UPDATE t1 SET col18=10 WHERE col18=1;
1133
1134 # view shows updated data
1135 SELECT * FROM VW1;
1136
1137 SELECT * FROM t1 WHERE col18=10;
1138 ALTER TABLE t1 ADD COLUMN col24 varbinary(40) default null;
1139 INSERT INTO t1(col18,col24) VALUES (6,REPEAT('a',10));
1140 SELECT col24,col18 FROM t1 WHERE col18 in (6,1,10) ORDER BY col18;
1141 ALTER TABLE t1 DROP INDEX prefix_idx;
1142 SELECT col18,col14 FROM t1 WHERE col14 like '_ccc%';
1143 ALTER TABLE t1 ADD INDEX prefix_idx (col24(10));
1144 SELECT col18,col24 FROM t1 WHERE col24 like '_a_a%';
1145
1146 DROP VIEW VW1;
1147 DROP TABLE t1;
1148 DROP TABLE trigger_table;
1149
1150
1151 --echo #
1152 --echo # case 13 Simple export, discard and import with instant ADD COLUMN.
1153 --echo #
1154 eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
1155 $c1_part;
1156 INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
1157 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
1158 SELECT c1 FROM t1;
1159 FLUSH TABLES t1 FOR EXPORT;
1160 SELECT * FROM t1 ORDER BY c1;
1161
1162 perl;
1163 require 'include/innodb-util.inc';
1164 if ($ENV{'PARTITIONS'})
1165 {
1166 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1167 } else {
1168 ib_backup_tablespaces("test", "t1");
1169 }
1170 EOF
1171
1172 UNLOCK TABLES;
1173
1174 DROP TABLE t1;
1175 eval CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
1176 $c1_part;
1177 ALTER TABLE t1 DISCARD TABLESPACE;
1178
1179
1180 perl;
1181 require 'include/innodb-util.inc';
1182 if ($ENV{'PARTITIONS'})
1183 {
1184 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1185 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1186 } else {
1187 ib_discard_tablespaces("test", "t1");
1188 ib_restore_tablespaces("test", "t1");
1189 }
1190 EOF
1191
1192 ALTER TABLE t1 IMPORT TABLESPACE;
1193 CHECK TABLE t1;
1194 SELECT * FROM t1 ORDER BY c1;
1195
1196 #SELECT name, instant_cols FROM information_schema.innodb_tables WHERE instant_cols > 0;
1197 #SELECT name, has_default FROM information_schema.innodb_columns WHERE has_default = 1;
1198
1199 DROP TABLE t1;
1200
1201
1202
1203 --echo #
1204 --echo # case 14 Simple export, discard and import with instant ADD COLUMN.
1205 --echo # However, this time the .cfg file would be missing
1206 --echo #
1207
1208 eval CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
1209 $c1_part;
1210 INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
1211 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
1212 SELECT c1 FROM t1;
1213 FLUSH TABLES t1 FOR EXPORT;
1214 SELECT * FROM t1 ORDER BY c1;
1215
1216 perl;
1217 require 'include/innodb-util.inc';
1218 if ($ENV{'PARTITIONS'})
1219 {
1220 ib_backup_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1221 } else {
1222 ib_backup_tablespaces("test", "t1");
1223 }
1224 EOF
1225
1226 UNLOCK TABLES;
1227
1228 DROP TABLE t1;
1229 eval CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
1230 $c1_part;
1231 ALTER TABLE t1 DISCARD TABLESPACE;
1232
1233
1234 perl;
1235 require 'include/innodb-util.inc';
1236 if ($ENV{'PARTITIONS'})
1237 {
1238 ib_discard_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1239 ib_restore_tablespaces("test", "t1#p#p0", "t1#p#p1", "t1#p#p2");
1240 } else {
1241 ib_discard_tablespaces("test", "t1");
1242 ib_restore_tablespaces("test", "t1");
1243 }
1244 EOF
1245
1246 --echo # remove the .cfg files
1247 --remove_files_wildcard $MYSQLD_DATADIR/test t1*.cfg
1248
1249 --error ER_TABLE_SCHEMA_MISMATCH
1250 ALTER TABLE t1 IMPORT TABLESPACE;
1251
1252 DROP TABLE t1;
1253
1254 --remove_files_wildcard $MYSQLD_DATADIR/test t1*.ibd
1255
1256 --disable_query_log
1257 call mtr.add_suppression("Got error -1 when reading table '.*'");
1258 call mtr.add_suppression("Error: tablespace id and flags in file '.*'.*");
1259 call mtr.add_suppression("The table .* doesn't have a corresponding tablespace, it was discarded");
1260 call mtr.add_suppression("Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT.");
1261 call mtr.add_suppression("Record overlaps another");
1262 call mtr.add_suppression("Apparent corruption in space .* page .* index `GEN_CLUST_INDEX`");
1263 call mtr.add_suppression("In page .* of index `GEN_CLUST_INDEX` of table");
1264 --enable_query_log
1265
1266 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.ibd
1267 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.cfg
1268
1269 SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table;