Debian Codegraph @

mysql-test/suite/parts/inc/partition_auto_increment_import.inc

raw
1 # NOTE: Currently run for InnoDB Storage Engine only
2
3 --echo # test auto increment with export import
4 let $MYSQLD_DATADIR=`SELECT @@datadir`;
5
6 SET SESSION information_schema_stats_expiry=0;
7
8 eval CREATE TABLE t1 (
9 col_1_int int AUTO_INCREMENT,
10 col_2_varchar VARCHAR (20),
11 primary key (col_1_int))
12 ENGINE = $engine
13 PARTITION BY HASH (col_1_int)
14 PARTITIONS 3;
15
16 SHOW CREATE TABLE t1;
17
18 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE
19 TABLE_SCHEMA='test' AND TABLE_NAME='t1';
20
21 --echo # Insert values in all partitions
22 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
23 SELECT * FROM t1 ORDER BY col_1_int;
24
25 --echo # Export table and save the data [auto_inc = 6]
26 SHOW CREATE TABLE t1;
27 FLUSH TABLES t1 FOR EXPORT;
28 --copy_file $MYSQLD_DATADIR/test/t1#p#p0.ibd $MYSQLD_DATADIR/test/t1#p#p0_backup.ibd
29 --copy_file $MYSQLD_DATADIR/test/t1#p#p1.ibd $MYSQLD_DATADIR/test/t1#p#p1_backup.ibd
30 --copy_file $MYSQLD_DATADIR/test/t1#p#p2.ibd $MYSQLD_DATADIR/test/t1#p#p2_backup.ibd
31 --copy_file $MYSQLD_DATADIR/test/t1#p#p0.cfg $MYSQLD_DATADIR/test/t1#p#p0_backup.cfg
32 --copy_file $MYSQLD_DATADIR/test/t1#p#p1.cfg $MYSQLD_DATADIR/test/t1#p#p1_backup.cfg
33 --copy_file $MYSQLD_DATADIR/test/t1#p#p2.cfg $MYSQLD_DATADIR/test/t1#p#p2_backup.cfg
34
35 UNLOCK TABLES;
36
37 --error ER_DUP_ENTRY
38 INSERT INTO t1 VALUES (5,'a5');
39
40 --echo # Check auto increment INSERT
41 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
42
43 SELECT * FROM t1 ORDER BY col_1_int;
44
45 --echo # Drop and create the table and import old data
46 DROP TABLE t1;
47 eval CREATE TABLE t1 (
48 col_1_int int AUTO_INCREMENT,
49 col_2_varchar VARCHAR (20),
50 primary key (col_1_int))
51 ENGINE = $engine
52 PARTITION BY HASH (col_1_int)
53 PARTITIONS 3;
54
55 ALTER TABLE t1 DISCARD TABLESPACE;
56 --move_file $MYSQLD_DATADIR/test/t1#p#p0_backup.ibd $MYSQLD_DATADIR/test/t1#p#p0.ibd
57 --move_file $MYSQLD_DATADIR/test/t1#p#p1_backup.ibd $MYSQLD_DATADIR/test/t1#p#p1.ibd
58 --move_file $MYSQLD_DATADIR/test/t1#p#p2_backup.ibd $MYSQLD_DATADIR/test/t1#p#p2.ibd
59 --move_file $MYSQLD_DATADIR/test/t1#p#p0_backup.cfg $MYSQLD_DATADIR/test/t1#p#p0.cfg
60 --move_file $MYSQLD_DATADIR/test/t1#p#p1_backup.cfg $MYSQLD_DATADIR/test/t1#p#p1.cfg
61 --move_file $MYSQLD_DATADIR/test/t1#p#p2_backup.cfg $MYSQLD_DATADIR/test/t1#p#p2.cfg
62
63 --echo # Import old table and saved data [auto_inc = 6]
64 ALTER TABLE t1 IMPORT TABLESPACE;
65 SHOW CREATE TABLE t1;
66
67 SELECT * FROM t1 ORDER BY col_1_int;
68
69 --error ER_DUP_ENTRY
70 INSERT INTO t1 VALUES (5,'a5');
71
72 --echo # Check auto increment INSERT
73 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
74
75 SELECT * FROM t1 ORDER BY col_1_int;
76
77 DROP TABLE t1;
78
79 --echo # test auto increment with export import and missing cfg file
80 eval CREATE TABLE t1 (
81 col_1_int int AUTO_INCREMENT,
82 col_2_varchar VARCHAR (20),
83 primary key (col_1_int))
84 ENGINE = $engine
85 PARTITION BY HASH (col_1_int)
86 PARTITIONS 3;
87
88 SHOW CREATE TABLE t1;
89
90 SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE
91 TABLE_SCHEMA='test' AND TABLE_NAME='t1';
92
93 --echo # Insert values in all partitions
94 INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5');
95 SELECT * FROM t1 ORDER BY col_1_int;
96
97 --echo # Export table and save the .ibd files only [auto_inc = 6]
98 SHOW CREATE TABLE t1;
99 FLUSH TABLES t1 FOR EXPORT;
100 --copy_file $MYSQLD_DATADIR/test/t1#p#p0.ibd $MYSQLD_DATADIR/test/t1#p#p0_backup.ibd
101 --copy_file $MYSQLD_DATADIR/test/t1#p#p1.ibd $MYSQLD_DATADIR/test/t1#p#p1_backup.ibd
102 --copy_file $MYSQLD_DATADIR/test/t1#p#p2.ibd $MYSQLD_DATADIR/test/t1#p#p2_backup.ibd
103
104 UNLOCK TABLES;
105
106 --error ER_DUP_ENTRY
107 INSERT INTO t1 VALUES (5,'a5');
108
109 --echo # Check auto increment INSERT
110 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
111
112 SELECT * FROM t1 ORDER BY col_1_int;
113
114 --echo # Drop and create the table and import old data
115 DROP TABLE t1;
116 eval CREATE TABLE t1 (
117 col_1_int int AUTO_INCREMENT,
118 col_2_varchar VARCHAR (20),
119 primary key (col_1_int))
120 ENGINE = $engine
121 PARTITION BY HASH (col_1_int)
122 PARTITIONS 3;
123
124 ALTER TABLE t1 DISCARD TABLESPACE;
125 --move_file $MYSQLD_DATADIR/test/t1#p#p0_backup.ibd $MYSQLD_DATADIR/test/t1#p#p0.ibd
126 --move_file $MYSQLD_DATADIR/test/t1#p#p1_backup.ibd $MYSQLD_DATADIR/test/t1#p#p1.ibd
127 --move_file $MYSQLD_DATADIR/test/t1#p#p2_backup.ibd $MYSQLD_DATADIR/test/t1#p#p2.ibd
128
129 --echo # Import old table and saved data from .ibd files [auto_inc = 6]
130 --replace_result #p# #P# #sp# #SP#
131 --replace_regex /Error opening '.*\/test\//Error opening '.\/test\//
132 ALTER TABLE t1 IMPORT TABLESPACE;
133 SHOW CREATE TABLE t1;
134
135 SELECT * FROM t1 ORDER BY col_1_int;
136
137 --error ER_DUP_ENTRY
138 INSERT INTO t1 VALUES (5,'a5');
139
140 --echo # Check auto increment INSERT
141 INSERT INTO t1(col_2_varchar) VALUES('a6'),('a7'),('a8');
142
143 SELECT * FROM t1 ORDER BY col_1_int;
144
145 DROP TABLE t1;
146 #------------------------------------------------------------------------------#