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
|
include/master-slave.inc
[connection master]
connection master;
set global binlog_alter_two_phase=true;
connection slave;
include/stop_slave.inc
SET STATEMENT sql_log_bin=0 FOR
ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
set global gtid_strict_mode=1;
# Legacy Master Slave
connect master_node,127.0.0.1,root,,$db_name, $M_port;
set gtid_domain_id= 0;;
connect slave_node,127.0.0.1,root,,test, $S_port;
set gtid_domain_id= 0;;
# myisam
connection master_node;
create table t1(a int, b int) engine=myisam;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
connection master_node;
drop table t1;
# innodb
connection master_node;
create table t1(a int, b int) engine=innodb;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
connection master_node;
drop table t1;
# aria
connection master_node;
create table t1(a int, b int) engine=aria;;
insert into t1 values(1,1);
insert into t1 values(2,2);
# Normal Alter
alter table t1 add column c int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
# Failed Alter
insert into t1 values(1,1, NULL);
alter table t1 change a a int unique;
ERROR 23000: Duplicate entry '1' for key 'a'
set @@session.binlog_alter_two_phase = 0;
alter table t1 change a a int;
set @@session.binlog_alter_two_phase = 1;
alter table t1 change a a int;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
connection master_node;
drop table t1;
# concurrent alter Myisam
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Aria
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Innodb copy
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
# concurrent alter Innodb Inplace
# Concurrent DML
# Rollback tests
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
ERROR 23000: Duplicate entry '2' for key 'b'
connection master;
select @@gtid_binlog_state;
@@gtid_binlog_state
0-1-410
RESET master;
connection slave;
select @@gtid_binlog_state;
@@gtid_binlog_state
0-1-410
set global gtid_strict_mode=0;
include/start_slave.inc
connection master;
set global binlog_alter_two_phase=false;
include/rpl_end.inc
|
