Debian Codegraph @

mysql-test/suite/innodb/r/transportable_tbsp-1.result

raw
1 SET @old_innodb_file_per_table = @@innodb_file_per_table;
2 SET GLOBAL innodb_file_per_table = 1;
3 SELECT @@innodb_file_per_table;
4 @@innodb_file_per_table
5 1
6 # Following testcases are created from JET cases (where import
7 # export instance are different servers)
8 # Here test will be run on same import and export instance.
9 #
10 # case 1 Simple export, discard and import.
11 #
12 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
13 ;
14 INSERT INTO t1 VALUES (1),(123),(331);
15 SELECT c1 FROM t1;
16 c1
17 1
18 123
19 331
20 FLUSH TABLES t1 FOR EXPORT;
21 SELECT * FROM t1 ORDER BY c1;
22 c1
23 1
24 123
25 331
26 backup: t1
27 UNLOCK TABLES;
28 DROP TABLE t1;
29 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
30 ;
31 ALTER TABLE t1 DISCARD TABLESPACE;
32 restore: t1 .ibd and .cfg files
33 ALTER TABLE t1 IMPORT TABLESPACE;
34 SELECT * FROM t1 ORDER BY c1;
35 c1
36 1
37 123
38 331
39 DROP TABLE t1;
40 #
41 # case 2 With indexes and more datatypes.
42 #
43 CREATE TABLE t1 (
44 col1 BIT(1),
45 col2 BOOLEAN,
46 col3 TINYINT,
47 col4 SMALLINT,
48 col5 MEDIUMINT,
49 col6 INT,
50 col7 BIGINT,
51 col8 FLOAT (14,3),
52 col9 DOUBLE (14,3),
53 col10 VARCHAR(20),
54 col11 TEXT,
55 col12 ENUM('a','b','c'),
56 col13 TEXT,
57 col14 CHAR(20),
58 col15 VARBINARY (400),
59 col16 BINARY(40),
60 col17 BLOB (400),
61 col18 INT NOT NULL PRIMARY KEY,
62 col19 DATE,
63 col20 DATETIME,
64 col21 TIMESTAMP NULL,
65 col22 TIME,
66 col23 YEAR) ENGINE = InnoDB
67 ;
68 Warnings:
69 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
70 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
71 CREATE INDEX idx1 ON t1(col18);
72 CREATE INDEX prefix_idx ON t1(col14 (10));
73 CREATE UNIQUE INDEX idx2 ON t1(col12);
74 CREATE UNIQUE INDEX idx3 ON t1(col8);
75 INSERT INTO t1 VALUES
76 (1,1,-128,32767,-8388608,2147483647,-9223372036854775808, 92233720368.222,
77 -92233720368.222,'aaa', + 'aaaaaaaaaa','b','bbbbb','ccccc',
78 REPEAT('d',40),REPEAT('d',40),REPEAT('d',40),1,'1000-01-01',
79 '3000-12-31 23:59:59.99','1990-01-01 00:00:01.00',
80 '01:59:59.00','1901');
81 INSERT INTO t1 VALUES
82 (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
83 NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
84 INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
85 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
86 ERROR 23000: Column 'col18' cannot be null
87 INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
88 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
89 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
90 FLUSH TABLES t1 WITH READ LOCK;
91 SELECT * FROM t1;
92 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
93  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
94 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
95 backup: t1
96 UNLOCK TABLES;
97 DROP TABLE t1;
98 CREATE TABLE t1 (
99 col1 BIT(1),
100 col2 BOOLEAN,
101 col3 TINYINT,
102 col4 SMALLINT,
103 col5 MEDIUMINT,
104 col6 INT,
105 col7 BIGINT,
106 col8 FLOAT (14,3),
107 col9 DOUBLE (14,3),
108 col10 VARCHAR(20),
109 col11 TEXT,
110 col12 ENUM('a','b','c'),
111 col13 TEXT,
112 col14 CHAR(20),
113 col15 VARBINARY (400),
114 col16 BINARY(40),
115 col17 BLOB (400),
116 col18 INT NOT NULL PRIMARY KEY,
117 col19 DATE,
118 col20 DATETIME,
119 col21 TIMESTAMP NULL,
120 col22 TIME,
121 col23 YEAR) ENGINE = InnoDB
122 ;
123 Warnings:
124 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
125 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
126 CREATE INDEX idx1 ON t1(col18);
127 CREATE INDEX prefix_idx ON t1(col14 (10));
128 CREATE UNIQUE INDEX idx2 ON t1(col12);
129 CREATE UNIQUE INDEX idx3 ON t1(col8);
130 ALTER TABLE t1 DISCARD TABLESPACE;
131 restore: t1 .ibd and .cfg files
132 ALTER TABLE t1 IMPORT TABLESPACE;
133 CHECK TABLE t1;
134 Table Op Msg_type Msg_text
135 test.t1 check status OK
136 SELECT * FROM t1;
137 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
138  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
139 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
140 DROP TABLE t1;
141 #
142 # case 3 - with blob objects
143 #
144 CREATE TABLE t1 (
145 col_1_varbinary VARBINARY (4000),
146 col_2_varchar VARCHAR (4000),
147 col_3_text TEXT (4000),
148 col_4_blob BLOB (4000),
149 col_5_text TEXT (4000),
150 col_6_varchar VARCHAR (4000),
151 col_7_binary BINARY (255)
152 ) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
153 ;
154 INSERT INTO t1 VALUES(
155 REPEAT('a', 4000),REPEAT('o', 4000),REPEAT('a', 4000), REPEAT('o', 4000),
156 REPEAT('a', 4000),REPEAT('a', 4000),REPEAT('a', 255));
157 SELECT col_1_varbinary = REPEAT("a", 4000),
158 col_2_varchar = REPEAT("o", 4000),
159 col_3_text = REPEAT("a", 4000),
160 col_4_blob = REPEAT("o", 4000),
161 col_5_text = REPEAT("a", 4000),
162 col_6_varchar = REPEAT("a", 4000),
163 col_7_binary = REPEAT("a", 255)
164 FROM t1;
165 col_1_varbinary = REPEAT("a", 4000) col_2_varchar = REPEAT("o", 4000) col_3_text = REPEAT("a", 4000) col_4_blob = REPEAT("o", 4000) col_5_text = REPEAT("a", 4000) col_6_varchar = REPEAT("a", 4000) col_7_binary = REPEAT("a", 255)
166 1 1 1 1 1 1 1
167 FLUSH TABLES t1 FOR EXPORT;
168 backup: t1
169 UNLOCK TABLES;
170 DROP TABLE t1;
171 CREATE TABLE t1 (
172 col_1_varbinary VARBINARY (4000),
173 col_2_varchar VARCHAR (4000),
174 col_3_text TEXT (4000),
175 col_4_blob BLOB (4000),
176 col_5_text TEXT (4000),
177 col_6_varchar VARCHAR (4000),
178 col_7_binary BINARY (255)
179 ) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
180 ;
181 ALTER TABLE t1 DISCARD TABLESPACE;
182 restore: t1 .ibd and .cfg files
183 ALTER TABLE t1 IMPORT TABLESPACE;
184 SELECT col_1_varbinary = REPEAT("a", 4000),
185 col_2_varchar = REPEAT("o", 4000),
186 col_3_text = REPEAT("a", 4000),
187 col_4_blob = REPEAT("o", 4000),
188 col_5_text = REPEAT("a", 4000),
189 col_6_varchar = REPEAT("a", 4000),
190 col_7_binary = REPEAT("a", 255)
191 FROM t1;
192 col_1_varbinary = REPEAT("a", 4000) col_2_varchar = REPEAT("o", 4000) col_3_text = REPEAT("a", 4000) col_4_blob = REPEAT("o", 4000) col_5_text = REPEAT("a", 4000) col_6_varchar = REPEAT("a", 4000) col_7_binary = REPEAT("a", 255)
193 1 1 1 1 1 1 1
194 DROP TABLE t1;
195 #
196 # case 4 - transportable tablespace with auto_increment
197 #
198 CREATE TABLE t1 (
199 col_1_int INT AUTO_INCREMENT,
200 col_2_varchar VARCHAR (20),
201 PRIMARY KEY (col_1_int)) ENGINE = InnoDB
202 ;
203 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3');
204 INSERT INTO t1 (col_2_varchar) VALUES ('a4'),('a5'),('a6');
205 SELECT * FROM t1 ORDER BY col_1_int;
206 col_1_int col_2_varchar
207 1 a1
208 2 a2
209 3 a3
210 4 a4
211 5 a5
212 6 a6
213 FLUSH TABLES t1 FOR EXPORT;
214 backup: t1
215 UNLOCK TABLES;
216 DROP TABLE t1;
217 CREATE TABLE t1 (
218 col_1_int INT AUTO_INCREMENT,
219 col_2_varchar VARCHAR (20),
220 PRIMARY KEY (col_1_int)) ENGINE = InnoDB
221 ;
222 ALTER TABLE t1 DISCARD TABLESPACE;
223 restore: t1 .ibd and .cfg files
224 ALTER TABLE t1 IMPORT TABLESPACE;
225 SELECT * FROM t1 ORDER BY col_1_int;
226 col_1_int col_2_varchar
227 1 a1
228 2 a2
229 3 a3
230 4 a4
231 5 a5
232 6 a6
233 INSERT INTO t1 VALUES (1,'a1');
234 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
235 INSERT INTO t1(col_2_varchar) VALUES ('a101'),('a102'),('a103');
236 SELECT * FROM t1 ORDER BY col_1_int;
237 col_1_int col_2_varchar
238 1 a1
239 2 a2
240 3 a3
241 4 a4
242 5 a5
243 6 a6
244 7 a101
245 8 a102
246 9 a103
247 ALTER TABLE t1 MODIFY col_1_int BIGINT;
248 SELECT * FROM t1 ORDER BY col_1_int;
249 col_1_int col_2_varchar
250 1 a1
251 2 a2
252 3 a3
253 4 a4
254 5 a5
255 6 a6
256 7 a101
257 8 a102
258 9 a103
259 DROP TABLE t1;
260 #
261 # case 5 - check with primary and foreign key
262 #
263 CREATE TABLE t1 (
264 col_1_int INT,col_2_varchar VARCHAR (20),
265 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB;
266 CREATE TABLE t1_fk (
267 col_1_int INT,col_2_varchar VARCHAR (20),
268 PRIMARY KEY (col_1_int),
269 FOREIGN KEY (col_2_varchar) REFERENCES t1(col_2_varchar)
270 ) ENGINE = InnoDB;
271 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
272 INSERT INTO t1_fk VALUES (1,'a1'),(2,'a2'),(3,'a3');
273 SELECT * FROM t1;
274 col_1_int col_2_varchar
275 1 a1
276 2 a2
277 3 a3
278 4 a4
279 5 a5
280 SELECT * FROM t1_fk;
281 col_1_int col_2_varchar
282 1 a1
283 2 a2
284 3 a3
285 FLUSH TABLES t1,t1_fk FOR EXPORT;
286 backup: t1
287 backup: t1_fk
288 UNLOCK TABLES;
289 DROP TABLE t1_fk,t1;
290 CREATE TABLE t1 (
291 col_1_int INT,col_2_varchar VARCHAR (20),
292 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB;
293 CREATE TABLE t1_fk (
294 col_1_int INT,col_2_varchar VARCHAR (20),
295 PRIMARY KEY (col_1_int),
296 FOREIGN KEY (col_2_varchar) REFERENCES t1(col_2_varchar)
297 ) ENGINE = InnoDB;
298 # Alter table discard table is not allowed with foreign_key_checks = 1
299 SET foreign_key_checks = 0;
300 ALTER TABLE t1_fk DISCARD TABLESPACE;
301 ALTER TABLE t1 DISCARD TABLESPACE;
302 SET foreign_key_checks = 1;
303 restore: t1 .ibd and .cfg files
304 restore: t1_fk .ibd and .cfg files
305 ALTER TABLE t1 IMPORT TABLESPACE;
306 ALTER TABLE t1_fk IMPORT TABLESPACE;
307 SELECT * FROM t1;
308 col_1_int col_2_varchar
309 1 a1
310 2 a2
311 3 a3
312 4 a4
313 5 a5
314 SELECT * FROM t1_fk;
315 col_1_int col_2_varchar
316 1 a1
317 2 a2
318 3 a3
319 # Enter Invalid value: PK-FK relationship violation
320 INSERT INTO t1_fk VALUES (100,'a100');
321 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1_fk`, CONSTRAINT `t1_fk_ibfk_1` FOREIGN KEY (`col_2_varchar`) REFERENCES `t1` (`col_2_varchar`))
322 SET AUTOCOMMIT = 0;
323 INSERT INTO t1_fk VALUES (4,'a4'),(5,'a5');
324 ROLLBACK;
325 SELECT * FROM t1_fk;
326 col_1_int col_2_varchar
327 1 a1
328 2 a2
329 3 a3
330 DROP TABLE t1_fk,t1;
331 SET AUTOCOMMIT = 1;
332 #
333 # case 6 - transportable tablespace with transactions
334 #
335 CREATE TABLE t1 (
336 col_1_int int,col_2_varchar VARCHAR (20),
337 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
338 ;
339 SET AUTOCOMMIT = 0;
340 INSERT INTO t1 VALUES (1,'a1'),(2,'a2');
341 SELECT * FROM t1;
342 col_1_int col_2_varchar
343 1 a1
344 2 a2
345 COMMIT;
346 INSERT INTO t1 VALUES (3,'a3'),(4,'a4');
347 ROLLBACK;
348 INSERT INTO t1 VALUES (5,'a5'),(6,'a6');
349 COMMIT;
350 SELECT * FROM t1 ORDER BY col_1_int;
351 col_1_int col_2_varchar
352 1 a1
353 2 a2
354 5 a5
355 6 a6
356 FLUSH TABLES t1 FOR EXPORT;
357 backup: t1
358 UNLOCK TABLES;
359 DROP TABLE t1;
360 CREATE TABLE t1 (
361 col_1_int int,col_2_varchar VARCHAR (20),
362 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
363 ;
364 ALTER TABLE t1 DISCARD TABLESPACE;
365 SET AUTOCOMMIT = 0;
366 restore: t1 .ibd and .cfg files
367 ALTER TABLE t1 IMPORT TABLESPACE;
368 INSERT INTO t1 VALUES (7,'a7'),(8,'a8');
369 COMMIT;
370 INSERT INTO t1 VALUES (9,'a9'),(10,'a10');
371 ROLLBACK;
372 INSERT INTO t1 VALUES (11,'a11'),(12,'a12');
373 COMMIT;
374 SELECT * FROM t1 ORDER BY col_1_int;
375 col_1_int col_2_varchar
376 1 a1
377 2 a2
378 5 a5
379 6 a6
380 7 a7
381 8 a8
382 11 a11
383 12 a12
384 SET AUTOCOMMIT = 1;
385 DROP TABLE t1;
386 #
387 # case 7 - Transportable tablespace with transaction
388 # (earlier failed with jet)
389 #
390 CREATE TABLE t1 (i int) ENGINE = InnoDB
391 ;
392 INSERT INTO t1 VALUES (100),(200),(300);
393 SELECT * FROM t1 ORDER BY i;
394 i
395 100
396 200
397 300
398 FLUSH TABLES t1 FOR EXPORT;
399 backup: t1
400 UNLOCK TABLES;
401 DROP TABLE t1;
402 CREATE TABLE t1 (i int) ENGINE = InnoDB
403 ;
404 ALTER TABLE t1 DISCARD TABLESPACE;
405 restore: t1 .ibd and .cfg files
406 ALTER TABLE t1 IMPORT TABLESPACE;
407 SELECT * FROM t1 ORDER BY i;
408 i
409 100
410 200
411 300
412 SET AUTOCOMMIT = 0;
413 INSERT INTO t1 VALUES (101),(102),(103);
414 COMMIT;
415 SELECT * FROM t1 ORDER BY i;
416 i
417 100
418 101
419 102
420 103
421 200
422 300
423 SET AUTOCOMMIT = 1;
424 DROP TABLE t1;
425 #
426 # case 8 - negative cases
427 #
428 CREATE TABLE t1 (i int) ENGINE = InnoDB
429 ;
430 INSERT INTO t1 VALUES (100),(200),(300);
431 SELECT * FROM t1 ORDER BY i;
432 i
433 100
434 200
435 300
436 FLUSH TABLES t1 FOR EXPORT;
437 backup: t1
438 UNLOCK TABLES;
439 # try if we can flush again
440 FLUSH TABLES t1 FOR EXPORT;
441 UNLOCK TABLES;
442 DROP TABLE t1;
443 # create table with incorrect schema
444 CREATE TABLE t1 (i bigint) ENGINE = InnoDB
445 ;
446 ALTER TABLE t1 DISCARD TABLESPACE;
447 restore: t1 .ibd and .cfg files
448 # error as mismatch in column data type
449 ALTER TABLE t1 IMPORT TABLESPACE;
450 ERROR HY000: Schema mismatch (Column i precise type mismatch.)
451 # explicitly delete ibd file before creating table with correct schema
452 unlink: t1.ibd
453 unlink: t1.cfg
454 DROP TABLE t1;
455 CREATE TABLE t1 (i int) ENGINE = InnoDB
456 ;
457 ALTER TABLE t1 DISCARD TABLESPACE;
458 restore: t1 .ibd and .cfg files
459 # Import should succeed
460 ALTER TABLE t1 IMPORT TABLESPACE;
461 # Try to import twice
462 ALTER TABLE t1 IMPORT TABLESPACE;
463 ERROR HY000: Tablespace 'test/t1' exists.
464 SELECT * FROM t1 ORDER BY i;
465 i
466 100
467 200
468 300
469 SET AUTOCOMMIT = 0;
470 INSERT INTO t1 VALUES (101),(102),(103);
471 COMMIT;
472 SELECT * FROM t1 ORDER BY i;
473 i
474 100
475 101
476 102
477 103
478 200
479 300
480 DROP TABLE t1;
481 CREATE TABLE t1 (i int) ENGINE = InnoDB
482 ;
483 ALTER TABLE t1 DISCARD TABLESPACE;
484 # do not delete ibd file and try to import
485 restore: t1 .ibd and .cfg files
486 ALTER TABLE t1 IMPORT TABLESPACE;
487 DROP TABLE t1;
488 SET AUTOCOMMIT = 1;
489 #
490 # case 9 - empty table import
491 #
492 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
493 ;
494 SET AUTOCOMMIT = 0;
495 INSERT INTO t1 VALUES (1),(123),(331);
496 SELECT c1 FROM t1;
497 c1
498 1
499 123
500 331
501 ROLLBACK;
502 FLUSH TABLES t1 FOR EXPORT;
503 backup: t1
504 UNLOCK TABLES;
505 DROP TABLE t1;
506 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
507 ;
508 ALTER TABLE t1 DISCARD TABLESPACE;
509 restore: t1 .ibd and .cfg files
510 ALTER TABLE t1 IMPORT TABLESPACE;
511 SELECT * FROM t1 ORDER BY c1;
512 c1
513 DROP TABLE t1;
514 SET AUTOCOMMIT = 1;
515 #
516 # case 10 - Transportable tablespaces with prefix index
517 #
518 CREATE TABLE t1 (col_1 CHAR (255),
519 col_2 VARCHAR (255), col_3 VARCHAR (255),
520 col_4 VARCHAR (255),col_5 VARCHAR (255),
521 col_6 text (255), col_7 text (255),
522 col_8 text (255),col_9 text (255),
523 col_10 BLOB (255),col_11 BLOB (255),
524 col_12 BLOB (255), col_13 BLOB (255),
525 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
526 ;
527 CREATE INDEX prefix_idx ON t1(
528 col_1 (50),col_2 (50),col_3 (50),
529 col_4 (50),col_5 (50),col_6 (50),
530 col_7 (50),col_8 (50),col_9 (50),
531 col_10 (50),col_11 (50),col_12 (50),
532 col_13(50));
533 INSERT INTO t1 VALUES (
534 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
535 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
536 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
537 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
538 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
539 SELECT
540 col_1 = REPEAT("col1_00001",10),
541 col_2 = REPEAT("col2_00001",10),
542 col_3 = REPEAT("col3_00001",10),
543 col_4 = REPEAT("col4_00001",10),
544 col_5 = REPEAT("col5_00001",10),
545 col_6 = REPEAT("col6_00001",10),
546 col_7 = REPEAT("col7_00001",10),
547 col_8 = REPEAT("col8_00001",10),
548 col_9 = REPEAT("col9_00001",10),
549 col_10 = REPEAT("col10_00001",10),
550 col_11 = REPEAT("col11_00001",10),
551 col_12 = REPEAT("col12_00001",10),
552 col_13 = REPEAT("col13_00001",10),
553 col_14 = REPEAT("col14_00001",10),
554 col_15
555 FROM t1;
556 col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
557 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
558 FLUSH TABLES t1 FOR EXPORT;
559 backup: t1
560 UNLOCK TABLES;
561 DROP TABLE t1;
562 CREATE TABLE t1 (col_1 CHAR (255),
563 col_2 VARCHAR (255), col_3 VARCHAR (255),
564 col_4 VARCHAR (255),col_5 VARCHAR (255),
565 col_6 text (255), col_7 text (255),
566 col_8 text (255),col_9 text (255),
567 col_10 BLOB (255),col_11 BLOB (255),
568 col_12 BLOB (255), col_13 BLOB (255),
569 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
570 ;
571 CREATE INDEX prefix_idx ON t1(
572 col_1 (50),col_2 (50),col_3 (50),
573 col_4 (50),col_5 (50),col_6 (50),
574 col_7 (50),col_8 (50),col_9 (50),
575 col_10 (50),col_11 (50),col_12 (50),
576 col_13(50));
577 ALTER TABLE t1 DISCARD TABLESPACE;
578 restore: t1 .ibd and .cfg files
579 ALTER TABLE t1 IMPORT TABLESPACE;
580 SELECT
581 col_1 = REPEAT("col1_00001",10),
582 col_2 = REPEAT("col2_00001",10),
583 col_3 = REPEAT("col3_00001",10),
584 col_4 = REPEAT("col4_00001",10),
585 col_5 = REPEAT("col5_00001",10),
586 col_6 = REPEAT("col6_00001",10),
587 col_7 = REPEAT("col7_00001",10),
588 col_8 = REPEAT("col8_00001",10),
589 col_9 = REPEAT("col9_00001",10),
590 col_10 = REPEAT("col10_00001",10),
591 col_11 = REPEAT("col11_00001",10),
592 col_12 = REPEAT("col12_00001",10),
593 col_13 = REPEAT("col13_00001",10),
594 col_14 = REPEAT("col14_00001",10),
595 col_15
596 FROM t1;
597 col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
598 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
599 DROP TABLE t1;
600 #
601 # case 11 - Transportable tablespaces with secondary index
602 #
603 CREATE TABLE t1 (col_1 CHAR (255),
604 col_2 VARCHAR (255), col_3 VARCHAR (255),
605 col_4 VARCHAR (255),col_5 VARCHAR (255),
606 col_6 text (255), col_7 text (255),
607 col_8 text (255),col_9 text (255),
608 col_10 BLOB (255),col_11 BLOB (255),
609 col_12 BLOB (255), col_13 BLOB (255),
610 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
611 ;
612 CREATE INDEX idx1 ON t1(col_1);
613 CREATE INDEX idx2 ON t1(col_2);
614 CREATE INDEX idx3 ON t1(col_3);
615 CREATE INDEX idx4 ON t1(col_4);
616 CREATE INDEX idx5 ON t1(col_5);
617 CREATE INDEX idx6 ON t1(col_6(255));
618 CREATE INDEX idx7 ON t1(col_7(255));
619 CREATE INDEX idx8 ON t1(col_8(255));
620 CREATE INDEX idx9 ON t1(col_9(255));
621 CREATE INDEX idx10 ON t1(col_10(255));
622 CREATE INDEX idx11 ON t1(col_11(255));
623 CREATE INDEX idx12 ON t1(col_12(255));
624 CREATE INDEX idx13 ON t1(col_13(255));
625 CREATE INDEX idx14 ON t1(col_14(255));
626 INSERT INTO t1 VALUES (
627 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
628 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
629 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
630 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
631 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
632 SELECT
633 col_1 = REPEAT("col1_00001",10),
634 col_2 = REPEAT("col2_00001",10),
635 col_3 = REPEAT("col3_00001",10),
636 col_4 = REPEAT("col4_00001",10),
637 col_5 = REPEAT("col5_00001",10),
638 col_6 = REPEAT("col6_00001",10),
639 col_7 = REPEAT("col7_00001",10),
640 col_8 = REPEAT("col8_00001",10),
641 col_9 = REPEAT("col9_00001",10),
642 col_10 = REPEAT("col10_00001",10),
643 col_11 = REPEAT("col11_00001",10),
644 col_12 = REPEAT("col12_00001",10),
645 col_13 = REPEAT("col13_00001",10),
646 col_14 = REPEAT("col14_00001",10),
647 col_15
648 FROM t1;
649 col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
650 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
651 FLUSH TABLES t1 FOR EXPORT;
652 backup: t1
653 UNLOCK TABLES;
654 DROP TABLE t1;
655 CREATE TABLE t1 (col_1 CHAR (255),
656 col_2 VARCHAR (255), col_3 VARCHAR (255),
657 col_4 VARCHAR (255),col_5 VARCHAR (255),
658 col_6 text (255), col_7 text (255),
659 col_8 text (255),col_9 text (255),
660 col_10 BLOB (255),col_11 BLOB (255),
661 col_12 BLOB (255), col_13 BLOB (255),
662 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
663 ;
664 CREATE INDEX idx1 ON t1(col_1);
665 CREATE INDEX idx2 ON t1(col_2);
666 CREATE INDEX idx3 ON t1(col_3);
667 CREATE INDEX idx4 ON t1(col_4);
668 CREATE INDEX idx5 ON t1(col_5);
669 CREATE INDEX idx6 ON t1(col_6(255));
670 CREATE INDEX idx7 ON t1(col_7(255));
671 CREATE INDEX idx8 ON t1(col_8(255));
672 CREATE INDEX idx9 ON t1(col_9(255));
673 CREATE INDEX idx10 ON t1(col_10(255));
674 CREATE INDEX idx11 ON t1(col_11(255));
675 CREATE INDEX idx12 ON t1(col_12(255));
676 CREATE INDEX idx13 ON t1(col_13(255));
677 CREATE INDEX idx14 ON t1(col_14(255));
678 ALTER TABLE t1 DISCARD TABLESPACE;
679 restore: t1 .ibd and .cfg files
680 ALTER TABLE t1 IMPORT TABLESPACE;
681 SELECT
682 col_1 = REPEAT("col1_00001",10),
683 col_2 = REPEAT("col2_00001",10),
684 col_3 = REPEAT("col3_00001",10),
685 col_4 = REPEAT("col4_00001",10),
686 col_5 = REPEAT("col5_00001",10),
687 col_6 = REPEAT("col6_00001",10),
688 col_7 = REPEAT("col7_00001",10),
689 col_8 = REPEAT("col8_00001",10),
690 col_9 = REPEAT("col9_00001",10),
691 col_10 = REPEAT("col10_00001",10),
692 col_11 = REPEAT("col11_00001",10),
693 col_12 = REPEAT("col12_00001",10),
694 col_13 = REPEAT("col13_00001",10),
695 col_14 = REPEAT("col14_00001",10),
696 col_15
697 FROM t1;
698 col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
699 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
700 # perform transaction on imported table
701 SET AUTOCOMMIT = 0;
702 INSERT INTO t1(col_15) VALUES (15000),(16000);
703 SELECT col_15 FROM t1 WHERE col_15 > 11000;
704 col_15
705 15000
706 16000
707 ROLLBACK;
708 SELECT col_15 FROM t1 WHERE col_15 > 11000;
709 col_15
710 INSERT INTO t1(col_15) VALUES (15000),(16000);
711 COMMIT;
712 SELECT col_15 FROM t1 WHERE col_15 > 11000;
713 col_15
714 15000
715 16000
716 # Perform DDL operations on imported table.
717 ALTER TABLE t1 DROP INDEX idx1;
718 ALTER TABLE t1 DROP INDEX idx6;
719 ALTER TABLE t1 DROP INDEX idx10;
720 SELECT
721 col_1 = REPEAT("col1_00001",10),
722 col_2 = REPEAT("col2_00001",10),
723 col_3 = REPEAT("col3_00001",10),
724 col_4 = REPEAT("col4_00001",10),
725 col_5 = REPEAT("col5_00001",10),
726 col_6 = REPEAT("col6_00001",10),
727 col_7 = REPEAT("col7_00001",10),
728 col_8 = REPEAT("col8_00001",10),
729 col_9 = REPEAT("col9_00001",10),
730 col_10 = REPEAT("col10_00001",10),
731 col_11 = REPEAT("col11_00001",10),
732 col_12 = REPEAT("col12_00001",10),
733 col_13 = REPEAT("col13_00001",10),
734 col_14 = REPEAT("col14_00001",10),
735 col_15
736 FROM t1;
737 col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
738 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
739 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15000
740 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16000
741 ALTER TABLE t1 ADD INDEX idx1 (col_1);
742 ALTER TABLE t1 ADD INDEX idx6 (col_1(255));
743 Warnings:
744 Warning 1831 Duplicate index 'idx6' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
745 ALTER TABLE t1 ADD INDEX idx10 (col_10(255));
746 SELECT
747 col_1 = REPEAT("col1_00001",10),
748 col_2 = REPEAT("col2_00001",10),
749 col_3 = REPEAT("col3_00001",10),
750 col_4 = REPEAT("col4_00001",10),
751 col_5 = REPEAT("col5_00001",10),
752 col_6 = REPEAT("col6_00001",10),
753 col_7 = REPEAT("col7_00001",10),
754 col_8 = REPEAT("col8_00001",10),
755 col_9 = REPEAT("col9_00001",10),
756 col_10 = REPEAT("col10_00001",10),
757 col_11 = REPEAT("col11_00001",10),
758 col_12 = REPEAT("col12_00001",10),
759 col_13 = REPEAT("col13_00001",10),
760 col_14 = REPEAT("col14_00001",10),
761 col_15
762 FROM t1;
763 col_1 = REPEAT("col1_00001",10) col_2 = REPEAT("col2_00001",10) col_3 = REPEAT("col3_00001",10) col_4 = REPEAT("col4_00001",10) col_5 = REPEAT("col5_00001",10) col_6 = REPEAT("col6_00001",10) col_7 = REPEAT("col7_00001",10) col_8 = REPEAT("col8_00001",10) col_9 = REPEAT("col9_00001",10) col_10 = REPEAT("col10_00001",10) col_11 = REPEAT("col11_00001",10) col_12 = REPEAT("col12_00001",10) col_13 = REPEAT("col13_00001",10) col_14 = REPEAT("col14_00001",10) col_15
764 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
765 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15000
766 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16000
767 DROP TABLE t1;
768 SET AUTOCOMMIT = 1;
769 #
770 # case 12 - Transportable tablespaces with trigger / view
771 #
772 CREATE TABLE t1(col1 bit(1),
773 col2 boolean,col3 tinyint, col4 smallint,
774 col5 mediumint,col6 int, col7 bigint,
775 col8 float (14,3),col9 double (14,3),
776 col10 VARCHAR(20) CHARACTER SET utf8,
777 col11 TEXT CHARACTER SET binary,
778 col12 ENUM('a','b','c') CHARACTER SET binary,
779 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
780 col14 CHAR(20), col15 VARBINARY (400),
781 col16 BINARY(40), col17 BLOB (400),
782 col18 int not null primary key,
783 col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
784 col22 TIME, col23 YEAR) ENGINE = InnoDB
785 ;
786 Warnings:
787 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
788 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
789 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
790 # table for trigger action
791 CREATE TABLE trigger_table (i int) ENGINE = InnoDB;
792 CREATE TRIGGER tri AFTER INSERT ON t1
793 FOR EACH ROW INSERT INTO trigger_table VALUES(NEW.col18);
794 CREATE OR REPLACE VIEW VW1 AS SELECT * FROM t1;
795 CREATE INDEX idx1 ON t1(col18);
796 CREATE INDEX prefix_idx ON t1(col14 (10));
797 CREATE UNIQUE INDEX idx2 ON t1(col12);
798 CREATE UNIQUE INDEX idx3 ON t1(col8);
799 INSERT INTO t1 VALUES (
800 1,1,-128,32767,-8388608,2147483647,-9223372036854775808,92233720368.222,
801 -92233720368.222,'aaa','aaaaaaaaaa','b','bbbbb','ccccc',REPEAT('d',40),
802 REPEAT('d',40),REPEAT('d',40),1,'1000-01-01','3000-12-31 23:59:59.99',
803 '1990-01-01 00:00:01.00','01:59:59.00','1901');
804 INSERT INTO t1 VALUES (
805 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
806 NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
807 INSERT INTO t1 VALUES (
808 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
809 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
810 ERROR 23000: Column 'col18' cannot be null
811 INSERT INTO t1 VALUES (
812 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
813 NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
814 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
815 SELECT * FROM t1;
816 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
817  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
818 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
819 SELECT * FROM trigger_table;
820 i
821 1
822 3
823 SELECT * FROM VW1;
824 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
825  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
826 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
827 FLUSH TABLES t1 FOR EXPORT;
828 backup: t1
829 UNLOCK TABLES;
830 # trigger is also dropped when table is dropped
831 DROP TABLE t1;
832 CREATE TABLE t1(col1 bit(1),
833 col2 boolean,col3 tinyint, col4 smallint,
834 col5 mediumint,col6 int, col7 bigint,
835 col8 float (14,3),col9 double (14,3),
836 col10 VARCHAR(20) CHARACTER SET utf8,
837 col11 TEXT CHARACTER SET binary,
838 col12 ENUM('a','b','c') CHARACTER SET binary,
839 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
840 col14 CHAR(20), col15 VARBINARY (400),
841 col16 BINARY(40), col17 BLOB (400),
842 col18 int not null primary key,
843 col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
844 col22 TIME, col23 YEAR) ENGINE = InnoDB
845 ;
846 Warnings:
847 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
848 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
849 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
850 CREATE INDEX idx1 ON t1(col18);
851 CREATE INDEX prefix_idx ON t1(col14 (10));
852 CREATE UNIQUE INDEX idx2 ON t1(col12);
853 CREATE UNIQUE INDEX idx3 ON t1(col8);
854 ALTER TABLE t1 DISCARD TABLESPACE;
855 restore: t1 .ibd and .cfg files
856 ALTER TABLE t1 IMPORT TABLESPACE;
857 SELECT * FROM t1;
858 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
859  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
860 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
861 SELECT * FROM trigger_table;
862 i
863 1
864 3
865 SELECT * FROM VW1;
866 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
867  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 1 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
868 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
869 INSERT INTO t1(col18) VALUES (5);
870 SELECT * FROM trigger_table;
871 i
872 1
873 3
874 UPDATE t1 SET col18=10 WHERE col18=1;
875 SELECT * FROM VW1;
876 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
877 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
878 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 5 NULL NULL NULL NULL NULL
879  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 10 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
880 SELECT * FROM t1 WHERE col18=10;
881 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
882  1 -128 32767 -8388608 2147483647 -9223372036854775808 92233719808.000 -92233720368.222 aaa aaaaaaaaaa b bbbbb ccccc dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd dddddddddddddddddddddddddddddddddddddddd 10 1000-01-01 3001-01-01 00:00:00 1990-01-01 00:00:01 01:59:59 1901
883 ALTER TABLE t1 ADD COLUMN col24 varbinary(40) default null;
884 INSERT INTO t1(col18,col24) VALUES (6,REPEAT('a',10));
885 SELECT col24,col18 FROM t1 WHERE col18 in (6,1,10) ORDER BY col18;
886 col24 col18
887 aaaaaaaaaa 6
888 NULL 10
889 ALTER TABLE t1 DROP INDEX prefix_idx;
890 SELECT col18,col14 FROM t1 WHERE col14 like '_ccc%';
891 col18 col14
892 10 ccccc
893 ALTER TABLE t1 ADD INDEX prefix_idx (col24(10));
894 SELECT col18,col24 FROM t1 WHERE col24 like '_a_a%';
895 col18 col24
896 6 aaaaaaaaaa
897 DROP VIEW VW1;
898 DROP TABLE t1;
899 DROP TABLE trigger_table;
900 #
901 # case 13 Simple export, discard and import with instant ADD COLUMN.
902 #
903 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
904 ;
905 INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
906 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
907 SELECT c1 FROM t1;
908 c1
909 1
910 123
911 212
912 331
913 332
914 333
915 334
916 335
917 FLUSH TABLES t1 FOR EXPORT;
918 SELECT * FROM t1 ORDER BY c1;
919 c1 c2
920 1 500
921 123 500
922 212 500
923 331 500
924 332 500
925 333 500
926 334 500
927 335 500
928 backup: t1
929 UNLOCK TABLES;
930 DROP TABLE t1;
931 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
932 ;
933 ALTER TABLE t1 DISCARD TABLESPACE;
934 restore: t1 .ibd and .cfg files
935 ALTER TABLE t1 IMPORT TABLESPACE;
936 CHECK TABLE t1;
937 Table Op Msg_type Msg_text
938 test.t1 check status OK
939 SELECT * FROM t1 ORDER BY c1;
940 c1 c2
941 1 500
942 123 500
943 212 500
944 331 500
945 332 500
946 333 500
947 334 500
948 335 500
949 DROP TABLE t1;
950 #
951 # case 14 Simple export, discard and import with instant ADD COLUMN.
952 # However, this time the .cfg file would be missing
953 #
954 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
955 ;
956 INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
957 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
958 SELECT c1 FROM t1;
959 c1
960 1
961 123
962 212
963 331
964 332
965 333
966 334
967 335
968 FLUSH TABLES t1 FOR EXPORT;
969 SELECT * FROM t1 ORDER BY c1;
970 c1 c2
971 1 500
972 123 500
973 212 500
974 331 500
975 332 500
976 333 500
977 334 500
978 335 500
979 backup: t1
980 UNLOCK TABLES;
981 DROP TABLE t1;
982 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
983 ;
984 ALTER TABLE t1 DISCARD TABLESPACE;
985 restore: t1 .ibd and .cfg files
986 # remove the .cfg files
987 ALTER TABLE t1 IMPORT TABLESPACE;
988 ERROR HY000: Schema mismatch (Clustered index validation failed. Because the .cfg file is missing, table definition of the IBD file could be different. Or the data file itself is already corrupted.)
989 DROP TABLE t1;
990 SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table;