Debian Codegraph @

mysql-test/suite/innodb/include/instant_ddl_import.inc

raw
1 ##########################################################################
2 # Test script to test EXPORT/IMPORT for table having INSTANT ADD/DROP
3 # columns.
4 #
5 # $row_format is to be set to the row_format on which test is to be run.
6 ##########################################################################
7
8 --write_file $MYSQLTEST_VARDIR/tmp/export.inc EOF
9 FLUSH TABLE t1 FOR EXPORT;
10 --echo # Copy cfg/ibd files into temp location
11 --copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/t1.cfg_back
12 --copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/t1.ibd_back
13 UNLOCK TABLES;
14 EOF
15
16 --write_file $MYSQLTEST_VARDIR/tmp/import.inc EOF
17 ALTER TABLE t1 DISCARD TABLESPACE;
18 --echo # Copy cfg/ibd files from temp location
19 --copy_file $MYSQLD_DATADIR/t1.cfg_back $MYSQLD_DATADIR/test/t1.cfg
20 --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t1.ibd
21 --error $err_no
22 ALTER TABLE t1 IMPORT TABLESPACE;
23 EOF
24
25 --let $MYSQLD_DATADIR = `SELECT @@datadir`
26
27 --echo # Create a table with 2 columns. [c1, c2]
28 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=$row_format;
29 INSERT INTO t1 values ("row1_c1", "a");
30 SELECT * FROM t1 ORDER BY c1;
31
32 --echo # Add a new column at the end. [c1, c2, +c3]
33 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
34 INSERT INTO t1 values ("row2_c1", "b", "row2_c3");
35 SELECT * FROM t1 ORDER BY c1;
36
37 --echo # Add a new column in between. [c1, +c4, c2, c3]
38 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
39 INSERT INTO t1 values ("row3_c1", "row3_c4", "c", "row3_c3");
40 SELECT * FROM t1 ORDER BY c1;
41
42 --echo # ------------------------------------------------------------
43 --echo # Scenario 1.1 : Same table with tablespace discarded
44 --echo # ------------------------------------------------------------
45 --echo # EXPORT
46 --source $MYSQLTEST_VARDIR/tmp/export.inc
47
48 --echo # IMPORT
49 --let $err_no=0
50 --source $MYSQLTEST_VARDIR/tmp/import.inc
51
52 SELECT * FROM t1 ORDER BY c1;
53 --let $table_name=t1
54 --source suite/innodb/include/print_instant_metadata.inc
55
56 --remove_file $MYSQLD_DATADIR/t1.cfg_back
57 --remove_file $MYSQLD_DATADIR/t1.ibd_back
58
59 --echo # ------------------------------------------------------------
60 --echo # Scenario 1.2 : New table created with like
61 --echo # ------------------------------------------------------------
62 --echo # EXPORT
63 --source $MYSQLTEST_VARDIR/tmp/export.inc
64
65 CREATE TABLE t2 like t1;
66 DROP TABLE t1;
67 ALTER TABLE t2 RENAME to t1;
68 SHOW CREATE TABLE t1;
69
70 --echo # IMPORT
71 --let $err_no=0
72 --source $MYSQLTEST_VARDIR/tmp/import.inc
73
74 SELECT * FROM t1;
75 --let $table_name=t1
76 --source suite/innodb/include/print_instant_metadata.inc
77
78 --remove_file $MYSQLD_DATADIR/t1.cfg_back
79 --remove_file $MYSQLD_DATADIR/t1.ibd_back
80
81
82 --echo # ------------------------------------------------------------
83 --echo # Drop a column somewhere in between. [c1, c4, -c2, c3]
84 --echo # ------------------------------------------------------------
85 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
86
87 --let $table_name=t1
88 --source suite/innodb/include/print_instant_metadata.inc
89
90 SELECT * FROM t1 ORDER BY c1;
91
92 INSERT INTO t1 values ("row4_c1", "row4_c4", "row4_c3");
93 SELECT * FROM t1 ORDER BY c1;
94
95 --echo # ------------------------------------------------------------
96 --echo # Scenario 2.1 : Same table with tablespace discarded
97 --echo # ------------------------------------------------------------
98 --echo # EXPORT
99 --source $MYSQLTEST_VARDIR/tmp/export.inc
100
101 --echo # IMPORT
102 --let $err_no=0
103 --source $MYSQLTEST_VARDIR/tmp/import.inc
104
105 SELECT * FROM t1 ORDER BY c1;
106 --let $table_name=t1
107 --source suite/innodb/include/print_instant_metadata.inc
108
109 --remove_file $MYSQLD_DATADIR/t1.cfg_back
110 --remove_file $MYSQLD_DATADIR/t1.ibd_back
111
112 --echo # ------------------------------------------------------------
113 --echo # Scenario 2.2 : New table created with like
114 --echo # ------------------------------------------------------------
115 --echo # EXPORT
116 --source $MYSQLTEST_VARDIR/tmp/export.inc
117
118 CREATE TABLE t2 like t1;
119 DROP TABLE t1;
120 ALTER TABLE t2 RENAME to t1;
121 SHOW CREATE TABLE t1;
122
123 --echo # IMPORT
124 --let $err_no=0
125 --source $MYSQLTEST_VARDIR/tmp/import.inc
126
127 SELECT * FROM t1;
128 --let $table_name=t1
129 --source suite/innodb/include/print_instant_metadata.inc
130
131 --remove_file $MYSQLD_DATADIR/t1.cfg_back
132 --remove_file $MYSQLD_DATADIR/t1.ibd_back
133 DROP TABLE t1;
134
135 --echo # ------------------------------------------------------------
136 --echo # Scenarios 3 : Error scenarios
137 --echo # ------------------------------------------------------------
138
139 --echo # ------------------------------------------------------------
140 --echo # Scenario 3.1 : Target table has INSTANT, source doesn't
141 --echo # ------------------------------------------------------------
142 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=$row_format;
143 INSERT INTO t1 values ("row1_c1", "row1_c2");
144 SELECT * FROM t1 ORDER BY c1;
145
146 --echo # EXPORT
147 --source $MYSQLTEST_VARDIR/tmp/export.inc
148 DROP TABLE t1;
149
150 --eval CREATE TABLE t1 (c1 char(20) KEY) ROW_FORMAT=$row_format;
151 ALTER TABLE t1 ADD COLUMN c2 char(20) DEFAULT "def_c2", ALGORITHM=INSTANT;
152
153 --echo # IMPORT
154 --let $err_no=ER_TABLE_SCHEMA_MISMATCH
155 --source $MYSQLTEST_VARDIR/tmp/import.inc
156
157 --remove_file $MYSQLD_DATADIR/t1.cfg_back
158 --remove_file $MYSQLD_DATADIR/t1.ibd_back
159 --remove_file $MYSQLD_DATADIR/test/t1.cfg
160 --remove_file $MYSQLD_DATADIR/test/t1.ibd
161 DROP TABLE t1;
162
163 --echo # ------------------------------------------------------------
164 --echo # Scenario 3.2 : Both has INSTANT, but different instant version
165 --echo # ------------------------------------------------------------
166 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=$row_format;
167 INSERT INTO t1 values ("row1_c1", "row1_c2");
168 SELECT * FROM t1 ORDER BY c1;
169 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
170 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
171 SELECT * FROM t1 ORDER BY c1;
172 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
173 INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3");
174 SELECT * FROM t1 ORDER BY c1;
175
176 --echo # EXPORT
177 --source $MYSQLTEST_VARDIR/tmp/export.inc
178 DROP TABLE t1;
179
180 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=$row_format;
181 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
182
183 --echo # IMPORT
184 --let $err_no=ER_TABLE_SCHEMA_MISMATCH
185 --source $MYSQLTEST_VARDIR/tmp/import.inc
186
187 --remove_file $MYSQLD_DATADIR/t1.cfg_back
188 --remove_file $MYSQLD_DATADIR/t1.ibd_back
189 --remove_file $MYSQLD_DATADIR/test/t1.cfg
190 --remove_file $MYSQLD_DATADIR/test/t1.ibd
191 DROP TABLE t1;
192
193 --echo # ------------------------------------------------------------
194 --echo # Scenario 3.3 : Both has INSTANT, but different col count
195 --echo # ------------------------------------------------------------
196 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=$row_format;
197 INSERT INTO t1 values ("row1_c1", "row1_c2");
198 SELECT * FROM t1 ORDER BY c1;
199 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
200 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
201 SELECT * FROM t1 ORDER BY c1;
202
203 --echo # EXPORT
204 --source $MYSQLTEST_VARDIR/tmp/export.inc
205 DROP TABLE t1;
206
207 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=$row_format;
208 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
209
210 --echo # IMPORT
211 --let $err_no=ER_TABLE_SCHEMA_MISMATCH
212 --source $MYSQLTEST_VARDIR/tmp/import.inc
213
214 --remove_file $MYSQLD_DATADIR/t1.cfg_back
215 --remove_file $MYSQLD_DATADIR/t1.ibd_back
216 --remove_file $MYSQLD_DATADIR/test/t1.cfg
217 --remove_file $MYSQLD_DATADIR/test/t1.ibd
218 DROP TABLE t1;
219
220 --echo # ------------------------------------------------------------
221 --echo # Scenario 3.4 : Both has INSTANT, but different col count
222 --echo # ------------------------------------------------------------
223 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=$row_format;
224 INSERT INTO t1 values ("row1_c1", "row1_c2");
225 SELECT * FROM t1 ORDER BY c1;
226 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
227 INSERT INTO t1 values ("row2_c1", "row2_c4", "row2_c2", "row2_c3");
228 SELECT * FROM t1 ORDER BY c1;
229
230 --echo # EXPORT
231 --source $MYSQLTEST_VARDIR/tmp/export.inc
232 DROP TABLE t1;
233
234 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=$row_format;
235 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
236
237 --echo # IMPORT
238 --let $err_no=ER_TABLE_SCHEMA_MISMATCH
239 --source $MYSQLTEST_VARDIR/tmp/import.inc
240
241 --remove_file $MYSQLD_DATADIR/t1.cfg_back
242 --remove_file $MYSQLD_DATADIR/t1.ibd_back
243 --remove_file $MYSQLD_DATADIR/test/t1.cfg
244 --remove_file $MYSQLD_DATADIR/test/t1.ibd
245 DROP TABLE t1;
246
247 --echo # ------------------------------------------------------------
248 --echo # Scenario 3.5 : Both has INSTANT, but different default value
249 --echo # ------------------------------------------------------------
250 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=$row_format;
251 INSERT INTO t1 values ("row1_c1", "row1_c2");
252 SELECT * FROM t1 ORDER BY c1;
253 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
254 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
255 SELECT * FROM t1 ORDER BY c1;
256
257 --echo # EXPORT
258 --source $MYSQLTEST_VARDIR/tmp/export.inc
259 DROP TABLE t1;
260
261 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=$row_format;
262 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "newdefault_c3", ALGORITHM=INSTANT;
263
264 --echo # IMPORT
265 --let $err_no=ER_TABLE_SCHEMA_MISMATCH
266 --source $MYSQLTEST_VARDIR/tmp/import.inc
267
268 --remove_file $MYSQLD_DATADIR/t1.cfg_back
269 --remove_file $MYSQLD_DATADIR/t1.ibd_back
270 --remove_file $MYSQLD_DATADIR/test/t1.cfg
271 --remove_file $MYSQLD_DATADIR/test/t1.ibd
272 DROP TABLE t1;
273
274 --echo ###########
275 --echo # CLEANUP #
276 --echo ###########
277 --remove_file $MYSQLTEST_VARDIR/tmp/import.inc
278 --remove_file $MYSQLTEST_VARDIR/tmp/export.inc