Debian Codegraph @

mysql-test/suite/rpl/include/start_alter_options.inc

raw
1 # This test will test all the option related to the Alter Table command
2 # NOTE not all alter statements will follow alter_algorithm since for some statements
3 # copy is only option
4 # parameters
5 # $alter_algorithm {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}
6 # $show_binlog
7 #
8 --let $force_needed= force ,
9 # Error that is caused by a particular ALTER's option combination
10 --let $alter_error = 0
11
12 --connection slave
13 stop slave;
14 --let $gtid_strict_mode= `select @@gtid_strict_mode`
15 --let $slave_parallel_threads= `select @@slave_parallel_threads`
16 --let $slave_parallel_mode= `select @@slave_parallel_mode`
17 change master to master_use_gtid= current_pos;
18 SET GLOBAL slave_parallel_threads=4;
19 set global slave_parallel_mode=optimistic;
20 set global gtid_strict_mode=1;
21 start slave;
22 --connection master
23 set binlog_alter_two_phase=true;
24 create table t1(a int , b int) engine=innodb charset=latin1;
25 create table a1(a int , b int) engine=myisam charset=latin1;
26 create temporary table tmp_tbl(a int, b int) engine=innodb charset=latin1;
27
28 # These are grammer rules for ALTER TABLE we will got through all alter table
29 # rules in this test.
30 # | ADD [COLUMN] [IF NOT EXISTS] col_name column_definition
31 # [FIRST | AFTER col_name ]
32 # | ADD [COLUMN] [IF NOT EXISTS] (col_name column_definition,...)
33 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
34 {
35 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
36 }
37 --error 0,$alter_error
38 --eval alter table t1 add column if not exists c int , $force_needed algorithm=$alter_algorithm
39 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
40 {
41 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
42 }
43 --error 0,$alter_error
44 --eval alter table t1 add column d int first, $force_needed algorithm=$alter_algorithm
45 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
46 {
47 --let $alter_error=ER_BAD_FIELD_ERROR
48 }
49 --error 0,$alter_error
50 --eval alter table t1 add column e int after c, $force_needed algorithm=$alter_algorithm
51 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
52 {
53 --let $alter_error=ER_BAD_FIELD_ERROR
54 }
55 --error 0,$alter_error
56 --eval alter table t1 add column f int after c, $force_needed add column g int first ,add column h char, algorithm=$alter_algorithm
57 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
58 {
59 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
60 }
61 --error 0,$alter_error
62 --eval alter table t1 drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm
63 if ($alter_algorithm == 'copy')
64 {
65 --eval alter table tmp_tbl add column if not exists c int , $force_needed algorithm=$alter_algorithm
66 --eval alter table tmp_tbl add column d int first, $force_needed algorithm=$alter_algorithm
67 --eval alter table tmp_tbl add column e int after c, $force_needed algorithm=$alter_algorithm
68 --eval alter table tmp_tbl add column f int after c, $force_needed add column g int first ,add column h char, algorithm=$alter_algorithm
69 --eval alter table tmp_tbl drop column c, drop column d, drop column e, drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm
70 }
71 --echo # show binlog and clear status
72 --sync_slave_with_master
73 if ($show_binlog)
74 {
75 --source include/show_binlog_events.inc
76 }
77 reset master;
78 --connection master
79
80
81 #
82 # | ADD {INDEX|KEY} [IF NOT EXISTS] [index_name]
83 # [index_type] (index_col_name,...) [index_option] ...
84 # | ADD [CONSTRAINT [symbol]]
85 # UNIQUE [INDEX|KEY] [index_name]
86 # [index_type] (index_col_name,...) [index_option] ...
87 #
88 # | ADD FULLTEXT [INDEX|KEY] [index_name]
89 # (index_col_name,...) [index_option] ...
90 # | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
91 # | DROP PRIMARY KEY
92 # | DROP {INDEX|KEY} [IF EXISTS] index_name
93 # | DROP FOREIGN KEY [IF EXISTS] fk_symbol
94 # | DROP CONSTRAINT [IF EXISTS] constraint_name
95 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
96 {
97 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
98 }
99 --error 0,$alter_error
100 --eval alter table t1 add column f int after b, $force_needed add column g int first ,add column h varchar(100), algorithm=$alter_algorithm
101 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
102 {
103 --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST
104 }
105 --error 0,$alter_error
106 --eval alter table t1 add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm
107 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
108 {
109 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
110 }
111 --error 0,$alter_error
112 --eval alter table t1 drop index index_1, $force_needed algorithm=$alter_algorithm
113 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
114 {
115 --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST
116 }
117 --error 0,$alter_error
118 --eval alter table t1 add unique key unique_1(g), $force_needed algorithm=$alter_algorithm
119 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
120 {
121 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
122 }
123 --error 0,$alter_error
124 --eval alter table t1 drop index unique_1, $force_needed algorithm=$alter_algorithm
125 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
126 {
127 --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST
128 }
129 --error 0,$alter_error
130 --eval alter table t1 add fulltext key f_1(h), $force_needed algorithm=$alter_algorithm
131 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
132 {
133 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
134 }
135 --error 0,$alter_error
136 --eval alter table t1 drop column f, drop column g , $force_needed algorithm=$alter_algorithm
137 if ($alter_algorithm == 'copy')
138 {
139 --eval alter table tmp_tbl add column f int after b, $force_needed add column g int first ,add column h varchar(100), algorithm=$alter_algorithm
140 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
141 {
142 --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST
143 }
144 --error 0,$alter_error
145 --eval alter table tmp_tbl add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm
146 --eval alter table tmp_tbl drop index index_1, $force_needed algorithm=$alter_algorithm
147 --eval alter table tmp_tbl add unique key unique_1(g), $force_needed algorithm=$alter_algorithm
148 --eval alter table tmp_tbl drop index unique_1, $force_needed algorithm=$alter_algorithm
149 --eval alter table tmp_tbl drop column f, drop column g , $force_needed algorithm=$alter_algorithm
150 }
151 # | ADD [CONSTRAINT [symbol]] PRIMARY KEY
152 # [index_type] (index_col_name,...) [index_option] ...
153 # primary key changes cant use inplace algorithm
154 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
155 {
156 --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST
157 }
158 --error 0,$alter_error
159 --eval alter table t1 add primary key(h), $force_needed algorithm=copy
160 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
161 {
162 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
163 }
164 --error 0,$alter_error
165 --eval alter table t1 drop primary key, $force_needed algorithm=copy
166 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
167 {
168 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
169 }
170 --error 0,$alter_error
171 --eval alter table t1 drop column h, $force_needed algorithm=copy
172 if ($alter_algorithm == 'copy')
173 {
174 --eval alter table tmp_tbl add primary key(h), $force_needed algorithm=copy
175 --eval alter table tmp_tbl drop primary key, $force_needed algorithm=copy
176 --eval alter table tmp_tbl drop column h, $force_needed algorithm=copy
177 }
178 --echo # show binlog and clear status
179 --sync_slave_with_master
180 if ($show_binlog)
181 {
182 --source include/show_binlog_events.inc
183 }
184 reset master;
185 --connection master
186
187 ## NOTE force_needed and algorithm will not be used for system versioning
188 # | ADD PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name)
189 # | ADD SYSTEM VERSIONING
190 # | DROP SYSTEM VERSIONING
191 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
192 {
193 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
194 }
195 --error 0,$alter_error
196 --eval alter table t1 add column f varchar(100) after b, add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm
197 --eval alter table t1 add period for system_time(f,h)
198 --eval alter table t1 add system versioning
199 --eval alter table t1 drop system versioning
200 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
201 {
202 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
203 }
204 --error 0,$alter_error
205 --eval alter table t1 drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm
206 --echo # show binlog and clear status
207 if ($alter_algorithm == 'copy')
208 {
209 --eval alter table tmp_tbl add column f varchar(100) after b, add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm
210 --eval alter table tmp_tbl drop column f, drop column g , drop column h, $force_needed algorithm=$alter_algorithm
211 }
212 --sync_slave_with_master
213 if ($show_binlog)
214 {
215 --source include/show_binlog_events.inc
216 }
217 reset master;
218 --connection master
219
220
221 # | ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
222 # | ALTER [COLUMN] col_name DROP DEFAULT
223 # | CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition
224 # [FIRST|AFTER col_name]
225 # | MODIFY [COLUMN] [IF EXISTS] col_name column_definition
226 # [FIRST | AFTER col_name]
227 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
228 {
229 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
230 }
231 --error 0,$alter_error
232 --eval alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm ;
233 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
234 {
235 --let $alter_error=ER_BAD_FIELD_ERROR
236 }
237 --error 0,$alter_error
238 --eval alter table t1 alter column f set default "****", $force_needed algorithm=$alter_algorithm ;
239 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
240 {
241 --let $alter_error=ER_BAD_FIELD_ERROR
242 }
243 --error 0,$alter_error
244 --eval alter table t1 alter column f drop default, $force_needed algorithm=$alter_algorithm ;
245 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
246 {
247 --let $alter_error=ER_BAD_FIELD_ERROR
248 }
249 --error 0,$alter_error
250 --eval alter table t1 change column g new_g char, $force_needed algorithm=copy;
251 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
252 {
253 --let $alter_error=ER_BAD_FIELD_ERROR
254 }
255 --error 0,$alter_error
256 --eval alter table t1 modify column h varchar(100), $force_needed algorithm=copy;
257 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
258 {
259 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
260 }
261 --error 0,$alter_error
262 --eval alter table t1 drop column new_g ,drop column f, drop column h, $force_needed algorithm=$alter_algorithm ;
263 if ($alter_algorithm == 'copy')
264 {
265 --eval alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm ;
266 --eval alter table tmp_tbl alter column f set default "****", $force_needed algorithm=$alter_algorithm ;
267 --eval alter table tmp_tbl alter column f drop default, $force_needed algorithm=$alter_algorithm ;
268 --eval alter table tmp_tbl change column g new_g char, $force_needed algorithm=copy;
269 --eval alter table tmp_tbl modify column h varchar(100), $force_needed algorithm=copy;
270 --eval alter table tmp_tbl drop column new_g ,drop column f, drop column h, $force_needed algorithm=$alter_algorithm ;
271 }
272 --echo # show binlog and clear status
273 --sync_slave_with_master
274 if ($show_binlog)
275 {
276 --source include/show_binlog_events.inc
277 }
278 reset master;
279 --connection master
280
281 # | DISABLE KEYS
282 # | ENABLE KEYS
283 # | RENAME [TO] new_tbl_name
284 # | ORDER BY col_name [, col_name] ...
285 # | RENAME COLUMN old_col_name TO new_col_name
286 # | RENAME {INDEX|KEY} old_index_name TO new_index_name
287 # | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
288 # | [DEFAULT] CHARACTER SET [=] charset_name
289 # | [DEFAULT] COLLATE [=] collation_name
290 # | DISCARD TABLESPACE
291 # | IMPORT TABLESPACE
292 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
293 {
294 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
295 }
296 --error 0,$alter_error
297 --eval alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm
298 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
299 {
300 --let $alter_error=ER_KEY_COLUMN_DOES_NOT_EXIST
301 }
302 --error 0,$alter_error
303 --eval alter table t1 add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm
304 --eval alter table t1 disable keys, $force_needed algorithm=copy
305 --eval alter table t1 enable keys, $force_needed algorithm=copy
306 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
307 {
308 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
309 }
310 --error 0,$alter_error
311 --eval alter table t1 rename t2, $force_needed algorithm=$alter_algorithm
312 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
313 {
314 --let $alter_error=ER_NO_SUCH_TABLE
315 }
316 --error 0,$alter_error
317 --eval alter table t2 rename t1, $force_needed algorithm=$alter_algorithm
318 --eval alter table a1 order by a
319 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
320 {
321 --let $alter_error=ER_BAD_FIELD_ERROR
322 }
323 --error 0,$alter_error
324 --eval alter table t1 rename column f to new_f, $force_needed algorithm=copy
325 --eval alter table t1 convert to character set 'utf8', $force_needed algorithm=copy
326 --eval alter table t1 default character set 'utf8', $force_needed algorithm=copy
327 --eval alter table t1 default collate 'utf8_icelandic_ci', $force_needed algorithm=copy
328 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
329 {
330 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
331 }
332 --error 0,$alter_error
333 --eval alter table t1 drop column new_f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm
334 if ($alter_algorithm == 'copy')
335 {
336 --eval alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm
337 --eval alter table tmp_tbl add index if not exists index_1(f), $force_needed algorithm=$alter_algorithm
338 --eval alter table tmp_tbl disable keys, $force_needed algorithm=copy
339 --eval alter table tmp_tbl enable keys, $force_needed algorithm=copy
340 --eval alter table a1 order by a
341 --eval alter table tmp_tbl rename column f to new_f, $force_needed algorithm=copy
342 --eval alter table tmp_tbl convert to character set 'utf8', $force_needed algorithm=copy
343 --eval alter table tmp_tbl default character set 'utf8', $force_needed algorithm=copy
344 --eval alter table tmp_tbl default collate 'utf8_icelandic_ci', $force_needed algorithm=copy
345 --eval alter table tmp_tbl drop column new_f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm
346 }
347 ##--eval alter table t1 discard tablespace;
348 ######--eval alter table t1 import tablespace;
349
350 --echo # show binlog and clear status
351 --sync_slave_with_master
352 if ($show_binlog)
353 {
354 --source include/show_binlog_events.inc
355 }
356 reset master;
357 --connection master
358
359 # Only add partition and remove partition is tested
360 # | ADD PARTITION (partition_definition)
361 # | REMOVE PARTITIONING
362 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
363 {
364 --let $alter_error=ER_ALTER_OPERATION_NOT_SUPPORTED
365 }
366 --error 0,$alter_error
367 --eval alter table t1 add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm
368 --eval alter table t1 partition by hash(b) partitions 8
369 --eval alter table t1 remove partitioning
370 if (`select '$alter_algorithm' = 'instant' or '$alter_algorithm' = 'nocopy'`)
371 {
372 --let $alter_error=ER_CANT_DROP_FIELD_OR_KEY
373 }
374 --error 0,$alter_error
375 --eval alter table t1 drop column f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm
376 if ($alter_algorithm == 'copy')
377 {
378 --eval alter table tmp_tbl add column f varchar(100) after b,add column g varchar(100) first ,add column h char, $force_needed algorithm=$alter_algorithm
379 --eval alter table tmp_tbl drop column f ,drop column g, drop column h, $force_needed algorithm=$alter_algorithm
380 }
381 --echo # show binlog and clear status
382 --sync_slave_with_master
383 if ($show_binlog)
384 {
385 --source include/show_binlog_events.inc
386 }
387 reset master;
388 --connection master
389
390 # clean master/slave
391 --connection master
392 drop table t1, a1;
393 drop temporary table tmp_tbl;
394 --sync_slave_with_master
395 --source include/stop_slave.inc
396 --eval set global slave_parallel_threads = $slave_parallel_threads;
397 --eval set global slave_parallel_mode = $slave_parallel_mode;
398 --eval set global gtid_strict_mode = $gtid_strict_mode;
399 --source include/start_slave.inc