Debian Codegraph @

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

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 include/stop_slave.inc
5 change master to master_use_gtid=slave_pos;
6 include/start_slave.inc
7 include/sync_with_master_gtid.inc
8 #
9 # Test Case 1: When both gtid_binlog_pos and gtid_slave_pos are
10 # empty, MASTER_DEMOTE_TO_SLAVE=1 results in no change to replication
11 # state.
12 #
13 include/rpl_change_master_demote.inc
14 ##############################################
15 # Connection semantics change:
16 # * True primary is now connection 'slave'
17 # * True replica is now connection 'master'
18 ##############################################
19 connection master;
20 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
21 VARIABLE_NAME GLOBAL_VALUE
22 GTID_BINLOG_POS
23 GTID_CURRENT_POS
24 GTID_SLAVE_POS
25 # First ensure gtid_slave_pos after master_demote_to_slave matches
26 # gtid_current_pos calculation
27 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 ;
28 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
29 VARIABLE_NAME GLOBAL_VALUE
30 GTID_BINLOG_POS
31 GTID_CURRENT_POS
32 GTID_SLAVE_POS
33 # Validating gtid_slave_pos == gtid_binlog_pos..
34 # ..success
35 connection slave;
36 # Ensuring replication works correctly after role swap
37 connection slave;
38 set session gtid_domain_id= 0;
39 CREATE TABLE repl_t (a int);
40 INSERT INTO repl_t VALUES (1);
41 connection master;
42 # Validating that replication works..
43 # ..success
44 # Cleaning up replication check data
45 connection slave;
46 DROP TABLE repl_t;
47 connection master;
48 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
49 VARIABLE_NAME GLOBAL_VALUE
50 GTID_BINLOG_POS
51 GTID_CURRENT_POS 0-2-3
52 GTID_SLAVE_POS 0-2-3
53 ##############################################
54 # Connection semantics change:
55 # * True primary is back to connection 'master'
56 # * True replica is back to connection 'slave'
57 ##############################################
58 connection master;
59 connection slave;
60 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
61 connection master;
62 #
63 # Test Case 2: If gtid_slave_pos is empty, gtid_binlog_pos will
64 # completely overwrite it with MASTER_DEMOTE_TO_SLAVE=1.
65 #
66 connection slave;
67 include/stop_slave.inc
68 RESET MASTER;
69 set @@global.gtid_slave_pos="";
70 include/start_slave.inc
71 connection master;
72 RESET MASTER;
73 set @@global.gtid_slave_pos="";
74 set session gtid_domain_id= 0;
75 CREATE TABLE t1 (a int);
76 INSERT INTO t1 VALUES (1);
77 include/save_master_gtid.inc
78 connection slave;
79 include/sync_with_master_gtid.inc
80 include/rpl_change_master_demote.inc
81 ##############################################
82 # Connection semantics change:
83 # * True primary is now connection 'slave'
84 # * True replica is now connection 'master'
85 ##############################################
86 connection master;
87 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
88 VARIABLE_NAME GLOBAL_VALUE
89 GTID_BINLOG_POS 0-1-2
90 GTID_CURRENT_POS 0-1-2
91 GTID_SLAVE_POS
92 # First ensure gtid_slave_pos after master_demote_to_slave matches
93 # gtid_current_pos calculation
94 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 ;
95 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
96 VARIABLE_NAME GLOBAL_VALUE
97 GTID_BINLOG_POS 0-1-2
98 GTID_CURRENT_POS 0-1-2
99 GTID_SLAVE_POS 0-1-2
100 # Validating gtid_slave_pos == gtid_binlog_pos..
101 # ..success
102 connection slave;
103 # Ensuring replication works correctly after role swap
104 connection slave;
105 set session gtid_domain_id= 0;
106 CREATE TABLE repl_t (a int);
107 INSERT INTO repl_t VALUES (1);
108 connection master;
109 # Validating that replication works..
110 # ..success
111 # Cleaning up replication check data
112 connection slave;
113 DROP TABLE repl_t;
114 connection master;
115 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
116 VARIABLE_NAME GLOBAL_VALUE
117 GTID_BINLOG_POS 0-1-2
118 GTID_CURRENT_POS 0-2-5
119 GTID_SLAVE_POS 0-2-5
120 ##############################################
121 # Connection semantics change:
122 # * True primary is back to connection 'master'
123 # * True replica is back to connection 'slave'
124 ##############################################
125 connection master;
126 connection slave;
127 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
128 connection master;
129 #
130 # Test Case 3: Using a single domain id, if neither gtid_slave_pos nor
131 # gtid_binlog_pos are empty, and gtid_binlog_pos is more recent, then
132 # gtid_binlog_pos will overwrite gtid_slave_pos when
133 # MASTER_DEMOTE_TO_SLAVE=1.
134 #
135 connection master;
136 INSERT INTO t1 VALUES (2);
137 include/save_master_gtid.inc
138 connection slave;
139 include/sync_with_master_gtid.inc
140 include/rpl_change_master_demote.inc
141 ##############################################
142 # Connection semantics change:
143 # * True primary is now connection 'slave'
144 # * True replica is now connection 'master'
145 ##############################################
146 connection master;
147 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
148 VARIABLE_NAME GLOBAL_VALUE
149 GTID_BINLOG_POS 0-1-6
150 GTID_CURRENT_POS 0-1-6
151 GTID_SLAVE_POS 0-2-5
152 # First ensure gtid_slave_pos after master_demote_to_slave matches
153 # gtid_current_pos calculation
154 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 ;
155 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
156 VARIABLE_NAME GLOBAL_VALUE
157 GTID_BINLOG_POS 0-1-6
158 GTID_CURRENT_POS 0-1-6
159 GTID_SLAVE_POS 0-1-6
160 # Validating gtid_slave_pos == gtid_binlog_pos..
161 # ..success
162 connection slave;
163 # Ensuring replication works correctly after role swap
164 connection slave;
165 set session gtid_domain_id= 0;
166 CREATE TABLE repl_t (a int);
167 INSERT INTO repl_t VALUES (1);
168 connection master;
169 # Validating that replication works..
170 # ..success
171 # Cleaning up replication check data
172 connection slave;
173 DROP TABLE repl_t;
174 connection master;
175 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
176 VARIABLE_NAME GLOBAL_VALUE
177 GTID_BINLOG_POS 0-1-6
178 GTID_CURRENT_POS 0-2-9
179 GTID_SLAVE_POS 0-2-9
180 ##############################################
181 # Connection semantics change:
182 # * True primary is back to connection 'master'
183 # * True replica is back to connection 'slave'
184 ##############################################
185 connection master;
186 connection slave;
187 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
188 connection master;
189 #
190 # Test Case 4: If gtid_slave_pos and gtid_binlog_pos are equivalent,
191 # MASTER_DEMOTE_TO_SLAVE=1 will not change gtid_slave_pos.
192 #
193 connection master;
194 # update gtid_binlog_pos and demote it (we have proven this works)
195 INSERT INTO t1 VALUES (3);
196 # Update to account for statements to verify replication in include file
197 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
198 RESET SLAVE ALL;
199 include/save_master_gtid.inc
200 connection slave;
201 include/sync_with_master_gtid.inc
202 include/rpl_change_master_demote.inc
203 ##############################################
204 # Connection semantics change:
205 # * True primary is now connection 'slave'
206 # * True replica is now connection 'master'
207 ##############################################
208 connection master;
209 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
210 VARIABLE_NAME GLOBAL_VALUE
211 GTID_BINLOG_POS 0-1-10
212 GTID_CURRENT_POS 0-1-10
213 GTID_SLAVE_POS 0-1-10
214 # First ensure gtid_slave_pos after master_demote_to_slave matches
215 # gtid_current_pos calculation
216 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 ;
217 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
218 VARIABLE_NAME GLOBAL_VALUE
219 GTID_BINLOG_POS 0-1-10
220 GTID_CURRENT_POS 0-1-10
221 GTID_SLAVE_POS 0-1-10
222 # Validating gtid_slave_pos == gtid_binlog_pos..
223 # ..success
224 connection slave;
225 # Ensuring replication works correctly after role swap
226 connection slave;
227 set session gtid_domain_id= 0;
228 CREATE TABLE repl_t (a int);
229 INSERT INTO repl_t VALUES (1);
230 connection master;
231 # Validating that replication works..
232 # ..success
233 # Cleaning up replication check data
234 connection slave;
235 DROP TABLE repl_t;
236 connection master;
237 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
238 VARIABLE_NAME GLOBAL_VALUE
239 GTID_BINLOG_POS 0-1-10
240 GTID_CURRENT_POS 0-2-13
241 GTID_SLAVE_POS 0-2-13
242 ##############################################
243 # Connection semantics change:
244 # * True primary is back to connection 'master'
245 # * True replica is back to connection 'slave'
246 ##############################################
247 connection master;
248 connection slave;
249 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
250 connection master;
251 #
252 # Test Case 5: If a new domain id is added into gtid_binlog_pos while
253 # gtid_slave_pos already has a state, MASTER_DEMOTE_TO_SLAVE=1 will
254 # append a new GTID to gtid_slave_pos with the latest seq_no from that
255 # domain.
256 #
257 connection master;
258 # Domain_id
259 set session gtid_domain_id= 0;
260 INSERT INTO t1 VALUES (4);
261 set session gtid_domain_id= 1;
262 CREATE TABLE t2 (a int);
263 INSERT INTO t2 VALUES (1);
264 include/save_master_gtid.inc
265 connection slave;
266 include/sync_with_master_gtid.inc
267 include/rpl_change_master_demote.inc
268 ##############################################
269 # Connection semantics change:
270 # * True primary is now connection 'slave'
271 # * True replica is now connection 'master'
272 ##############################################
273 connection master;
274 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
275 VARIABLE_NAME GLOBAL_VALUE
276 GTID_BINLOG_POS 0-1-14,1-1-2
277 GTID_CURRENT_POS 0-1-14,1-1-2
278 GTID_SLAVE_POS 0-2-13
279 # First ensure gtid_slave_pos after master_demote_to_slave matches
280 # gtid_current_pos calculation
281 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 ;
282 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
283 VARIABLE_NAME GLOBAL_VALUE
284 GTID_BINLOG_POS 0-1-14,1-1-2
285 GTID_CURRENT_POS 0-1-14,1-1-2
286 GTID_SLAVE_POS 0-1-14,1-1-2
287 # Validating gtid_slave_pos == gtid_binlog_pos..
288 # ..success
289 connection slave;
290 # Ensuring replication works correctly after role swap
291 connection slave;
292 set session gtid_domain_id= 0;
293 CREATE TABLE repl_t (a int);
294 INSERT INTO repl_t VALUES (1);
295 connection master;
296 # Validating that replication works..
297 # ..success
298 # Cleaning up replication check data
299 connection slave;
300 DROP TABLE repl_t;
301 connection master;
302 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
303 VARIABLE_NAME GLOBAL_VALUE
304 GTID_BINLOG_POS 0-1-14,1-1-2
305 GTID_CURRENT_POS 0-2-17,1-1-2
306 GTID_SLAVE_POS 0-2-17,1-1-2
307 ##############################################
308 # Connection semantics change:
309 # * True primary is back to connection 'master'
310 # * True replica is back to connection 'slave'
311 ##############################################
312 connection master;
313 connection slave;
314 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
315 connection master;
316 #
317 # Test Case 6: If gtid_slave_pos has multiple GTID positions and
318 # gtid_binlog_pos contains updates on existing domain ids, new
319 # domains, and differing server_ids, MASTER_DEMOTE_TO_SLAVE=1 will
320 # update gtid_slave_pos such that it will have the seq_nos only from
321 # the GTIDs last applied by this server. In other words, any GTIDs
322 # with server ids that don't match that of the demoting server will be
323 # ignored in the update.
324 #
325 connection master;
326 # Update to account for statements to verify replication in include file
327 set session gtid_domain_id= 0;
328 INSERT INTO t1 VALUES (5);
329 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
330 RESET SLAVE ALL;
331 set session gtid_domain_id= 1;
332 INSERT INTO t2 VALUES (2);
333 set session gtid_domain_id= 2;
334 CREATE TABLE t3 (a int);
335 INSERT INTO t3 VALUES (1);
336 # The following events have a different server_id and should not go into
337 # gtid_slave_pos
338 set @old_server_id = @@server_id;
339 set session gtid_domain_id= 1;
340 set session server_id= 3;
341 INSERT INTO t2 VALUES (3);
342 set session gtid_domain_id= 4;
343 set session server_id= 3;
344 CREATE TABLE t5 (a int);
345 INSERT INTO t5 VALUES (1);
346 set session server_id= @old_server_id;
347 include/save_master_gtid.inc
348 connection slave;
349 include/sync_with_master_gtid.inc
350 include/rpl_change_master_demote.inc
351 ##############################################
352 # Connection semantics change:
353 # * True primary is now connection 'slave'
354 # * True replica is now connection 'master'
355 ##############################################
356 connection master;
357 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
358 VARIABLE_NAME GLOBAL_VALUE
359 GTID_BINLOG_POS 0-1-18,1-3-4,2-1-2,4-3-2
360 GTID_CURRENT_POS 0-1-18,1-1-2,2-1-2
361 GTID_SLAVE_POS 0-1-18,1-1-2
362 # First ensure gtid_slave_pos after master_demote_to_slave matches
363 # gtid_current_pos calculation
364 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 ;
365 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
366 VARIABLE_NAME GLOBAL_VALUE
367 GTID_BINLOG_POS 0-1-18,1-3-4,2-1-2,4-3-2
368 GTID_CURRENT_POS 0-1-18,1-1-2,2-1-2
369 GTID_SLAVE_POS 0-1-18,1-1-2,2-1-2
370 # Validating gtid_slave_pos == gtid_binlog_pos..
371 # ..success
372 connection slave;
373 # Ensuring replication works correctly after role swap
374 connection slave;
375 set session gtid_domain_id= 0;
376 CREATE TABLE repl_t (a int);
377 INSERT INTO repl_t VALUES (1);
378 connection master;
379 # Validating that replication works..
380 # ..success
381 # Cleaning up replication check data
382 connection slave;
383 DROP TABLE repl_t;
384 connection master;
385 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
386 VARIABLE_NAME GLOBAL_VALUE
387 GTID_BINLOG_POS 0-1-18,1-3-4,2-1-2,4-3-2
388 GTID_CURRENT_POS 0-2-21,1-3-4,2-1-2,4-3-2
389 GTID_SLAVE_POS 0-2-21,1-3-4,2-1-2,4-3-2
390 ##############################################
391 # Connection semantics change:
392 # * True primary is back to connection 'master'
393 # * True replica is back to connection 'slave'
394 ##############################################
395 connection master;
396 connection slave;
397 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
398 connection master;
399 #
400 # Test Case 7: If MASTER_DEMOTE_TO_SLAVE=1 is combined with
401 # IGNORE_DOMAIN_IDS such that gtid_binlog_pos has more recent GTIDs
402 # than gtid_slave_pos in ignored domains, the CHANGE MASTER TO command
403 # will still update gtid_slave_pos with the most recent transactions
404 # from gtid_binlog_pos, despite being ignored by CHANGE MASTER TO
405 # because they were already applied on the server.
406 #
407 connection master;
408 set session gtid_domain_id= 2;
409 INSERT INTO t3 VALUES (2);
410 set session gtid_domain_id= 3;
411 CREATE TABLE t4 (a int);
412 INSERT INTO t4 VALUES (1);
413 include/save_master_gtid.inc
414 connection slave;
415 include/sync_with_master_gtid.inc
416 include/rpl_change_master_demote.inc
417 ##############################################
418 # Connection semantics change:
419 # * True primary is now connection 'slave'
420 # * True replica is now connection 'master'
421 ##############################################
422 connection master;
423 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
424 VARIABLE_NAME GLOBAL_VALUE
425 GTID_BINLOG_POS 0-1-18,1-3-4,2-1-3,3-1-2,4-3-2
426 GTID_CURRENT_POS 0-2-21,1-3-4,2-1-3,3-1-2,4-3-2
427 GTID_SLAVE_POS 0-2-21,1-3-4,2-1-2,4-3-2
428 # First ensure gtid_slave_pos after master_demote_to_slave matches
429 # gtid_current_pos calculation
430 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 , ignore_domain_ids=(2,3);
431 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
432 VARIABLE_NAME GLOBAL_VALUE
433 GTID_BINLOG_POS 0-1-18,1-3-4,2-1-3,3-1-2,4-3-2
434 GTID_CURRENT_POS 0-2-21,1-3-4,2-1-3,3-1-2,4-3-2
435 GTID_SLAVE_POS 0-2-21,1-3-4,2-1-3,3-1-2,4-3-2
436 # Validating gtid_slave_pos == gtid_binlog_pos..
437 # ..success
438 connection slave;
439 # Ensuring replication works correctly after role swap
440 connection slave;
441 set session gtid_domain_id= 0;
442 CREATE TABLE repl_t (a int);
443 INSERT INTO repl_t VALUES (1);
444 connection master;
445 # Validating that replication works..
446 # ..success
447 # Cleaning up replication check data
448 connection slave;
449 DROP TABLE repl_t;
450 connection master;
451 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
452 VARIABLE_NAME GLOBAL_VALUE
453 GTID_BINLOG_POS 0-1-18,1-3-4,2-1-3,3-1-2,4-3-2
454 GTID_CURRENT_POS 0-2-24,1-3-4,2-1-3,3-1-2,4-3-2
455 GTID_SLAVE_POS 0-2-24,1-3-4,2-1-3,3-1-2,4-3-2
456 ##############################################
457 # Connection semantics change:
458 # * True primary is back to connection 'master'
459 # * True replica is back to connection 'slave'
460 ##############################################
461 connection master;
462 connection slave;
463 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
464 connection master;
465 #
466 # Test Case 8: If gtid_binlog_pos is more recent than gtid_slave_pos,
467 # and MASTER_DEMOTE_TO_SLAVE=1 is combined with a later call to
468 # START SLAVE UNTIL master_gtid_pos=<G> such that
469 # gtid_slave_pos < G < gtid_binlog_pos, then the slave should stop
470 # immediately after SSU because gtid_slave_pos should be updated to be
471 # after G.
472 #
473 connection master;
474 set session gtid_domain_id= 0;
475 INSERT INTO t1 VALUES (6);
476 # Tagging ssu_middle_binlog_pos here to be used for START SLAVE UNTIL
477 INSERT INTO t1 VALUES (7);
478 include/save_master_gtid.inc
479 # Ensure slave is up-to-date with master and then disable slave status
480 connection slave;
481 include/sync_with_master_gtid.inc
482 include/stop_slave.inc
483 connection master;
484 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' ORDER BY VARIABLE_NAME ASC;
485 VARIABLE_NAME GLOBAL_VALUE
486 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
487 GTID_SLAVE_POS 0-2-24,1-3-4,2-1-3,3-1-2,4-3-2
488 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=Slave_Pos, master_demote_to_slave=1;
489 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' ORDER BY VARIABLE_NAME ASC;
490 VARIABLE_NAME GLOBAL_VALUE
491 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
492 GTID_SLAVE_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
493 # GTID ssu_middle_binlog_pos should be considered in the past because
494 # gtid_slave_pos should be updated using the latest binlog gtids.
495 # The following call to sync_with_master_gtid.inc uses the latest
496 # binlog position and should still succeed despite the SSU stop
497 # position pointing to a previous event (because
498 # master_demote_to_slave=1 merges gtid_binlog_pos into gtid_slave_pos).
499 START SLAVE UNTIL master_gtid_pos="ssu_middle_binlog_pos";
500 Warnings:
501 Note 1278 It is recommended to use --skip-slave-start when doing step-by-step replication with START SLAVE UNTIL; otherwise, you will get problems if you get an unexpected slave's mariadbd restart
502 # Slave needs time to start and stop automatically
503 # Waiting for both SQL and IO threads to have started..
504 # Waiting for Slave SQL and IO threads to be killed..
505 # Validating neither SQL nor IO threads are running..
506 # ..success
507 # Clean slave state of master
508 RESET SLAVE ALL;
509 #
510 # Test Case 9: If gtid_slave_pos is more recent than gtid_binlog_pos
511 # when demoting the master to become a slave, the replication state
512 # should be preserved.
513 #
514 # rpl_change_master_demote.inc should force
515 # gtid_slave_pos > gtid_binlog_pos in domain 0 (due to the step which
516 # validates replication works)
517 connection master;
518 include/rpl_change_master_demote.inc
519 ##############################################
520 # Connection semantics change:
521 # * True primary is now connection 'slave'
522 # * True replica is now connection 'master'
523 ##############################################
524 connection master;
525 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
526 VARIABLE_NAME GLOBAL_VALUE
527 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
528 GTID_CURRENT_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
529 GTID_SLAVE_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
530 # First ensure gtid_slave_pos after master_demote_to_slave matches
531 # gtid_current_pos calculation
532 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 , ignore_domain_ids=(2,3);
533 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
534 VARIABLE_NAME GLOBAL_VALUE
535 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
536 GTID_CURRENT_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
537 GTID_SLAVE_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
538 # Validating gtid_slave_pos == gtid_binlog_pos..
539 # ..success
540 connection slave;
541 Warnings:
542 Note 1255 Slave already has been stopped
543 # Ensuring replication works correctly after role swap
544 connection slave;
545 set session gtid_domain_id= 0;
546 CREATE TABLE repl_t (a int);
547 INSERT INTO repl_t VALUES (1);
548 connection master;
549 # Validating that replication works..
550 # ..success
551 # Cleaning up replication check data
552 connection slave;
553 DROP TABLE repl_t;
554 connection master;
555 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
556 VARIABLE_NAME GLOBAL_VALUE
557 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
558 GTID_CURRENT_POS 0-2-29,1-3-4,2-1-3,3-1-2,4-3-2
559 GTID_SLAVE_POS 0-2-29,1-3-4,2-1-3,3-1-2,4-3-2
560 ##############################################
561 # Connection semantics change:
562 # * True primary is back to connection 'master'
563 # * True replica is back to connection 'slave'
564 ##############################################
565 connection master;
566 connection slave;
567 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
568 connection master;
569 # Demote master to slave with the more recent gtid_slave_pos
570 connection master;
571 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
572 # Validating gtid_slave_pos is unchanged..
573 # ..success
574 # Validating gtid_slave_pos == gtid_binlog_pos..
575 # ..success
576 # Clean slave state of master
577 RESET SLAVE ALL;
578 #
579 # Test Case 10: MASTER_DEMOTE_TO_SLAVE=0 should not change replication
580 # state, regardless of gtid_slave_pos in comparison to gtid_binlog_pos.
581 #
582 # In domain 0, make gtid_slave_pos > gtid_binlog_pos
583 connection master;
584 include/rpl_change_master_demote.inc
585 ##############################################
586 # Connection semantics change:
587 # * True primary is now connection 'slave'
588 # * True replica is now connection 'master'
589 ##############################################
590 connection master;
591 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
592 VARIABLE_NAME GLOBAL_VALUE
593 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
594 GTID_CURRENT_POS 0-2-29,1-3-4,2-1-3,3-1-2,4-3-2
595 GTID_SLAVE_POS 0-2-29,1-3-4,2-1-3,3-1-2,4-3-2
596 # First ensure gtid_slave_pos after master_demote_to_slave matches
597 # gtid_current_pos calculation
598 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_ssl_verify_server_cert=0, master_use_gtid=slave_pos, master_demote_to_slave=1 , ignore_domain_ids=(2,3);
599 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
600 VARIABLE_NAME GLOBAL_VALUE
601 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
602 GTID_CURRENT_POS 0-2-29,1-3-4,2-1-3,3-1-2,4-3-2
603 GTID_SLAVE_POS 0-2-29,1-3-4,2-1-3,3-1-2,4-3-2
604 # Validating gtid_slave_pos == gtid_binlog_pos..
605 # ..success
606 connection slave;
607 # Ensuring replication works correctly after role swap
608 connection slave;
609 set session gtid_domain_id= 0;
610 CREATE TABLE repl_t (a int);
611 INSERT INTO repl_t VALUES (1);
612 connection master;
613 # Validating that replication works..
614 # ..success
615 # Cleaning up replication check data
616 connection slave;
617 DROP TABLE repl_t;
618 connection master;
619 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' OR VARIABLE_NAME LIKE 'gtid_current_pos' ORDER BY VARIABLE_NAME ASC;
620 VARIABLE_NAME GLOBAL_VALUE
621 GTID_BINLOG_POS 0-1-26,1-3-4,2-1-3,3-1-2,4-3-2
622 GTID_CURRENT_POS 0-2-32,1-3-4,2-1-3,3-1-2,4-3-2
623 GTID_SLAVE_POS 0-2-32,1-3-4,2-1-3,3-1-2,4-3-2
624 ##############################################
625 # Connection semantics change:
626 # * True primary is back to connection 'master'
627 # * True replica is back to connection 'slave'
628 ##############################################
629 connection master;
630 connection slave;
631 CHANGE MASTER TO master_host='127.0.0.1', master_port=MASTER_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
632 connection master;
633 # Tag gtid_slave_pos now (before binlog updates) for later comparison
634 connection master;
635 # In domain 1, make gtid_slave_pos < gtid_binlog_pos
636 set session gtid_domain_id= 1;
637 INSERT INTO t2 VALUES (4);
638 # In domains 2, 3 and 4, gtid_slave_pos == gtid_binlog_pos
639 # Include a new domain id (5)
640 set session gtid_domain_id= 5;
641 CREATE TABLE t6 (a int);
642 INSERT INTO t6 VALUES (1);
643 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' ORDER BY VARIABLE_NAME ASC;
644 VARIABLE_NAME GLOBAL_VALUE
645 GTID_BINLOG_POS 0-1-26,1-1-5,2-1-3,3-1-2,4-3-2,5-1-2
646 GTID_SLAVE_POS 0-2-32,1-3-4,2-1-3,3-1-2,4-3-2
647 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=0;
648 # Validating gtid_slave_pos is unchanged..
649 # ..success
650 # Clean slave state of master
651 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=slave_pos, master_demote_to_slave=1;
652 RESET SLAVE ALL;
653 SELECT VARIABLE_NAME, GLOBAL_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'gtid_binlog_pos' OR VARIABLE_NAME LIKE 'gtid_slave_pos' ORDER BY VARIABLE_NAME ASC;
654 VARIABLE_NAME GLOBAL_VALUE
655 GTID_BINLOG_POS 0-1-26,1-1-5,2-1-3,3-1-2,4-3-2,5-1-2
656 GTID_SLAVE_POS 0-2-32,1-1-5,2-1-3,3-1-2,4-3-2,5-1-2
657 #
658 #
659 # Stop slave for error test cases
660 connection slave;
661 include/stop_slave.inc
662 #
663 # Error Case 1: MASTER_DEMOTE_TO_SLAVE=1 combined with
664 # MASTER_USE_GTID=NO should result in an error.
665 #
666 connection master;
667 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=no, master_demote_to_slave=1;
668 ERROR HY000: CHANGE MASTER TO option 'MASTER_DEMOTE_TO_SLAVE=TRUE' is missing requirement Using_Gtid=Slave_Pos
669 #
670 # Error Case 2: Error when MASTER_DEMOTE_TO_SLAVE=1 is used without
671 # binary logging enabled.
672 #
673 connection master;
674 # Restarting master without binary log
675 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=Slave_Pos, master_demote_to_slave=1;
676 ERROR HY000: You are not using binary logging
677 # Restarting master to re-enable binary log
678 connection server_1;
679 connection default;
680 #
681 # Error Case 3: Error when MASTER_DEMOTE_TO_SLAVE is provided a
682 # non-boolean value.
683 #
684 connection master;
685 CHANGE MASTER TO master_host='127.0.0.1', master_port=SLAVE_PORT, master_user='root', master_use_gtid=Slave_Pos, master_demote_to_slave=invalid;
686 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'invalid' at line 1
687 #
688 # MDEV-31768
689 # Ensure MASTER_DEMOTE_TO_REPLICA aliases MASTER_DEMOTE_TO_SLAVE
690 #
691 connection slave;
692 RESET MASTER;
693 include/reset_slave.inc
694 CREATE TABLE t_mdev_31768 (a int);
695 CHANGE MASTER TO master_use_gtid=Replica_Pos, master_demote_to_replica=1;
696 # Validating alias MASTER_DEMOTE_TO_REPLICA provides intended behavior..
697 # ..success
698 DROP TABLE t_mdev_31768;
699 RESET MASTER;
700 include/reset_slave.inc
701 # Clear primary binlog state to match replica
702 connection master;
703 RESET MASTER;
704 #
705 # Cleanup
706 #
707 connection master;
708 DROP TABLE t1, t2, t3, t4, t5, t6;
709 include/save_master_gtid.inc
710 connection slave;
711 include/start_slave.inc
712 include/sync_with_master_gtid.inc
713 include/rpl_end.inc
714 #
715 ### End rpl_change_master_demote.test