Debian Codegraph @

mysql-test/suite/innodb/r/innodb_autoextend_import_export.result

raw
1 #
2 # IMPORT/EXPORT for non-partitioned tables with autoextend_size
3 #
4
5 # Create source and destination databases and stored procedure to load the tables
6
7 CREATE DATABASE db_source;
8 USE db_source;
9 CREATE PROCEDURE bulk_insert_source()
10 BEGIN
11 DECLARE i INT DEFAULT 1;
12 WHILE i < 500 DO
13 INSERT INTO tsource VALUES(i, repeat('aaaaaa', 10000));
14 SET i = i + 1;
15 END WHILE;
16 END
17 |
18 CREATE DATABASE db_destination;
19 USE db_destination;
20 CREATE PROCEDURE bulk_insert_dest()
21 BEGIN
22 DECLARE i INT DEFAULT 1;
23 WHILE i < 500 DO
24 INSERT INTO tsource VALUES(i, repeat('aaaaaa', 10000));
25 SET i = i + 1;
26 END WHILE;
27 END
28 |
29 # Scenario-1:
30 # Create and populate a non-partitioned table on the source db with autoextend_size 4m
31 # Create a table on the destination db with same schema and autoextend_size
32 # values as that on the source db
33 # ALTER TABLE ... DISCARD TABLESPACE on the destination table
34 # FLUSH TABLE ... FOR EXPORT on the source table
35 # Copy .cfg and .ibd files from the source to the destination db
36 # UNLOCK TABLES
37 # ALTER TABLE ... IMPORT TABLESPACE on the destination table
38 # Verify that the number of rows seen on the destination table are same as seen on the source table
39
40 USE db_source;
41 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
42 call bulk_insert_source();
43 SELECT COUNT(*) FROM tsource;
44 COUNT(*)
45 499
46 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
47 WHERE NAME LIKE '%tsource%';
48 NAME FILE_SIZE AUTOEXTEND_SIZE
49 db_source/tsource 37748736 4194304
50 USE db_destination;
51 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
52 ALTER TABLE tsource DISCARD TABLESPACE;
53 USE db_source;
54 FLUSH TABLE tsource FOR EXPORT;
55 # List of files on source
56 tsource.cfg
57 tsource.ibd
58 # List of files on destination
59 tsource.cfg
60 tsource.ibd
61 UNLOCK TABLES;
62 USE db_destination;
63 ALTER TABLE tsource IMPORT TABLESPACE;
64 SELECT COUNT(*) FROM tsource;
65 COUNT(*)
66 499
67 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
68 WHERE NAME LIKE '%tsource%';
69 NAME FILE_SIZE AUTOEXTEND_SIZE
70 db_source/tsource 37748736 4194304
71 db_destination/tsource 37748736 4194304
72 DROP TABLE tsource;
73 USE db_source;
74 DROP TABLE tsource;
75 # Scenario-2:
76 # Same as scenario-1. But, do not copy the .cfg file and verify the contents on the source and destination
77
78 USE db_source;
79 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
80 call bulk_insert_source();
81 SELECT COUNT(*) FROM tsource;
82 COUNT(*)
83 499
84 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
85 WHERE NAME LIKE '%tsource%';
86 NAME FILE_SIZE AUTOEXTEND_SIZE
87 db_source/tsource 37748736 4194304
88 USE db_destination;
89 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M;
90 ALTER TABLE tsource DISCARD TABLESPACE;
91 USE db_source;
92 FLUSH TABLE tsource FOR EXPORT;
93 # List of files on source
94 tsource.cfg
95 tsource.ibd
96 # List of files on destination
97 tsource.ibd
98 UNLOCK TABLES;
99 USE db_destination;
100 ALTER TABLE tsource IMPORT TABLESPACE;
101 SELECT COUNT(*) FROM tsource;
102 COUNT(*)
103 499
104 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
105 WHERE NAME LIKE '%tsource%';
106 NAME FILE_SIZE AUTOEXTEND_SIZE
107 db_source/tsource 37748736 4194304
108 db_destination/tsource 37748736 4194304
109 DROP TABLE tsource;
110 USE db_source;
111 DROP TABLE tsource;
112 # Scenario-3:
113 # Create an empty table on source with autoextend_size 8m
114 # Create a table with the same schema as the source but with autoextend_size 4m
115 # Discard tablespace on destination
116 # Flush source table for export
117 # Copy .cfg and .ibd file from source to destination
118 # UNLOCK TABLES
119 # Import tablespace on the destination
120 # Load data on the destination table
121
122 USE db_source;
123 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M;
124 USE db_destination;
125 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4m;
126 ALTER TABLE tsource DISCARD TABLESPACE;
127 USE db_source;
128 FLUSH TABLE tsource FOR EXPORT;
129 # List of files on source
130 tsource.cfg
131 tsource.ibd
132 # List of files on destination
133 tsource.cfg
134 tsource.ibd
135 UNLOCK TABLES;
136 USE db_destination;
137 ALTER TABLE tsource IMPORT TABLESPACE;
138 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
139 WHERE NAME LIKE '%tsource%';
140 NAME FILE_SIZE AUTOEXTEND_SIZE
141 db_source/tsource 8388608 8388608
142 db_destination/tsource 8388608 4194304
143 call bulk_insert_dest();
144 SELECT COUNT(*) FROM tsource;
145 COUNT(*)
146 499
147 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
148 WHERE NAME LIKE '%tsource%';
149 NAME FILE_SIZE AUTOEXTEND_SIZE
150 db_source/tsource 8388608 8388608
151 db_destination/tsource 37748736 4194304
152 DROP TABLE tsource;
153 USE db_source;
154 DROP TABLE tsource;
155 # Scenario-4:
156 # Create a table with encryption turned on with autoextend_size as 8M
157 # Create a table on the destination with the same schema as the source but with autoextend_size 4M
158 # Discard tablespace on destination
159 # Flush source table for export
160 # Copy .cfg, .cfp and .ibd file from source to destination
161 # UNLOCK TABLES
162 # Import tablespace on the destination
163 # Load data on the destination table
164
165 USE db_source;
166 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M, ENCRYPTION='Y';
167 call bulk_insert_source();
168 SELECT COUNT(*) FROM tsource;
169 COUNT(*)
170 499
171 USE db_destination;
172 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4m, ENCRYPTION='Y';
173 ALTER TABLE tsource DISCARD TABLESPACE;
174 USE db_source;
175 FLUSH TABLE tsource FOR EXPORT;
176 # List of files on source
177 tsource.cfg
178 tsource.cfp
179 tsource.ibd
180 # List of files on destination
181 tsource.cfg
182 tsource.cfp
183 tsource.ibd
184 UNLOCK TABLES;
185 USE db_destination;
186 ALTER TABLE tsource IMPORT TABLESPACE;
187 SELECT COUNT(*) FROM tsource;
188 COUNT(*)
189 499
190 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
191 WHERE NAME LIKE '%tsource%';
192 NAME FILE_SIZE AUTOEXTEND_SIZE
193 db_source/tsource 41943040 8388608
194 db_destination/tsource 41943040 4194304
195 DROP TABLE tsource;
196 USE db_source;
197 DROP TABLE tsource;
198 # Scenario-5:
199 # Create a table with encryption turned on with autoextend_size as 8M
200 # Create a table on the destination with the same schema as the source but with default autoextend_size
201 # Discard tablespace on destination
202 # Flush source table for export
203 # Copy .cfg and .ibd file from source to destination but do not copy .cfp file
204 # UNLOCK TABLES
205 # Import tablespace on the destination
206 # Load data on the destination table
207
208 USE db_source;
209 CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M, ENCRYPTION='Y';
210 call bulk_insert_source();
211 SELECT COUNT(*) FROM tsource;
212 COUNT(*)
213 499
214 USE db_destination;
215 CREATE TABLE tsource (c1 INT, c2 TEXT) ENCRYPTION='Y';
216 ALTER TABLE tsource DISCARD TABLESPACE;
217 USE db_source;
218 FLUSH TABLE tsource FOR EXPORT;
219 # List of files on source
220 tsource.cfg
221 tsource.cfp
222 tsource.ibd
223 # List of files on destination
224 tsource.cfg
225 tsource.ibd
226 UNLOCK TABLES;
227 USE db_destination;
228 ALTER TABLE tsource IMPORT TABLESPACE;
229 ERROR HY000: Schema mismatch (Table is in an encrypted tablespace, but the encryption meta-data file cannot be found while importing.)
230 DROP TABLE tsource;
231 # List of files to be removed from destination
232 tsource.cfg
233 tsource.ibd
234 CREATE TABLE tsource (c1 INT, c2 TEXT) ENCRYPTION='Y';
235 ALTER TABLE tsource DISCARD TABLESPACE;
236 USE db_source;
237 FLUSH TABLE tsource FOR EXPORT;
238 SELECT COUNT(*) FROM tsource;
239 COUNT(*)
240 499
241 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
242 WHERE NAME LIKE '%tsource%';
243 NAME FILE_SIZE AUTOEXTEND_SIZE
244 db_source/tsource 41943040 8388608
245 db_destination/tsource 0 0
246 # List of files on source
247 tsource.cfg
248 tsource.cfp
249 tsource.ibd
250 # List of files on destination
251 tsource.cfg
252 tsource.cfp
253 tsource.ibd
254 UNLOCK TABLES;
255 USE db_destination;
256 ALTER TABLE tsource IMPORT TABLESPACE;
257 SELECT COUNT(*) FROM tsource;
258 COUNT(*)
259 499
260 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
261 WHERE NAME LIKE '%tsource%';
262 NAME FILE_SIZE AUTOEXTEND_SIZE
263 db_source/tsource 41943040 8388608
264 db_destination/tsource 41943040 0
265 DROP TABLE tsource;
266 USE db_source;
267 DROP TABLE tsource;
268 #
269 # IMPORT/EXPORT for partitioned tables with autoextend_size
270 #
271
272 # Scenario-1:
273 # Create and populate a partitioned table on the source
274 # Create similar partitioned table on the destination
275 # Discard tablespace on the destination
276 # Flush table for export on source
277 # Copy .cfg and .ibd files from source to destination
278 # UNLOCK TABLES
279 # Import tablespace on the destination
280 # Verify the contents of the source and destination tables
281 # Verify that the autoextend_size values are retained
282 # on all the partitions after import
283
284 USE db_source;
285 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
286 PARTITION BY RANGE(c1) (
287 PARTITION p0 VALUES LESS THAN (250),
288 PARTITION p1 VALUES LESS THAN (600),
289 PARTITION p2 VALUES LESS THAN MAXVALUE);
290 call bulk_insert_source();
291 SELECT COUNT(*) FROM tsource;
292 COUNT(*)
293 499
294 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
295 WHERE NAME LIKE '%tsource%';
296 NAME FILE_SIZE AUTOEXTEND_SIZE
297 db_source/tsource#p#p0 20971520 4194304
298 db_source/tsource#p#p1 20971520 4194304
299 db_source/tsource#p#p2 4194304 4194304
300 USE db_destination;
301 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M
302 PARTITION BY RANGE(c1) (
303 PARTITION p0 VALUES LESS THAN (250),
304 PARTITION p1 VALUES LESS THAN (600),
305 PARTITION p2 VALUES LESS THAN MAXVALUE);
306 ALTER TABLE tsource DISCARD TABLESPACE;
307 USE db_source;
308 FLUSH TABLE tsource FOR EXPORT;
309 # List of files on source
310 tsource#p#p0.cfg
311 tsource#p#p0.ibd
312 tsource#p#p1.cfg
313 tsource#p#p1.ibd
314 tsource#p#p2.cfg
315 tsource#p#p2.ibd
316 # List of files on destination
317 tsource#p#p0.cfg
318 tsource#p#p0.ibd
319 tsource#p#p1.cfg
320 tsource#p#p1.ibd
321 tsource#p#p2.cfg
322 tsource#p#p2.ibd
323 UNLOCK TABLES;
324 USE db_destination;
325 ALTER TABLE tsource IMPORT TABLESPACE;
326 SELECT COUNT(*) FROM tsource;
327 COUNT(*)
328 499
329 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
330 WHERE NAME LIKE '%tsource%';
331 NAME FILE_SIZE AUTOEXTEND_SIZE
332 db_source/tsource#p#p0 20971520 4194304
333 db_source/tsource#p#p1 20971520 4194304
334 db_source/tsource#p#p2 4194304 4194304
335 db_destination/tsource#p#p0 20971520 8388608
336 db_destination/tsource#p#p1 20971520 8388608
337 db_destination/tsource#p#p2 4194304 8388608
338 DROP TABLE tsource;
339 USE db_source;
340 DROP TABLE tsource;
341 # Scenario-2:
342 # Same as scenario-1, but without copying the .cfg files to the destination db
343
344 USE db_source;
345 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
346 PARTITION BY RANGE(c1) (
347 PARTITION p0 VALUES LESS THAN (250),
348 PARTITION p1 VALUES LESS THAN (600),
349 PARTITION p2 VALUES LESS THAN MAXVALUE);
350 call bulk_insert_source();
351 SELECT COUNT(*) FROM tsource;
352 COUNT(*)
353 499
354 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
355 WHERE NAME LIKE '%tsource%';
356 NAME FILE_SIZE AUTOEXTEND_SIZE
357 db_source/tsource#p#p0 20971520 4194304
358 db_source/tsource#p#p1 20971520 4194304
359 db_source/tsource#p#p2 4194304 4194304
360 USE db_destination;
361 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M
362 PARTITION BY RANGE(c1) (
363 PARTITION p0 VALUES LESS THAN (250),
364 PARTITION p1 VALUES LESS THAN (600),
365 PARTITION p2 VALUES LESS THAN MAXVALUE);
366 ALTER TABLE tsource DISCARD TABLESPACE;
367 USE db_source;
368 FLUSH TABLE tsource FOR EXPORT;
369 # List of files on source
370 tsource#p#p0.cfg
371 tsource#p#p0.ibd
372 tsource#p#p1.cfg
373 tsource#p#p1.ibd
374 tsource#p#p2.cfg
375 tsource#p#p2.ibd
376 # List of files on destination
377 tsource#p#p0.ibd
378 tsource#p#p1.ibd
379 tsource#p#p2.ibd
380 UNLOCK TABLES;
381 USE db_destination;
382 ALTER TABLE tsource IMPORT TABLESPACE;
383 SELECT COUNT(*) FROM tsource;
384 COUNT(*)
385 499
386 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
387 WHERE NAME LIKE '%tsource%';
388 NAME FILE_SIZE AUTOEXTEND_SIZE
389 db_source/tsource#p#p0 20971520 4194304
390 db_source/tsource#p#p1 20971520 4194304
391 db_source/tsource#p#p2 4194304 4194304
392 db_destination/tsource#p#p0 20971520 8388608
393 db_destination/tsource#p#p1 20971520 8388608
394 db_destination/tsource#p#p2 4194304 8388608
395 DROP TABLE tsource;
396 USE db_source;
397 DROP TABLE tsource;
398 # Scenario-3:
399 # Same as scenario-1, but without copying .cfg file for one partition
400
401 USE db_source;
402 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M
403 PARTITION BY RANGE(c1) (
404 PARTITION p0 VALUES LESS THAN (250),
405 PARTITION p1 VALUES LESS THAN (600),
406 PARTITION p2 VALUES LESS THAN MAXVALUE);
407 call bulk_insert_source();
408 SELECT COUNT(*) FROM tsource;
409 COUNT(*)
410 499
411 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
412 WHERE NAME LIKE '%tsource%';
413 NAME FILE_SIZE AUTOEXTEND_SIZE
414 db_source/tsource#p#p0 20971520 4194304
415 db_source/tsource#p#p1 20971520 4194304
416 db_source/tsource#p#p2 4194304 4194304
417 USE db_destination;
418 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M
419 PARTITION BY RANGE(c1) (
420 PARTITION p0 VALUES LESS THAN (250),
421 PARTITION p1 VALUES LESS THAN (600),
422 PARTITION p2 VALUES LESS THAN MAXVALUE);
423 ALTER TABLE tsource DISCARD TABLESPACE;
424 USE db_source;
425 FLUSH TABLE tsource FOR EXPORT;
426 # List of files on source
427 tsource#p#p0.cfg
428 tsource#p#p0.ibd
429 tsource#p#p1.cfg
430 tsource#p#p1.ibd
431 tsource#p#p2.cfg
432 tsource#p#p2.ibd
433 # List of files on destination
434 tsource#p#p0.cfg
435 tsource#p#p0.ibd
436 tsource#p#p1.ibd
437 tsource#p#p2.cfg
438 tsource#p#p2.ibd
439 UNLOCK TABLES;
440 USE db_destination;
441 ALTER TABLE tsource IMPORT TABLESPACE;
442 SELECT COUNT(*) FROM tsource;
443 COUNT(*)
444 499
445 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
446 WHERE NAME LIKE '%tsource%';
447 NAME FILE_SIZE AUTOEXTEND_SIZE
448 db_source/tsource#p#p0 20971520 4194304
449 db_source/tsource#p#p1 20971520 4194304
450 db_source/tsource#p#p2 4194304 4194304
451 db_destination/tsource#p#p0 20971520 8388608
452 db_destination/tsource#p#p1 20971520 8388608
453 db_destination/tsource#p#p2 4194304 8388608
454 DROP TABLE tsource;
455 USE db_source;
456 DROP TABLE tsource;
457 # Scenario-4:
458 # Create a partitioned table on the source with encryption turned on and autoextend_size 4m
459 # Load the table on the source db
460 # Create a partitioned table on the destination with encryption turned on and autoextend_size
461 # 4m and schema similar to that on the source db
462 # ALTER TABLE ... DISCARD TABLESPACE on destination db
463 # FLUSH TABLE ... FOR EXPORT on source db
464 # Copy the .ibd, .cfg and .cfp files from source db to destination db
465 # UNLOCK TABLES
466 # ALTER TABLE ... IMPORT TABLESPACE on destination db
467 # Verify that the data is successfully imported on the destination db
468
469 USE db_source;
470 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
471 PARTITION BY RANGE(c1) (
472 PARTITION p0 VALUES LESS THAN (250),
473 PARTITION p1 VALUES LESS THAN (600),
474 PARTITION p2 VALUES LESS THAN MAXVALUE);
475 call bulk_insert_source();
476 SELECT COUNT(*) FROM tsource;
477 COUNT(*)
478 499
479 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
480 WHERE NAME LIKE '%tsource%';
481 NAME FILE_SIZE AUTOEXTEND_SIZE
482 db_source/tsource#p#p0 20971520 4194304
483 db_source/tsource#p#p1 20971520 4194304
484 db_source/tsource#p#p2 4194304 4194304
485 USE db_destination;
486 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
487 PARTITION BY RANGE(c1) (
488 PARTITION p0 VALUES LESS THAN (250),
489 PARTITION p1 VALUES LESS THAN (600),
490 PARTITION p2 VALUES LESS THAN MAXVALUE);
491 ALTER TABLE tsource DISCARD TABLESPACE;
492 USE db_source;
493 FLUSH TABLE tsource FOR EXPORT;
494 # List of files on source
495 tsource#p#p0.cfg
496 tsource#p#p0.cfp
497 tsource#p#p0.ibd
498 tsource#p#p1.cfg
499 tsource#p#p1.cfp
500 tsource#p#p1.ibd
501 tsource#p#p2.cfg
502 tsource#p#p2.cfp
503 tsource#p#p2.ibd
504 # List of files on destination
505 tsource#p#p0.cfg
506 tsource#p#p0.cfp
507 tsource#p#p0.ibd
508 tsource#p#p1.cfg
509 tsource#p#p1.cfp
510 tsource#p#p1.ibd
511 tsource#p#p2.cfg
512 tsource#p#p2.cfp
513 tsource#p#p2.ibd
514 UNLOCK TABLES;
515 USE db_destination;
516 ALTER TABLE tsource IMPORT TABLESPACE;
517 SELECT COUNT(*) FROM tsource;
518 COUNT(*)
519 499
520 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
521 WHERE NAME LIKE '%tsource%';
522 NAME FILE_SIZE AUTOEXTEND_SIZE
523 db_source/tsource#p#p0 20971520 4194304
524 db_source/tsource#p#p1 20971520 4194304
525 db_source/tsource#p#p2 4194304 4194304
526 db_destination/tsource#p#p0 20971520 4194304
527 db_destination/tsource#p#p1 20971520 4194304
528 db_destination/tsource#p#p2 4194304 4194304
529 DROP TABLE tsource;
530 USE db_source;
531 DROP TABLE tsource;
532 # Scenario-5:
533 # Same as scenario-4, but do not copy the .cfp files from the source
534 # to the destination db
535 # Verify that the data is successfully imported on the destination db
536
537 USE db_source;
538 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
539 PARTITION BY RANGE(c1) (
540 PARTITION p0 VALUES LESS THAN (250),
541 PARTITION p1 VALUES LESS THAN (600),
542 PARTITION p2 VALUES LESS THAN MAXVALUE);
543 call bulk_insert_source();
544 SELECT COUNT(*) FROM tsource;
545 COUNT(*)
546 499
547 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
548 WHERE NAME LIKE '%tsource%';
549 NAME FILE_SIZE AUTOEXTEND_SIZE
550 db_source/tsource#p#p0 20971520 4194304
551 db_source/tsource#p#p1 20971520 4194304
552 db_source/tsource#p#p2 4194304 4194304
553 USE db_destination;
554 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
555 PARTITION BY RANGE(c1) (
556 PARTITION p0 VALUES LESS THAN (250),
557 PARTITION p1 VALUES LESS THAN (600),
558 PARTITION p2 VALUES LESS THAN MAXVALUE);
559 ALTER TABLE tsource DISCARD TABLESPACE;
560 USE db_source;
561 FLUSH TABLE tsource FOR EXPORT;
562 # List of files on source
563 tsource#p#p0.cfg
564 tsource#p#p0.cfp
565 tsource#p#p0.ibd
566 tsource#p#p1.cfg
567 tsource#p#p1.cfp
568 tsource#p#p1.ibd
569 tsource#p#p2.cfg
570 tsource#p#p2.cfp
571 tsource#p#p2.ibd
572 # List of files on destination
573 tsource#p#p0.cfg
574 tsource#p#p0.ibd
575 tsource#p#p1.cfg
576 tsource#p#p1.ibd
577 tsource#p#p2.cfg
578 tsource#p#p2.ibd
579 UNLOCK TABLES;
580 USE db_destination;
581 ALTER TABLE tsource IMPORT TABLESPACE;
582 ERROR HY000: Schema mismatch (Table is in an encrypted tablespace, but the encryption meta-data file cannot be found while importing.)
583 DROP TABLE tsource;
584 # List of files to be removed from destination
585 tsource#p#p0.cfg
586 tsource#p#p0.ibd
587 tsource#p#p1.cfg
588 tsource#p#p1.ibd
589 tsource#p#p2.cfg
590 tsource#p#p2.ibd
591 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
592 PARTITION BY RANGE(c1) (
593 PARTITION p0 VALUES LESS THAN (250),
594 PARTITION p1 VALUES LESS THAN (600),
595 PARTITION p2 VALUES LESS THAN MAXVALUE);
596 ALTER TABLE tsource DISCARD TABLESPACE;
597 USE db_source;
598 FLUSH TABLE tsource FOR EXPORT;
599 # List of files on source
600 tsource#p#p0.cfg
601 tsource#p#p0.cfp
602 tsource#p#p0.ibd
603 tsource#p#p1.cfg
604 tsource#p#p1.cfp
605 tsource#p#p1.ibd
606 tsource#p#p2.cfg
607 tsource#p#p2.cfp
608 tsource#p#p2.ibd
609 # List of files on destination
610 tsource#p#p0.cfg
611 tsource#p#p0.cfp
612 tsource#p#p0.ibd
613 tsource#p#p1.cfg
614 tsource#p#p1.cfp
615 tsource#p#p1.ibd
616 tsource#p#p2.cfg
617 tsource#p#p2.cfp
618 tsource#p#p2.ibd
619 UNLOCK TABLES;
620 USE db_destination;
621 ALTER TABLE tsource IMPORT TABLESPACE;
622 SELECT COUNT(*) FROM tsource;
623 COUNT(*)
624 499
625 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
626 WHERE NAME LIKE '%tsource%';
627 NAME FILE_SIZE AUTOEXTEND_SIZE
628 db_source/tsource#p#p0 20971520 4194304
629 db_source/tsource#p#p1 20971520 4194304
630 db_source/tsource#p#p2 4194304 4194304
631 db_destination/tsource#p#p0 20971520 4194304
632 db_destination/tsource#p#p1 20971520 4194304
633 db_destination/tsource#p#p2 4194304 4194304
634 DROP TABLE tsource;
635 USE db_source;
636 DROP TABLE tsource;
637 # Scenario-6:
638 # Same as scenario-4, but do not copy the .cfp and .cfg files from the source
639 # to the destination db
640 # Verify that the data is successfully imported on the destination db
641
642 USE db_source;
643 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
644 PARTITION BY RANGE(c1) (
645 PARTITION p0 VALUES LESS THAN (250),
646 PARTITION p1 VALUES LESS THAN (600),
647 PARTITION p2 VALUES LESS THAN MAXVALUE);
648 call bulk_insert_source();
649 SELECT COUNT(*) FROM tsource;
650 COUNT(*)
651 499
652 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
653 WHERE NAME LIKE '%tsource%';
654 NAME FILE_SIZE AUTOEXTEND_SIZE
655 db_source/tsource#p#p0 20971520 4194304
656 db_source/tsource#p#p1 20971520 4194304
657 db_source/tsource#p#p2 4194304 4194304
658 USE db_destination;
659 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
660 PARTITION BY RANGE(c1) (
661 PARTITION p0 VALUES LESS THAN (250),
662 PARTITION p1 VALUES LESS THAN (600),
663 PARTITION p2 VALUES LESS THAN MAXVALUE);
664 ALTER TABLE tsource DISCARD TABLESPACE;
665 USE db_source;
666 FLUSH TABLE tsource FOR EXPORT;
667 # List of files on source
668 tsource#p#p0.cfg
669 tsource#p#p0.cfp
670 tsource#p#p0.ibd
671 tsource#p#p1.cfg
672 tsource#p#p1.cfp
673 tsource#p#p1.ibd
674 tsource#p#p2.cfg
675 tsource#p#p2.cfp
676 tsource#p#p2.ibd
677 # List of files on destination
678 tsource#p#p0.ibd
679 tsource#p#p1.ibd
680 tsource#p#p2.ibd
681 UNLOCK TABLES;
682 USE db_destination;
683 ALTER TABLE tsource IMPORT TABLESPACE;
684 ERROR HY000: Schema mismatch (Table is in an encrypted tablespace, but the encryption meta-data file cannot be found while importing.)
685 DROP TABLE tsource;
686 # List of files to be removed from destination
687 tsource#p#p0.ibd
688 tsource#p#p1.ibd
689 tsource#p#p2.ibd
690 CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y'
691 PARTITION BY RANGE(c1) (
692 PARTITION p0 VALUES LESS THAN (250),
693 PARTITION p1 VALUES LESS THAN (600),
694 PARTITION p2 VALUES LESS THAN MAXVALUE);
695 ALTER TABLE tsource DISCARD TABLESPACE;
696 USE db_source;
697 FLUSH TABLE tsource FOR EXPORT;
698 # List of files on source
699 tsource#p#p0.cfg
700 tsource#p#p0.cfp
701 tsource#p#p0.ibd
702 tsource#p#p1.cfg
703 tsource#p#p1.cfp
704 tsource#p#p1.ibd
705 tsource#p#p2.cfg
706 tsource#p#p2.cfp
707 tsource#p#p2.ibd
708 # List of files on destination
709 tsource#p#p0.cfg
710 tsource#p#p0.cfp
711 tsource#p#p0.ibd
712 tsource#p#p1.cfg
713 tsource#p#p1.cfp
714 tsource#p#p1.ibd
715 tsource#p#p2.cfg
716 tsource#p#p2.cfp
717 tsource#p#p2.ibd
718 UNLOCK TABLES;
719 USE db_destination;
720 ALTER TABLE tsource IMPORT TABLESPACE;
721 SELECT COUNT(*) FROM tsource;
722 COUNT(*)
723 499
724 SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces
725 WHERE NAME LIKE '%tsource%';
726 NAME FILE_SIZE AUTOEXTEND_SIZE
727 db_source/tsource#p#p0 20971520 4194304
728 db_source/tsource#p#p1 20971520 4194304
729 db_source/tsource#p#p2 4194304 4194304
730 db_destination/tsource#p#p0 20971520 4194304
731 db_destination/tsource#p#p1 20971520 4194304
732 db_destination/tsource#p#p2 4194304 4194304
733 DROP TABLE tsource;
734 USE db_source;
735 DROP TABLE tsource;
736 # Cleanup
737 USE db_source;
738 DROP PROCEDURE bulk_insert_source;
739 DROP DATABASE db_source;
740 USE db_destination;
741 DROP PROCEDURE bulk_insert_dest;
742 DROP DATABASE db_destination;