Debian Codegraph @

mysql-test/suite/rpl/r/rpl_start_alter_mysqlbinlog_2.result

raw
1 connect server_1,127.0.0.1,root,,,$SERVER_MYPORT_1;
2 connect server_2,127.0.0.1,root,,,$SERVER_MYPORT_2;
3 connect server_3,127.0.0.1,root,,,$SERVER_MYPORT_3;
4 connection server_1;
5 SET @save_binlog_alter_two_phase= @@GLOBAL.binlog_alter_two_phase;
6 SET GLOBAL binlog_alter_two_phase = ON;
7 SET binlog_alter_two_phase = ON;
8 # Create table and perform CA and RA
9 CREATE TABLE t1( a INT, b INT) ENGINE=InnoDB;
10 INSERT INTO t1 VALUES(1,1);
11 INSERT INTO t1 VALUES(2,2);
12 # Normal Alter
13 ALTER TABLE t1 ADD COLUMN c INT;
14 # Failed Alter
15 INSERT INTO t1 VALUES(1,1, NULL);
16 ALTER TABLE t1 CHANGE a a INT UNIQUE;
17 ERROR 23000: Duplicate entry '1' for key 'a'
18 SHOW CREATE TABLE t1;
19 Table Create Table
20 t1 CREATE TABLE `t1` (
21 `a` int(11) DEFAULT NULL,
22 `b` int(11) DEFAULT NULL,
23 `c` int(11) DEFAULT NULL
24 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
25 SELECT @@gtid_binlog_state;
26 @@gtid_binlog_state
27 0-1-8
28 # apply the binlog
29 DROP TABLE t1;
30 # reset the binlog
31 RESET MASTER;
32 # execute the binlog
33 SELECT @@gtid_binlog_state;
34 @@gtid_binlog_state
35 0-1-8
36 # Same as before
37 SHOW CREATE TABLE t1;
38 Table Create Table
39 t1 CREATE TABLE `t1` (
40 `a` int(11) DEFAULT NULL,
41 `b` int(11) DEFAULT NULL,
42 `c` int(11) DEFAULT NULL
43 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
44 DROP TABLE t1;
45 # reset the binlog
46 RESET MASTER;
47 RESET SLAVE;
48 connection server_2;
49 SET @save_binlog_alter_two_phase= @@GLOBAL.binlog_alter_two_phase;
50 SET GLOBAL binlog_alter_two_phase = ON;
51 connection server_3;
52 SET STATEMENT sql_log_bin=0 FOR
53 CALL mtr.add_suppression("The table mysql.gtid_slave_pos was removed. This change will not take full effect until all SQL threads have been restarted");
54 SET STATEMENT sql_log_bin=0 FOR
55 ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
56 SET @save_gtid_strict_mode= @@GLOBAL.gtid_strict_mode;
57 SET @slave_parallel_threads= @@GLOBAL.slave_parallel_threads;
58 SET @slave_parallel_mode= @@GLOBAL.slave_parallel_mode;
59 SET GLOBAL slave_parallel_threads=20;
60 SET GLOBAL slave_parallel_mode=optimistic;
61 SET GLOBAL gtid_strict_mode=1;
62 connection server_1;
63 SET gtid_domain_id= 11;
64 CREATE DATABASE s1;
65 USE s1;
66 connect master_node,127.0.0.1,root,,$db_name, $M_port;
67 set gtid_domain_id= 11;;
68 connect slave_node,127.0.0.1,root,,test, $S_port;
69 set gtid_domain_id= 11;;
70 # myisam
71 connection master_node;
72 create table t1(a int, b int) engine=myisam;;
73 insert into t1 values(1,1);
74 insert into t1 values(2,2);
75 # Normal Alter
76 alter table t1 add column c int;
77 show create table t1;
78 Table Create Table
79 t1 CREATE TABLE `t1` (
80 `a` int(11) DEFAULT NULL,
81 `b` int(11) DEFAULT NULL,
82 `c` int(11) DEFAULT NULL
83 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
84 # Failed Alter
85 insert into t1 values(1,1, NULL);
86 alter table t1 change a a int unique;
87 ERROR 23000: Duplicate entry '1' for key 'a'
88 set @@session.binlog_alter_two_phase = 0;
89 alter table t1 change a a int;
90 set @@session.binlog_alter_two_phase = 1;
91 alter table t1 change a a int;
92 show create table t1;
93 Table Create Table
94 t1 CREATE TABLE `t1` (
95 `a` int(11) DEFAULT NULL,
96 `b` int(11) DEFAULT NULL,
97 `c` int(11) DEFAULT NULL
98 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
99 connection master_node;
100 drop table t1;
101 # innodb
102 connection master_node;
103 create table t1(a int, b int) engine=innodb;;
104 insert into t1 values(1,1);
105 insert into t1 values(2,2);
106 # Normal Alter
107 alter table t1 add column c int;
108 show create table t1;
109 Table Create Table
110 t1 CREATE TABLE `t1` (
111 `a` int(11) DEFAULT NULL,
112 `b` int(11) DEFAULT NULL,
113 `c` int(11) DEFAULT NULL
114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
115 # Failed Alter
116 insert into t1 values(1,1, NULL);
117 alter table t1 change a a int unique;
118 ERROR 23000: Duplicate entry '1' for key 'a'
119 set @@session.binlog_alter_two_phase = 0;
120 alter table t1 change a a int;
121 set @@session.binlog_alter_two_phase = 1;
122 alter table t1 change a a int;
123 show create table t1;
124 Table Create Table
125 t1 CREATE TABLE `t1` (
126 `a` int(11) DEFAULT NULL,
127 `b` int(11) DEFAULT NULL,
128 `c` int(11) DEFAULT NULL
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
130 connection master_node;
131 drop table t1;
132 # aria
133 connection master_node;
134 create table t1(a int, b int) engine=aria;;
135 insert into t1 values(1,1);
136 insert into t1 values(2,2);
137 # Normal Alter
138 alter table t1 add column c int;
139 show create table t1;
140 Table Create Table
141 t1 CREATE TABLE `t1` (
142 `a` int(11) DEFAULT NULL,
143 `b` int(11) DEFAULT NULL,
144 `c` int(11) DEFAULT NULL
145 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
146 # Failed Alter
147 insert into t1 values(1,1, NULL);
148 alter table t1 change a a int unique;
149 ERROR 23000: Duplicate entry '1' for key 'a'
150 set @@session.binlog_alter_two_phase = 0;
151 alter table t1 change a a int;
152 set @@session.binlog_alter_two_phase = 1;
153 alter table t1 change a a int;
154 show create table t1;
155 Table Create Table
156 t1 CREATE TABLE `t1` (
157 `a` int(11) DEFAULT NULL,
158 `b` int(11) DEFAULT NULL,
159 `c` int(11) DEFAULT NULL
160 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
161 connection master_node;
162 drop table t1;
163 # concurrent alter Myisam
164 # Concurrent DML
165 # Rollback tests
166 ERROR 23000: Duplicate entry '2' for key 'b'
167 ERROR 23000: Duplicate entry '2' for key 'b'
168 ERROR 23000: Duplicate entry '2' for key 'b'
169 ERROR 23000: Duplicate entry '2' for key 'b'
170 ERROR 23000: Duplicate entry '2' for key 'b'
171 ERROR 23000: Duplicate entry '2' for key 'b'
172 ERROR 23000: Duplicate entry '2' for key 'b'
173 ERROR 23000: Duplicate entry '2' for key 'b'
174 ERROR 23000: Duplicate entry '2' for key 'b'
175 ERROR 23000: Duplicate entry '2' for key 'b'
176 # concurrent alter Aria
177 # Concurrent DML
178 # Rollback tests
179 ERROR 23000: Duplicate entry '2' for key 'b'
180 ERROR 23000: Duplicate entry '2' for key 'b'
181 ERROR 23000: Duplicate entry '2' for key 'b'
182 ERROR 23000: Duplicate entry '2' for key 'b'
183 ERROR 23000: Duplicate entry '2' for key 'b'
184 ERROR 23000: Duplicate entry '2' for key 'b'
185 ERROR 23000: Duplicate entry '2' for key 'b'
186 ERROR 23000: Duplicate entry '2' for key 'b'
187 ERROR 23000: Duplicate entry '2' for key 'b'
188 ERROR 23000: Duplicate entry '2' for key 'b'
189 # concurrent alter Innodb copy
190 # Concurrent DML
191 # Rollback tests
192 ERROR 23000: Duplicate entry '2' for key 'b'
193 ERROR 23000: Duplicate entry '2' for key 'b'
194 ERROR 23000: Duplicate entry '2' for key 'b'
195 ERROR 23000: Duplicate entry '2' for key 'b'
196 ERROR 23000: Duplicate entry '2' for key 'b'
197 ERROR 23000: Duplicate entry '2' for key 'b'
198 ERROR 23000: Duplicate entry '2' for key 'b'
199 ERROR 23000: Duplicate entry '2' for key 'b'
200 ERROR 23000: Duplicate entry '2' for key 'b'
201 ERROR 23000: Duplicate entry '2' for key 'b'
202 # concurrent alter Innodb Inplace
203 # Concurrent DML
204 # Rollback tests
205 ERROR 23000: Duplicate entry '2' for key 'b'
206 ERROR 23000: Duplicate entry '2' for key 'b'
207 ERROR 23000: Duplicate entry '2' for key 'b'
208 ERROR 23000: Duplicate entry '2' for key 'b'
209 ERROR 23000: Duplicate entry '2' for key 'b'
210 ERROR 23000: Duplicate entry '2' for key 'b'
211 ERROR 23000: Duplicate entry '2' for key 'b'
212 ERROR 23000: Duplicate entry '2' for key 'b'
213 ERROR 23000: Duplicate entry '2' for key 'b'
214 ERROR 23000: Duplicate entry '2' for key 'b'
215 connection server_1;
216 DROP DATABASE s1;
217 connection server_2;
218 SET gtid_domain_id= 12;
219 CREATE DATABASE s2;
220 USE s2;
221 connect master_node,127.0.0.1,root,,$db_name, $M_port;
222 set gtid_domain_id= 12;;
223 connect slave_node,127.0.0.1,root,,test, $S_port;
224 set gtid_domain_id= 12;;
225 # myisam
226 connection master_node;
227 create table t1(a int, b int) engine=myisam;;
228 insert into t1 values(1,1);
229 insert into t1 values(2,2);
230 # Normal Alter
231 alter table t1 add column c int;
232 show create table t1;
233 Table Create Table
234 t1 CREATE TABLE `t1` (
235 `a` int(11) DEFAULT NULL,
236 `b` int(11) DEFAULT NULL,
237 `c` int(11) DEFAULT NULL
238 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
239 # Failed Alter
240 insert into t1 values(1,1, NULL);
241 alter table t1 change a a int unique;
242 ERROR 23000: Duplicate entry '1' for key 'a'
243 set @@session.binlog_alter_two_phase = 0;
244 alter table t1 change a a int;
245 set @@session.binlog_alter_two_phase = 1;
246 alter table t1 change a a int;
247 show create table t1;
248 Table Create Table
249 t1 CREATE TABLE `t1` (
250 `a` int(11) DEFAULT NULL,
251 `b` int(11) DEFAULT NULL,
252 `c` int(11) DEFAULT NULL
253 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
254 connection master_node;
255 drop table t1;
256 # innodb
257 connection master_node;
258 create table t1(a int, b int) engine=innodb;;
259 insert into t1 values(1,1);
260 insert into t1 values(2,2);
261 # Normal Alter
262 alter table t1 add column c int;
263 show create table t1;
264 Table Create Table
265 t1 CREATE TABLE `t1` (
266 `a` int(11) DEFAULT NULL,
267 `b` int(11) DEFAULT NULL,
268 `c` int(11) DEFAULT NULL
269 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
270 # Failed Alter
271 insert into t1 values(1,1, NULL);
272 alter table t1 change a a int unique;
273 ERROR 23000: Duplicate entry '1' for key 'a'
274 set @@session.binlog_alter_two_phase = 0;
275 alter table t1 change a a int;
276 set @@session.binlog_alter_two_phase = 1;
277 alter table t1 change a a int;
278 show create table t1;
279 Table Create Table
280 t1 CREATE TABLE `t1` (
281 `a` int(11) DEFAULT NULL,
282 `b` int(11) DEFAULT NULL,
283 `c` int(11) DEFAULT NULL
284 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
285 connection master_node;
286 drop table t1;
287 # aria
288 connection master_node;
289 create table t1(a int, b int) engine=aria;;
290 insert into t1 values(1,1);
291 insert into t1 values(2,2);
292 # Normal Alter
293 alter table t1 add column c int;
294 show create table t1;
295 Table Create Table
296 t1 CREATE TABLE `t1` (
297 `a` int(11) DEFAULT NULL,
298 `b` int(11) DEFAULT NULL,
299 `c` int(11) DEFAULT NULL
300 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
301 # Failed Alter
302 insert into t1 values(1,1, NULL);
303 alter table t1 change a a int unique;
304 ERROR 23000: Duplicate entry '1' for key 'a'
305 set @@session.binlog_alter_two_phase = 0;
306 alter table t1 change a a int;
307 set @@session.binlog_alter_two_phase = 1;
308 alter table t1 change a a int;
309 show create table t1;
310 Table Create Table
311 t1 CREATE TABLE `t1` (
312 `a` int(11) DEFAULT NULL,
313 `b` int(11) DEFAULT NULL,
314 `c` int(11) DEFAULT NULL
315 ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1
316 connection master_node;
317 drop table t1;
318 # concurrent alter Myisam
319 # Concurrent DML
320 # Rollback tests
321 ERROR 23000: Duplicate entry '2' for key 'b'
322 ERROR 23000: Duplicate entry '2' for key 'b'
323 ERROR 23000: Duplicate entry '2' for key 'b'
324 ERROR 23000: Duplicate entry '2' for key 'b'
325 ERROR 23000: Duplicate entry '2' for key 'b'
326 ERROR 23000: Duplicate entry '2' for key 'b'
327 ERROR 23000: Duplicate entry '2' for key 'b'
328 ERROR 23000: Duplicate entry '2' for key 'b'
329 ERROR 23000: Duplicate entry '2' for key 'b'
330 ERROR 23000: Duplicate entry '2' for key 'b'
331 # concurrent alter Aria
332 # Concurrent DML
333 # Rollback tests
334 ERROR 23000: Duplicate entry '2' for key 'b'
335 ERROR 23000: Duplicate entry '2' for key 'b'
336 ERROR 23000: Duplicate entry '2' for key 'b'
337 ERROR 23000: Duplicate entry '2' for key 'b'
338 ERROR 23000: Duplicate entry '2' for key 'b'
339 ERROR 23000: Duplicate entry '2' for key 'b'
340 ERROR 23000: Duplicate entry '2' for key 'b'
341 ERROR 23000: Duplicate entry '2' for key 'b'
342 ERROR 23000: Duplicate entry '2' for key 'b'
343 ERROR 23000: Duplicate entry '2' for key 'b'
344 # concurrent alter Innodb copy
345 # Concurrent DML
346 # Rollback tests
347 ERROR 23000: Duplicate entry '2' for key 'b'
348 ERROR 23000: Duplicate entry '2' for key 'b'
349 ERROR 23000: Duplicate entry '2' for key 'b'
350 ERROR 23000: Duplicate entry '2' for key 'b'
351 ERROR 23000: Duplicate entry '2' for key 'b'
352 ERROR 23000: Duplicate entry '2' for key 'b'
353 ERROR 23000: Duplicate entry '2' for key 'b'
354 ERROR 23000: Duplicate entry '2' for key 'b'
355 ERROR 23000: Duplicate entry '2' for key 'b'
356 ERROR 23000: Duplicate entry '2' for key 'b'
357 # concurrent alter Innodb Inplace
358 # Concurrent DML
359 # Rollback tests
360 ERROR 23000: Duplicate entry '2' for key 'b'
361 ERROR 23000: Duplicate entry '2' for key 'b'
362 ERROR 23000: Duplicate entry '2' for key 'b'
363 ERROR 23000: Duplicate entry '2' for key 'b'
364 ERROR 23000: Duplicate entry '2' for key 'b'
365 ERROR 23000: Duplicate entry '2' for key 'b'
366 ERROR 23000: Duplicate entry '2' for key 'b'
367 ERROR 23000: Duplicate entry '2' for key 'b'
368 ERROR 23000: Duplicate entry '2' for key 'b'
369 ERROR 23000: Duplicate entry '2' for key 'b'
370 connection server_2;
371 DROP DATABASE s2;
372 connection server_3;
373 START ALL SLAVES;
374 Warnings:
375 Note 1937 SLAVE 'm2' started
376 Note 1937 SLAVE 'm1' started
377 SET default_master_connection = 'm1';
378 include/wait_for_slave_to_start.inc
379 SET default_master_connection = 'm2';
380 include/wait_for_slave_to_start.inc
381 SET default_master_connection = 'm1';
382 include/sync_with_master_gtid.inc
383 SET default_master_connection = 'm2';
384 include/sync_with_master_gtid.inc
385 # Stop slaves and apply binlog
386 connection server_3;
387 SET default_master_connection = 'm1';
388 include/stop_slave.inc
389 SET default_master_connection = 'm2';
390 include/stop_slave.inc
391 SET GLOBAL slave_parallel_threads = @slave_parallel_threads;
392 SET GLOBAL slave_parallel_mode = @slave_parallel_mode;
393 SET GLOBAL gtid_strict_mode = @save_gtid_strict_mode;
394 SET GLOBAL gtid_domain_id= 0;
395 SELECT @@gtid_binlog_state;
396 @@gtid_binlog_state
397 11-1-412,12-2-412
398 # reset the binlog
399 RESET MASTER;
400 # execute the binlog
401 SELECT @@gtid_binlog_state;
402 @@gtid_binlog_state
403 11-1-412,12-2-412
404 # One more time to simulate S->S case
405 RESET MASTER;
406 # execute the binlog
407 SELECT @@gtid_binlog_state;
408 @@gtid_binlog_state
409 11-1-412,12-2-412
410 RESET MASTER;
411 RESET SLAVE ALL;
412 SET GLOBAL gtid_slave_pos= '';
413 connection server_1;
414 SET GLOBAL binlog_alter_two_phase=@save_binlog_alter_two_phase;
415 SET GLOBAL gtid_domain_id= 0;
416 RESET MASTER;
417 connection server_2;
418 SET GLOBAL gtid_domain_id= 0;
419 SET GLOBAL binlog_alter_two_phase=@save_binlog_alter_two_phase;
420 RESET MASTER;
421 disconnect server_1;
422 disconnect server_2;
423 disconnect server_3;