Debian Codegraph @

mysql-test/suite/innodb/r/innodb-import-partition.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 PARTITION BY HASH(c1) PARTITIONS 3;
14 INSERT INTO t1 VALUES (1),(123),(331);
15 SELECT c1 FROM t1;
16 c1
17 123
18 1
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#p#p0
27 backup: t1#p#p1
28 backup: t1#p#p2
29 UNLOCK TABLES;
30 DROP TABLE t1;
31 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
32 PARTITION BY HASH(c1) PARTITIONS 3;
33 ALTER TABLE t1 DISCARD TABLESPACE;
34 restore: t1#p#p0 .ibd and .cfg files
35 restore: t1#p#p1 .ibd and .cfg files
36 restore: t1#p#p2 .ibd and .cfg files
37 ALTER TABLE t1 IMPORT TABLESPACE;
38 SELECT * FROM t1 ORDER BY c1;
39 c1
40 1
41 123
42 331
43 DROP TABLE t1;
44 #
45 # case 2 With indexes and more datatypes.
46 #
47 CREATE TABLE t1 (
48 col1 BIT(1),
49 col2 BOOLEAN,
50 col3 TINYINT,
51 col4 SMALLINT,
52 col5 MEDIUMINT,
53 col6 INT,
54 col7 BIGINT,
55 col8 FLOAT (14,3),
56 col9 DOUBLE (14,3),
57 col10 VARCHAR(20),
58 col11 TEXT,
59 col12 ENUM('a','b','c'),
60 col13 TEXT,
61 col14 CHAR(20),
62 col15 VARBINARY (400),
63 col16 BINARY(40),
64 col17 BLOB (400),
65 col18 INT NOT NULL PRIMARY KEY,
66 col19 DATE,
67 col20 DATETIME,
68 col21 TIMESTAMP NULL,
69 col22 TIME,
70 col23 YEAR) ENGINE = InnoDB
71 PARTITION BY RANGE (col18)
72 SUBPARTITION BY HASH (col18) SUBPARTITIONS 3
73 (PARTITION `First_partition` VALUES LESS THAN (10),
74 PARTITION `Last_partition` VALUES LESS THAN MAXVALUE);
75 Warnings:
76 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
77 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
78 CREATE INDEX idx1 ON t1(col18);
79 CREATE INDEX prefix_idx ON t1(col14 (10));
80 INSERT INTO t1 VALUES
81 (1,1,-128,32767,-8388608,2147483647,-9223372036854775808, 92233720368.222,
82 -92233720368.222,'aaa', + 'aaaaaaaaaa','b','bbbbb','ccccc',
83 REPEAT('d',40),REPEAT('d',40),REPEAT('d',40),1,'1000-01-01',
84 '3000-12-31 23:59:59.99','1990-01-01 00:00:01.00',
85 '01:59:59.00','1901');
86 INSERT INTO t1 VALUES
87 (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
88 NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
89 INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
90 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
91 ERROR 23000: Column 'col18' cannot be null
92 INSERT INTO t1 VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,
93 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
94 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
95 FLUSH TABLES t1 WITH READ LOCK;
96 SELECT * FROM t1;
97 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
98 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
99  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
100 backup: t1#p#first_partition#sp#first_partitionsp0
101 backup: t1#p#first_partition#sp#first_partitionsp1
102 backup: t1#p#first_partition#sp#first_partitionsp2
103 backup: t1#p#last_partition#sp#last_partitionsp0
104 backup: t1#p#last_partition#sp#last_partitionsp1
105 backup: t1#p#last_partition#sp#last_partitionsp2
106 UNLOCK TABLES;
107 DROP TABLE t1;
108 CREATE TABLE t1 (
109 col1 BIT(1),
110 col2 BOOLEAN,
111 col3 TINYINT,
112 col4 SMALLINT,
113 col5 MEDIUMINT,
114 col6 INT,
115 col7 BIGINT,
116 col8 FLOAT (14,3),
117 col9 DOUBLE (14,3),
118 col10 VARCHAR(20),
119 col11 TEXT,
120 col12 ENUM('a','b','c'),
121 col13 TEXT,
122 col14 CHAR(20),
123 col15 VARBINARY (400),
124 col16 BINARY(40),
125 col17 BLOB (400),
126 col18 INT NOT NULL PRIMARY KEY,
127 col19 DATE,
128 col20 DATETIME,
129 col21 TIMESTAMP NULL,
130 col22 TIME,
131 col23 YEAR) ENGINE = InnoDB
132 PARTITION BY RANGE (col18)
133 SUBPARTITION BY HASH (col18) SUBPARTITIONS 3
134 (PARTITION `First_partition` VALUES LESS THAN (10),
135 PARTITION `Last_partition` VALUES LESS THAN MAXVALUE);
136 Warnings:
137 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
138 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
139 CREATE INDEX idx1 ON t1(col18);
140 CREATE INDEX prefix_idx ON t1(col14 (10));
141 ALTER TABLE t1 DISCARD TABLESPACE;
142 restore: t1#p#first_partition#sp#first_partitionsp0 .ibd and .cfg files
143 restore: t1#p#first_partition#sp#first_partitionsp1 .ibd and .cfg files
144 restore: t1#p#first_partition#sp#first_partitionsp2 .ibd and .cfg files
145 restore: t1#p#last_partition#sp#last_partitionsp0 .ibd and .cfg files
146 restore: t1#p#last_partition#sp#last_partitionsp1 .ibd and .cfg files
147 restore: t1#p#last_partition#sp#last_partitionsp2 .ibd and .cfg files
148 ALTER TABLE t1 IMPORT TABLESPACE;
149 CHECK TABLE t1;
150 Table Op Msg_type Msg_text
151 test.t1 check status OK
152 SELECT * FROM t1;
153 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
154 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
155  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
156 DROP TABLE t1;
157 #
158 # case 3 - with blob objects
159 #
160 CREATE TABLE t1 (
161 col_1_varbinary VARBINARY (4000),
162 col_2_varchar VARCHAR (4000),
163 col_3_text TEXT (4000),
164 col_4_blob BLOB (4000),
165 col_5_text TEXT (4000),
166 col_6_varchar VARCHAR (4000),
167 col_7_binary BINARY (255)
168 ) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
169 PARTITION BY KEY (col_7_binary) PARTITIONS 3;
170 INSERT INTO t1 VALUES(
171 REPEAT('a', 4000),REPEAT('o', 4000),REPEAT('a', 4000), REPEAT('o', 4000),
172 REPEAT('a', 4000),REPEAT('a', 4000),REPEAT('a', 255));
173 SELECT col_1_varbinary = REPEAT("a", 4000),
174 col_2_varchar = REPEAT("o", 4000),
175 col_3_text = REPEAT("a", 4000),
176 col_4_blob = REPEAT("o", 4000),
177 col_5_text = REPEAT("a", 4000),
178 col_6_varchar = REPEAT("a", 4000),
179 col_7_binary = REPEAT("a", 255)
180 FROM t1;
181 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)
182 1 1 1 1 1 1 1
183 FLUSH TABLES t1 FOR EXPORT;
184 backup: t1#p#p0
185 backup: t1#p#p1
186 backup: t1#p#p2
187 UNLOCK TABLES;
188 DROP TABLE t1;
189 CREATE TABLE t1 (
190 col_1_varbinary VARBINARY (4000),
191 col_2_varchar VARCHAR (4000),
192 col_3_text TEXT (4000),
193 col_4_blob BLOB (4000),
194 col_5_text TEXT (4000),
195 col_6_varchar VARCHAR (4000),
196 col_7_binary BINARY (255)
197 ) ROW_FORMAT=DYNAMIC ENGINE = InnoDB
198 PARTITION BY KEY (col_7_binary) PARTITIONS 3;
199 ALTER TABLE t1 DISCARD TABLESPACE;
200 restore: t1#p#p0 .ibd and .cfg files
201 restore: t1#p#p1 .ibd and .cfg files
202 restore: t1#p#p2 .ibd and .cfg files
203 ALTER TABLE t1 IMPORT TABLESPACE;
204 SELECT col_1_varbinary = REPEAT("a", 4000),
205 col_2_varchar = REPEAT("o", 4000),
206 col_3_text = REPEAT("a", 4000),
207 col_4_blob = REPEAT("o", 4000),
208 col_5_text = REPEAT("a", 4000),
209 col_6_varchar = REPEAT("a", 4000),
210 col_7_binary = REPEAT("a", 255)
211 FROM t1;
212 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)
213 1 1 1 1 1 1 1
214 DROP TABLE t1;
215 #
216 # case 4 - transportable tablespace with auto_increment
217 #
218 CREATE TABLE t1 (
219 col_1_int INT AUTO_INCREMENT,
220 col_2_varchar VARCHAR (20),
221 PRIMARY KEY (col_1_int)) ENGINE = InnoDB
222 PARTITION BY HASH (col_1_int) PARTITIONS 3;
223 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3');
224 INSERT INTO t1 (col_2_varchar) VALUES ('a4'),('a5'),('a6');
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 FLUSH TABLES t1 FOR EXPORT;
234 backup: t1#p#p0
235 backup: t1#p#p1
236 backup: t1#p#p2
237 UNLOCK TABLES;
238 DROP TABLE t1;
239 CREATE TABLE t1 (
240 col_1_int INT AUTO_INCREMENT,
241 col_2_varchar VARCHAR (20),
242 PRIMARY KEY (col_1_int)) ENGINE = InnoDB
243 PARTITION BY HASH (col_1_int) PARTITIONS 3;
244 ALTER TABLE t1 DISCARD TABLESPACE;
245 restore: t1#p#p0 .ibd and .cfg files
246 restore: t1#p#p1 .ibd and .cfg files
247 restore: t1#p#p2 .ibd and .cfg files
248 ALTER TABLE t1 IMPORT TABLESPACE;
249 SELECT * FROM t1 ORDER BY col_1_int;
250 col_1_int col_2_varchar
251 1 a1
252 2 a2
253 3 a3
254 4 a4
255 5 a5
256 6 a6
257 INSERT INTO t1 VALUES (1,'a1');
258 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
259 INSERT INTO t1(col_2_varchar) VALUES ('a101'),('a102'),('a103');
260 SELECT * FROM t1 ORDER BY col_1_int;
261 col_1_int col_2_varchar
262 1 a1
263 2 a2
264 3 a3
265 4 a4
266 5 a5
267 6 a6
268 7 a101
269 8 a102
270 9 a103
271 ALTER TABLE t1 MODIFY col_1_int BIGINT;
272 SELECT * FROM t1 ORDER BY col_1_int;
273 col_1_int col_2_varchar
274 1 a1
275 2 a2
276 3 a3
277 4 a4
278 5 a5
279 6 a6
280 7 a101
281 8 a102
282 9 a103
283 DROP TABLE t1;
284 #
285 # case 5 - check with primary and foreign key
286 #
287 # Foreign Keys not supported by partitioned tables.
288 #
289 # case 6 - transportable tablespace with transactions
290 #
291 CREATE TABLE t1 (
292 col_1_int int,col_2_varchar VARCHAR (20),
293 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
294 PARTITION BY KEY (col_2_varchar) PARTITIONS 3;
295 SET AUTOCOMMIT = 0;
296 INSERT INTO t1 VALUES (1,'a1'),(2,'a2');
297 SELECT * FROM t1;
298 col_1_int col_2_varchar
299 1 a1
300 2 a2
301 COMMIT;
302 INSERT INTO t1 VALUES (3,'a3'),(4,'a4');
303 ROLLBACK;
304 INSERT INTO t1 VALUES (5,'a5'),(6,'a6');
305 COMMIT;
306 SELECT * FROM t1 ORDER BY col_1_int;
307 col_1_int col_2_varchar
308 1 a1
309 2 a2
310 5 a5
311 6 a6
312 FLUSH TABLES t1 FOR EXPORT;
313 backup: t1#p#p0
314 backup: t1#p#p1
315 backup: t1#p#p2
316 UNLOCK TABLES;
317 DROP TABLE t1;
318 CREATE TABLE t1 (
319 col_1_int int,col_2_varchar VARCHAR (20),
320 PRIMARY KEY (col_2_varchar)) ENGINE = InnoDB
321 PARTITION BY KEY (col_2_varchar) PARTITIONS 3;
322 ALTER TABLE t1 DISCARD TABLESPACE;
323 SET AUTOCOMMIT = 0;
324 restore: t1#p#p0 .ibd and .cfg files
325 restore: t1#p#p1 .ibd and .cfg files
326 restore: t1#p#p2 .ibd and .cfg files
327 ALTER TABLE t1 IMPORT TABLESPACE;
328 INSERT INTO t1 VALUES (7,'a7'),(8,'a8');
329 COMMIT;
330 INSERT INTO t1 VALUES (9,'a9'),(10,'a10');
331 ROLLBACK;
332 INSERT INTO t1 VALUES (11,'a11'),(12,'a12');
333 COMMIT;
334 SELECT * FROM t1 ORDER BY col_1_int;
335 col_1_int col_2_varchar
336 1 a1
337 2 a2
338 5 a5
339 6 a6
340 7 a7
341 8 a8
342 11 a11
343 12 a12
344 SET AUTOCOMMIT = 1;
345 DROP TABLE t1;
346 #
347 # case 7 - Transportable tablespace with transaction
348 # (earlier failed with jet)
349 #
350 CREATE TABLE t1 (i int) ENGINE = InnoDB
351 PARTITION BY HASH (i) PARTITIONS 3;
352 INSERT INTO t1 VALUES (100),(200),(300);
353 SELECT * FROM t1 ORDER BY i;
354 i
355 100
356 200
357 300
358 FLUSH TABLES t1 FOR EXPORT;
359 backup: t1#p#p0
360 backup: t1#p#p1
361 backup: t1#p#p2
362 UNLOCK TABLES;
363 DROP TABLE t1;
364 CREATE TABLE t1 (i int) ENGINE = InnoDB
365 PARTITION BY HASH (i) PARTITIONS 3;
366 ALTER TABLE t1 DISCARD TABLESPACE;
367 restore: t1#p#p0 .ibd and .cfg files
368 restore: t1#p#p1 .ibd and .cfg files
369 restore: t1#p#p2 .ibd and .cfg files
370 ALTER TABLE t1 IMPORT TABLESPACE;
371 SELECT * FROM t1 ORDER BY i;
372 i
373 100
374 200
375 300
376 SET AUTOCOMMIT = 0;
377 INSERT INTO t1 VALUES (101),(102),(103);
378 COMMIT;
379 SELECT * FROM t1 ORDER BY i;
380 i
381 100
382 101
383 102
384 103
385 200
386 300
387 SET AUTOCOMMIT = 1;
388 DROP TABLE t1;
389 #
390 # case 8 - negative cases
391 #
392 CREATE TABLE t1 (i int) ENGINE = InnoDB
393 PARTITION BY HASH (i) PARTITIONS 3;
394 INSERT INTO t1 VALUES (100),(200),(300);
395 SELECT * FROM t1 ORDER BY i;
396 i
397 100
398 200
399 300
400 FLUSH TABLES t1 FOR EXPORT;
401 backup: t1#p#p0
402 backup: t1#p#p1
403 backup: t1#p#p2
404 UNLOCK TABLES;
405 # try if we can flush again
406 FLUSH TABLES t1 FOR EXPORT;
407 UNLOCK TABLES;
408 DROP TABLE t1;
409 # create table with incorrect schema
410 CREATE TABLE t1 (i bigint) ENGINE = InnoDB
411 PARTITION BY HASH (i) PARTITIONS 3;
412 ALTER TABLE t1 DISCARD TABLESPACE;
413 restore: t1#p#p0 .ibd and .cfg files
414 restore: t1#p#p1 .ibd and .cfg files
415 restore: t1#p#p2 .ibd and .cfg files
416 # error as mismatch in column data type
417 ALTER TABLE t1 IMPORT TABLESPACE;
418 ERROR HY000: Schema mismatch (Column i precise type mismatch.)
419 # explicitly delete ibd file before creating table with correct schema
420 unlink: t1#p#p0.ibd
421 unlink: t1#p#p0.cfg
422 unlink: t1#p#p1.ibd
423 unlink: t1#p#p1.cfg
424 unlink: t1#p#p2.ibd
425 unlink: t1#p#p2.cfg
426 DROP TABLE t1;
427 CREATE TABLE t1 (i int) ENGINE = InnoDB
428 PARTITION BY HASH (i) PARTITIONS 3;
429 ALTER TABLE t1 DISCARD TABLESPACE;
430 restore: t1#p#p0 .ibd and .cfg files
431 restore: t1#p#p1 .ibd and .cfg files
432 restore: t1#p#p2 .ibd and .cfg files
433 # Import should succeed
434 ALTER TABLE t1 IMPORT TABLESPACE;
435 # Try to import twice
436 ALTER TABLE t1 IMPORT TABLESPACE;
437 ERROR HY000: Tablespace 'test/t1#P#p0' exists.
438 SELECT * FROM t1 ORDER BY i;
439 i
440 100
441 200
442 300
443 SET AUTOCOMMIT = 0;
444 INSERT INTO t1 VALUES (101),(102),(103);
445 COMMIT;
446 SELECT * FROM t1 ORDER BY i;
447 i
448 100
449 101
450 102
451 103
452 200
453 300
454 DROP TABLE t1;
455 CREATE TABLE t1 (i int) ENGINE = InnoDB
456 PARTITION BY HASH (i) PARTITIONS 3;
457 ALTER TABLE t1 DISCARD TABLESPACE;
458 # do not delete ibd file and try to import
459 restore: t1#p#p0 .ibd and .cfg files
460 restore: t1#p#p1 .ibd and .cfg files
461 restore: t1#p#p2 .ibd and .cfg files
462 ALTER TABLE t1 IMPORT TABLESPACE;
463 DROP TABLE t1;
464 SET AUTOCOMMIT = 1;
465 #
466 # case 9 - empty table import
467 #
468 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
469 PARTITION BY HASH(c1) PARTITIONS 3;
470 SET AUTOCOMMIT = 0;
471 INSERT INTO t1 VALUES (1),(123),(331);
472 SELECT c1 FROM t1;
473 c1
474 123
475 1
476 331
477 ROLLBACK;
478 FLUSH TABLES t1 FOR EXPORT;
479 backup: t1#p#p0
480 backup: t1#p#p1
481 backup: t1#p#p2
482 UNLOCK TABLES;
483 DROP TABLE t1;
484 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
485 PARTITION BY HASH(c1) PARTITIONS 3;
486 ALTER TABLE t1 DISCARD TABLESPACE;
487 restore: t1#p#p0 .ibd and .cfg files
488 restore: t1#p#p1 .ibd and .cfg files
489 restore: t1#p#p2 .ibd and .cfg files
490 ALTER TABLE t1 IMPORT TABLESPACE;
491 SELECT * FROM t1 ORDER BY c1;
492 c1
493 DROP TABLE t1;
494 SET AUTOCOMMIT = 1;
495 #
496 # case 10 - Transportable tablespaces with prefix index
497 #
498 CREATE TABLE t1 (col_1 CHAR (255),
499 col_2 VARCHAR (255), col_3 VARCHAR (255),
500 col_4 VARCHAR (255),col_5 VARCHAR (255),
501 col_6 text (255), col_7 text (255),
502 col_8 text (255),col_9 text (255),
503 col_10 BLOB (255),col_11 BLOB (255),
504 col_12 BLOB (255), col_13 BLOB (255),
505 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
506 PARTITION BY KEY (col_1) PARTITIONS 3;
507 CREATE INDEX prefix_idx ON t1(
508 col_1 (50),col_2 (50),col_3 (50),
509 col_4 (50),col_5 (50),col_6 (50),
510 col_7 (50),col_8 (50),col_9 (50),
511 col_10 (50),col_11 (50),col_12 (50),
512 col_13(50));
513 Warnings:
514 Warning 1681 Column 'test.t1.col_1' having prefix key part 'col_1(50)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release.
515 INSERT INTO t1 VALUES (
516 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
517 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
518 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
519 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
520 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
521 SELECT
522 col_1 = REPEAT("col1_00001",10),
523 col_2 = REPEAT("col2_00001",10),
524 col_3 = REPEAT("col3_00001",10),
525 col_4 = REPEAT("col4_00001",10),
526 col_5 = REPEAT("col5_00001",10),
527 col_6 = REPEAT("col6_00001",10),
528 col_7 = REPEAT("col7_00001",10),
529 col_8 = REPEAT("col8_00001",10),
530 col_9 = REPEAT("col9_00001",10),
531 col_10 = REPEAT("col10_00001",10),
532 col_11 = REPEAT("col11_00001",10),
533 col_12 = REPEAT("col12_00001",10),
534 col_13 = REPEAT("col13_00001",10),
535 col_14 = REPEAT("col14_00001",10),
536 col_15
537 FROM t1;
538 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
539 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
540 FLUSH TABLES t1 FOR EXPORT;
541 backup: t1#p#p0
542 backup: t1#p#p1
543 backup: t1#p#p2
544 UNLOCK TABLES;
545 DROP TABLE t1;
546 CREATE TABLE t1 (col_1 CHAR (255),
547 col_2 VARCHAR (255), col_3 VARCHAR (255),
548 col_4 VARCHAR (255),col_5 VARCHAR (255),
549 col_6 text (255), col_7 text (255),
550 col_8 text (255),col_9 text (255),
551 col_10 BLOB (255),col_11 BLOB (255),
552 col_12 BLOB (255), col_13 BLOB (255),
553 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
554 PARTITION BY KEY (col_1) PARTITIONS 3;
555 CREATE INDEX prefix_idx ON t1(
556 col_1 (50),col_2 (50),col_3 (50),
557 col_4 (50),col_5 (50),col_6 (50),
558 col_7 (50),col_8 (50),col_9 (50),
559 col_10 (50),col_11 (50),col_12 (50),
560 col_13(50));
561 Warnings:
562 Warning 1681 Column 'test.t1.col_1' having prefix key part 'col_1(50)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release.
563 ALTER TABLE t1 DISCARD TABLESPACE;
564 restore: t1#p#p0 .ibd and .cfg files
565 restore: t1#p#p1 .ibd and .cfg files
566 restore: t1#p#p2 .ibd and .cfg files
567 ALTER TABLE t1 IMPORT TABLESPACE;
568 SELECT
569 col_1 = REPEAT("col1_00001",10),
570 col_2 = REPEAT("col2_00001",10),
571 col_3 = REPEAT("col3_00001",10),
572 col_4 = REPEAT("col4_00001",10),
573 col_5 = REPEAT("col5_00001",10),
574 col_6 = REPEAT("col6_00001",10),
575 col_7 = REPEAT("col7_00001",10),
576 col_8 = REPEAT("col8_00001",10),
577 col_9 = REPEAT("col9_00001",10),
578 col_10 = REPEAT("col10_00001",10),
579 col_11 = REPEAT("col11_00001",10),
580 col_12 = REPEAT("col12_00001",10),
581 col_13 = REPEAT("col13_00001",10),
582 col_14 = REPEAT("col14_00001",10),
583 col_15
584 FROM t1;
585 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
586 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
587 DROP TABLE t1;
588 #
589 # case 11 - Transportable tablespaces with secondary index
590 #
591 CREATE TABLE t1 (col_1 CHAR (255),
592 col_2 VARCHAR (255), col_3 VARCHAR (255),
593 col_4 VARCHAR (255),col_5 VARCHAR (255),
594 col_6 text (255), col_7 text (255),
595 col_8 text (255),col_9 text (255),
596 col_10 BLOB (255),col_11 BLOB (255),
597 col_12 BLOB (255), col_13 BLOB (255),
598 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
599 PARTITION BY KEY (col_1) PARTITIONS 3;
600 CREATE INDEX idx1 ON t1(col_1);
601 CREATE INDEX idx2 ON t1(col_2);
602 CREATE INDEX idx3 ON t1(col_3);
603 CREATE INDEX idx4 ON t1(col_4);
604 CREATE INDEX idx5 ON t1(col_5);
605 CREATE INDEX idx6 ON t1(col_6(255));
606 CREATE INDEX idx7 ON t1(col_7(255));
607 CREATE INDEX idx8 ON t1(col_8(255));
608 CREATE INDEX idx9 ON t1(col_9(255));
609 CREATE INDEX idx10 ON t1(col_10(255));
610 CREATE INDEX idx11 ON t1(col_11(255));
611 CREATE INDEX idx12 ON t1(col_12(255));
612 CREATE INDEX idx13 ON t1(col_13(255));
613 CREATE INDEX idx14 ON t1(col_14(255));
614 INSERT INTO t1 VALUES (
615 REPEAT("col1_00001",10),REPEAT("col2_00001",10),REPEAT("col3_00001",10),
616 REPEAT("col4_00001",10),REPEAT("col5_00001",10),REPEAT("col6_00001",10),
617 REPEAT("col7_00001",10),REPEAT("col8_00001",10),REPEAT("col9_00001",10),
618 REPEAT("col10_00001",10),REPEAT("col11_00001",10),REPEAT("col12_00001",10),
619 REPEAT("col13_00001",10),REPEAT("col14_00001",10),1);
620 SELECT
621 col_1 = REPEAT("col1_00001",10),
622 col_2 = REPEAT("col2_00001",10),
623 col_3 = REPEAT("col3_00001",10),
624 col_4 = REPEAT("col4_00001",10),
625 col_5 = REPEAT("col5_00001",10),
626 col_6 = REPEAT("col6_00001",10),
627 col_7 = REPEAT("col7_00001",10),
628 col_8 = REPEAT("col8_00001",10),
629 col_9 = REPEAT("col9_00001",10),
630 col_10 = REPEAT("col10_00001",10),
631 col_11 = REPEAT("col11_00001",10),
632 col_12 = REPEAT("col12_00001",10),
633 col_13 = REPEAT("col13_00001",10),
634 col_14 = REPEAT("col14_00001",10),
635 col_15
636 FROM t1;
637 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
638 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
639 FLUSH TABLES t1 FOR EXPORT;
640 backup: t1#p#p0
641 backup: t1#p#p1
642 backup: t1#p#p2
643 UNLOCK TABLES;
644 DROP TABLE t1;
645 CREATE TABLE t1 (col_1 CHAR (255),
646 col_2 VARCHAR (255), col_3 VARCHAR (255),
647 col_4 VARCHAR (255),col_5 VARCHAR (255),
648 col_6 text (255), col_7 text (255),
649 col_8 text (255),col_9 text (255),
650 col_10 BLOB (255),col_11 BLOB (255),
651 col_12 BLOB (255), col_13 BLOB (255),
652 col_14 BLOB (255), col_15 int) ENGINE = InnoDB charset latin1
653 PARTITION BY KEY (col_1) PARTITIONS 3;
654 CREATE INDEX idx1 ON t1(col_1);
655 CREATE INDEX idx2 ON t1(col_2);
656 CREATE INDEX idx3 ON t1(col_3);
657 CREATE INDEX idx4 ON t1(col_4);
658 CREATE INDEX idx5 ON t1(col_5);
659 CREATE INDEX idx6 ON t1(col_6(255));
660 CREATE INDEX idx7 ON t1(col_7(255));
661 CREATE INDEX idx8 ON t1(col_8(255));
662 CREATE INDEX idx9 ON t1(col_9(255));
663 CREATE INDEX idx10 ON t1(col_10(255));
664 CREATE INDEX idx11 ON t1(col_11(255));
665 CREATE INDEX idx12 ON t1(col_12(255));
666 CREATE INDEX idx13 ON t1(col_13(255));
667 CREATE INDEX idx14 ON t1(col_14(255));
668 ALTER TABLE t1 DISCARD TABLESPACE;
669 restore: t1#p#p0 .ibd and .cfg files
670 restore: t1#p#p1 .ibd and .cfg files
671 restore: t1#p#p2 .ibd and .cfg files
672 ALTER TABLE t1 IMPORT TABLESPACE;
673 SELECT
674 col_1 = REPEAT("col1_00001",10),
675 col_2 = REPEAT("col2_00001",10),
676 col_3 = REPEAT("col3_00001",10),
677 col_4 = REPEAT("col4_00001",10),
678 col_5 = REPEAT("col5_00001",10),
679 col_6 = REPEAT("col6_00001",10),
680 col_7 = REPEAT("col7_00001",10),
681 col_8 = REPEAT("col8_00001",10),
682 col_9 = REPEAT("col9_00001",10),
683 col_10 = REPEAT("col10_00001",10),
684 col_11 = REPEAT("col11_00001",10),
685 col_12 = REPEAT("col12_00001",10),
686 col_13 = REPEAT("col13_00001",10),
687 col_14 = REPEAT("col14_00001",10),
688 col_15
689 FROM t1;
690 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
691 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
692 # perform transaction on imported table
693 SET AUTOCOMMIT = 0;
694 INSERT INTO t1(col_15) VALUES (15000),(16000);
695 SELECT col_15 FROM t1 WHERE col_15 > 11000;
696 col_15
697 15000
698 16000
699 ROLLBACK;
700 SELECT col_15 FROM t1 WHERE col_15 > 11000;
701 col_15
702 INSERT INTO t1(col_15) VALUES (15000),(16000);
703 COMMIT;
704 SELECT col_15 FROM t1 WHERE col_15 > 11000;
705 col_15
706 15000
707 16000
708 # Perform DDL operations on imported table.
709 ALTER TABLE t1 DROP INDEX idx1;
710 ALTER TABLE t1 DROP INDEX idx6;
711 ALTER TABLE t1 DROP INDEX idx10;
712 SELECT
713 col_1 = REPEAT("col1_00001",10),
714 col_2 = REPEAT("col2_00001",10),
715 col_3 = REPEAT("col3_00001",10),
716 col_4 = REPEAT("col4_00001",10),
717 col_5 = REPEAT("col5_00001",10),
718 col_6 = REPEAT("col6_00001",10),
719 col_7 = REPEAT("col7_00001",10),
720 col_8 = REPEAT("col8_00001",10),
721 col_9 = REPEAT("col9_00001",10),
722 col_10 = REPEAT("col10_00001",10),
723 col_11 = REPEAT("col11_00001",10),
724 col_12 = REPEAT("col12_00001",10),
725 col_13 = REPEAT("col13_00001",10),
726 col_14 = REPEAT("col14_00001",10),
727 col_15
728 FROM t1;
729 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
730 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
731 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15000
732 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16000
733 ALTER TABLE t1 ADD INDEX idx1 (col_1);
734 ALTER TABLE t1 ADD INDEX idx6 (col_1(255));
735 Warnings:
736 Warning 1831 Duplicate index 'idx6' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
737 ALTER TABLE t1 ADD INDEX idx10 (col_10(255));
738 SELECT
739 col_1 = REPEAT("col1_00001",10),
740 col_2 = REPEAT("col2_00001",10),
741 col_3 = REPEAT("col3_00001",10),
742 col_4 = REPEAT("col4_00001",10),
743 col_5 = REPEAT("col5_00001",10),
744 col_6 = REPEAT("col6_00001",10),
745 col_7 = REPEAT("col7_00001",10),
746 col_8 = REPEAT("col8_00001",10),
747 col_9 = REPEAT("col9_00001",10),
748 col_10 = REPEAT("col10_00001",10),
749 col_11 = REPEAT("col11_00001",10),
750 col_12 = REPEAT("col12_00001",10),
751 col_13 = REPEAT("col13_00001",10),
752 col_14 = REPEAT("col14_00001",10),
753 col_15
754 FROM t1;
755 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
756 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1
757 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 15000
758 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16000
759 DROP TABLE t1;
760 SET AUTOCOMMIT = 1;
761 #
762 # case 12 - Transportable tablespaces with trigger / view
763 #
764 CREATE TABLE t1(col1 bit(1),
765 col2 boolean,col3 tinyint, col4 smallint,
766 col5 mediumint,col6 int, col7 bigint,
767 col8 float (14,3),col9 double (14,3),
768 col10 VARCHAR(20) CHARACTER SET utf8,
769 col11 TEXT CHARACTER SET binary,
770 col12 ENUM('a','b','c') CHARACTER SET binary,
771 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
772 col14 CHAR(20), col15 VARBINARY (400),
773 col16 BINARY(40), col17 BLOB (400),
774 col18 int not null primary key,
775 col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
776 col22 TIME, col23 YEAR) ENGINE = InnoDB
777 PARTITION BY HASH (col18) PARTITIONS 3;
778 Warnings:
779 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
780 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
781 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.
782 # table for trigger action
783 CREATE TABLE trigger_table (i int) ENGINE = InnoDB;
784 CREATE TRIGGER tri AFTER INSERT ON t1
785 FOR EACH ROW INSERT INTO trigger_table VALUES(NEW.col18);
786 CREATE OR REPLACE VIEW VW1 AS SELECT * FROM t1;
787 CREATE INDEX idx1 ON t1(col18);
788 CREATE INDEX prefix_idx ON t1(col14 (10));
789 INSERT INTO t1 VALUES (
790 1,1,-128,32767,-8388608,2147483647,-9223372036854775808,92233720368.222,
791 -92233720368.222,'aaa','aaaaaaaaaa','b','bbbbb','ccccc',REPEAT('d',40),
792 REPEAT('d',40),REPEAT('d',40),1,'1000-01-01','3000-12-31 23:59:59.99',
793 '1990-01-01 00:00:01.00','01:59:59.00','1901');
794 INSERT INTO t1 VALUES (
795 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
796 NULL,NULL,3,NULL,NULL,NULL,NULL,NULL);
797 INSERT INTO t1 VALUES (
798 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
799 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
800 ERROR 23000: Column 'col18' cannot be null
801 INSERT INTO t1 VALUES (
802 NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
803 NULL,NULL,1,NULL,NULL,NULL,NULL,NULL);
804 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
805 SELECT * FROM t1;
806 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
807 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
808  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
809 SELECT * FROM trigger_table;
810 i
811 1
812 3
813 SELECT * FROM VW1;
814 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
815 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
816  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
817 FLUSH TABLES t1 FOR EXPORT;
818 backup: t1#p#p0
819 backup: t1#p#p1
820 backup: t1#p#p2
821 UNLOCK TABLES;
822 # trigger is also dropped when table is dropped
823 DROP TABLE t1;
824 CREATE TABLE t1(col1 bit(1),
825 col2 boolean,col3 tinyint, col4 smallint,
826 col5 mediumint,col6 int, col7 bigint,
827 col8 float (14,3),col9 double (14,3),
828 col10 VARCHAR(20) CHARACTER SET utf8,
829 col11 TEXT CHARACTER SET binary,
830 col12 ENUM('a','b','c') CHARACTER SET binary,
831 col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
832 col14 CHAR(20), col15 VARBINARY (400),
833 col16 BINARY(40), col17 BLOB (400),
834 col18 int not null primary key,
835 col19 DATE,col20 DATETIME, col21 TIMESTAMP NULL,
836 col22 TIME, col23 YEAR) ENGINE = InnoDB
837 PARTITION BY HASH (col18) PARTITIONS 3;
838 Warnings:
839 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
840 Warning 1681 Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
841 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.
842 CREATE INDEX idx1 ON t1(col18);
843 CREATE INDEX prefix_idx ON t1(col14 (10));
844 ALTER TABLE t1 DISCARD TABLESPACE;
845 restore: t1#p#p0 .ibd and .cfg files
846 restore: t1#p#p1 .ibd and .cfg files
847 restore: t1#p#p2 .ibd and .cfg files
848 ALTER TABLE t1 IMPORT TABLESPACE;
849 SELECT * FROM t1;
850 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
851 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
852  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
853 SELECT * FROM trigger_table;
854 i
855 1
856 3
857 SELECT * FROM VW1;
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 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
860  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
861 INSERT INTO t1(col18) VALUES (5);
862 SELECT * FROM trigger_table;
863 i
864 1
865 3
866 UPDATE t1 SET col18=10 WHERE col18=1;
867 SELECT * FROM VW1;
868 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
869 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 3 NULL NULL NULL NULL NULL
870  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
871 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 5 NULL NULL NULL NULL NULL
872 SELECT * FROM t1 WHERE col18=10;
873 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23
874  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
875 ALTER TABLE t1 ADD COLUMN col24 varbinary(40) default null;
876 INSERT INTO t1(col18,col24) VALUES (6,REPEAT('a',10));
877 SELECT col24,col18 FROM t1 WHERE col18 in (6,1,10) ORDER BY col18;
878 col24 col18
879 aaaaaaaaaa 6
880 NULL 10
881 ALTER TABLE t1 DROP INDEX prefix_idx;
882 SELECT col18,col14 FROM t1 WHERE col14 like '_ccc%';
883 col18 col14
884 10 ccccc
885 ALTER TABLE t1 ADD INDEX prefix_idx (col24(10));
886 SELECT col18,col24 FROM t1 WHERE col24 like '_a_a%';
887 col18 col24
888 6 aaaaaaaaaa
889 DROP VIEW VW1;
890 DROP TABLE t1;
891 DROP TABLE trigger_table;
892 #
893 # case 13 Simple export, discard and import with instant ADD COLUMN.
894 #
895 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
896 PARTITION BY HASH(c1) PARTITIONS 3;
897 INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
898 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
899 SELECT c1 FROM t1;
900 c1
901 123
902 333
903 1
904 331
905 334
906 212
907 332
908 335
909 FLUSH TABLES t1 FOR EXPORT;
910 SELECT * FROM t1 ORDER BY c1;
911 c1 c2
912 1 500
913 123 500
914 212 500
915 331 500
916 332 500
917 333 500
918 334 500
919 335 500
920 backup: t1#p#p0
921 backup: t1#p#p1
922 backup: t1#p#p2
923 UNLOCK TABLES;
924 DROP TABLE t1;
925 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
926 PARTITION BY HASH(c1) PARTITIONS 3;
927 ALTER TABLE t1 DISCARD TABLESPACE;
928 restore: t1#p#p0 .ibd and .cfg files
929 restore: t1#p#p1 .ibd and .cfg files
930 restore: t1#p#p2 .ibd and .cfg files
931 ALTER TABLE t1 IMPORT TABLESPACE;
932 CHECK TABLE t1;
933 Table Op Msg_type Msg_text
934 test.t1 check status OK
935 SELECT * FROM t1 ORDER BY c1;
936 c1 c2
937 1 500
938 123 500
939 212 500
940 331 500
941 332 500
942 333 500
943 334 500
944 335 500
945 DROP TABLE t1;
946 #
947 # case 14 Simple export, discard and import with instant ADD COLUMN.
948 # However, this time the .cfg file would be missing
949 #
950 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
951 PARTITION BY HASH(c1) PARTITIONS 3;
952 INSERT INTO t1 VALUES (1), (123), (212), (331), (332), (333), (334), (335) ;
953 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 500;
954 SELECT c1 FROM t1;
955 c1
956 123
957 333
958 1
959 331
960 334
961 212
962 332
963 335
964 FLUSH TABLES t1 FOR EXPORT;
965 SELECT * FROM t1 ORDER BY c1;
966 c1 c2
967 1 500
968 123 500
969 212 500
970 331 500
971 332 500
972 333 500
973 334 500
974 335 500
975 backup: t1#p#p0
976 backup: t1#p#p1
977 backup: t1#p#p2
978 UNLOCK TABLES;
979 DROP TABLE t1;
980 CREATE TABLE t1 (c1 INT, c2 INT) ENGINE = InnoDB
981 PARTITION BY HASH(c1) PARTITIONS 3;
982 ALTER TABLE t1 DISCARD TABLESPACE;
983 restore: t1#p#p0 .ibd and .cfg files
984 restore: t1#p#p1 .ibd and .cfg files
985 restore: t1#p#p2 .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;
991 #
992 # DISCARD/IMPORT selected partitions only
993 #
994 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
995 PARTITION BY HASH (c1) PARTITIONS 3;
996 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
997 SELECT c1 FROM t1 ORDER BY c1;
998 c1
999 1
1000 2
1001 3
1002 4
1003 5
1004 6
1005 FLUSH TABLES t1 FOR EXPORT;
1006 SELECT * FROM t1 ORDER BY c1;
1007 c1
1008 1
1009 2
1010 3
1011 4
1012 5
1013 6
1014 backup: t1#p#p0
1015 backup: t1#p#p1
1016 backup: t1#p#p2
1017 UNLOCK TABLES;
1018 DROP TABLE t1;
1019 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
1020 PARTITION BY HASH (c1) PARTITIONS 3;
1021 INSERT INTO t1 VALUES (31), (32), (33);
1022 ALTER TABLE t1 DISCARD PARTITION p0,p2 TABLESPACE;
1023 restore: t1#p#p0 .ibd and .cfg files
1024 restore: t1#p#p2 .ibd and .cfg files
1025 ALTER TABLE t1 IMPORT PARTITION p0, p2 TABLESPACE;
1026 SELECT * FROM t1 ORDER BY c1;
1027 c1
1028 2
1029 3
1030 5
1031 6
1032 31
1033 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
1034 restore: t1#p#p1 .ibd and .cfg files
1035 ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
1036 SELECT * FROM t1 ORDER BY c1;
1037 c1
1038 1
1039 2
1040 3
1041 4
1042 5
1043 6
1044 INSERT INTO t1 VALUES (31), (32), (33);
1045 SELECT * FROM t1 ORDER BY c1;
1046 c1
1047 1
1048 2
1049 3
1050 4
1051 5
1052 6
1053 31
1054 32
1055 33
1056 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
1057 restore: t1#p#p0 .ibd and .cfg files
1058 restore: t1#p#p1 .ibd and .cfg files
1059 restore: t1#p#p2 .ibd and .cfg files
1060 ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
1061 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
1062 c1
1063 1
1064 4
1065 INSERT INTO t1 PARTITION (p1) VALUES (61), (64);
1066 FLUSH TABLES;
1067 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
1068 c1
1069 1
1070 4
1071 61
1072 64
1073 Warnings:
1074 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
1075 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
1076 SELECT * FROM t1 ORDER BY c1;
1077 ERROR HY000: Tablespace has been discarded for table 't1'
1078 ALTER TABLE t1 DISCARD PARTITION TABLESPACE;
1079 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
1080 ALTER TABLE t1 IMPORT PARTITION TABLESPACE;
1081 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
1082 ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
1083 ERROR HY000: Unknown partition 'pNonExisting' in table 't1'
1084 ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
1085 ERROR HY000: Unknown partition 'pNonExisting' in table 't1'
1086 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
1087 restore: t1#p#p1 .ibd and .cfg files
1088 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
1089 SELECT * FROM t1 ORDER BY c1;
1090 c1
1091 1
1092 2
1093 3
1094 4
1095 5
1096 6
1097 DROP TABLE t1;
1098 #
1099 # DISCARD/IMPORT selected subpartitions only
1100 #
1101 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
1102 PARTITION BY RANGE (c1)
1103 SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
1104 (PARTITION p0 VALUES LESS THAN (100),
1105 PARTITION p1 VALUES LESS THAN MAXVALUE);
1106 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
1107 INSERT INTO t1 VALUES (101),(102),(103),(104),(105),(106);
1108 SELECT c1 FROM t1 ORDER BY c1;
1109 c1
1110 1
1111 2
1112 3
1113 4
1114 5
1115 6
1116 101
1117 102
1118 103
1119 104
1120 105
1121 106
1122 FLUSH TABLES t1 FOR EXPORT;
1123 SELECT * FROM t1 ORDER BY c1;
1124 c1
1125 1
1126 2
1127 3
1128 4
1129 5
1130 6
1131 101
1132 102
1133 103
1134 104
1135 105
1136 106
1137 backup: t1#p#p0#sp#p0sp0
1138 backup: t1#p#p0#sp#p0sp1
1139 backup: t1#p#p0#sp#p0sp2
1140 backup: t1#p#p1#sp#p1sp0
1141 backup: t1#p#p1#sp#p1sp1
1142 backup: t1#p#p1#sp#p1sp2
1143 UNLOCK TABLES;
1144 DROP TABLE t1;
1145 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
1146 PARTITION BY RANGE (c1)
1147 SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
1148 (PARTITION p0 VALUES LESS THAN (100),
1149 PARTITION p1 VALUES LESS THAN MAXVALUE);
1150 INSERT INTO t1 VALUES (31), (32), (33);
1151 INSERT INTO t1 VALUES (131), (132), (133);
1152 ALTER TABLE t1 DISCARD PARTITION p0sp1,p1sp0 TABLESPACE;
1153 restore: t1#p#p0#sp#p0sp1 .ibd and .cfg files
1154 restore: t1#p#p1#sp#p1sp0 .ibd and .cfg files
1155 ALTER TABLE t1 IMPORT PARTITION p0sp1, p1sp0 TABLESPACE;
1156 SELECT * FROM t1 ORDER BY c1;
1157 c1
1158 1
1159 4
1160 32
1161 33
1162 102
1163 105
1164 131
1165 133
1166 ALTER TABLE t1 DISCARD PARTITION p1, p0sp2 TABLESPACE;
1167 restore: t1#p#p0#sp#p0sp2 .ibd and .cfg files
1168 restore: t1#p#p1#sp#p1sp0 .ibd and .cfg files
1169 restore: t1#p#p1#sp#p1sp1 .ibd and .cfg files
1170 restore: t1#p#p1#sp#p1sp2 .ibd and .cfg files
1171 ALTER TABLE t1 IMPORT PARTITION p0sp2, p1 TABLESPACE;
1172 SELECT * FROM t1 ORDER BY c1;
1173 c1
1174 1
1175 2
1176 4
1177 5
1178 33
1179 101
1180 102
1181 103
1182 104
1183 105
1184 106
1185 INSERT INTO t1 VALUES (31), (32), (33);
1186 SELECT * FROM t1 ORDER BY c1;
1187 c1
1188 1
1189 2
1190 4
1191 5
1192 31
1193 32
1194 33
1195 33
1196 101
1197 102
1198 103
1199 104
1200 105
1201 106
1202 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
1203 restore: t1#p#p0#sp#p0sp0 .ibd and .cfg files
1204 restore: t1#p#p0#sp#p0sp1 .ibd and .cfg files
1205 restore: t1#p#p0#sp#p0sp2 .ibd and .cfg files
1206 restore: t1#p#p1#sp#p1sp0 .ibd and .cfg files
1207 restore: t1#p#p1#sp#p1sp1 .ibd and .cfg files
1208 restore: t1#p#p1#sp#p1sp2 .ibd and .cfg files
1209 ALTER TABLE t1 IMPORT PARTITION p1sp2 TABLESPACE;
1210 SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
1211 c1
1212 101
1213 104
1214 INSERT INTO t1 PARTITION (p1sp2) VALUES (161), (164);
1215 FLUSH TABLES;
1216 SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
1217 c1
1218 101
1219 104
1220 161
1221 164
1222 Warnings:
1223 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
1224 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
1225 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
1226 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
1227 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
1228 SELECT * FROM t1 ORDER BY c1;
1229 ERROR HY000: Tablespace has been discarded for table 't1'
1230 ALTER TABLE t1 DISCARD PARTITION p1sp2 TABLESPACE;
1231 restore: t1#p#p1#sp#p1sp2 .ibd and .cfg files
1232 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
1233 Warnings:
1234 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p0#SP#p0sp0.
1235 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p0#SP#p0sp1.
1236 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p0#SP#p0sp2.
1237 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p1#SP#p1sp0.
1238 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p1#SP#p1sp1.
1239 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p1#SP#p1sp2.
1240 restore: t1#p#p0#sp#p0sp0 .ibd and .cfg files
1241 restore: t1#p#p0#sp#p0sp1 .ibd and .cfg files
1242 restore: t1#p#p1#sp#p1sp0 .ibd and .cfg files
1243 restore: t1#p#p1#sp#p1sp1 .ibd and .cfg files
1244 restore: t1#p#p1#sp#p1sp2 .ibd and .cfg files
1245 # Removing p0sp2's .ibd file
1246 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
1247 ERROR HY000: Internal error: Cannot reset LSNs in table `test`.`t1` /* Partition `p0`, Subpartition `p0sp2` */ : Tablespace not found
1248 restore: t1#p#p0#sp#p0sp2 .ibd and .cfg files
1249 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
1250 ERROR HY000: Tablespace 'test/t1#P#p0#SP#p0sp0' exists.
1251 ALTER TABLE t1 IMPORT PARTITION p0sp2 TABLESPACE;
1252 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
1253 Warnings:
1254 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p1#SP#p1sp0.
1255 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p1#SP#p1sp1.
1256 Warning 1812 InnoDB: Tablespace is missing for table test/t1#P#p1#SP#p1sp2.
1257 restore: t1#p#p0#sp#p0sp0 .ibd and .cfg files
1258 restore: t1#p#p0#sp#p0sp1 .ibd and .cfg files
1259 restore: t1#p#p0#sp#p0sp2 .ibd and .cfg files
1260 restore: t1#p#p1#sp#p1sp0 .ibd and .cfg files
1261 restore: t1#p#p1#sp#p1sp1 .ibd and .cfg files
1262 restore: t1#p#p1#sp#p1sp2 .ibd and .cfg files
1263 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
1264 SELECT * FROM t1 ORDER BY c1;
1265 c1
1266 1
1267 2
1268 3
1269 4
1270 5
1271 6
1272 101
1273 102
1274 103
1275 104
1276 105
1277 106
1278 DROP TABLE t1;
1279 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
1280 ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
1281 ERROR HY000: Partition management on a not partitioned table is not possible
1282 ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
1283 ERROR HY000: Partition management on a not partitioned table is not possible
1284 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
1285 ERROR HY000: Partition management on a not partitioned table is not possible
1286 DROP TABLE t1;
1287 # discard partition one by one
1288 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
1289 PARTITION BY RANGE (c1)
1290 SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
1291 (PARTITION p0 VALUES LESS THAN (100),
1292 PARTITION p1 VALUES LESS THAN MAXVALUE);
1293 ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
1294 restore: t1#p#p0#sp#p0sp0 .ibd and .cfg files
1295 ALTER TABLE t1 IMPORT PARTITION p0sp0 TABLESPACE;
1296 SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
1297 c1
1298 3
1299 6
1300 SELECT * FROM t1 ORDER BY c1;
1301 c1
1302 3
1303 6
1304 ALTER TABLE t1 REBUILD PARTITION p0;
1305 SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
1306 c1
1307 3
1308 6
1309 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
1310 restore: t1#p#p1#sp#p1sp0 .ibd and .cfg files
1311 restore: t1#p#p1#sp#p1sp1 .ibd and .cfg files
1312 restore: t1#p#p1#sp#p1sp2 .ibd and .cfg files
1313 ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
1314 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
1315 c1
1316 101
1317 102
1318 103
1319 104
1320 105
1321 106
1322 ALTER TABLE t1 OPTIMIZE PARTITION p1;
1323 Table Op Msg_type Msg_text
1324 test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.
1325 test.t1 optimize status OK
1326 SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
1327 c1
1328 101
1329 102
1330 103
1331 104
1332 105
1333 106
1334 ALTER TABLE t1 CHECK PARTITION p1;
1335 Table Op Msg_type Msg_text
1336 test.t1 check status OK
1337 ALTER TABLE t1 ANALYZE PARTITION p1;
1338 Table Op Msg_type Msg_text
1339 test.t1 analyze status OK
1340 ALTER TABLE t1 REPAIR PARTITION p0sp0,p1;
1341 Table Op Msg_type Msg_text
1342 test.t1 repair status OK
1343 SELECT * FROM t1 ORDER BY c1;
1344 c1
1345 3
1346 6
1347 101
1348 102
1349 103
1350 104
1351 105
1352 106
1353 ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
1354 ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
1355 ALTER TABLE t1 DISCARD PARTITION p0sp1 TABLESPACE;
1356 ALTER TABLE t1 DISCARD PARTITION p0sp2 TABLESPACE;
1357 restore: t1#p#p0#sp#p0sp0 .ibd and .cfg files
1358 restore: t1#p#p0#sp#p0sp1 .ibd and .cfg files
1359 restore: t1#p#p0#sp#p0sp2 .ibd and .cfg files
1360 restore: t1#p#p1#sp#p1sp0 .ibd and .cfg files
1361 restore: t1#p#p1#sp#p1sp1 .ibd and .cfg files
1362 restore: t1#p#p1#sp#p1sp2 .ibd and .cfg files
1363 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
1364 SELECT * FROM t1 ORDER BY c1;
1365 c1
1366 1
1367 2
1368 3
1369 4
1370 5
1371 6
1372 101
1373 102
1374 103
1375 104
1376 105
1377 106
1378 DROP TABLE t1;
1379 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) PARTITION BY HASH (a) PARTITIONS 3;
1380 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
1381 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
1382 SELECT * FROM t1;
1383 a b c
1384 12 12 100
1385 21 21 100
1386 27 27 100
1387 1 1 100
1388 22 22 100
1389 2 2 100
1390 11 11 100
1391 26 26 100
1392 INSERT INTO t1(a, b) VALUES(5, 5), (6, 6), (28, 28);
1393 ALTER TABLE t1 TRUNCATE PARTITION p2;
1394 SELECT * FROM t1;
1395 a b c
1396 6 6 100
1397 12 12 100
1398 21 21 100
1399 27 27 100
1400 1 1 100
1401 22 22 100
1402 28 28 100
1403 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 200;
1404 ALTER TABLE t1 TRUNCATE PARTITION p1;
1405 SELECT * FROM t1;
1406 a b c d
1407 6 6 100 200
1408 12 12 100 200
1409 21 21 100 200
1410 27 27 100 200
1411 INSERT INTO t1(a, b) VALUES(3, 3), (4, 4);
1412 SELECT * FROM t1;
1413 a b c d
1414 3 3 100 200
1415 6 6 100 200
1416 12 12 100 200
1417 21 21 100 200
1418 27 27 100 200
1419 4 4 100 200
1420 CHECK TABLE t1;
1421 Table Op Msg_type Msg_text
1422 test.t1 check status OK
1423 FLUSH TABLES t1 FOR EXPORT;
1424 SELECT * FROM t1;
1425 a b c d
1426 3 3 100 200
1427 6 6 100 200
1428 12 12 100 200
1429 21 21 100 200
1430 27 27 100 200
1431 4 4 100 200
1432 backup: t1#p#p0
1433 backup: t1#p#p1
1434 backup: t1#p#p2
1435 UNLOCK TABLES;
1436 DROP TABLE t1;
1437 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT, d INT) PARTITION BY HASH (a) PARTITIONS 3;
1438 ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
1439 restore: t1#p#p0 .ibd and .cfg files
1440 restore: t1#p#p1 .ibd and .cfg files
1441 restore: t1#p#p2 .ibd and .cfg files
1442 ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
1443 CHECK TABLE t1;
1444 Table Op Msg_type Msg_text
1445 test.t1 check status OK
1446 SELECT * FROM t1;
1447 a b c d
1448 3 3 100 200
1449 6 6 100 200
1450 12 12 100 200
1451 21 21 100 200
1452 27 27 100 200
1453 4 4 100 200
1454 INSERT INTO t1(a, b) VALUES(8, 8), (9, 9), (13, 13), (14, 14), (23, 23), (24, 24);
1455 SELECT * FROM t1;
1456 a b c d
1457 3 3 100 200
1458 6 6 100 200
1459 9 9 NULL NULL
1460 12 12 100 200
1461 21 21 100 200
1462 24 24 NULL NULL
1463 27 27 100 200
1464 4 4 100 200
1465 13 13 NULL NULL
1466 8 8 NULL NULL
1467 14 14 NULL NULL
1468 23 23 NULL NULL
1469 UPDATE t1 SET c = 90 WHERE a < 10;
1470 SELECT * FROM t1;
1471 a b c d
1472 3 3 90 200
1473 6 6 90 200
1474 9 9 90 NULL
1475 12 12 100 200
1476 21 21 100 200
1477 24 24 NULL NULL
1478 27 27 100 200
1479 4 4 90 200
1480 13 13 NULL NULL
1481 8 8 90 NULL
1482 14 14 NULL NULL
1483 23 23 NULL NULL
1484 UPDATE t1 SET c = 110 WHERE a > 20;
1485 SELECT * FROM t1;
1486 a b c d
1487 3 3 90 200
1488 6 6 90 200
1489 9 9 90 NULL
1490 12 12 100 200
1491 21 21 110 200
1492 24 24 110 NULL
1493 27 27 110 200
1494 4 4 90 200
1495 13 13 NULL NULL
1496 8 8 90 NULL
1497 14 14 NULL NULL
1498 23 23 110 NULL
1499 CHECK TABLE t1;
1500 Table Op Msg_type Msg_text
1501 test.t1 check status OK
1502 DROP TABLE t1;