Debian Codegraph @

mysql-test/suite/innodb/t/innodb_autoextend_import_export.test

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