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