Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_conflict_epoch2.result

raw
1 Testing NDB algorithm
2 NDB is almost the same as NDB, except that:
3 - The Primary / Secondary roles are controlled by a server variable
4 - Secondary I/U/D ops are 'reflected' back to the Secondary to handle
5 the delete vs delete limitations of NDB
6
7 This testcase provides some regression coverage w.r.t. NDB, by
8 running many of the same tests on NDB, but also switching
9 the cluster roles.
10 The main result differences w.r.t ndb_rpl_conflict_epoch.result are :
11 - Result file has 2 passes for different roles
12 - Master and Local server ids swapped in second run
13 - Delete-Delete-Insert (case 8) shows no divergence
14
15 Prior to including the generic NDB test files, we setup the
16 conflict role variables
17 Setup connections for use in this file
18 First we have Cluster A as Primary, Cluster B as Secondary
19 SET GLOBAL ndb_applier_conflict_role="PRIMARY";
20 SET GLOBAL ndb_applier_conflict_role="SECONDARY";
21 include/master-slave.inc
22 Warnings:
23 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
24 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
25 [connection master]
26 Setup circular replication
27 The circle is setup between Primary and Secondary
28 Setup ndb_replication and t1 exceptions table
29 Populate ndb_replication table as necessary
30 -- 0 extra gci bits
31 create table `test`.`t1$EX`
32 (server_id int unsigned,
33 master_server_id int unsigned,
34 master_epoch bigint unsigned,
35 count int unsigned,
36 a int not null,
37 d int,
38 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
39 Create table
40 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
41 Create other table
42 create table test.t2(a int primary key, b int) engine = ndb;
43 ----------------------------------
44 Test 1 : Basic two-way replication
45 ----------------------------------
46 insert into test.t1 values (1, "Metropole");
47 FLUSH LOGS;
48 -- Verify that Max rep epoch has been updated
49 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
50 -- Now update data on slave
51 update test.t1 set b="Favorit" where a=1;
52 -- Now check data on Primary
53 select * from test.t1 order by a;
54 a b
55 1 Favorit
56 Conflict algorithm counter diff :
57 Exception table contents
58 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
59 server_id master_server_id master_epoch count a d
60 SELECT * FROM `t1$EX` ORDER BY a, d;
61 server_id master_server_id master_epoch count a d
62 -- Now perform multiple consecutive updates on the Secondary
63 update test.t1 set b="Elephant house" where a=1;
64 update test.t1 set b="Beach house" where a=1;
65 select * from test.t1 order by a;
66 a b
67 1 Beach house
68 -- Now check they've applied on the Primary
69 select * from test.t1 order by a;
70 a b
71 1 Beach house
72 Conflict algorithm counter diff :
73 Exception table contents
74 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
75 server_id master_server_id master_epoch count a d
76 SELECT * FROM `t1$EX` ORDER BY a, d;
77 server_id master_server_id master_epoch count a d
78 --------------------------------------------
79 Test 2 : Normal Insert from Secondary Master
80 --------------------------------------------
81 -- Insert a new row on the Secondary
82 insert into test.t1 values (2, "Forrest");
83 -- Check it exists on the Primary
84 select * from test.t1 order by a;
85 a b
86 1 Beach house
87 2 Forrest
88 -- Update from the Secondary
89 update test.t1 set b="Reds" where a=2;
90 select * from test.t1 order by a;
91 a b
92 1 Beach house
93 2 Reds
94 delete from test.t1 where a=2;
95 select * from test.t1 order by a;
96 a b
97 1 Beach house
98 Conflict algorithm counter diff :
99 Exception table contents
100 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
101 server_id master_server_id master_epoch count a d
102 SELECT * FROM `t1$EX` ORDER BY a, d;
103 server_id master_server_id master_epoch count a d
104 -------------------------------
105 Test 3 : Insert-Insert conflict
106 -------------------------------
107 stop slave;
108 Warnings:
109 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
110 -- Insert a row on the Primary Master
111 insert into test.t1 values (2, "Loopy Lornas");
112 -- Insert a row on the secondary Master
113 insert into test.t1 values (2, "Cloisters");
114 -- Examine data on Primary Master (should be unaffected)
115 select * from test.t1 order by a;
116 a b
117 1 Beach house
118 2 Loopy Lornas
119 -- Examine conflict indicators on Primary Master
120 Conflict algorithm counter diff :
121 ndb_conflict_fn_epoch2
122 1
123 Exception table contents
124 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
125 server_id master_server_id master_epoch count a d
126 1 3 # 1 # #
127 SELECT * FROM `t1$EX` ORDER BY a, d;
128 server_id master_server_id master_epoch count a d
129 1 3 # # 2 NULL
130 -- Examine data on isolated secondary Master (should be as-set)
131 select * from test.t1 order by a;
132 a b
133 1 Beach house
134 2 Cloisters
135 -- Restart secondary Masters slave
136 start slave;
137 Warnings:
138 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
139 -- Reexamine secondary Master's data (should be same as Primary Masters)
140 select * from test.t1 order by a;
141 a b
142 1 Beach house
143 2 Loopy Lornas
144 -------------------------------
145 Test 4 : Update-Update conflict
146 -------------------------------
147 -- Stop replication to secondary master
148 stop slave;
149 Warnings:
150 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
151 -- Update row on Primary Master
152 update test.t1 set b="Peters Yard" where a=2;
153 -- Show data on Primary Master
154 select * from test.t1 order by a;
155 a b
156 1 Beach house
157 2 Peters Yard
158 Conflict algorithm counter diff :
159 ndb_conflict_fn_epoch2
160 1
161 Exception table contents
162 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
163 server_id master_server_id master_epoch count a d
164 1 3 # 1 # #
165 SELECT * FROM `t1$EX` ORDER BY a, d;
166 server_id master_server_id master_epoch count a d
167 1 3 # # 2 NULL
168 -- Update row on Secondary Master
169 update test.t1 set b="Toast" where a=2;
170 -- Examine data on Primary Master - should be unaffected
171 select * from test.t1 order by a;
172 a b
173 1 Beach house
174 2 Peters Yard
175 Conflict algorithm counter diff :
176 ndb_conflict_fn_epoch2
177 2
178 Exception table contents
179 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
180 server_id master_server_id master_epoch count a d
181 1 3 # 1 # #
182 1 3 # 2 # #
183 SELECT * FROM `t1$EX` ORDER BY a, d;
184 server_id master_server_id master_epoch count a d
185 1 3 # # 2 NULL
186 1 3 # # 2 NULL
187 -- Check data on secondary - should be as set
188 select * from test.t1 order by a;
189 a b
190 1 Beach house
191 2 Toast
192 -- Now restart slave, will re-align row
193 start slave;
194 Warnings:
195 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
196 -- Check that Secondary is re-aligned
197 select * from test.t1 order by a;
198 a b
199 1 Beach house
200 2 Peters Yard
201 -------------------------------
202 Test 5 : Update-Delete conflict
203 -------------------------------
204 -- Stop Secondary slave
205 stop slave;
206 Warnings:
207 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
208 -- Update on Primary Master
209 update test.t1 set b="Pear tree" where a = 2;
210 -- Delete on Secondary Master
211 delete from test.t1 where a = 2;
212 -- Examine data on Primary Master, should be unaffected
213 select * from test.t1 order by a;
214 a b
215 1 Beach house
216 2 Pear tree
217 Conflict algorithm counter diff :
218 ndb_conflict_fn_epoch2
219 3
220 Exception table contents
221 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
222 server_id master_server_id master_epoch count a d
223 1 3 # 1 # #
224 1 3 # 2 # #
225 1 3 # 3 # #
226 SELECT * FROM `t1$EX` ORDER BY a, d;
227 server_id master_server_id master_epoch count a d
228 1 3 # # 2 NULL
229 1 3 # # 2 NULL
230 1 3 # # 2 NULL
231 -- Examine data on Secondary Master before slave restart, still missing
232 select * from test.t1 order by a;
233 a b
234 1 Beach house
235 -- Restart Secondary Master slave
236 start slave;
237 Warnings:
238 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
239 -- Examine data on Secondary Master after slave restart, aligned with Master
240 select * from test.t1 order by a;
241 a b
242 1 Beach house
243 2 Pear tree
244 -------------------------------
245 Test 6 : Delete-Update conflict
246 -------------------------------
247 -- Stop Secondary slave
248 stop slave;
249 Warnings:
250 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
251 -- Delete on Primary Master
252 delete from test.t1 where a=2;
253 -- Update on Secondary Master
254 update test.t1 set b="Black pig" where a=2;
255 -- Examine data on Primary Master, should be unaffected (no row)
256 select * from test.t1 order by a;
257 a b
258 1 Beach house
259 Conflict algorithm counter diff :
260 ndb_conflict_fn_epoch2
261 4
262 Exception table contents
263 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
264 server_id master_server_id master_epoch count a d
265 1 3 # 1 # #
266 1 3 # 2 # #
267 1 3 # 3 # #
268 1 3 # 4 # #
269 SELECT * FROM `t1$EX` ORDER BY a, d;
270 server_id master_server_id master_epoch count a d
271 1 3 # # 2 NULL
272 1 3 # # 2 NULL
273 1 3 # # 2 NULL
274 1 3 # # 2 NULL
275 -- Examine data on Secondary Master before slave restart, should be as inserted
276 select * from test.t1 order by a;
277 a b
278 1 Beach house
279 2 Black pig
280 -- Restart Secondary Master slave
281 start slave;
282 Warnings:
283 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
284 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
285 select * from test.t1 order by a;
286 a b
287 1 Beach house
288 -------------------------------
289 Test 7 : Delete-Delete conflict
290 -------------------------------
291 -- Stop Secondary slave
292 stop slave;
293 Warnings:
294 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
295 -- Delete on Primary Master
296 delete from test.t1 where a=1;
297 -- Delete on Secondary Master
298 delete from test.t1 where a=1;
299 -- Examine data on Primary Master, no row
300 select * from test.t1 order by a;
301 a b
302 Conflict algorithm counter diff :
303 ndb_conflict_fn_epoch2
304 5
305 ndb_epoch_delete_delete
306 1
307 Exception table contents
308 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
309 server_id master_server_id master_epoch count a d
310 1 3 # 1 # #
311 1 3 # 2 # #
312 1 3 # 3 # #
313 1 3 # 4 # #
314 1 3 # 5 # #
315 SELECT * FROM `t1$EX` ORDER BY a, d;
316 server_id master_server_id master_epoch count a d
317 1 3 # # 1 NULL
318 1 3 # # 2 NULL
319 1 3 # # 2 NULL
320 1 3 # # 2 NULL
321 1 3 # # 2 NULL
322 -- Examine data on Secondary Master before slave restart, no row
323 select * from test.t1 order by a;
324 a b
325 -- Restart Secondary Master slave
326 start slave;
327 Warnings:
328 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
329 -- Examine data on Secondary Master after slave restart, no row
330 select * from test.t1 order by a;
331 a b
332 ------------------------------------------------
333 Test 8 : Delete-Delete, Insert conflict exposure
334 ------------------------------------------------
335 -- Insert a row on Secondary Master
336 insert into test.t1 values (3, "Espy");
337 -- Check it's present on Primary Master
338 select * from test.t1 order by a;
339 a b
340 3 Espy
341 -- Stop replication in both directions
342 stop slave;
343 Warnings:
344 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
345 stop slave;
346 Warnings:
347 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
348 -- Delete row from both clusters
349 delete from test.t1 where a=3;
350 delete from test.t1 where a=3;
351 -- Follow up with Insert from Secondary master
352 insert into test.t1 values (3, "Dalriada");
353 -- Restart replication in both directions
354 start slave;
355 Warnings:
356 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
357 start slave;
358 Warnings:
359 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
360 -- Check data on both sites
361 -- diverged with NDB$EPOCH
362 -- consistent with NDB$EPOCH2
363 -- Secondary master :
364 select * from test.t1 order by a;
365 a b
366 3 Dalriada
367 -- Primary master :
368 select * from test.t1 order by a;
369 a b
370 3 Dalriada
371 --Remove extra row
372 delete from test.t1 where a=3;
373 -- Note that Delete-Delete conflict detected below
374 Conflict algorithm counter diff :
375 ndb_conflict_fn_epoch2
376 6
377 ndb_epoch_delete_delete
378 2
379 Exception table contents
380 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
381 server_id master_server_id master_epoch count a d
382 1 3 # 1 # #
383 1 3 # 2 # #
384 1 3 # 3 # #
385 1 3 # 4 # #
386 1 3 # 5 # #
387 1 3 # 6 # #
388 SELECT * FROM `t1$EX` ORDER BY a, d;
389 server_id master_server_id master_epoch count a d
390 1 3 # # 1 NULL
391 1 3 # # 2 NULL
392 1 3 # # 2 NULL
393 1 3 # # 2 NULL
394 1 3 # # 2 NULL
395 1 3 # # 3 NULL
396 ------------------------------------------------
397 Test 9 : Insert, Insert-Update-Delete conflict
398 ------------------------------------------------
399 -- Stop replication on Secondary Master
400 stop slave;
401 Warnings:
402 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
403 -- Insert row on Primary Master
404 insert into test.t1 values (4, "Haymarket");
405 -- Insert row on Secondary Master
406 insert into test.t1 values (4, "Outhouse");
407 -- Update row on Secondary Master
408 update test.t1 set b="Mathers" where a = 4;
409 -- Delete row on Secondary Master
410 delete from test.t1 where a=4;
411 -- Examine data (none) on Secondary Master
412 select * from test.t1 order by a;
413 a b
414 -- Examine data on Primary Master, should be unaffected
415 select * from test.t1 order by a;
416 a b
417 4 Haymarket
418 Conflict algorithm counter diff :
419 ndb_conflict_fn_epoch2
420 9
421 ndb_epoch_delete_delete
422 2
423 Exception table contents
424 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
425 server_id master_server_id master_epoch count a d
426 1 3 # 1 # #
427 1 3 # 2 # #
428 1 3 # 3 # #
429 1 3 # 4 # #
430 1 3 # 5 # #
431 1 3 # 6 # #
432 1 3 # 7 # #
433 1 3 # 8 # #
434 1 3 # 9 # #
435 SELECT * FROM `t1$EX` ORDER BY a, d;
436 server_id master_server_id master_epoch count a d
437 1 3 # # 1 NULL
438 1 3 # # 2 NULL
439 1 3 # # 2 NULL
440 1 3 # # 2 NULL
441 1 3 # # 2 NULL
442 1 3 # # 3 NULL
443 1 3 # # 4 NULL
444 1 3 # # 4 NULL
445 1 3 # # 4 NULL
446 -- Examine data on Secondary Master (none)
447 select * from test.t1 order by a;
448 a b
449 -- Restart Secondary Master's slave
450 start slave;
451 Warnings:
452 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
453 -- Check data on Secondary Master, should be same as Primary Master
454 select * from test.t1;
455 a b
456 4 Haymarket
457 ------------------------------------------------
458 Test 10 : Update, Delete-Insert-Update conflict
459 ------------------------------------------------
460 -- Stop replication on Secondary Master
461 stop slave;
462 Warnings:
463 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
464 -- Update row on Primary Master
465 update test.t1 set b="Blind poet" where a=4;
466 -- Delete row on Secondary Master
467 delete from test.t1 where a=4;
468 -- Insert row on Secondary Master
469 insert into test.t1 values (4, "Drouthy Neebors");
470 -- Update row on Secondary Master
471 update test.t1 set b="The Tankard" where a=4;
472 -- Check data on Primary Master, should be unaffected
473 select * from test.t1 order by a;
474 a b
475 4 Blind poet
476 Conflict algorithm counter diff :
477 ndb_conflict_fn_epoch2
478 12
479 ndb_epoch_delete_delete
480 2
481 Exception table contents
482 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
483 server_id master_server_id master_epoch count a d
484 1 3 # 1 # #
485 1 3 # 2 # #
486 1 3 # 3 # #
487 1 3 # 4 # #
488 1 3 # 5 # #
489 1 3 # 6 # #
490 1 3 # 7 # #
491 1 3 # 8 # #
492 1 3 # 9 # #
493 1 3 # 10 # #
494 1 3 # 11 # #
495 1 3 # 12 # #
496 SELECT * FROM `t1$EX` ORDER BY a, d;
497 server_id master_server_id master_epoch count a d
498 1 3 # # 1 NULL
499 1 3 # # 2 NULL
500 1 3 # # 2 NULL
501 1 3 # # 2 NULL
502 1 3 # # 2 NULL
503 1 3 # # 3 NULL
504 1 3 # # 4 NULL
505 1 3 # # 4 NULL
506 1 3 # # 4 NULL
507 1 3 # # 4 NULL
508 1 3 # # 4 NULL
509 1 3 # # 4 NULL
510 -- Check data on Secondary Master, as set
511 select * from test.t1 order by a;
512 a b
513 4 The Tankard
514 -- Restart Secondary Master slave
515 start slave;
516 Warnings:
517 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
518 -- Check data on Secondary Master - should be as Primary
519 select * from test.t1 order by a;
520 a b
521 4 Blind poet
522 ------------------------------------------------------------------------
523 Test 11 : Test Secondary insert-update-delete accepted
524 ------------------------------------------------------------------------
525 Insert row on Secondary
526 insert into test.t1 values (5, "Minders");
527 Update row on Secondary
528 update test.t1 set b="Southsider" where a=5;
529 Delete row on Secondary
530 delete from test.t1 where a=5;
531 Check data on Primary, should be none. No new conflicts
532 select * from test.t1 order by a;
533 a b
534 4 Blind poet
535 Conflict algorithm counter diff :
536 ndb_conflict_fn_epoch2
537 12
538 ndb_epoch_delete_delete
539 2
540 Exception table contents
541 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
542 server_id master_server_id master_epoch count a d
543 1 3 # 1 # #
544 1 3 # 2 # #
545 1 3 # 3 # #
546 1 3 # 4 # #
547 1 3 # 5 # #
548 1 3 # 6 # #
549 1 3 # 7 # #
550 1 3 # 8 # #
551 1 3 # 9 # #
552 1 3 # 10 # #
553 1 3 # 11 # #
554 1 3 # 12 # #
555 SELECT * FROM `t1$EX` ORDER BY a, d;
556 server_id master_server_id master_epoch count a d
557 1 3 # # 1 NULL
558 1 3 # # 2 NULL
559 1 3 # # 2 NULL
560 1 3 # # 2 NULL
561 1 3 # # 2 NULL
562 1 3 # # 3 NULL
563 1 3 # # 4 NULL
564 1 3 # # 4 NULL
565 1 3 # # 4 NULL
566 1 3 # # 4 NULL
567 1 3 # # 4 NULL
568 1 3 # # 4 NULL
569 Conflict algorithm counter diff :
570 ndb_conflict_fn_epoch2
571 12
572 ndb_epoch_delete_delete
573 2
574 Exception table contents
575 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
576 server_id master_server_id master_epoch count a d
577 1 3 # 1 # #
578 1 3 # 2 # #
579 1 3 # 3 # #
580 1 3 # 4 # #
581 1 3 # 5 # #
582 1 3 # 6 # #
583 1 3 # 7 # #
584 1 3 # 8 # #
585 1 3 # 9 # #
586 1 3 # 10 # #
587 1 3 # 11 # #
588 1 3 # 12 # #
589 SELECT * FROM `t1$EX` ORDER BY a, d;
590 server_id master_server_id master_epoch count a d
591 1 3 # # 1 NULL
592 1 3 # # 2 NULL
593 1 3 # # 2 NULL
594 1 3 # # 2 NULL
595 1 3 # # 2 NULL
596 1 3 # # 3 NULL
597 1 3 # # 4 NULL
598 1 3 # # 4 NULL
599 1 3 # # 4 NULL
600 1 3 # # 4 NULL
601 1 3 # # 4 NULL
602 1 3 # # 4 NULL
603 drop table test.t1;
604 drop table test.t2;
605 drop table test.t1$EX;
606 Populate ndb_replication table as necessary
607 -- 1 extra gci bits
608 create table `test`.`t1$EX`
609 (server_id int unsigned,
610 master_server_id int unsigned,
611 master_epoch bigint unsigned,
612 count int unsigned,
613 a int not null,
614 d int,
615 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
616 Create table
617 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
618 Create other table
619 create table test.t2(a int primary key, b int) engine = ndb;
620 ----------------------------------
621 Test 1 : Basic two-way replication
622 ----------------------------------
623 insert into test.t1 values (1, "Metropole");
624 FLUSH LOGS;
625 -- Verify that Max rep epoch has been updated
626 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
627 -- Now update data on slave
628 update test.t1 set b="Favorit" where a=1;
629 -- Now check data on Primary
630 select * from test.t1 order by a;
631 a b
632 1 Favorit
633 Conflict algorithm counter diff :
634 Exception table contents
635 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
636 server_id master_server_id master_epoch count a d
637 SELECT * FROM `t1$EX` ORDER BY a, d;
638 server_id master_server_id master_epoch count a d
639 -- Now perform multiple consecutive updates on the Secondary
640 update test.t1 set b="Elephant house" where a=1;
641 update test.t1 set b="Beach house" where a=1;
642 select * from test.t1 order by a;
643 a b
644 1 Beach house
645 -- Now check they've applied on the Primary
646 select * from test.t1 order by a;
647 a b
648 1 Beach house
649 Conflict algorithm counter diff :
650 Exception table contents
651 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
652 server_id master_server_id master_epoch count a d
653 SELECT * FROM `t1$EX` ORDER BY a, d;
654 server_id master_server_id master_epoch count a d
655 --------------------------------------------
656 Test 2 : Normal Insert from Secondary Master
657 --------------------------------------------
658 -- Insert a new row on the Secondary
659 insert into test.t1 values (2, "Forrest");
660 -- Check it exists on the Primary
661 select * from test.t1 order by a;
662 a b
663 1 Beach house
664 2 Forrest
665 -- Update from the Secondary
666 update test.t1 set b="Reds" where a=2;
667 select * from test.t1 order by a;
668 a b
669 1 Beach house
670 2 Reds
671 delete from test.t1 where a=2;
672 select * from test.t1 order by a;
673 a b
674 1 Beach house
675 Conflict algorithm counter diff :
676 Exception table contents
677 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
678 server_id master_server_id master_epoch count a d
679 SELECT * FROM `t1$EX` ORDER BY a, d;
680 server_id master_server_id master_epoch count a d
681 -------------------------------
682 Test 3 : Insert-Insert conflict
683 -------------------------------
684 stop slave;
685 Warnings:
686 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
687 -- Insert a row on the Primary Master
688 insert into test.t1 values (2, "Loopy Lornas");
689 -- Insert a row on the secondary Master
690 insert into test.t1 values (2, "Cloisters");
691 -- Examine data on Primary Master (should be unaffected)
692 select * from test.t1 order by a;
693 a b
694 1 Beach house
695 2 Loopy Lornas
696 -- Examine conflict indicators on Primary Master
697 Conflict algorithm counter diff :
698 ndb_conflict_fn_epoch2
699 1
700 Exception table contents
701 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
702 server_id master_server_id master_epoch count a d
703 1 3 # 1 # #
704 SELECT * FROM `t1$EX` ORDER BY a, d;
705 server_id master_server_id master_epoch count a d
706 1 3 # # 2 NULL
707 -- Examine data on isolated secondary Master (should be as-set)
708 select * from test.t1 order by a;
709 a b
710 1 Beach house
711 2 Cloisters
712 -- Restart secondary Masters slave
713 start slave;
714 Warnings:
715 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
716 -- Reexamine secondary Master's data (should be same as Primary Masters)
717 select * from test.t1 order by a;
718 a b
719 1 Beach house
720 2 Loopy Lornas
721 -------------------------------
722 Test 4 : Update-Update conflict
723 -------------------------------
724 -- Stop replication to secondary master
725 stop slave;
726 Warnings:
727 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
728 -- Update row on Primary Master
729 update test.t1 set b="Peters Yard" where a=2;
730 -- Show data on Primary Master
731 select * from test.t1 order by a;
732 a b
733 1 Beach house
734 2 Peters Yard
735 Conflict algorithm counter diff :
736 ndb_conflict_fn_epoch2
737 1
738 Exception table contents
739 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
740 server_id master_server_id master_epoch count a d
741 1 3 # 1 # #
742 SELECT * FROM `t1$EX` ORDER BY a, d;
743 server_id master_server_id master_epoch count a d
744 1 3 # # 2 NULL
745 -- Update row on Secondary Master
746 update test.t1 set b="Toast" where a=2;
747 -- Examine data on Primary Master - should be unaffected
748 select * from test.t1 order by a;
749 a b
750 1 Beach house
751 2 Peters Yard
752 Conflict algorithm counter diff :
753 ndb_conflict_fn_epoch2
754 2
755 Exception table contents
756 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
757 server_id master_server_id master_epoch count a d
758 1 3 # 1 # #
759 1 3 # 2 # #
760 SELECT * FROM `t1$EX` ORDER BY a, d;
761 server_id master_server_id master_epoch count a d
762 1 3 # # 2 NULL
763 1 3 # # 2 NULL
764 -- Check data on secondary - should be as set
765 select * from test.t1 order by a;
766 a b
767 1 Beach house
768 2 Toast
769 -- Now restart slave, will re-align row
770 start slave;
771 Warnings:
772 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
773 -- Check that Secondary is re-aligned
774 select * from test.t1 order by a;
775 a b
776 1 Beach house
777 2 Peters Yard
778 -------------------------------
779 Test 5 : Update-Delete conflict
780 -------------------------------
781 -- Stop Secondary slave
782 stop slave;
783 Warnings:
784 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
785 -- Update on Primary Master
786 update test.t1 set b="Pear tree" where a = 2;
787 -- Delete on Secondary Master
788 delete from test.t1 where a = 2;
789 -- Examine data on Primary Master, should be unaffected
790 select * from test.t1 order by a;
791 a b
792 1 Beach house
793 2 Pear tree
794 Conflict algorithm counter diff :
795 ndb_conflict_fn_epoch2
796 3
797 Exception table contents
798 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
799 server_id master_server_id master_epoch count a d
800 1 3 # 1 # #
801 1 3 # 2 # #
802 1 3 # 3 # #
803 SELECT * FROM `t1$EX` ORDER BY a, d;
804 server_id master_server_id master_epoch count a d
805 1 3 # # 2 NULL
806 1 3 # # 2 NULL
807 1 3 # # 2 NULL
808 -- Examine data on Secondary Master before slave restart, still missing
809 select * from test.t1 order by a;
810 a b
811 1 Beach house
812 -- Restart Secondary Master slave
813 start slave;
814 Warnings:
815 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
816 -- Examine data on Secondary Master after slave restart, aligned with Master
817 select * from test.t1 order by a;
818 a b
819 1 Beach house
820 2 Pear tree
821 -------------------------------
822 Test 6 : Delete-Update conflict
823 -------------------------------
824 -- Stop Secondary slave
825 stop slave;
826 Warnings:
827 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
828 -- Delete on Primary Master
829 delete from test.t1 where a=2;
830 -- Update on Secondary Master
831 update test.t1 set b="Black pig" where a=2;
832 -- Examine data on Primary Master, should be unaffected (no row)
833 select * from test.t1 order by a;
834 a b
835 1 Beach house
836 Conflict algorithm counter diff :
837 ndb_conflict_fn_epoch2
838 4
839 Exception table contents
840 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
841 server_id master_server_id master_epoch count a d
842 1 3 # 1 # #
843 1 3 # 2 # #
844 1 3 # 3 # #
845 1 3 # 4 # #
846 SELECT * FROM `t1$EX` ORDER BY a, d;
847 server_id master_server_id master_epoch count a d
848 1 3 # # 2 NULL
849 1 3 # # 2 NULL
850 1 3 # # 2 NULL
851 1 3 # # 2 NULL
852 -- Examine data on Secondary Master before slave restart, should be as inserted
853 select * from test.t1 order by a;
854 a b
855 1 Beach house
856 2 Black pig
857 -- Restart Secondary Master slave
858 start slave;
859 Warnings:
860 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
861 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
862 select * from test.t1 order by a;
863 a b
864 1 Beach house
865 -------------------------------
866 Test 7 : Delete-Delete conflict
867 -------------------------------
868 -- Stop Secondary slave
869 stop slave;
870 Warnings:
871 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
872 -- Delete on Primary Master
873 delete from test.t1 where a=1;
874 -- Delete on Secondary Master
875 delete from test.t1 where a=1;
876 -- Examine data on Primary Master, no row
877 select * from test.t1 order by a;
878 a b
879 Conflict algorithm counter diff :
880 ndb_conflict_fn_epoch2
881 5
882 ndb_epoch_delete_delete
883 1
884 Exception table contents
885 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
886 server_id master_server_id master_epoch count a d
887 1 3 # 1 # #
888 1 3 # 2 # #
889 1 3 # 3 # #
890 1 3 # 4 # #
891 1 3 # 5 # #
892 SELECT * FROM `t1$EX` ORDER BY a, d;
893 server_id master_server_id master_epoch count a d
894 1 3 # # 1 NULL
895 1 3 # # 2 NULL
896 1 3 # # 2 NULL
897 1 3 # # 2 NULL
898 1 3 # # 2 NULL
899 -- Examine data on Secondary Master before slave restart, no row
900 select * from test.t1 order by a;
901 a b
902 -- Restart Secondary Master slave
903 start slave;
904 Warnings:
905 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
906 -- Examine data on Secondary Master after slave restart, no row
907 select * from test.t1 order by a;
908 a b
909 ------------------------------------------------
910 Test 8 : Delete-Delete, Insert conflict exposure
911 ------------------------------------------------
912 -- Insert a row on Secondary Master
913 insert into test.t1 values (3, "Espy");
914 -- Check it's present on Primary Master
915 select * from test.t1 order by a;
916 a b
917 3 Espy
918 -- Stop replication in both directions
919 stop slave;
920 Warnings:
921 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
922 stop slave;
923 Warnings:
924 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
925 -- Delete row from both clusters
926 delete from test.t1 where a=3;
927 delete from test.t1 where a=3;
928 -- Follow up with Insert from Secondary master
929 insert into test.t1 values (3, "Dalriada");
930 -- Restart replication in both directions
931 start slave;
932 Warnings:
933 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
934 start slave;
935 Warnings:
936 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
937 -- Check data on both sites
938 -- diverged with NDB$EPOCH
939 -- consistent with NDB$EPOCH2
940 -- Secondary master :
941 select * from test.t1 order by a;
942 a b
943 3 Dalriada
944 -- Primary master :
945 select * from test.t1 order by a;
946 a b
947 3 Dalriada
948 --Remove extra row
949 delete from test.t1 where a=3;
950 -- Note that Delete-Delete conflict detected below
951 Conflict algorithm counter diff :
952 ndb_conflict_fn_epoch2
953 6
954 ndb_epoch_delete_delete
955 2
956 Exception table contents
957 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
958 server_id master_server_id master_epoch count a d
959 1 3 # 1 # #
960 1 3 # 2 # #
961 1 3 # 3 # #
962 1 3 # 4 # #
963 1 3 # 5 # #
964 1 3 # 6 # #
965 SELECT * FROM `t1$EX` ORDER BY a, d;
966 server_id master_server_id master_epoch count a d
967 1 3 # # 1 NULL
968 1 3 # # 2 NULL
969 1 3 # # 2 NULL
970 1 3 # # 2 NULL
971 1 3 # # 2 NULL
972 1 3 # # 3 NULL
973 ------------------------------------------------
974 Test 9 : Insert, Insert-Update-Delete conflict
975 ------------------------------------------------
976 -- Stop replication on Secondary Master
977 stop slave;
978 Warnings:
979 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
980 -- Insert row on Primary Master
981 insert into test.t1 values (4, "Haymarket");
982 -- Insert row on Secondary Master
983 insert into test.t1 values (4, "Outhouse");
984 -- Update row on Secondary Master
985 update test.t1 set b="Mathers" where a = 4;
986 -- Delete row on Secondary Master
987 delete from test.t1 where a=4;
988 -- Examine data (none) on Secondary Master
989 select * from test.t1 order by a;
990 a b
991 -- Examine data on Primary Master, should be unaffected
992 select * from test.t1 order by a;
993 a b
994 4 Haymarket
995 Conflict algorithm counter diff :
996 ndb_conflict_fn_epoch2
997 9
998 ndb_epoch_delete_delete
999 2
1000 Exception table contents
1001 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1002 server_id master_server_id master_epoch count a d
1003 1 3 # 1 # #
1004 1 3 # 2 # #
1005 1 3 # 3 # #
1006 1 3 # 4 # #
1007 1 3 # 5 # #
1008 1 3 # 6 # #
1009 1 3 # 7 # #
1010 1 3 # 8 # #
1011 1 3 # 9 # #
1012 SELECT * FROM `t1$EX` ORDER BY a, d;
1013 server_id master_server_id master_epoch count a d
1014 1 3 # # 1 NULL
1015 1 3 # # 2 NULL
1016 1 3 # # 2 NULL
1017 1 3 # # 2 NULL
1018 1 3 # # 2 NULL
1019 1 3 # # 3 NULL
1020 1 3 # # 4 NULL
1021 1 3 # # 4 NULL
1022 1 3 # # 4 NULL
1023 -- Examine data on Secondary Master (none)
1024 select * from test.t1 order by a;
1025 a b
1026 -- Restart Secondary Master's slave
1027 start slave;
1028 Warnings:
1029 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1030 -- Check data on Secondary Master, should be same as Primary Master
1031 select * from test.t1;
1032 a b
1033 4 Haymarket
1034 ------------------------------------------------
1035 Test 10 : Update, Delete-Insert-Update conflict
1036 ------------------------------------------------
1037 -- Stop replication on Secondary Master
1038 stop slave;
1039 Warnings:
1040 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1041 -- Update row on Primary Master
1042 update test.t1 set b="Blind poet" where a=4;
1043 -- Delete row on Secondary Master
1044 delete from test.t1 where a=4;
1045 -- Insert row on Secondary Master
1046 insert into test.t1 values (4, "Drouthy Neebors");
1047 -- Update row on Secondary Master
1048 update test.t1 set b="The Tankard" where a=4;
1049 -- Check data on Primary Master, should be unaffected
1050 select * from test.t1 order by a;
1051 a b
1052 4 Blind poet
1053 Conflict algorithm counter diff :
1054 ndb_conflict_fn_epoch2
1055 12
1056 ndb_epoch_delete_delete
1057 2
1058 Exception table contents
1059 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1060 server_id master_server_id master_epoch count a d
1061 1 3 # 1 # #
1062 1 3 # 2 # #
1063 1 3 # 3 # #
1064 1 3 # 4 # #
1065 1 3 # 5 # #
1066 1 3 # 6 # #
1067 1 3 # 7 # #
1068 1 3 # 8 # #
1069 1 3 # 9 # #
1070 1 3 # 10 # #
1071 1 3 # 11 # #
1072 1 3 # 12 # #
1073 SELECT * FROM `t1$EX` ORDER BY a, d;
1074 server_id master_server_id master_epoch count a d
1075 1 3 # # 1 NULL
1076 1 3 # # 2 NULL
1077 1 3 # # 2 NULL
1078 1 3 # # 2 NULL
1079 1 3 # # 2 NULL
1080 1 3 # # 3 NULL
1081 1 3 # # 4 NULL
1082 1 3 # # 4 NULL
1083 1 3 # # 4 NULL
1084 1 3 # # 4 NULL
1085 1 3 # # 4 NULL
1086 1 3 # # 4 NULL
1087 -- Check data on Secondary Master, as set
1088 select * from test.t1 order by a;
1089 a b
1090 4 The Tankard
1091 -- Restart Secondary Master slave
1092 start slave;
1093 Warnings:
1094 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1095 -- Check data on Secondary Master - should be as Primary
1096 select * from test.t1 order by a;
1097 a b
1098 4 Blind poet
1099 ------------------------------------------------------------------------
1100 Test 11 : Test Secondary insert-update-delete accepted
1101 ------------------------------------------------------------------------
1102 Insert row on Secondary
1103 insert into test.t1 values (5, "Minders");
1104 Update row on Secondary
1105 update test.t1 set b="Southsider" where a=5;
1106 Delete row on Secondary
1107 delete from test.t1 where a=5;
1108 Check data on Primary, should be none. No new conflicts
1109 select * from test.t1 order by a;
1110 a b
1111 4 Blind poet
1112 Conflict algorithm counter diff :
1113 ndb_conflict_fn_epoch2
1114 12
1115 ndb_epoch_delete_delete
1116 2
1117 Exception table contents
1118 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1119 server_id master_server_id master_epoch count a d
1120 1 3 # 1 # #
1121 1 3 # 2 # #
1122 1 3 # 3 # #
1123 1 3 # 4 # #
1124 1 3 # 5 # #
1125 1 3 # 6 # #
1126 1 3 # 7 # #
1127 1 3 # 8 # #
1128 1 3 # 9 # #
1129 1 3 # 10 # #
1130 1 3 # 11 # #
1131 1 3 # 12 # #
1132 SELECT * FROM `t1$EX` ORDER BY a, d;
1133 server_id master_server_id master_epoch count a d
1134 1 3 # # 1 NULL
1135 1 3 # # 2 NULL
1136 1 3 # # 2 NULL
1137 1 3 # # 2 NULL
1138 1 3 # # 2 NULL
1139 1 3 # # 3 NULL
1140 1 3 # # 4 NULL
1141 1 3 # # 4 NULL
1142 1 3 # # 4 NULL
1143 1 3 # # 4 NULL
1144 1 3 # # 4 NULL
1145 1 3 # # 4 NULL
1146 Conflict algorithm counter diff :
1147 ndb_conflict_fn_epoch2
1148 12
1149 ndb_epoch_delete_delete
1150 2
1151 Exception table contents
1152 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1153 server_id master_server_id master_epoch count a d
1154 1 3 # 1 # #
1155 1 3 # 2 # #
1156 1 3 # 3 # #
1157 1 3 # 4 # #
1158 1 3 # 5 # #
1159 1 3 # 6 # #
1160 1 3 # 7 # #
1161 1 3 # 8 # #
1162 1 3 # 9 # #
1163 1 3 # 10 # #
1164 1 3 # 11 # #
1165 1 3 # 12 # #
1166 SELECT * FROM `t1$EX` ORDER BY a, d;
1167 server_id master_server_id master_epoch count a d
1168 1 3 # # 1 NULL
1169 1 3 # # 2 NULL
1170 1 3 # # 2 NULL
1171 1 3 # # 2 NULL
1172 1 3 # # 2 NULL
1173 1 3 # # 3 NULL
1174 1 3 # # 4 NULL
1175 1 3 # # 4 NULL
1176 1 3 # # 4 NULL
1177 1 3 # # 4 NULL
1178 1 3 # # 4 NULL
1179 1 3 # # 4 NULL
1180 drop table test.t1;
1181 drop table test.t2;
1182 drop table test.t1$EX;
1183 Populate ndb_replication table as necessary
1184 -- 31 extra gci bits
1185 create table `test`.`t1$EX`
1186 (server_id int unsigned,
1187 master_server_id int unsigned,
1188 master_epoch bigint unsigned,
1189 count int unsigned,
1190 a int not null,
1191 d int,
1192 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
1193 Create table
1194 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
1195 Create other table
1196 create table test.t2(a int primary key, b int) engine = ndb;
1197 ----------------------------------
1198 Test 1 : Basic two-way replication
1199 ----------------------------------
1200 insert into test.t1 values (1, "Metropole");
1201 FLUSH LOGS;
1202 -- Verify that Max rep epoch has been updated
1203 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
1204 -- Now update data on slave
1205 update test.t1 set b="Favorit" where a=1;
1206 -- Now check data on Primary
1207 select * from test.t1 order by a;
1208 a b
1209 1 Favorit
1210 Conflict algorithm counter diff :
1211 Exception table contents
1212 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1213 server_id master_server_id master_epoch count a d
1214 SELECT * FROM `t1$EX` ORDER BY a, d;
1215 server_id master_server_id master_epoch count a d
1216 -- Now perform multiple consecutive updates on the Secondary
1217 update test.t1 set b="Elephant house" where a=1;
1218 update test.t1 set b="Beach house" where a=1;
1219 select * from test.t1 order by a;
1220 a b
1221 1 Beach house
1222 -- Now check they've applied on the Primary
1223 select * from test.t1 order by a;
1224 a b
1225 1 Beach house
1226 Conflict algorithm counter diff :
1227 Exception table contents
1228 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1229 server_id master_server_id master_epoch count a d
1230 SELECT * FROM `t1$EX` ORDER BY a, d;
1231 server_id master_server_id master_epoch count a d
1232 --------------------------------------------
1233 Test 2 : Normal Insert from Secondary Master
1234 --------------------------------------------
1235 -- Insert a new row on the Secondary
1236 insert into test.t1 values (2, "Forrest");
1237 -- Check it exists on the Primary
1238 select * from test.t1 order by a;
1239 a b
1240 1 Beach house
1241 2 Forrest
1242 -- Update from the Secondary
1243 update test.t1 set b="Reds" where a=2;
1244 select * from test.t1 order by a;
1245 a b
1246 1 Beach house
1247 2 Reds
1248 delete from test.t1 where a=2;
1249 select * from test.t1 order by a;
1250 a b
1251 1 Beach house
1252 Conflict algorithm counter diff :
1253 Exception table contents
1254 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1255 server_id master_server_id master_epoch count a d
1256 SELECT * FROM `t1$EX` ORDER BY a, d;
1257 server_id master_server_id master_epoch count a d
1258 -------------------------------
1259 Test 3 : Insert-Insert conflict
1260 -------------------------------
1261 stop slave;
1262 Warnings:
1263 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1264 -- Insert a row on the Primary Master
1265 insert into test.t1 values (2, "Loopy Lornas");
1266 -- Insert a row on the secondary Master
1267 insert into test.t1 values (2, "Cloisters");
1268 -- Examine data on Primary Master (should be unaffected)
1269 select * from test.t1 order by a;
1270 a b
1271 1 Beach house
1272 2 Loopy Lornas
1273 -- Examine conflict indicators on Primary Master
1274 Conflict algorithm counter diff :
1275 ndb_conflict_fn_epoch2
1276 1
1277 Exception table contents
1278 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1279 server_id master_server_id master_epoch count a d
1280 1 3 # 1 # #
1281 SELECT * FROM `t1$EX` ORDER BY a, d;
1282 server_id master_server_id master_epoch count a d
1283 1 3 # # 2 NULL
1284 -- Examine data on isolated secondary Master (should be as-set)
1285 select * from test.t1 order by a;
1286 a b
1287 1 Beach house
1288 2 Cloisters
1289 -- Restart secondary Masters slave
1290 start slave;
1291 Warnings:
1292 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1293 -- Reexamine secondary Master's data (should be same as Primary Masters)
1294 select * from test.t1 order by a;
1295 a b
1296 1 Beach house
1297 2 Loopy Lornas
1298 -------------------------------
1299 Test 4 : Update-Update conflict
1300 -------------------------------
1301 -- Stop replication to secondary master
1302 stop slave;
1303 Warnings:
1304 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1305 -- Update row on Primary Master
1306 update test.t1 set b="Peters Yard" where a=2;
1307 -- Show data on Primary Master
1308 select * from test.t1 order by a;
1309 a b
1310 1 Beach house
1311 2 Peters Yard
1312 Conflict algorithm counter diff :
1313 ndb_conflict_fn_epoch2
1314 1
1315 Exception table contents
1316 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1317 server_id master_server_id master_epoch count a d
1318 1 3 # 1 # #
1319 SELECT * FROM `t1$EX` ORDER BY a, d;
1320 server_id master_server_id master_epoch count a d
1321 1 3 # # 2 NULL
1322 -- Update row on Secondary Master
1323 update test.t1 set b="Toast" where a=2;
1324 -- Examine data on Primary Master - should be unaffected
1325 select * from test.t1 order by a;
1326 a b
1327 1 Beach house
1328 2 Peters Yard
1329 Conflict algorithm counter diff :
1330 ndb_conflict_fn_epoch2
1331 2
1332 Exception table contents
1333 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1334 server_id master_server_id master_epoch count a d
1335 1 3 # 1 # #
1336 1 3 # 2 # #
1337 SELECT * FROM `t1$EX` ORDER BY a, d;
1338 server_id master_server_id master_epoch count a d
1339 1 3 # # 2 NULL
1340 1 3 # # 2 NULL
1341 -- Check data on secondary - should be as set
1342 select * from test.t1 order by a;
1343 a b
1344 1 Beach house
1345 2 Toast
1346 -- Now restart slave, will re-align row
1347 start slave;
1348 Warnings:
1349 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1350 -- Check that Secondary is re-aligned
1351 select * from test.t1 order by a;
1352 a b
1353 1 Beach house
1354 2 Peters Yard
1355 -------------------------------
1356 Test 5 : Update-Delete conflict
1357 -------------------------------
1358 -- Stop Secondary slave
1359 stop slave;
1360 Warnings:
1361 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1362 -- Update on Primary Master
1363 update test.t1 set b="Pear tree" where a = 2;
1364 -- Delete on Secondary Master
1365 delete from test.t1 where a = 2;
1366 -- Examine data on Primary Master, should be unaffected
1367 select * from test.t1 order by a;
1368 a b
1369 1 Beach house
1370 2 Pear tree
1371 Conflict algorithm counter diff :
1372 ndb_conflict_fn_epoch2
1373 3
1374 Exception table contents
1375 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1376 server_id master_server_id master_epoch count a d
1377 1 3 # 1 # #
1378 1 3 # 2 # #
1379 1 3 # 3 # #
1380 SELECT * FROM `t1$EX` ORDER BY a, d;
1381 server_id master_server_id master_epoch count a d
1382 1 3 # # 2 NULL
1383 1 3 # # 2 NULL
1384 1 3 # # 2 NULL
1385 -- Examine data on Secondary Master before slave restart, still missing
1386 select * from test.t1 order by a;
1387 a b
1388 1 Beach house
1389 -- Restart Secondary Master slave
1390 start slave;
1391 Warnings:
1392 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1393 -- Examine data on Secondary Master after slave restart, aligned with Master
1394 select * from test.t1 order by a;
1395 a b
1396 1 Beach house
1397 2 Pear tree
1398 -------------------------------
1399 Test 6 : Delete-Update conflict
1400 -------------------------------
1401 -- Stop Secondary slave
1402 stop slave;
1403 Warnings:
1404 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1405 -- Delete on Primary Master
1406 delete from test.t1 where a=2;
1407 -- Update on Secondary Master
1408 update test.t1 set b="Black pig" where a=2;
1409 -- Examine data on Primary Master, should be unaffected (no row)
1410 select * from test.t1 order by a;
1411 a b
1412 1 Beach house
1413 Conflict algorithm counter diff :
1414 ndb_conflict_fn_epoch2
1415 4
1416 Exception table contents
1417 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1418 server_id master_server_id master_epoch count a d
1419 1 3 # 1 # #
1420 1 3 # 2 # #
1421 1 3 # 3 # #
1422 1 3 # 4 # #
1423 SELECT * FROM `t1$EX` ORDER BY a, d;
1424 server_id master_server_id master_epoch count a d
1425 1 3 # # 2 NULL
1426 1 3 # # 2 NULL
1427 1 3 # # 2 NULL
1428 1 3 # # 2 NULL
1429 -- Examine data on Secondary Master before slave restart, should be as inserted
1430 select * from test.t1 order by a;
1431 a b
1432 1 Beach house
1433 2 Black pig
1434 -- Restart Secondary Master slave
1435 start slave;
1436 Warnings:
1437 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1438 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
1439 select * from test.t1 order by a;
1440 a b
1441 1 Beach house
1442 -------------------------------
1443 Test 7 : Delete-Delete conflict
1444 -------------------------------
1445 -- Stop Secondary slave
1446 stop slave;
1447 Warnings:
1448 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1449 -- Delete on Primary Master
1450 delete from test.t1 where a=1;
1451 -- Delete on Secondary Master
1452 delete from test.t1 where a=1;
1453 -- Examine data on Primary Master, no row
1454 select * from test.t1 order by a;
1455 a b
1456 Conflict algorithm counter diff :
1457 ndb_conflict_fn_epoch2
1458 5
1459 ndb_epoch_delete_delete
1460 1
1461 Exception table contents
1462 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1463 server_id master_server_id master_epoch count a d
1464 1 3 # 1 # #
1465 1 3 # 2 # #
1466 1 3 # 3 # #
1467 1 3 # 4 # #
1468 1 3 # 5 # #
1469 SELECT * FROM `t1$EX` ORDER BY a, d;
1470 server_id master_server_id master_epoch count a d
1471 1 3 # # 1 NULL
1472 1 3 # # 2 NULL
1473 1 3 # # 2 NULL
1474 1 3 # # 2 NULL
1475 1 3 # # 2 NULL
1476 -- Examine data on Secondary Master before slave restart, no row
1477 select * from test.t1 order by a;
1478 a b
1479 -- Restart Secondary Master slave
1480 start slave;
1481 Warnings:
1482 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1483 -- Examine data on Secondary Master after slave restart, no row
1484 select * from test.t1 order by a;
1485 a b
1486 ------------------------------------------------
1487 Test 8 : Delete-Delete, Insert conflict exposure
1488 ------------------------------------------------
1489 -- Insert a row on Secondary Master
1490 insert into test.t1 values (3, "Espy");
1491 -- Check it's present on Primary Master
1492 select * from test.t1 order by a;
1493 a b
1494 3 Espy
1495 -- Stop replication in both directions
1496 stop slave;
1497 Warnings:
1498 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1499 stop slave;
1500 Warnings:
1501 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1502 -- Delete row from both clusters
1503 delete from test.t1 where a=3;
1504 delete from test.t1 where a=3;
1505 -- Follow up with Insert from Secondary master
1506 insert into test.t1 values (3, "Dalriada");
1507 -- Restart replication in both directions
1508 start slave;
1509 Warnings:
1510 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1511 start slave;
1512 Warnings:
1513 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1514 -- Check data on both sites
1515 -- diverged with NDB$EPOCH
1516 -- consistent with NDB$EPOCH2
1517 -- Secondary master :
1518 select * from test.t1 order by a;
1519 a b
1520 3 Dalriada
1521 -- Primary master :
1522 select * from test.t1 order by a;
1523 a b
1524 3 Dalriada
1525 --Remove extra row
1526 delete from test.t1 where a=3;
1527 -- Note that Delete-Delete conflict detected below
1528 Conflict algorithm counter diff :
1529 ndb_conflict_fn_epoch2
1530 6
1531 ndb_epoch_delete_delete
1532 2
1533 Exception table contents
1534 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1535 server_id master_server_id master_epoch count a d
1536 1 3 # 1 # #
1537 1 3 # 2 # #
1538 1 3 # 3 # #
1539 1 3 # 4 # #
1540 1 3 # 5 # #
1541 1 3 # 6 # #
1542 SELECT * FROM `t1$EX` ORDER BY a, d;
1543 server_id master_server_id master_epoch count a d
1544 1 3 # # 1 NULL
1545 1 3 # # 2 NULL
1546 1 3 # # 2 NULL
1547 1 3 # # 2 NULL
1548 1 3 # # 2 NULL
1549 1 3 # # 3 NULL
1550 ------------------------------------------------
1551 Test 9 : Insert, Insert-Update-Delete conflict
1552 ------------------------------------------------
1553 -- Stop replication on Secondary Master
1554 stop slave;
1555 Warnings:
1556 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1557 -- Insert row on Primary Master
1558 insert into test.t1 values (4, "Haymarket");
1559 -- Insert row on Secondary Master
1560 insert into test.t1 values (4, "Outhouse");
1561 -- Update row on Secondary Master
1562 update test.t1 set b="Mathers" where a = 4;
1563 -- Delete row on Secondary Master
1564 delete from test.t1 where a=4;
1565 -- Examine data (none) on Secondary Master
1566 select * from test.t1 order by a;
1567 a b
1568 -- Examine data on Primary Master, should be unaffected
1569 select * from test.t1 order by a;
1570 a b
1571 4 Haymarket
1572 Conflict algorithm counter diff :
1573 ndb_conflict_fn_epoch2
1574 9
1575 ndb_epoch_delete_delete
1576 2
1577 Exception table contents
1578 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1579 server_id master_server_id master_epoch count a d
1580 1 3 # 1 # #
1581 1 3 # 2 # #
1582 1 3 # 3 # #
1583 1 3 # 4 # #
1584 1 3 # 5 # #
1585 1 3 # 6 # #
1586 1 3 # 7 # #
1587 1 3 # 8 # #
1588 1 3 # 9 # #
1589 SELECT * FROM `t1$EX` ORDER BY a, d;
1590 server_id master_server_id master_epoch count a d
1591 1 3 # # 1 NULL
1592 1 3 # # 2 NULL
1593 1 3 # # 2 NULL
1594 1 3 # # 2 NULL
1595 1 3 # # 2 NULL
1596 1 3 # # 3 NULL
1597 1 3 # # 4 NULL
1598 1 3 # # 4 NULL
1599 1 3 # # 4 NULL
1600 -- Examine data on Secondary Master (none)
1601 select * from test.t1 order by a;
1602 a b
1603 -- Restart Secondary Master's slave
1604 start slave;
1605 Warnings:
1606 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1607 -- Check data on Secondary Master, should be same as Primary Master
1608 select * from test.t1;
1609 a b
1610 4 Haymarket
1611 ------------------------------------------------
1612 Test 10 : Update, Delete-Insert-Update conflict
1613 ------------------------------------------------
1614 -- Stop replication on Secondary Master
1615 stop slave;
1616 Warnings:
1617 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1618 -- Update row on Primary Master
1619 update test.t1 set b="Blind poet" where a=4;
1620 -- Delete row on Secondary Master
1621 delete from test.t1 where a=4;
1622 -- Insert row on Secondary Master
1623 insert into test.t1 values (4, "Drouthy Neebors");
1624 -- Update row on Secondary Master
1625 update test.t1 set b="The Tankard" where a=4;
1626 -- Check data on Primary Master, should be unaffected
1627 select * from test.t1 order by a;
1628 a b
1629 4 Blind poet
1630 Conflict algorithm counter diff :
1631 ndb_conflict_fn_epoch2
1632 12
1633 ndb_epoch_delete_delete
1634 2
1635 Exception table contents
1636 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1637 server_id master_server_id master_epoch count a d
1638 1 3 # 1 # #
1639 1 3 # 2 # #
1640 1 3 # 3 # #
1641 1 3 # 4 # #
1642 1 3 # 5 # #
1643 1 3 # 6 # #
1644 1 3 # 7 # #
1645 1 3 # 8 # #
1646 1 3 # 9 # #
1647 1 3 # 10 # #
1648 1 3 # 11 # #
1649 1 3 # 12 # #
1650 SELECT * FROM `t1$EX` ORDER BY a, d;
1651 server_id master_server_id master_epoch count a d
1652 1 3 # # 1 NULL
1653 1 3 # # 2 NULL
1654 1 3 # # 2 NULL
1655 1 3 # # 2 NULL
1656 1 3 # # 2 NULL
1657 1 3 # # 3 NULL
1658 1 3 # # 4 NULL
1659 1 3 # # 4 NULL
1660 1 3 # # 4 NULL
1661 1 3 # # 4 NULL
1662 1 3 # # 4 NULL
1663 1 3 # # 4 NULL
1664 -- Check data on Secondary Master, as set
1665 select * from test.t1 order by a;
1666 a b
1667 4 The Tankard
1668 -- Restart Secondary Master slave
1669 start slave;
1670 Warnings:
1671 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1672 -- Check data on Secondary Master - should be as Primary
1673 select * from test.t1 order by a;
1674 a b
1675 4 Blind poet
1676 ------------------------------------------------------------------------
1677 Test 11 : Test Secondary insert-update-delete accepted
1678 ------------------------------------------------------------------------
1679 Insert row on Secondary
1680 insert into test.t1 values (5, "Minders");
1681 Update row on Secondary
1682 update test.t1 set b="Southsider" where a=5;
1683 Delete row on Secondary
1684 delete from test.t1 where a=5;
1685 Check data on Primary, should be none. No new conflicts
1686 select * from test.t1 order by a;
1687 a b
1688 4 Blind poet
1689 Conflict algorithm counter diff :
1690 ndb_conflict_fn_epoch2
1691 12
1692 ndb_epoch_delete_delete
1693 2
1694 Exception table contents
1695 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1696 server_id master_server_id master_epoch count a d
1697 1 3 # 1 # #
1698 1 3 # 2 # #
1699 1 3 # 3 # #
1700 1 3 # 4 # #
1701 1 3 # 5 # #
1702 1 3 # 6 # #
1703 1 3 # 7 # #
1704 1 3 # 8 # #
1705 1 3 # 9 # #
1706 1 3 # 10 # #
1707 1 3 # 11 # #
1708 1 3 # 12 # #
1709 SELECT * FROM `t1$EX` ORDER BY a, d;
1710 server_id master_server_id master_epoch count a d
1711 1 3 # # 1 NULL
1712 1 3 # # 2 NULL
1713 1 3 # # 2 NULL
1714 1 3 # # 2 NULL
1715 1 3 # # 2 NULL
1716 1 3 # # 3 NULL
1717 1 3 # # 4 NULL
1718 1 3 # # 4 NULL
1719 1 3 # # 4 NULL
1720 1 3 # # 4 NULL
1721 1 3 # # 4 NULL
1722 1 3 # # 4 NULL
1723 Conflict algorithm counter diff :
1724 ndb_conflict_fn_epoch2
1725 12
1726 ndb_epoch_delete_delete
1727 2
1728 Exception table contents
1729 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1730 server_id master_server_id master_epoch count a d
1731 1 3 # 1 # #
1732 1 3 # 2 # #
1733 1 3 # 3 # #
1734 1 3 # 4 # #
1735 1 3 # 5 # #
1736 1 3 # 6 # #
1737 1 3 # 7 # #
1738 1 3 # 8 # #
1739 1 3 # 9 # #
1740 1 3 # 10 # #
1741 1 3 # 11 # #
1742 1 3 # 12 # #
1743 SELECT * FROM `t1$EX` ORDER BY a, d;
1744 server_id master_server_id master_epoch count a d
1745 1 3 # # 1 NULL
1746 1 3 # # 2 NULL
1747 1 3 # # 2 NULL
1748 1 3 # # 2 NULL
1749 1 3 # # 2 NULL
1750 1 3 # # 3 NULL
1751 1 3 # # 4 NULL
1752 1 3 # # 4 NULL
1753 1 3 # # 4 NULL
1754 1 3 # # 4 NULL
1755 1 3 # # 4 NULL
1756 1 3 # # 4 NULL
1757 drop table test.t1;
1758 drop table test.t2;
1759 drop table test.t1$EX;
1760 Populate ndb_replication table as necessary
1761 -- Default extra Gci bits
1762 create table `test`.`t1$EX`
1763 (server_id int unsigned,
1764 master_server_id int unsigned,
1765 master_epoch bigint unsigned,
1766 count int unsigned,
1767 a int not null,
1768 d int,
1769 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
1770 Create table
1771 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
1772 Create other table
1773 create table test.t2(a int primary key, b int) engine = ndb;
1774 ----------------------------------
1775 Test 1 : Basic two-way replication
1776 ----------------------------------
1777 insert into test.t1 values (1, "Metropole");
1778 FLUSH LOGS;
1779 -- Verify that Max rep epoch has been updated
1780 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
1781 -- Now update data on slave
1782 update test.t1 set b="Favorit" where a=1;
1783 -- Now check data on Primary
1784 select * from test.t1 order by a;
1785 a b
1786 1 Favorit
1787 Conflict algorithm counter diff :
1788 Exception table contents
1789 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1790 server_id master_server_id master_epoch count a d
1791 SELECT * FROM `t1$EX` ORDER BY a, d;
1792 server_id master_server_id master_epoch count a d
1793 -- Now perform multiple consecutive updates on the Secondary
1794 update test.t1 set b="Elephant house" where a=1;
1795 update test.t1 set b="Beach house" where a=1;
1796 select * from test.t1 order by a;
1797 a b
1798 1 Beach house
1799 -- Now check they've applied on the Primary
1800 select * from test.t1 order by a;
1801 a b
1802 1 Beach house
1803 Conflict algorithm counter diff :
1804 Exception table contents
1805 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1806 server_id master_server_id master_epoch count a d
1807 SELECT * FROM `t1$EX` ORDER BY a, d;
1808 server_id master_server_id master_epoch count a d
1809 --------------------------------------------
1810 Test 2 : Normal Insert from Secondary Master
1811 --------------------------------------------
1812 -- Insert a new row on the Secondary
1813 insert into test.t1 values (2, "Forrest");
1814 -- Check it exists on the Primary
1815 select * from test.t1 order by a;
1816 a b
1817 1 Beach house
1818 2 Forrest
1819 -- Update from the Secondary
1820 update test.t1 set b="Reds" where a=2;
1821 select * from test.t1 order by a;
1822 a b
1823 1 Beach house
1824 2 Reds
1825 delete from test.t1 where a=2;
1826 select * from test.t1 order by a;
1827 a b
1828 1 Beach house
1829 Conflict algorithm counter diff :
1830 Exception table contents
1831 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1832 server_id master_server_id master_epoch count a d
1833 SELECT * FROM `t1$EX` ORDER BY a, d;
1834 server_id master_server_id master_epoch count a d
1835 -------------------------------
1836 Test 3 : Insert-Insert conflict
1837 -------------------------------
1838 stop slave;
1839 Warnings:
1840 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1841 -- Insert a row on the Primary Master
1842 insert into test.t1 values (2, "Loopy Lornas");
1843 -- Insert a row on the secondary Master
1844 insert into test.t1 values (2, "Cloisters");
1845 -- Examine data on Primary Master (should be unaffected)
1846 select * from test.t1 order by a;
1847 a b
1848 1 Beach house
1849 2 Loopy Lornas
1850 -- Examine conflict indicators on Primary Master
1851 Conflict algorithm counter diff :
1852 ndb_conflict_fn_epoch2
1853 1
1854 Exception table contents
1855 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1856 server_id master_server_id master_epoch count a d
1857 1 3 # 1 # #
1858 SELECT * FROM `t1$EX` ORDER BY a, d;
1859 server_id master_server_id master_epoch count a d
1860 1 3 # # 2 NULL
1861 -- Examine data on isolated secondary Master (should be as-set)
1862 select * from test.t1 order by a;
1863 a b
1864 1 Beach house
1865 2 Cloisters
1866 -- Restart secondary Masters slave
1867 start slave;
1868 Warnings:
1869 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1870 -- Reexamine secondary Master's data (should be same as Primary Masters)
1871 select * from test.t1 order by a;
1872 a b
1873 1 Beach house
1874 2 Loopy Lornas
1875 -------------------------------
1876 Test 4 : Update-Update conflict
1877 -------------------------------
1878 -- Stop replication to secondary master
1879 stop slave;
1880 Warnings:
1881 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1882 -- Update row on Primary Master
1883 update test.t1 set b="Peters Yard" where a=2;
1884 -- Show data on Primary Master
1885 select * from test.t1 order by a;
1886 a b
1887 1 Beach house
1888 2 Peters Yard
1889 Conflict algorithm counter diff :
1890 ndb_conflict_fn_epoch2
1891 1
1892 Exception table contents
1893 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1894 server_id master_server_id master_epoch count a d
1895 1 3 # 1 # #
1896 SELECT * FROM `t1$EX` ORDER BY a, d;
1897 server_id master_server_id master_epoch count a d
1898 1 3 # # 2 NULL
1899 -- Update row on Secondary Master
1900 update test.t1 set b="Toast" where a=2;
1901 -- Examine data on Primary Master - should be unaffected
1902 select * from test.t1 order by a;
1903 a b
1904 1 Beach house
1905 2 Peters Yard
1906 Conflict algorithm counter diff :
1907 ndb_conflict_fn_epoch2
1908 2
1909 Exception table contents
1910 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1911 server_id master_server_id master_epoch count a d
1912 1 3 # 1 # #
1913 1 3 # 2 # #
1914 SELECT * FROM `t1$EX` ORDER BY a, d;
1915 server_id master_server_id master_epoch count a d
1916 1 3 # # 2 NULL
1917 1 3 # # 2 NULL
1918 -- Check data on secondary - should be as set
1919 select * from test.t1 order by a;
1920 a b
1921 1 Beach house
1922 2 Toast
1923 -- Now restart slave, will re-align row
1924 start slave;
1925 Warnings:
1926 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1927 -- Check that Secondary is re-aligned
1928 select * from test.t1 order by a;
1929 a b
1930 1 Beach house
1931 2 Peters Yard
1932 -------------------------------
1933 Test 5 : Update-Delete conflict
1934 -------------------------------
1935 -- Stop Secondary slave
1936 stop slave;
1937 Warnings:
1938 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1939 -- Update on Primary Master
1940 update test.t1 set b="Pear tree" where a = 2;
1941 -- Delete on Secondary Master
1942 delete from test.t1 where a = 2;
1943 -- Examine data on Primary Master, should be unaffected
1944 select * from test.t1 order by a;
1945 a b
1946 1 Beach house
1947 2 Pear tree
1948 Conflict algorithm counter diff :
1949 ndb_conflict_fn_epoch2
1950 3
1951 Exception table contents
1952 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1953 server_id master_server_id master_epoch count a d
1954 1 3 # 1 # #
1955 1 3 # 2 # #
1956 1 3 # 3 # #
1957 SELECT * FROM `t1$EX` ORDER BY a, d;
1958 server_id master_server_id master_epoch count a d
1959 1 3 # # 2 NULL
1960 1 3 # # 2 NULL
1961 1 3 # # 2 NULL
1962 -- Examine data on Secondary Master before slave restart, still missing
1963 select * from test.t1 order by a;
1964 a b
1965 1 Beach house
1966 -- Restart Secondary Master slave
1967 start slave;
1968 Warnings:
1969 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1970 -- Examine data on Secondary Master after slave restart, aligned with Master
1971 select * from test.t1 order by a;
1972 a b
1973 1 Beach house
1974 2 Pear tree
1975 -------------------------------
1976 Test 6 : Delete-Update conflict
1977 -------------------------------
1978 -- Stop Secondary slave
1979 stop slave;
1980 Warnings:
1981 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1982 -- Delete on Primary Master
1983 delete from test.t1 where a=2;
1984 -- Update on Secondary Master
1985 update test.t1 set b="Black pig" where a=2;
1986 -- Examine data on Primary Master, should be unaffected (no row)
1987 select * from test.t1 order by a;
1988 a b
1989 1 Beach house
1990 Conflict algorithm counter diff :
1991 ndb_conflict_fn_epoch2
1992 4
1993 Exception table contents
1994 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
1995 server_id master_server_id master_epoch count a d
1996 1 3 # 1 # #
1997 1 3 # 2 # #
1998 1 3 # 3 # #
1999 1 3 # 4 # #
2000 SELECT * FROM `t1$EX` ORDER BY a, d;
2001 server_id master_server_id master_epoch count a d
2002 1 3 # # 2 NULL
2003 1 3 # # 2 NULL
2004 1 3 # # 2 NULL
2005 1 3 # # 2 NULL
2006 -- Examine data on Secondary Master before slave restart, should be as inserted
2007 select * from test.t1 order by a;
2008 a b
2009 1 Beach house
2010 2 Black pig
2011 -- Restart Secondary Master slave
2012 start slave;
2013 Warnings:
2014 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2015 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
2016 select * from test.t1 order by a;
2017 a b
2018 1 Beach house
2019 -------------------------------
2020 Test 7 : Delete-Delete conflict
2021 -------------------------------
2022 -- Stop Secondary slave
2023 stop slave;
2024 Warnings:
2025 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2026 -- Delete on Primary Master
2027 delete from test.t1 where a=1;
2028 -- Delete on Secondary Master
2029 delete from test.t1 where a=1;
2030 -- Examine data on Primary Master, no row
2031 select * from test.t1 order by a;
2032 a b
2033 Conflict algorithm counter diff :
2034 ndb_conflict_fn_epoch2
2035 5
2036 ndb_epoch_delete_delete
2037 1
2038 Exception table contents
2039 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2040 server_id master_server_id master_epoch count a d
2041 1 3 # 1 # #
2042 1 3 # 2 # #
2043 1 3 # 3 # #
2044 1 3 # 4 # #
2045 1 3 # 5 # #
2046 SELECT * FROM `t1$EX` ORDER BY a, d;
2047 server_id master_server_id master_epoch count a d
2048 1 3 # # 1 NULL
2049 1 3 # # 2 NULL
2050 1 3 # # 2 NULL
2051 1 3 # # 2 NULL
2052 1 3 # # 2 NULL
2053 -- Examine data on Secondary Master before slave restart, no row
2054 select * from test.t1 order by a;
2055 a b
2056 -- Restart Secondary Master slave
2057 start slave;
2058 Warnings:
2059 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2060 -- Examine data on Secondary Master after slave restart, no row
2061 select * from test.t1 order by a;
2062 a b
2063 ------------------------------------------------
2064 Test 8 : Delete-Delete, Insert conflict exposure
2065 ------------------------------------------------
2066 -- Insert a row on Secondary Master
2067 insert into test.t1 values (3, "Espy");
2068 -- Check it's present on Primary Master
2069 select * from test.t1 order by a;
2070 a b
2071 3 Espy
2072 -- Stop replication in both directions
2073 stop slave;
2074 Warnings:
2075 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2076 stop slave;
2077 Warnings:
2078 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2079 -- Delete row from both clusters
2080 delete from test.t1 where a=3;
2081 delete from test.t1 where a=3;
2082 -- Follow up with Insert from Secondary master
2083 insert into test.t1 values (3, "Dalriada");
2084 -- Restart replication in both directions
2085 start slave;
2086 Warnings:
2087 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2088 start slave;
2089 Warnings:
2090 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2091 -- Check data on both sites
2092 -- diverged with NDB$EPOCH
2093 -- consistent with NDB$EPOCH2
2094 -- Secondary master :
2095 select * from test.t1 order by a;
2096 a b
2097 3 Dalriada
2098 -- Primary master :
2099 select * from test.t1 order by a;
2100 a b
2101 3 Dalriada
2102 --Remove extra row
2103 delete from test.t1 where a=3;
2104 -- Note that Delete-Delete conflict detected below
2105 Conflict algorithm counter diff :
2106 ndb_conflict_fn_epoch2
2107 6
2108 ndb_epoch_delete_delete
2109 2
2110 Exception table contents
2111 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2112 server_id master_server_id master_epoch count a d
2113 1 3 # 1 # #
2114 1 3 # 2 # #
2115 1 3 # 3 # #
2116 1 3 # 4 # #
2117 1 3 # 5 # #
2118 1 3 # 6 # #
2119 SELECT * FROM `t1$EX` ORDER BY a, d;
2120 server_id master_server_id master_epoch count a d
2121 1 3 # # 1 NULL
2122 1 3 # # 2 NULL
2123 1 3 # # 2 NULL
2124 1 3 # # 2 NULL
2125 1 3 # # 2 NULL
2126 1 3 # # 3 NULL
2127 ------------------------------------------------
2128 Test 9 : Insert, Insert-Update-Delete conflict
2129 ------------------------------------------------
2130 -- Stop replication on Secondary Master
2131 stop slave;
2132 Warnings:
2133 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2134 -- Insert row on Primary Master
2135 insert into test.t1 values (4, "Haymarket");
2136 -- Insert row on Secondary Master
2137 insert into test.t1 values (4, "Outhouse");
2138 -- Update row on Secondary Master
2139 update test.t1 set b="Mathers" where a = 4;
2140 -- Delete row on Secondary Master
2141 delete from test.t1 where a=4;
2142 -- Examine data (none) on Secondary Master
2143 select * from test.t1 order by a;
2144 a b
2145 -- Examine data on Primary Master, should be unaffected
2146 select * from test.t1 order by a;
2147 a b
2148 4 Haymarket
2149 Conflict algorithm counter diff :
2150 ndb_conflict_fn_epoch2
2151 9
2152 ndb_epoch_delete_delete
2153 2
2154 Exception table contents
2155 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2156 server_id master_server_id master_epoch count a d
2157 1 3 # 1 # #
2158 1 3 # 2 # #
2159 1 3 # 3 # #
2160 1 3 # 4 # #
2161 1 3 # 5 # #
2162 1 3 # 6 # #
2163 1 3 # 7 # #
2164 1 3 # 8 # #
2165 1 3 # 9 # #
2166 SELECT * FROM `t1$EX` ORDER BY a, d;
2167 server_id master_server_id master_epoch count a d
2168 1 3 # # 1 NULL
2169 1 3 # # 2 NULL
2170 1 3 # # 2 NULL
2171 1 3 # # 2 NULL
2172 1 3 # # 2 NULL
2173 1 3 # # 3 NULL
2174 1 3 # # 4 NULL
2175 1 3 # # 4 NULL
2176 1 3 # # 4 NULL
2177 -- Examine data on Secondary Master (none)
2178 select * from test.t1 order by a;
2179 a b
2180 -- Restart Secondary Master's slave
2181 start slave;
2182 Warnings:
2183 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2184 -- Check data on Secondary Master, should be same as Primary Master
2185 select * from test.t1;
2186 a b
2187 4 Haymarket
2188 ------------------------------------------------
2189 Test 10 : Update, Delete-Insert-Update conflict
2190 ------------------------------------------------
2191 -- Stop replication on Secondary Master
2192 stop slave;
2193 Warnings:
2194 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2195 -- Update row on Primary Master
2196 update test.t1 set b="Blind poet" where a=4;
2197 -- Delete row on Secondary Master
2198 delete from test.t1 where a=4;
2199 -- Insert row on Secondary Master
2200 insert into test.t1 values (4, "Drouthy Neebors");
2201 -- Update row on Secondary Master
2202 update test.t1 set b="The Tankard" where a=4;
2203 -- Check data on Primary Master, should be unaffected
2204 select * from test.t1 order by a;
2205 a b
2206 4 Blind poet
2207 Conflict algorithm counter diff :
2208 ndb_conflict_fn_epoch2
2209 12
2210 ndb_epoch_delete_delete
2211 2
2212 Exception table contents
2213 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2214 server_id master_server_id master_epoch count a d
2215 1 3 # 1 # #
2216 1 3 # 2 # #
2217 1 3 # 3 # #
2218 1 3 # 4 # #
2219 1 3 # 5 # #
2220 1 3 # 6 # #
2221 1 3 # 7 # #
2222 1 3 # 8 # #
2223 1 3 # 9 # #
2224 1 3 # 10 # #
2225 1 3 # 11 # #
2226 1 3 # 12 # #
2227 SELECT * FROM `t1$EX` ORDER BY a, d;
2228 server_id master_server_id master_epoch count a d
2229 1 3 # # 1 NULL
2230 1 3 # # 2 NULL
2231 1 3 # # 2 NULL
2232 1 3 # # 2 NULL
2233 1 3 # # 2 NULL
2234 1 3 # # 3 NULL
2235 1 3 # # 4 NULL
2236 1 3 # # 4 NULL
2237 1 3 # # 4 NULL
2238 1 3 # # 4 NULL
2239 1 3 # # 4 NULL
2240 1 3 # # 4 NULL
2241 -- Check data on Secondary Master, as set
2242 select * from test.t1 order by a;
2243 a b
2244 4 The Tankard
2245 -- Restart Secondary Master slave
2246 start slave;
2247 Warnings:
2248 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2249 -- Check data on Secondary Master - should be as Primary
2250 select * from test.t1 order by a;
2251 a b
2252 4 Blind poet
2253 ------------------------------------------------------------------------
2254 Test 11 : Test Secondary insert-update-delete accepted
2255 ------------------------------------------------------------------------
2256 Insert row on Secondary
2257 insert into test.t1 values (5, "Minders");
2258 Update row on Secondary
2259 update test.t1 set b="Southsider" where a=5;
2260 Delete row on Secondary
2261 delete from test.t1 where a=5;
2262 Check data on Primary, should be none. No new conflicts
2263 select * from test.t1 order by a;
2264 a b
2265 4 Blind poet
2266 Conflict algorithm counter diff :
2267 ndb_conflict_fn_epoch2
2268 12
2269 ndb_epoch_delete_delete
2270 2
2271 Exception table contents
2272 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2273 server_id master_server_id master_epoch count a d
2274 1 3 # 1 # #
2275 1 3 # 2 # #
2276 1 3 # 3 # #
2277 1 3 # 4 # #
2278 1 3 # 5 # #
2279 1 3 # 6 # #
2280 1 3 # 7 # #
2281 1 3 # 8 # #
2282 1 3 # 9 # #
2283 1 3 # 10 # #
2284 1 3 # 11 # #
2285 1 3 # 12 # #
2286 SELECT * FROM `t1$EX` ORDER BY a, d;
2287 server_id master_server_id master_epoch count a d
2288 1 3 # # 1 NULL
2289 1 3 # # 2 NULL
2290 1 3 # # 2 NULL
2291 1 3 # # 2 NULL
2292 1 3 # # 2 NULL
2293 1 3 # # 3 NULL
2294 1 3 # # 4 NULL
2295 1 3 # # 4 NULL
2296 1 3 # # 4 NULL
2297 1 3 # # 4 NULL
2298 1 3 # # 4 NULL
2299 1 3 # # 4 NULL
2300 Conflict algorithm counter diff :
2301 ndb_conflict_fn_epoch2
2302 12
2303 ndb_epoch_delete_delete
2304 2
2305 Exception table contents
2306 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2307 server_id master_server_id master_epoch count a d
2308 1 3 # 1 # #
2309 1 3 # 2 # #
2310 1 3 # 3 # #
2311 1 3 # 4 # #
2312 1 3 # 5 # #
2313 1 3 # 6 # #
2314 1 3 # 7 # #
2315 1 3 # 8 # #
2316 1 3 # 9 # #
2317 1 3 # 10 # #
2318 1 3 # 11 # #
2319 1 3 # 12 # #
2320 SELECT * FROM `t1$EX` ORDER BY a, d;
2321 server_id master_server_id master_epoch count a d
2322 1 3 # # 1 NULL
2323 1 3 # # 2 NULL
2324 1 3 # # 2 NULL
2325 1 3 # # 2 NULL
2326 1 3 # # 2 NULL
2327 1 3 # # 3 NULL
2328 1 3 # # 4 NULL
2329 1 3 # # 4 NULL
2330 1 3 # # 4 NULL
2331 1 3 # # 4 NULL
2332 1 3 # # 4 NULL
2333 1 3 # # 4 NULL
2334 drop table test.t1;
2335 drop table test.t2;
2336 drop table test.t1$EX;
2337 Now test batched conflict detection/handling
2338 create table test.t1 (
2339 a int primary key,
2340 b int,
2341 c varchar(2000)) engine=ndb;
2342 -- Stop replication from Primary -> Secondary
2343 stop slave;
2344 Warnings:
2345 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2346 -- Insert a row on Primary
2347 insert into test.t1 values (1,1,repeat('B', 2000));
2348 -- Generate a large batch of inserts with early + late conflicts
2349 create procedure test.doit (`rows` int)
2350 begin
2351 set @x = 0;
2352 START TRANSACTION;
2353 repeat
2354 insert into test.t1 values (@x, @x, repeat('B', 2000));
2355 set @x = @x + 1;
2356 until @x = `rows`
2357 end repeat;
2358 COMMIT;
2359 START TRANSACTION;
2360 update test.t1 set b=999, c=repeat('E',2000) where a=1;
2361 COMMIT;
2362 START TRANSACTION;
2363 delete from test.t1 where a=1;
2364 COMMIT;
2365 START TRANSACTION;
2366 insert into test.t1 values (1,1,'A');
2367 COMMIT;
2368 end%
2369 call test.doit(100);
2370 drop procedure test.doit;
2371 -- Look at Primary status, expect 4 conflicts
2372 Conflict algorithm counter diff :
2373 ndb_conflict_fn_epoch2
2374 4
2375 Exception table contents
2376 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2377 SELECT * FROM `t1$EX` ORDER BY a, d;
2378 start slave;
2379 Warnings:
2380 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2381 -- Look at Seconday row
2382 select a,b,sha1(c) from test.t1 where a=1;
2383 a b sha1(c)
2384 1 1 6d41e1d402596dff36396d1f0f288d17a4b9800a
2385 -- Check it's the same on the Secondary
2386 select a,b,sha1(c) from test.t1 where a=1;
2387 a b sha1(c)
2388 1 1 6d41e1d402596dff36396d1f0f288d17a4b9800a
2389 Test batching of DELETE vs DELETE with following INSERT
2390 delete from test.t1;
2391 insert into test.t1 values (1, 1, "Ma Brows");
2392 -- Stop Slave in both directions
2393 stop slave;
2394 Warnings:
2395 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2396 stop slave;
2397 Warnings:
2398 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2399 -- Delete row on Primary Cluster
2400 delete from test.t1 where a=1;
2401 -- Delete row on Secondary Cluster, followed by Insert in later 'batch'
2402 create procedure test.doit (`rows` int)
2403 begin
2404 set @x = 2;
2405 START TRANSACTION;
2406 delete from test.t1 where a=1;
2407 repeat
2408 insert into test.t1 values (@x, @x, repeat('B', 2000));
2409 set @x = @x + 1;
2410 until @x = (`rows` + 2)
2411 end repeat;
2412 COMMIT;
2413 START TRANSACTION;
2414 insert into test.t1 values (1, 1, 'Malleny arms');
2415 COMMIT;
2416 end%
2417 call test.doit(200);
2418 -- Restart slave on Primary Cluster
2419 start replica;
2420 -- Show data on Primary Cluster (should have row inserted on Secondary)
2421 select * from test.t1 where a=1;
2422 a b c
2423 1 1 Malleny arms
2424 -- Show data on Secondary Cluster (should have row inserted on Secondary)
2425 select * from test.t1 where a=1;
2426 a b c
2427 1 1 Malleny arms
2428 --Restart slave on Secondary Cluster
2429 start slave;
2430 Warnings:
2431 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2432 -- Show data on Clusters after slaves restarted+synced
2433 For NDB$EPOCH, the data will be missing
2434 as expected with delete vs delete conflict
2435 followed closely by Insert
2436 For NDB$EPOCH2, the data will be present on both
2437 clusters.
2438 PRIMARY
2439 select * from test.t1 where a=1;
2440 a b c
2441 1 1 Malleny arms
2442 SECONDARY
2443 select * from test.t1 where a=1;
2444 a b c
2445 1 1 Malleny arms
2446 -- Force wait for master to be in-sync with slave
2447 To avoid race between DML and table drop
2448 flush logs;
2449 drop procedure test.doit;
2450 drop table test.t1;
2451 delete from mysql.ndb_replication;
2452 insert into mysql.ndb_replication values
2453 ("test", "t3", 0, 7, "NDB$EPOCH2(32)"),
2454 ("test", "t4", 0, 7, "NDB$EPOCH2(-1)");
2455 create table test.t3 (a int primary key) engine=ndb;
2456 ERROR HY000: Error in parsing conflict function. Message: NDB$EPOCH2(32), Too many extra Gci bits at ')'
2457 show warnings;
2458 Level Code Message
2459 Error 1626 Error in parsing conflict function. Message: NDB$EPOCH2(32), Too many extra Gci bits at ')'
2460 Error 1031 Table storage engine for 't3' doesn't have this option
2461 create table test.t4 (a int primary key) engine=ndb;
2462 ERROR HY000: Error in parsing conflict function. Message: NDB$EPOCH2(-1), Too many extra Gci bits at ')'
2463 show warnings;
2464 Level Code Message
2465 Error 1626 Error in parsing conflict function. Message: NDB$EPOCH2(-1), Too many extra Gci bits at ')'
2466 Error 1031 Table storage engine for 't4' doesn't have this option
2467 -- Force sync before dropping table to avoid race
2468 flush logs;
2469 flush logs;
2470 drop table mysql.ndb_replication;
2471 -- Attempt to get system back in pre-test state
2472 stop replica;
2473 reset replica;
2474 include/rpl_end.inc
2475 Next we have Cluster A as Secondary, Cluster B as Primary
2476 SET GLOBAL ndb_applier_conflict_role="NONE";
2477 SET GLOBAL ndb_applier_conflict_role="SECONDARY";
2478 SET GLOBAL ndb_applier_conflict_role="NONE";
2479 SET GLOBAL ndb_applier_conflict_role="PRIMARY";
2480 We set a special control var to allow the generic scripts
2481 to handle this topology
2482 Remove some connections which can't be setup twice (ugly)
2483 include/master-slave.inc [rpl_server_count=2]
2484 Warnings:
2485 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
2486 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2487 [connection master]
2488 Setup circular replication
2489 The circle is setup between Primary and Secondary
2490 Setup ndb_replication and t1 exceptions table
2491 Populate ndb_replication table as necessary
2492 -- 0 extra gci bits
2493 create table `test`.`t1$EX`
2494 (server_id int unsigned,
2495 master_server_id int unsigned,
2496 master_epoch bigint unsigned,
2497 count int unsigned,
2498 a int not null,
2499 d int,
2500 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
2501 Create table
2502 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
2503 Create other table
2504 create table test.t2(a int primary key, b int) engine = ndb;
2505 ----------------------------------
2506 Test 1 : Basic two-way replication
2507 ----------------------------------
2508 insert into test.t1 values (1, "Metropole");
2509 FLUSH LOGS;
2510 -- Verify that Max rep epoch has been updated
2511 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
2512 -- Now update data on slave
2513 update test.t1 set b="Favorit" where a=1;
2514 -- Now check data on Primary
2515 select * from test.t1 order by a;
2516 a b
2517 1 Favorit
2518 Conflict algorithm counter diff :
2519 Exception table contents
2520 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2521 server_id master_server_id master_epoch count a d
2522 SELECT * FROM `t1$EX` ORDER BY a, d;
2523 server_id master_server_id master_epoch count a d
2524 -- Now perform multiple consecutive updates on the Secondary
2525 update test.t1 set b="Elephant house" where a=1;
2526 update test.t1 set b="Beach house" where a=1;
2527 select * from test.t1 order by a;
2528 a b
2529 1 Beach house
2530 -- Now check they've applied on the Primary
2531 select * from test.t1 order by a;
2532 a b
2533 1 Beach house
2534 Conflict algorithm counter diff :
2535 Exception table contents
2536 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2537 server_id master_server_id master_epoch count a d
2538 SELECT * FROM `t1$EX` ORDER BY a, d;
2539 server_id master_server_id master_epoch count a d
2540 --------------------------------------------
2541 Test 2 : Normal Insert from Secondary Master
2542 --------------------------------------------
2543 -- Insert a new row on the Secondary
2544 insert into test.t1 values (2, "Forrest");
2545 -- Check it exists on the Primary
2546 select * from test.t1 order by a;
2547 a b
2548 1 Beach house
2549 2 Forrest
2550 -- Update from the Secondary
2551 update test.t1 set b="Reds" where a=2;
2552 select * from test.t1 order by a;
2553 a b
2554 1 Beach house
2555 2 Reds
2556 delete from test.t1 where a=2;
2557 select * from test.t1 order by a;
2558 a b
2559 1 Beach house
2560 Conflict algorithm counter diff :
2561 Exception table contents
2562 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2563 server_id master_server_id master_epoch count a d
2564 SELECT * FROM `t1$EX` ORDER BY a, d;
2565 server_id master_server_id master_epoch count a d
2566 -------------------------------
2567 Test 3 : Insert-Insert conflict
2568 -------------------------------
2569 stop slave;
2570 Warnings:
2571 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2572 -- Insert a row on the Primary Master
2573 insert into test.t1 values (2, "Loopy Lornas");
2574 -- Insert a row on the secondary Master
2575 insert into test.t1 values (2, "Cloisters");
2576 -- Examine data on Primary Master (should be unaffected)
2577 select * from test.t1 order by a;
2578 a b
2579 1 Beach house
2580 2 Loopy Lornas
2581 -- Examine conflict indicators on Primary Master
2582 Conflict algorithm counter diff :
2583 ndb_conflict_fn_epoch2
2584 1
2585 Exception table contents
2586 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2587 server_id master_server_id master_epoch count a d
2588 3 1 # 1 # #
2589 SELECT * FROM `t1$EX` ORDER BY a, d;
2590 server_id master_server_id master_epoch count a d
2591 3 1 # # 2 NULL
2592 -- Examine data on isolated secondary Master (should be as-set)
2593 select * from test.t1 order by a;
2594 a b
2595 1 Beach house
2596 2 Cloisters
2597 -- Restart secondary Masters slave
2598 start slave;
2599 Warnings:
2600 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2601 -- Reexamine secondary Master's data (should be same as Primary Masters)
2602 select * from test.t1 order by a;
2603 a b
2604 1 Beach house
2605 2 Loopy Lornas
2606 -------------------------------
2607 Test 4 : Update-Update conflict
2608 -------------------------------
2609 -- Stop replication to secondary master
2610 stop slave;
2611 Warnings:
2612 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2613 -- Update row on Primary Master
2614 update test.t1 set b="Peters Yard" where a=2;
2615 -- Show data on Primary Master
2616 select * from test.t1 order by a;
2617 a b
2618 1 Beach house
2619 2 Peters Yard
2620 Conflict algorithm counter diff :
2621 ndb_conflict_fn_epoch2
2622 1
2623 Exception table contents
2624 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2625 server_id master_server_id master_epoch count a d
2626 3 1 # 1 # #
2627 SELECT * FROM `t1$EX` ORDER BY a, d;
2628 server_id master_server_id master_epoch count a d
2629 3 1 # # 2 NULL
2630 -- Update row on Secondary Master
2631 update test.t1 set b="Toast" where a=2;
2632 -- Examine data on Primary Master - should be unaffected
2633 select * from test.t1 order by a;
2634 a b
2635 1 Beach house
2636 2 Peters Yard
2637 Conflict algorithm counter diff :
2638 ndb_conflict_fn_epoch2
2639 2
2640 Exception table contents
2641 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2642 server_id master_server_id master_epoch count a d
2643 3 1 # 1 # #
2644 3 1 # 2 # #
2645 SELECT * FROM `t1$EX` ORDER BY a, d;
2646 server_id master_server_id master_epoch count a d
2647 3 1 # # 2 NULL
2648 3 1 # # 2 NULL
2649 -- Check data on secondary - should be as set
2650 select * from test.t1 order by a;
2651 a b
2652 1 Beach house
2653 2 Toast
2654 -- Now restart slave, will re-align row
2655 start slave;
2656 Warnings:
2657 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2658 -- Check that Secondary is re-aligned
2659 select * from test.t1 order by a;
2660 a b
2661 1 Beach house
2662 2 Peters Yard
2663 -------------------------------
2664 Test 5 : Update-Delete conflict
2665 -------------------------------
2666 -- Stop Secondary slave
2667 stop slave;
2668 Warnings:
2669 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2670 -- Update on Primary Master
2671 update test.t1 set b="Pear tree" where a = 2;
2672 -- Delete on Secondary Master
2673 delete from test.t1 where a = 2;
2674 -- Examine data on Primary Master, should be unaffected
2675 select * from test.t1 order by a;
2676 a b
2677 1 Beach house
2678 2 Pear tree
2679 Conflict algorithm counter diff :
2680 ndb_conflict_fn_epoch2
2681 3
2682 Exception table contents
2683 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2684 server_id master_server_id master_epoch count a d
2685 3 1 # 1 # #
2686 3 1 # 2 # #
2687 3 1 # 3 # #
2688 SELECT * FROM `t1$EX` ORDER BY a, d;
2689 server_id master_server_id master_epoch count a d
2690 3 1 # # 2 NULL
2691 3 1 # # 2 NULL
2692 3 1 # # 2 NULL
2693 -- Examine data on Secondary Master before slave restart, still missing
2694 select * from test.t1 order by a;
2695 a b
2696 1 Beach house
2697 -- Restart Secondary Master slave
2698 start slave;
2699 Warnings:
2700 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2701 -- Examine data on Secondary Master after slave restart, aligned with Master
2702 select * from test.t1 order by a;
2703 a b
2704 1 Beach house
2705 2 Pear tree
2706 -------------------------------
2707 Test 6 : Delete-Update conflict
2708 -------------------------------
2709 -- Stop Secondary slave
2710 stop slave;
2711 Warnings:
2712 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2713 -- Delete on Primary Master
2714 delete from test.t1 where a=2;
2715 -- Update on Secondary Master
2716 update test.t1 set b="Black pig" where a=2;
2717 -- Examine data on Primary Master, should be unaffected (no row)
2718 select * from test.t1 order by a;
2719 a b
2720 1 Beach house
2721 Conflict algorithm counter diff :
2722 ndb_conflict_fn_epoch2
2723 4
2724 Exception table contents
2725 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2726 server_id master_server_id master_epoch count a d
2727 3 1 # 1 # #
2728 3 1 # 2 # #
2729 3 1 # 3 # #
2730 3 1 # 4 # #
2731 SELECT * FROM `t1$EX` ORDER BY a, d;
2732 server_id master_server_id master_epoch count a d
2733 3 1 # # 2 NULL
2734 3 1 # # 2 NULL
2735 3 1 # # 2 NULL
2736 3 1 # # 2 NULL
2737 -- Examine data on Secondary Master before slave restart, should be as inserted
2738 select * from test.t1 order by a;
2739 a b
2740 1 Beach house
2741 2 Black pig
2742 -- Restart Secondary Master slave
2743 start slave;
2744 Warnings:
2745 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2746 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
2747 select * from test.t1 order by a;
2748 a b
2749 1 Beach house
2750 -------------------------------
2751 Test 7 : Delete-Delete conflict
2752 -------------------------------
2753 -- Stop Secondary slave
2754 stop slave;
2755 Warnings:
2756 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2757 -- Delete on Primary Master
2758 delete from test.t1 where a=1;
2759 -- Delete on Secondary Master
2760 delete from test.t1 where a=1;
2761 -- Examine data on Primary Master, no row
2762 select * from test.t1 order by a;
2763 a b
2764 Conflict algorithm counter diff :
2765 ndb_conflict_fn_epoch2
2766 5
2767 ndb_epoch_delete_delete
2768 1
2769 Exception table contents
2770 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2771 server_id master_server_id master_epoch count a d
2772 3 1 # 1 # #
2773 3 1 # 2 # #
2774 3 1 # 3 # #
2775 3 1 # 4 # #
2776 3 1 # 5 # #
2777 SELECT * FROM `t1$EX` ORDER BY a, d;
2778 server_id master_server_id master_epoch count a d
2779 3 1 # # 1 NULL
2780 3 1 # # 2 NULL
2781 3 1 # # 2 NULL
2782 3 1 # # 2 NULL
2783 3 1 # # 2 NULL
2784 -- Examine data on Secondary Master before slave restart, no row
2785 select * from test.t1 order by a;
2786 a b
2787 -- Restart Secondary Master slave
2788 start slave;
2789 Warnings:
2790 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2791 -- Examine data on Secondary Master after slave restart, no row
2792 select * from test.t1 order by a;
2793 a b
2794 ------------------------------------------------
2795 Test 8 : Delete-Delete, Insert conflict exposure
2796 ------------------------------------------------
2797 -- Insert a row on Secondary Master
2798 insert into test.t1 values (3, "Espy");
2799 -- Check it's present on Primary Master
2800 select * from test.t1 order by a;
2801 a b
2802 3 Espy
2803 -- Stop replication in both directions
2804 stop slave;
2805 Warnings:
2806 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2807 stop slave;
2808 Warnings:
2809 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2810 -- Delete row from both clusters
2811 delete from test.t1 where a=3;
2812 delete from test.t1 where a=3;
2813 -- Follow up with Insert from Secondary master
2814 insert into test.t1 values (3, "Dalriada");
2815 -- Restart replication in both directions
2816 start slave;
2817 Warnings:
2818 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2819 start slave;
2820 Warnings:
2821 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2822 -- Check data on both sites
2823 -- diverged with NDB$EPOCH
2824 -- consistent with NDB$EPOCH2
2825 -- Secondary master :
2826 select * from test.t1 order by a;
2827 a b
2828 3 Dalriada
2829 -- Primary master :
2830 select * from test.t1 order by a;
2831 a b
2832 3 Dalriada
2833 --Remove extra row
2834 delete from test.t1 where a=3;
2835 -- Note that Delete-Delete conflict detected below
2836 Conflict algorithm counter diff :
2837 ndb_conflict_fn_epoch2
2838 6
2839 ndb_epoch_delete_delete
2840 2
2841 Exception table contents
2842 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2843 server_id master_server_id master_epoch count a d
2844 3 1 # 1 # #
2845 3 1 # 2 # #
2846 3 1 # 3 # #
2847 3 1 # 4 # #
2848 3 1 # 5 # #
2849 3 1 # 6 # #
2850 SELECT * FROM `t1$EX` ORDER BY a, d;
2851 server_id master_server_id master_epoch count a d
2852 3 1 # # 1 NULL
2853 3 1 # # 2 NULL
2854 3 1 # # 2 NULL
2855 3 1 # # 2 NULL
2856 3 1 # # 2 NULL
2857 3 1 # # 3 NULL
2858 ------------------------------------------------
2859 Test 9 : Insert, Insert-Update-Delete conflict
2860 ------------------------------------------------
2861 -- Stop replication on Secondary Master
2862 stop slave;
2863 Warnings:
2864 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2865 -- Insert row on Primary Master
2866 insert into test.t1 values (4, "Haymarket");
2867 -- Insert row on Secondary Master
2868 insert into test.t1 values (4, "Outhouse");
2869 -- Update row on Secondary Master
2870 update test.t1 set b="Mathers" where a = 4;
2871 -- Delete row on Secondary Master
2872 delete from test.t1 where a=4;
2873 -- Examine data (none) on Secondary Master
2874 select * from test.t1 order by a;
2875 a b
2876 -- Examine data on Primary Master, should be unaffected
2877 select * from test.t1 order by a;
2878 a b
2879 4 Haymarket
2880 Conflict algorithm counter diff :
2881 ndb_conflict_fn_epoch2
2882 9
2883 ndb_epoch_delete_delete
2884 2
2885 Exception table contents
2886 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2887 server_id master_server_id master_epoch count a d
2888 3 1 # 1 # #
2889 3 1 # 2 # #
2890 3 1 # 3 # #
2891 3 1 # 4 # #
2892 3 1 # 5 # #
2893 3 1 # 6 # #
2894 3 1 # 7 # #
2895 3 1 # 8 # #
2896 3 1 # 9 # #
2897 SELECT * FROM `t1$EX` ORDER BY a, d;
2898 server_id master_server_id master_epoch count a d
2899 3 1 # # 1 NULL
2900 3 1 # # 2 NULL
2901 3 1 # # 2 NULL
2902 3 1 # # 2 NULL
2903 3 1 # # 2 NULL
2904 3 1 # # 3 NULL
2905 3 1 # # 4 NULL
2906 3 1 # # 4 NULL
2907 3 1 # # 4 NULL
2908 -- Examine data on Secondary Master (none)
2909 select * from test.t1 order by a;
2910 a b
2911 -- Restart Secondary Master's slave
2912 start slave;
2913 Warnings:
2914 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2915 -- Check data on Secondary Master, should be same as Primary Master
2916 select * from test.t1;
2917 a b
2918 4 Haymarket
2919 ------------------------------------------------
2920 Test 10 : Update, Delete-Insert-Update conflict
2921 ------------------------------------------------
2922 -- Stop replication on Secondary Master
2923 stop slave;
2924 Warnings:
2925 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2926 -- Update row on Primary Master
2927 update test.t1 set b="Blind poet" where a=4;
2928 -- Delete row on Secondary Master
2929 delete from test.t1 where a=4;
2930 -- Insert row on Secondary Master
2931 insert into test.t1 values (4, "Drouthy Neebors");
2932 -- Update row on Secondary Master
2933 update test.t1 set b="The Tankard" where a=4;
2934 -- Check data on Primary Master, should be unaffected
2935 select * from test.t1 order by a;
2936 a b
2937 4 Blind poet
2938 Conflict algorithm counter diff :
2939 ndb_conflict_fn_epoch2
2940 12
2941 ndb_epoch_delete_delete
2942 2
2943 Exception table contents
2944 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
2945 server_id master_server_id master_epoch count a d
2946 3 1 # 1 # #
2947 3 1 # 2 # #
2948 3 1 # 3 # #
2949 3 1 # 4 # #
2950 3 1 # 5 # #
2951 3 1 # 6 # #
2952 3 1 # 7 # #
2953 3 1 # 8 # #
2954 3 1 # 9 # #
2955 3 1 # 10 # #
2956 3 1 # 11 # #
2957 3 1 # 12 # #
2958 SELECT * FROM `t1$EX` ORDER BY a, d;
2959 server_id master_server_id master_epoch count a d
2960 3 1 # # 1 NULL
2961 3 1 # # 2 NULL
2962 3 1 # # 2 NULL
2963 3 1 # # 2 NULL
2964 3 1 # # 2 NULL
2965 3 1 # # 3 NULL
2966 3 1 # # 4 NULL
2967 3 1 # # 4 NULL
2968 3 1 # # 4 NULL
2969 3 1 # # 4 NULL
2970 3 1 # # 4 NULL
2971 3 1 # # 4 NULL
2972 -- Check data on Secondary Master, as set
2973 select * from test.t1 order by a;
2974 a b
2975 4 The Tankard
2976 -- Restart Secondary Master slave
2977 start slave;
2978 Warnings:
2979 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2980 -- Check data on Secondary Master - should be as Primary
2981 select * from test.t1 order by a;
2982 a b
2983 4 Blind poet
2984 ------------------------------------------------------------------------
2985 Test 11 : Test Secondary insert-update-delete accepted
2986 ------------------------------------------------------------------------
2987 Insert row on Secondary
2988 insert into test.t1 values (5, "Minders");
2989 Update row on Secondary
2990 update test.t1 set b="Southsider" where a=5;
2991 Delete row on Secondary
2992 delete from test.t1 where a=5;
2993 Check data on Primary, should be none. No new conflicts
2994 select * from test.t1 order by a;
2995 a b
2996 4 Blind poet
2997 Conflict algorithm counter diff :
2998 ndb_conflict_fn_epoch2
2999 12
3000 ndb_epoch_delete_delete
3001 2
3002 Exception table contents
3003 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3004 server_id master_server_id master_epoch count a d
3005 3 1 # 1 # #
3006 3 1 # 2 # #
3007 3 1 # 3 # #
3008 3 1 # 4 # #
3009 3 1 # 5 # #
3010 3 1 # 6 # #
3011 3 1 # 7 # #
3012 3 1 # 8 # #
3013 3 1 # 9 # #
3014 3 1 # 10 # #
3015 3 1 # 11 # #
3016 3 1 # 12 # #
3017 SELECT * FROM `t1$EX` ORDER BY a, d;
3018 server_id master_server_id master_epoch count a d
3019 3 1 # # 1 NULL
3020 3 1 # # 2 NULL
3021 3 1 # # 2 NULL
3022 3 1 # # 2 NULL
3023 3 1 # # 2 NULL
3024 3 1 # # 3 NULL
3025 3 1 # # 4 NULL
3026 3 1 # # 4 NULL
3027 3 1 # # 4 NULL
3028 3 1 # # 4 NULL
3029 3 1 # # 4 NULL
3030 3 1 # # 4 NULL
3031 Conflict algorithm counter diff :
3032 ndb_conflict_fn_epoch2
3033 12
3034 ndb_epoch_delete_delete
3035 2
3036 Exception table contents
3037 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3038 server_id master_server_id master_epoch count a d
3039 3 1 # 1 # #
3040 3 1 # 2 # #
3041 3 1 # 3 # #
3042 3 1 # 4 # #
3043 3 1 # 5 # #
3044 3 1 # 6 # #
3045 3 1 # 7 # #
3046 3 1 # 8 # #
3047 3 1 # 9 # #
3048 3 1 # 10 # #
3049 3 1 # 11 # #
3050 3 1 # 12 # #
3051 SELECT * FROM `t1$EX` ORDER BY a, d;
3052 server_id master_server_id master_epoch count a d
3053 3 1 # # 1 NULL
3054 3 1 # # 2 NULL
3055 3 1 # # 2 NULL
3056 3 1 # # 2 NULL
3057 3 1 # # 2 NULL
3058 3 1 # # 3 NULL
3059 3 1 # # 4 NULL
3060 3 1 # # 4 NULL
3061 3 1 # # 4 NULL
3062 3 1 # # 4 NULL
3063 3 1 # # 4 NULL
3064 3 1 # # 4 NULL
3065 drop table test.t1;
3066 drop table test.t2;
3067 drop table test.t1$EX;
3068 Populate ndb_replication table as necessary
3069 -- 1 extra gci bits
3070 create table `test`.`t1$EX`
3071 (server_id int unsigned,
3072 master_server_id int unsigned,
3073 master_epoch bigint unsigned,
3074 count int unsigned,
3075 a int not null,
3076 d int,
3077 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
3078 Create table
3079 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
3080 Create other table
3081 create table test.t2(a int primary key, b int) engine = ndb;
3082 ----------------------------------
3083 Test 1 : Basic two-way replication
3084 ----------------------------------
3085 insert into test.t1 values (1, "Metropole");
3086 FLUSH LOGS;
3087 -- Verify that Max rep epoch has been updated
3088 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
3089 -- Now update data on slave
3090 update test.t1 set b="Favorit" where a=1;
3091 -- Now check data on Primary
3092 select * from test.t1 order by a;
3093 a b
3094 1 Favorit
3095 Conflict algorithm counter diff :
3096 Exception table contents
3097 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3098 server_id master_server_id master_epoch count a d
3099 SELECT * FROM `t1$EX` ORDER BY a, d;
3100 server_id master_server_id master_epoch count a d
3101 -- Now perform multiple consecutive updates on the Secondary
3102 update test.t1 set b="Elephant house" where a=1;
3103 update test.t1 set b="Beach house" where a=1;
3104 select * from test.t1 order by a;
3105 a b
3106 1 Beach house
3107 -- Now check they've applied on the Primary
3108 select * from test.t1 order by a;
3109 a b
3110 1 Beach house
3111 Conflict algorithm counter diff :
3112 Exception table contents
3113 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3114 server_id master_server_id master_epoch count a d
3115 SELECT * FROM `t1$EX` ORDER BY a, d;
3116 server_id master_server_id master_epoch count a d
3117 --------------------------------------------
3118 Test 2 : Normal Insert from Secondary Master
3119 --------------------------------------------
3120 -- Insert a new row on the Secondary
3121 insert into test.t1 values (2, "Forrest");
3122 -- Check it exists on the Primary
3123 select * from test.t1 order by a;
3124 a b
3125 1 Beach house
3126 2 Forrest
3127 -- Update from the Secondary
3128 update test.t1 set b="Reds" where a=2;
3129 select * from test.t1 order by a;
3130 a b
3131 1 Beach house
3132 2 Reds
3133 delete from test.t1 where a=2;
3134 select * from test.t1 order by a;
3135 a b
3136 1 Beach house
3137 Conflict algorithm counter diff :
3138 Exception table contents
3139 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3140 server_id master_server_id master_epoch count a d
3141 SELECT * FROM `t1$EX` ORDER BY a, d;
3142 server_id master_server_id master_epoch count a d
3143 -------------------------------
3144 Test 3 : Insert-Insert conflict
3145 -------------------------------
3146 stop slave;
3147 Warnings:
3148 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3149 -- Insert a row on the Primary Master
3150 insert into test.t1 values (2, "Loopy Lornas");
3151 -- Insert a row on the secondary Master
3152 insert into test.t1 values (2, "Cloisters");
3153 -- Examine data on Primary Master (should be unaffected)
3154 select * from test.t1 order by a;
3155 a b
3156 1 Beach house
3157 2 Loopy Lornas
3158 -- Examine conflict indicators on Primary Master
3159 Conflict algorithm counter diff :
3160 ndb_conflict_fn_epoch2
3161 1
3162 Exception table contents
3163 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3164 server_id master_server_id master_epoch count a d
3165 3 1 # 1 # #
3166 SELECT * FROM `t1$EX` ORDER BY a, d;
3167 server_id master_server_id master_epoch count a d
3168 3 1 # # 2 NULL
3169 -- Examine data on isolated secondary Master (should be as-set)
3170 select * from test.t1 order by a;
3171 a b
3172 1 Beach house
3173 2 Cloisters
3174 -- Restart secondary Masters slave
3175 start slave;
3176 Warnings:
3177 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3178 -- Reexamine secondary Master's data (should be same as Primary Masters)
3179 select * from test.t1 order by a;
3180 a b
3181 1 Beach house
3182 2 Loopy Lornas
3183 -------------------------------
3184 Test 4 : Update-Update conflict
3185 -------------------------------
3186 -- Stop replication to secondary master
3187 stop slave;
3188 Warnings:
3189 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3190 -- Update row on Primary Master
3191 update test.t1 set b="Peters Yard" where a=2;
3192 -- Show data on Primary Master
3193 select * from test.t1 order by a;
3194 a b
3195 1 Beach house
3196 2 Peters Yard
3197 Conflict algorithm counter diff :
3198 ndb_conflict_fn_epoch2
3199 1
3200 Exception table contents
3201 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3202 server_id master_server_id master_epoch count a d
3203 3 1 # 1 # #
3204 SELECT * FROM `t1$EX` ORDER BY a, d;
3205 server_id master_server_id master_epoch count a d
3206 3 1 # # 2 NULL
3207 -- Update row on Secondary Master
3208 update test.t1 set b="Toast" where a=2;
3209 -- Examine data on Primary Master - should be unaffected
3210 select * from test.t1 order by a;
3211 a b
3212 1 Beach house
3213 2 Peters Yard
3214 Conflict algorithm counter diff :
3215 ndb_conflict_fn_epoch2
3216 2
3217 Exception table contents
3218 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3219 server_id master_server_id master_epoch count a d
3220 3 1 # 1 # #
3221 3 1 # 2 # #
3222 SELECT * FROM `t1$EX` ORDER BY a, d;
3223 server_id master_server_id master_epoch count a d
3224 3 1 # # 2 NULL
3225 3 1 # # 2 NULL
3226 -- Check data on secondary - should be as set
3227 select * from test.t1 order by a;
3228 a b
3229 1 Beach house
3230 2 Toast
3231 -- Now restart slave, will re-align row
3232 start slave;
3233 Warnings:
3234 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3235 -- Check that Secondary is re-aligned
3236 select * from test.t1 order by a;
3237 a b
3238 1 Beach house
3239 2 Peters Yard
3240 -------------------------------
3241 Test 5 : Update-Delete conflict
3242 -------------------------------
3243 -- Stop Secondary slave
3244 stop slave;
3245 Warnings:
3246 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3247 -- Update on Primary Master
3248 update test.t1 set b="Pear tree" where a = 2;
3249 -- Delete on Secondary Master
3250 delete from test.t1 where a = 2;
3251 -- Examine data on Primary Master, should be unaffected
3252 select * from test.t1 order by a;
3253 a b
3254 1 Beach house
3255 2 Pear tree
3256 Conflict algorithm counter diff :
3257 ndb_conflict_fn_epoch2
3258 3
3259 Exception table contents
3260 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3261 server_id master_server_id master_epoch count a d
3262 3 1 # 1 # #
3263 3 1 # 2 # #
3264 3 1 # 3 # #
3265 SELECT * FROM `t1$EX` ORDER BY a, d;
3266 server_id master_server_id master_epoch count a d
3267 3 1 # # 2 NULL
3268 3 1 # # 2 NULL
3269 3 1 # # 2 NULL
3270 -- Examine data on Secondary Master before slave restart, still missing
3271 select * from test.t1 order by a;
3272 a b
3273 1 Beach house
3274 -- Restart Secondary Master slave
3275 start slave;
3276 Warnings:
3277 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3278 -- Examine data on Secondary Master after slave restart, aligned with Master
3279 select * from test.t1 order by a;
3280 a b
3281 1 Beach house
3282 2 Pear tree
3283 -------------------------------
3284 Test 6 : Delete-Update conflict
3285 -------------------------------
3286 -- Stop Secondary slave
3287 stop slave;
3288 Warnings:
3289 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3290 -- Delete on Primary Master
3291 delete from test.t1 where a=2;
3292 -- Update on Secondary Master
3293 update test.t1 set b="Black pig" where a=2;
3294 -- Examine data on Primary Master, should be unaffected (no row)
3295 select * from test.t1 order by a;
3296 a b
3297 1 Beach house
3298 Conflict algorithm counter diff :
3299 ndb_conflict_fn_epoch2
3300 4
3301 Exception table contents
3302 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3303 server_id master_server_id master_epoch count a d
3304 3 1 # 1 # #
3305 3 1 # 2 # #
3306 3 1 # 3 # #
3307 3 1 # 4 # #
3308 SELECT * FROM `t1$EX` ORDER BY a, d;
3309 server_id master_server_id master_epoch count a d
3310 3 1 # # 2 NULL
3311 3 1 # # 2 NULL
3312 3 1 # # 2 NULL
3313 3 1 # # 2 NULL
3314 -- Examine data on Secondary Master before slave restart, should be as inserted
3315 select * from test.t1 order by a;
3316 a b
3317 1 Beach house
3318 2 Black pig
3319 -- Restart Secondary Master slave
3320 start slave;
3321 Warnings:
3322 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3323 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
3324 select * from test.t1 order by a;
3325 a b
3326 1 Beach house
3327 -------------------------------
3328 Test 7 : Delete-Delete conflict
3329 -------------------------------
3330 -- Stop Secondary slave
3331 stop slave;
3332 Warnings:
3333 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3334 -- Delete on Primary Master
3335 delete from test.t1 where a=1;
3336 -- Delete on Secondary Master
3337 delete from test.t1 where a=1;
3338 -- Examine data on Primary Master, no row
3339 select * from test.t1 order by a;
3340 a b
3341 Conflict algorithm counter diff :
3342 ndb_conflict_fn_epoch2
3343 5
3344 ndb_epoch_delete_delete
3345 1
3346 Exception table contents
3347 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3348 server_id master_server_id master_epoch count a d
3349 3 1 # 1 # #
3350 3 1 # 2 # #
3351 3 1 # 3 # #
3352 3 1 # 4 # #
3353 3 1 # 5 # #
3354 SELECT * FROM `t1$EX` ORDER BY a, d;
3355 server_id master_server_id master_epoch count a d
3356 3 1 # # 1 NULL
3357 3 1 # # 2 NULL
3358 3 1 # # 2 NULL
3359 3 1 # # 2 NULL
3360 3 1 # # 2 NULL
3361 -- Examine data on Secondary Master before slave restart, no row
3362 select * from test.t1 order by a;
3363 a b
3364 -- Restart Secondary Master slave
3365 start slave;
3366 Warnings:
3367 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3368 -- Examine data on Secondary Master after slave restart, no row
3369 select * from test.t1 order by a;
3370 a b
3371 ------------------------------------------------
3372 Test 8 : Delete-Delete, Insert conflict exposure
3373 ------------------------------------------------
3374 -- Insert a row on Secondary Master
3375 insert into test.t1 values (3, "Espy");
3376 -- Check it's present on Primary Master
3377 select * from test.t1 order by a;
3378 a b
3379 3 Espy
3380 -- Stop replication in both directions
3381 stop slave;
3382 Warnings:
3383 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3384 stop slave;
3385 Warnings:
3386 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3387 -- Delete row from both clusters
3388 delete from test.t1 where a=3;
3389 delete from test.t1 where a=3;
3390 -- Follow up with Insert from Secondary master
3391 insert into test.t1 values (3, "Dalriada");
3392 -- Restart replication in both directions
3393 start slave;
3394 Warnings:
3395 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3396 start slave;
3397 Warnings:
3398 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3399 -- Check data on both sites
3400 -- diverged with NDB$EPOCH
3401 -- consistent with NDB$EPOCH2
3402 -- Secondary master :
3403 select * from test.t1 order by a;
3404 a b
3405 3 Dalriada
3406 -- Primary master :
3407 select * from test.t1 order by a;
3408 a b
3409 3 Dalriada
3410 --Remove extra row
3411 delete from test.t1 where a=3;
3412 -- Note that Delete-Delete conflict detected below
3413 Conflict algorithm counter diff :
3414 ndb_conflict_fn_epoch2
3415 6
3416 ndb_epoch_delete_delete
3417 2
3418 Exception table contents
3419 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3420 server_id master_server_id master_epoch count a d
3421 3 1 # 1 # #
3422 3 1 # 2 # #
3423 3 1 # 3 # #
3424 3 1 # 4 # #
3425 3 1 # 5 # #
3426 3 1 # 6 # #
3427 SELECT * FROM `t1$EX` ORDER BY a, d;
3428 server_id master_server_id master_epoch count a d
3429 3 1 # # 1 NULL
3430 3 1 # # 2 NULL
3431 3 1 # # 2 NULL
3432 3 1 # # 2 NULL
3433 3 1 # # 2 NULL
3434 3 1 # # 3 NULL
3435 ------------------------------------------------
3436 Test 9 : Insert, Insert-Update-Delete conflict
3437 ------------------------------------------------
3438 -- Stop replication on Secondary Master
3439 stop slave;
3440 Warnings:
3441 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3442 -- Insert row on Primary Master
3443 insert into test.t1 values (4, "Haymarket");
3444 -- Insert row on Secondary Master
3445 insert into test.t1 values (4, "Outhouse");
3446 -- Update row on Secondary Master
3447 update test.t1 set b="Mathers" where a = 4;
3448 -- Delete row on Secondary Master
3449 delete from test.t1 where a=4;
3450 -- Examine data (none) on Secondary Master
3451 select * from test.t1 order by a;
3452 a b
3453 -- Examine data on Primary Master, should be unaffected
3454 select * from test.t1 order by a;
3455 a b
3456 4 Haymarket
3457 Conflict algorithm counter diff :
3458 ndb_conflict_fn_epoch2
3459 9
3460 ndb_epoch_delete_delete
3461 2
3462 Exception table contents
3463 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3464 server_id master_server_id master_epoch count a d
3465 3 1 # 1 # #
3466 3 1 # 2 # #
3467 3 1 # 3 # #
3468 3 1 # 4 # #
3469 3 1 # 5 # #
3470 3 1 # 6 # #
3471 3 1 # 7 # #
3472 3 1 # 8 # #
3473 3 1 # 9 # #
3474 SELECT * FROM `t1$EX` ORDER BY a, d;
3475 server_id master_server_id master_epoch count a d
3476 3 1 # # 1 NULL
3477 3 1 # # 2 NULL
3478 3 1 # # 2 NULL
3479 3 1 # # 2 NULL
3480 3 1 # # 2 NULL
3481 3 1 # # 3 NULL
3482 3 1 # # 4 NULL
3483 3 1 # # 4 NULL
3484 3 1 # # 4 NULL
3485 -- Examine data on Secondary Master (none)
3486 select * from test.t1 order by a;
3487 a b
3488 -- Restart Secondary Master's slave
3489 start slave;
3490 Warnings:
3491 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3492 -- Check data on Secondary Master, should be same as Primary Master
3493 select * from test.t1;
3494 a b
3495 4 Haymarket
3496 ------------------------------------------------
3497 Test 10 : Update, Delete-Insert-Update conflict
3498 ------------------------------------------------
3499 -- Stop replication on Secondary Master
3500 stop slave;
3501 Warnings:
3502 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3503 -- Update row on Primary Master
3504 update test.t1 set b="Blind poet" where a=4;
3505 -- Delete row on Secondary Master
3506 delete from test.t1 where a=4;
3507 -- Insert row on Secondary Master
3508 insert into test.t1 values (4, "Drouthy Neebors");
3509 -- Update row on Secondary Master
3510 update test.t1 set b="The Tankard" where a=4;
3511 -- Check data on Primary Master, should be unaffected
3512 select * from test.t1 order by a;
3513 a b
3514 4 Blind poet
3515 Conflict algorithm counter diff :
3516 ndb_conflict_fn_epoch2
3517 12
3518 ndb_epoch_delete_delete
3519 2
3520 Exception table contents
3521 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3522 server_id master_server_id master_epoch count a d
3523 3 1 # 1 # #
3524 3 1 # 2 # #
3525 3 1 # 3 # #
3526 3 1 # 4 # #
3527 3 1 # 5 # #
3528 3 1 # 6 # #
3529 3 1 # 7 # #
3530 3 1 # 8 # #
3531 3 1 # 9 # #
3532 3 1 # 10 # #
3533 3 1 # 11 # #
3534 3 1 # 12 # #
3535 SELECT * FROM `t1$EX` ORDER BY a, d;
3536 server_id master_server_id master_epoch count a d
3537 3 1 # # 1 NULL
3538 3 1 # # 2 NULL
3539 3 1 # # 2 NULL
3540 3 1 # # 2 NULL
3541 3 1 # # 2 NULL
3542 3 1 # # 3 NULL
3543 3 1 # # 4 NULL
3544 3 1 # # 4 NULL
3545 3 1 # # 4 NULL
3546 3 1 # # 4 NULL
3547 3 1 # # 4 NULL
3548 3 1 # # 4 NULL
3549 -- Check data on Secondary Master, as set
3550 select * from test.t1 order by a;
3551 a b
3552 4 The Tankard
3553 -- Restart Secondary Master slave
3554 start slave;
3555 Warnings:
3556 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3557 -- Check data on Secondary Master - should be as Primary
3558 select * from test.t1 order by a;
3559 a b
3560 4 Blind poet
3561 ------------------------------------------------------------------------
3562 Test 11 : Test Secondary insert-update-delete accepted
3563 ------------------------------------------------------------------------
3564 Insert row on Secondary
3565 insert into test.t1 values (5, "Minders");
3566 Update row on Secondary
3567 update test.t1 set b="Southsider" where a=5;
3568 Delete row on Secondary
3569 delete from test.t1 where a=5;
3570 Check data on Primary, should be none. No new conflicts
3571 select * from test.t1 order by a;
3572 a b
3573 4 Blind poet
3574 Conflict algorithm counter diff :
3575 ndb_conflict_fn_epoch2
3576 12
3577 ndb_epoch_delete_delete
3578 2
3579 Exception table contents
3580 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3581 server_id master_server_id master_epoch count a d
3582 3 1 # 1 # #
3583 3 1 # 2 # #
3584 3 1 # 3 # #
3585 3 1 # 4 # #
3586 3 1 # 5 # #
3587 3 1 # 6 # #
3588 3 1 # 7 # #
3589 3 1 # 8 # #
3590 3 1 # 9 # #
3591 3 1 # 10 # #
3592 3 1 # 11 # #
3593 3 1 # 12 # #
3594 SELECT * FROM `t1$EX` ORDER BY a, d;
3595 server_id master_server_id master_epoch count a d
3596 3 1 # # 1 NULL
3597 3 1 # # 2 NULL
3598 3 1 # # 2 NULL
3599 3 1 # # 2 NULL
3600 3 1 # # 2 NULL
3601 3 1 # # 3 NULL
3602 3 1 # # 4 NULL
3603 3 1 # # 4 NULL
3604 3 1 # # 4 NULL
3605 3 1 # # 4 NULL
3606 3 1 # # 4 NULL
3607 3 1 # # 4 NULL
3608 Conflict algorithm counter diff :
3609 ndb_conflict_fn_epoch2
3610 12
3611 ndb_epoch_delete_delete
3612 2
3613 Exception table contents
3614 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3615 server_id master_server_id master_epoch count a d
3616 3 1 # 1 # #
3617 3 1 # 2 # #
3618 3 1 # 3 # #
3619 3 1 # 4 # #
3620 3 1 # 5 # #
3621 3 1 # 6 # #
3622 3 1 # 7 # #
3623 3 1 # 8 # #
3624 3 1 # 9 # #
3625 3 1 # 10 # #
3626 3 1 # 11 # #
3627 3 1 # 12 # #
3628 SELECT * FROM `t1$EX` ORDER BY a, d;
3629 server_id master_server_id master_epoch count a d
3630 3 1 # # 1 NULL
3631 3 1 # # 2 NULL
3632 3 1 # # 2 NULL
3633 3 1 # # 2 NULL
3634 3 1 # # 2 NULL
3635 3 1 # # 3 NULL
3636 3 1 # # 4 NULL
3637 3 1 # # 4 NULL
3638 3 1 # # 4 NULL
3639 3 1 # # 4 NULL
3640 3 1 # # 4 NULL
3641 3 1 # # 4 NULL
3642 drop table test.t1;
3643 drop table test.t2;
3644 drop table test.t1$EX;
3645 Populate ndb_replication table as necessary
3646 -- 31 extra gci bits
3647 create table `test`.`t1$EX`
3648 (server_id int unsigned,
3649 master_server_id int unsigned,
3650 master_epoch bigint unsigned,
3651 count int unsigned,
3652 a int not null,
3653 d int,
3654 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
3655 Create table
3656 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
3657 Create other table
3658 create table test.t2(a int primary key, b int) engine = ndb;
3659 ----------------------------------
3660 Test 1 : Basic two-way replication
3661 ----------------------------------
3662 insert into test.t1 values (1, "Metropole");
3663 FLUSH LOGS;
3664 -- Verify that Max rep epoch has been updated
3665 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
3666 -- Now update data on slave
3667 update test.t1 set b="Favorit" where a=1;
3668 -- Now check data on Primary
3669 select * from test.t1 order by a;
3670 a b
3671 1 Favorit
3672 Conflict algorithm counter diff :
3673 Exception table contents
3674 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3675 server_id master_server_id master_epoch count a d
3676 SELECT * FROM `t1$EX` ORDER BY a, d;
3677 server_id master_server_id master_epoch count a d
3678 -- Now perform multiple consecutive updates on the Secondary
3679 update test.t1 set b="Elephant house" where a=1;
3680 update test.t1 set b="Beach house" where a=1;
3681 select * from test.t1 order by a;
3682 a b
3683 1 Beach house
3684 -- Now check they've applied on the Primary
3685 select * from test.t1 order by a;
3686 a b
3687 1 Beach house
3688 Conflict algorithm counter diff :
3689 Exception table contents
3690 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3691 server_id master_server_id master_epoch count a d
3692 SELECT * FROM `t1$EX` ORDER BY a, d;
3693 server_id master_server_id master_epoch count a d
3694 --------------------------------------------
3695 Test 2 : Normal Insert from Secondary Master
3696 --------------------------------------------
3697 -- Insert a new row on the Secondary
3698 insert into test.t1 values (2, "Forrest");
3699 -- Check it exists on the Primary
3700 select * from test.t1 order by a;
3701 a b
3702 1 Beach house
3703 2 Forrest
3704 -- Update from the Secondary
3705 update test.t1 set b="Reds" where a=2;
3706 select * from test.t1 order by a;
3707 a b
3708 1 Beach house
3709 2 Reds
3710 delete from test.t1 where a=2;
3711 select * from test.t1 order by a;
3712 a b
3713 1 Beach house
3714 Conflict algorithm counter diff :
3715 Exception table contents
3716 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3717 server_id master_server_id master_epoch count a d
3718 SELECT * FROM `t1$EX` ORDER BY a, d;
3719 server_id master_server_id master_epoch count a d
3720 -------------------------------
3721 Test 3 : Insert-Insert conflict
3722 -------------------------------
3723 stop slave;
3724 Warnings:
3725 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3726 -- Insert a row on the Primary Master
3727 insert into test.t1 values (2, "Loopy Lornas");
3728 -- Insert a row on the secondary Master
3729 insert into test.t1 values (2, "Cloisters");
3730 -- Examine data on Primary Master (should be unaffected)
3731 select * from test.t1 order by a;
3732 a b
3733 1 Beach house
3734 2 Loopy Lornas
3735 -- Examine conflict indicators on Primary Master
3736 Conflict algorithm counter diff :
3737 ndb_conflict_fn_epoch2
3738 1
3739 Exception table contents
3740 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3741 server_id master_server_id master_epoch count a d
3742 3 1 # 1 # #
3743 SELECT * FROM `t1$EX` ORDER BY a, d;
3744 server_id master_server_id master_epoch count a d
3745 3 1 # # 2 NULL
3746 -- Examine data on isolated secondary Master (should be as-set)
3747 select * from test.t1 order by a;
3748 a b
3749 1 Beach house
3750 2 Cloisters
3751 -- Restart secondary Masters slave
3752 start slave;
3753 Warnings:
3754 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3755 -- Reexamine secondary Master's data (should be same as Primary Masters)
3756 select * from test.t1 order by a;
3757 a b
3758 1 Beach house
3759 2 Loopy Lornas
3760 -------------------------------
3761 Test 4 : Update-Update conflict
3762 -------------------------------
3763 -- Stop replication to secondary master
3764 stop slave;
3765 Warnings:
3766 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3767 -- Update row on Primary Master
3768 update test.t1 set b="Peters Yard" where a=2;
3769 -- Show data on Primary Master
3770 select * from test.t1 order by a;
3771 a b
3772 1 Beach house
3773 2 Peters Yard
3774 Conflict algorithm counter diff :
3775 ndb_conflict_fn_epoch2
3776 1
3777 Exception table contents
3778 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3779 server_id master_server_id master_epoch count a d
3780 3 1 # 1 # #
3781 SELECT * FROM `t1$EX` ORDER BY a, d;
3782 server_id master_server_id master_epoch count a d
3783 3 1 # # 2 NULL
3784 -- Update row on Secondary Master
3785 update test.t1 set b="Toast" where a=2;
3786 -- Examine data on Primary Master - should be unaffected
3787 select * from test.t1 order by a;
3788 a b
3789 1 Beach house
3790 2 Peters Yard
3791 Conflict algorithm counter diff :
3792 ndb_conflict_fn_epoch2
3793 2
3794 Exception table contents
3795 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3796 server_id master_server_id master_epoch count a d
3797 3 1 # 1 # #
3798 3 1 # 2 # #
3799 SELECT * FROM `t1$EX` ORDER BY a, d;
3800 server_id master_server_id master_epoch count a d
3801 3 1 # # 2 NULL
3802 3 1 # # 2 NULL
3803 -- Check data on secondary - should be as set
3804 select * from test.t1 order by a;
3805 a b
3806 1 Beach house
3807 2 Toast
3808 -- Now restart slave, will re-align row
3809 start slave;
3810 Warnings:
3811 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3812 -- Check that Secondary is re-aligned
3813 select * from test.t1 order by a;
3814 a b
3815 1 Beach house
3816 2 Peters Yard
3817 -------------------------------
3818 Test 5 : Update-Delete conflict
3819 -------------------------------
3820 -- Stop Secondary slave
3821 stop slave;
3822 Warnings:
3823 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3824 -- Update on Primary Master
3825 update test.t1 set b="Pear tree" where a = 2;
3826 -- Delete on Secondary Master
3827 delete from test.t1 where a = 2;
3828 -- Examine data on Primary Master, should be unaffected
3829 select * from test.t1 order by a;
3830 a b
3831 1 Beach house
3832 2 Pear tree
3833 Conflict algorithm counter diff :
3834 ndb_conflict_fn_epoch2
3835 3
3836 Exception table contents
3837 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3838 server_id master_server_id master_epoch count a d
3839 3 1 # 1 # #
3840 3 1 # 2 # #
3841 3 1 # 3 # #
3842 SELECT * FROM `t1$EX` ORDER BY a, d;
3843 server_id master_server_id master_epoch count a d
3844 3 1 # # 2 NULL
3845 3 1 # # 2 NULL
3846 3 1 # # 2 NULL
3847 -- Examine data on Secondary Master before slave restart, still missing
3848 select * from test.t1 order by a;
3849 a b
3850 1 Beach house
3851 -- Restart Secondary Master slave
3852 start slave;
3853 Warnings:
3854 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3855 -- Examine data on Secondary Master after slave restart, aligned with Master
3856 select * from test.t1 order by a;
3857 a b
3858 1 Beach house
3859 2 Pear tree
3860 -------------------------------
3861 Test 6 : Delete-Update conflict
3862 -------------------------------
3863 -- Stop Secondary slave
3864 stop slave;
3865 Warnings:
3866 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3867 -- Delete on Primary Master
3868 delete from test.t1 where a=2;
3869 -- Update on Secondary Master
3870 update test.t1 set b="Black pig" where a=2;
3871 -- Examine data on Primary Master, should be unaffected (no row)
3872 select * from test.t1 order by a;
3873 a b
3874 1 Beach house
3875 Conflict algorithm counter diff :
3876 ndb_conflict_fn_epoch2
3877 4
3878 Exception table contents
3879 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3880 server_id master_server_id master_epoch count a d
3881 3 1 # 1 # #
3882 3 1 # 2 # #
3883 3 1 # 3 # #
3884 3 1 # 4 # #
3885 SELECT * FROM `t1$EX` ORDER BY a, d;
3886 server_id master_server_id master_epoch count a d
3887 3 1 # # 2 NULL
3888 3 1 # # 2 NULL
3889 3 1 # # 2 NULL
3890 3 1 # # 2 NULL
3891 -- Examine data on Secondary Master before slave restart, should be as inserted
3892 select * from test.t1 order by a;
3893 a b
3894 1 Beach house
3895 2 Black pig
3896 -- Restart Secondary Master slave
3897 start slave;
3898 Warnings:
3899 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3900 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
3901 select * from test.t1 order by a;
3902 a b
3903 1 Beach house
3904 -------------------------------
3905 Test 7 : Delete-Delete conflict
3906 -------------------------------
3907 -- Stop Secondary slave
3908 stop slave;
3909 Warnings:
3910 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3911 -- Delete on Primary Master
3912 delete from test.t1 where a=1;
3913 -- Delete on Secondary Master
3914 delete from test.t1 where a=1;
3915 -- Examine data on Primary Master, no row
3916 select * from test.t1 order by a;
3917 a b
3918 Conflict algorithm counter diff :
3919 ndb_conflict_fn_epoch2
3920 5
3921 ndb_epoch_delete_delete
3922 1
3923 Exception table contents
3924 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3925 server_id master_server_id master_epoch count a d
3926 3 1 # 1 # #
3927 3 1 # 2 # #
3928 3 1 # 3 # #
3929 3 1 # 4 # #
3930 3 1 # 5 # #
3931 SELECT * FROM `t1$EX` ORDER BY a, d;
3932 server_id master_server_id master_epoch count a d
3933 3 1 # # 1 NULL
3934 3 1 # # 2 NULL
3935 3 1 # # 2 NULL
3936 3 1 # # 2 NULL
3937 3 1 # # 2 NULL
3938 -- Examine data on Secondary Master before slave restart, no row
3939 select * from test.t1 order by a;
3940 a b
3941 -- Restart Secondary Master slave
3942 start slave;
3943 Warnings:
3944 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3945 -- Examine data on Secondary Master after slave restart, no row
3946 select * from test.t1 order by a;
3947 a b
3948 ------------------------------------------------
3949 Test 8 : Delete-Delete, Insert conflict exposure
3950 ------------------------------------------------
3951 -- Insert a row on Secondary Master
3952 insert into test.t1 values (3, "Espy");
3953 -- Check it's present on Primary Master
3954 select * from test.t1 order by a;
3955 a b
3956 3 Espy
3957 -- Stop replication in both directions
3958 stop slave;
3959 Warnings:
3960 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3961 stop slave;
3962 Warnings:
3963 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
3964 -- Delete row from both clusters
3965 delete from test.t1 where a=3;
3966 delete from test.t1 where a=3;
3967 -- Follow up with Insert from Secondary master
3968 insert into test.t1 values (3, "Dalriada");
3969 -- Restart replication in both directions
3970 start slave;
3971 Warnings:
3972 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3973 start slave;
3974 Warnings:
3975 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
3976 -- Check data on both sites
3977 -- diverged with NDB$EPOCH
3978 -- consistent with NDB$EPOCH2
3979 -- Secondary master :
3980 select * from test.t1 order by a;
3981 a b
3982 3 Dalriada
3983 -- Primary master :
3984 select * from test.t1 order by a;
3985 a b
3986 3 Dalriada
3987 --Remove extra row
3988 delete from test.t1 where a=3;
3989 -- Note that Delete-Delete conflict detected below
3990 Conflict algorithm counter diff :
3991 ndb_conflict_fn_epoch2
3992 6
3993 ndb_epoch_delete_delete
3994 2
3995 Exception table contents
3996 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
3997 server_id master_server_id master_epoch count a d
3998 3 1 # 1 # #
3999 3 1 # 2 # #
4000 3 1 # 3 # #
4001 3 1 # 4 # #
4002 3 1 # 5 # #
4003 3 1 # 6 # #
4004 SELECT * FROM `t1$EX` ORDER BY a, d;
4005 server_id master_server_id master_epoch count a d
4006 3 1 # # 1 NULL
4007 3 1 # # 2 NULL
4008 3 1 # # 2 NULL
4009 3 1 # # 2 NULL
4010 3 1 # # 2 NULL
4011 3 1 # # 3 NULL
4012 ------------------------------------------------
4013 Test 9 : Insert, Insert-Update-Delete conflict
4014 ------------------------------------------------
4015 -- Stop replication on Secondary Master
4016 stop slave;
4017 Warnings:
4018 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4019 -- Insert row on Primary Master
4020 insert into test.t1 values (4, "Haymarket");
4021 -- Insert row on Secondary Master
4022 insert into test.t1 values (4, "Outhouse");
4023 -- Update row on Secondary Master
4024 update test.t1 set b="Mathers" where a = 4;
4025 -- Delete row on Secondary Master
4026 delete from test.t1 where a=4;
4027 -- Examine data (none) on Secondary Master
4028 select * from test.t1 order by a;
4029 a b
4030 -- Examine data on Primary Master, should be unaffected
4031 select * from test.t1 order by a;
4032 a b
4033 4 Haymarket
4034 Conflict algorithm counter diff :
4035 ndb_conflict_fn_epoch2
4036 9
4037 ndb_epoch_delete_delete
4038 2
4039 Exception table contents
4040 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4041 server_id master_server_id master_epoch count a d
4042 3 1 # 1 # #
4043 3 1 # 2 # #
4044 3 1 # 3 # #
4045 3 1 # 4 # #
4046 3 1 # 5 # #
4047 3 1 # 6 # #
4048 3 1 # 7 # #
4049 3 1 # 8 # #
4050 3 1 # 9 # #
4051 SELECT * FROM `t1$EX` ORDER BY a, d;
4052 server_id master_server_id master_epoch count a d
4053 3 1 # # 1 NULL
4054 3 1 # # 2 NULL
4055 3 1 # # 2 NULL
4056 3 1 # # 2 NULL
4057 3 1 # # 2 NULL
4058 3 1 # # 3 NULL
4059 3 1 # # 4 NULL
4060 3 1 # # 4 NULL
4061 3 1 # # 4 NULL
4062 -- Examine data on Secondary Master (none)
4063 select * from test.t1 order by a;
4064 a b
4065 -- Restart Secondary Master's slave
4066 start slave;
4067 Warnings:
4068 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4069 -- Check data on Secondary Master, should be same as Primary Master
4070 select * from test.t1;
4071 a b
4072 4 Haymarket
4073 ------------------------------------------------
4074 Test 10 : Update, Delete-Insert-Update conflict
4075 ------------------------------------------------
4076 -- Stop replication on Secondary Master
4077 stop slave;
4078 Warnings:
4079 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4080 -- Update row on Primary Master
4081 update test.t1 set b="Blind poet" where a=4;
4082 -- Delete row on Secondary Master
4083 delete from test.t1 where a=4;
4084 -- Insert row on Secondary Master
4085 insert into test.t1 values (4, "Drouthy Neebors");
4086 -- Update row on Secondary Master
4087 update test.t1 set b="The Tankard" where a=4;
4088 -- Check data on Primary Master, should be unaffected
4089 select * from test.t1 order by a;
4090 a b
4091 4 Blind poet
4092 Conflict algorithm counter diff :
4093 ndb_conflict_fn_epoch2
4094 12
4095 ndb_epoch_delete_delete
4096 2
4097 Exception table contents
4098 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4099 server_id master_server_id master_epoch count a d
4100 3 1 # 1 # #
4101 3 1 # 2 # #
4102 3 1 # 3 # #
4103 3 1 # 4 # #
4104 3 1 # 5 # #
4105 3 1 # 6 # #
4106 3 1 # 7 # #
4107 3 1 # 8 # #
4108 3 1 # 9 # #
4109 3 1 # 10 # #
4110 3 1 # 11 # #
4111 3 1 # 12 # #
4112 SELECT * FROM `t1$EX` ORDER BY a, d;
4113 server_id master_server_id master_epoch count a d
4114 3 1 # # 1 NULL
4115 3 1 # # 2 NULL
4116 3 1 # # 2 NULL
4117 3 1 # # 2 NULL
4118 3 1 # # 2 NULL
4119 3 1 # # 3 NULL
4120 3 1 # # 4 NULL
4121 3 1 # # 4 NULL
4122 3 1 # # 4 NULL
4123 3 1 # # 4 NULL
4124 3 1 # # 4 NULL
4125 3 1 # # 4 NULL
4126 -- Check data on Secondary Master, as set
4127 select * from test.t1 order by a;
4128 a b
4129 4 The Tankard
4130 -- Restart Secondary Master slave
4131 start slave;
4132 Warnings:
4133 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4134 -- Check data on Secondary Master - should be as Primary
4135 select * from test.t1 order by a;
4136 a b
4137 4 Blind poet
4138 ------------------------------------------------------------------------
4139 Test 11 : Test Secondary insert-update-delete accepted
4140 ------------------------------------------------------------------------
4141 Insert row on Secondary
4142 insert into test.t1 values (5, "Minders");
4143 Update row on Secondary
4144 update test.t1 set b="Southsider" where a=5;
4145 Delete row on Secondary
4146 delete from test.t1 where a=5;
4147 Check data on Primary, should be none. No new conflicts
4148 select * from test.t1 order by a;
4149 a b
4150 4 Blind poet
4151 Conflict algorithm counter diff :
4152 ndb_conflict_fn_epoch2
4153 12
4154 ndb_epoch_delete_delete
4155 2
4156 Exception table contents
4157 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4158 server_id master_server_id master_epoch count a d
4159 3 1 # 1 # #
4160 3 1 # 2 # #
4161 3 1 # 3 # #
4162 3 1 # 4 # #
4163 3 1 # 5 # #
4164 3 1 # 6 # #
4165 3 1 # 7 # #
4166 3 1 # 8 # #
4167 3 1 # 9 # #
4168 3 1 # 10 # #
4169 3 1 # 11 # #
4170 3 1 # 12 # #
4171 SELECT * FROM `t1$EX` ORDER BY a, d;
4172 server_id master_server_id master_epoch count a d
4173 3 1 # # 1 NULL
4174 3 1 # # 2 NULL
4175 3 1 # # 2 NULL
4176 3 1 # # 2 NULL
4177 3 1 # # 2 NULL
4178 3 1 # # 3 NULL
4179 3 1 # # 4 NULL
4180 3 1 # # 4 NULL
4181 3 1 # # 4 NULL
4182 3 1 # # 4 NULL
4183 3 1 # # 4 NULL
4184 3 1 # # 4 NULL
4185 Conflict algorithm counter diff :
4186 ndb_conflict_fn_epoch2
4187 12
4188 ndb_epoch_delete_delete
4189 2
4190 Exception table contents
4191 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4192 server_id master_server_id master_epoch count a d
4193 3 1 # 1 # #
4194 3 1 # 2 # #
4195 3 1 # 3 # #
4196 3 1 # 4 # #
4197 3 1 # 5 # #
4198 3 1 # 6 # #
4199 3 1 # 7 # #
4200 3 1 # 8 # #
4201 3 1 # 9 # #
4202 3 1 # 10 # #
4203 3 1 # 11 # #
4204 3 1 # 12 # #
4205 SELECT * FROM `t1$EX` ORDER BY a, d;
4206 server_id master_server_id master_epoch count a d
4207 3 1 # # 1 NULL
4208 3 1 # # 2 NULL
4209 3 1 # # 2 NULL
4210 3 1 # # 2 NULL
4211 3 1 # # 2 NULL
4212 3 1 # # 3 NULL
4213 3 1 # # 4 NULL
4214 3 1 # # 4 NULL
4215 3 1 # # 4 NULL
4216 3 1 # # 4 NULL
4217 3 1 # # 4 NULL
4218 3 1 # # 4 NULL
4219 drop table test.t1;
4220 drop table test.t2;
4221 drop table test.t1$EX;
4222 Populate ndb_replication table as necessary
4223 -- Default extra Gci bits
4224 create table `test`.`t1$EX`
4225 (server_id int unsigned,
4226 master_server_id int unsigned,
4227 master_epoch bigint unsigned,
4228 count int unsigned,
4229 a int not null,
4230 d int,
4231 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
4232 Create table
4233 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
4234 Create other table
4235 create table test.t2(a int primary key, b int) engine = ndb;
4236 ----------------------------------
4237 Test 1 : Basic two-way replication
4238 ----------------------------------
4239 insert into test.t1 values (1, "Metropole");
4240 FLUSH LOGS;
4241 -- Verify that Max rep epoch has been updated
4242 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
4243 -- Now update data on slave
4244 update test.t1 set b="Favorit" where a=1;
4245 -- Now check data on Primary
4246 select * from test.t1 order by a;
4247 a b
4248 1 Favorit
4249 Conflict algorithm counter diff :
4250 Exception table contents
4251 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4252 server_id master_server_id master_epoch count a d
4253 SELECT * FROM `t1$EX` ORDER BY a, d;
4254 server_id master_server_id master_epoch count a d
4255 -- Now perform multiple consecutive updates on the Secondary
4256 update test.t1 set b="Elephant house" where a=1;
4257 update test.t1 set b="Beach house" where a=1;
4258 select * from test.t1 order by a;
4259 a b
4260 1 Beach house
4261 -- Now check they've applied on the Primary
4262 select * from test.t1 order by a;
4263 a b
4264 1 Beach house
4265 Conflict algorithm counter diff :
4266 Exception table contents
4267 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4268 server_id master_server_id master_epoch count a d
4269 SELECT * FROM `t1$EX` ORDER BY a, d;
4270 server_id master_server_id master_epoch count a d
4271 --------------------------------------------
4272 Test 2 : Normal Insert from Secondary Master
4273 --------------------------------------------
4274 -- Insert a new row on the Secondary
4275 insert into test.t1 values (2, "Forrest");
4276 -- Check it exists on the Primary
4277 select * from test.t1 order by a;
4278 a b
4279 1 Beach house
4280 2 Forrest
4281 -- Update from the Secondary
4282 update test.t1 set b="Reds" where a=2;
4283 select * from test.t1 order by a;
4284 a b
4285 1 Beach house
4286 2 Reds
4287 delete from test.t1 where a=2;
4288 select * from test.t1 order by a;
4289 a b
4290 1 Beach house
4291 Conflict algorithm counter diff :
4292 Exception table contents
4293 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4294 server_id master_server_id master_epoch count a d
4295 SELECT * FROM `t1$EX` ORDER BY a, d;
4296 server_id master_server_id master_epoch count a d
4297 -------------------------------
4298 Test 3 : Insert-Insert conflict
4299 -------------------------------
4300 stop slave;
4301 Warnings:
4302 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4303 -- Insert a row on the Primary Master
4304 insert into test.t1 values (2, "Loopy Lornas");
4305 -- Insert a row on the secondary Master
4306 insert into test.t1 values (2, "Cloisters");
4307 -- Examine data on Primary Master (should be unaffected)
4308 select * from test.t1 order by a;
4309 a b
4310 1 Beach house
4311 2 Loopy Lornas
4312 -- Examine conflict indicators on Primary Master
4313 Conflict algorithm counter diff :
4314 ndb_conflict_fn_epoch2
4315 1
4316 Exception table contents
4317 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4318 server_id master_server_id master_epoch count a d
4319 3 1 # 1 # #
4320 SELECT * FROM `t1$EX` ORDER BY a, d;
4321 server_id master_server_id master_epoch count a d
4322 3 1 # # 2 NULL
4323 -- Examine data on isolated secondary Master (should be as-set)
4324 select * from test.t1 order by a;
4325 a b
4326 1 Beach house
4327 2 Cloisters
4328 -- Restart secondary Masters slave
4329 start slave;
4330 Warnings:
4331 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4332 -- Reexamine secondary Master's data (should be same as Primary Masters)
4333 select * from test.t1 order by a;
4334 a b
4335 1 Beach house
4336 2 Loopy Lornas
4337 -------------------------------
4338 Test 4 : Update-Update conflict
4339 -------------------------------
4340 -- Stop replication to secondary master
4341 stop slave;
4342 Warnings:
4343 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4344 -- Update row on Primary Master
4345 update test.t1 set b="Peters Yard" where a=2;
4346 -- Show data on Primary Master
4347 select * from test.t1 order by a;
4348 a b
4349 1 Beach house
4350 2 Peters Yard
4351 Conflict algorithm counter diff :
4352 ndb_conflict_fn_epoch2
4353 1
4354 Exception table contents
4355 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4356 server_id master_server_id master_epoch count a d
4357 3 1 # 1 # #
4358 SELECT * FROM `t1$EX` ORDER BY a, d;
4359 server_id master_server_id master_epoch count a d
4360 3 1 # # 2 NULL
4361 -- Update row on Secondary Master
4362 update test.t1 set b="Toast" where a=2;
4363 -- Examine data on Primary Master - should be unaffected
4364 select * from test.t1 order by a;
4365 a b
4366 1 Beach house
4367 2 Peters Yard
4368 Conflict algorithm counter diff :
4369 ndb_conflict_fn_epoch2
4370 2
4371 Exception table contents
4372 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4373 server_id master_server_id master_epoch count a d
4374 3 1 # 1 # #
4375 3 1 # 2 # #
4376 SELECT * FROM `t1$EX` ORDER BY a, d;
4377 server_id master_server_id master_epoch count a d
4378 3 1 # # 2 NULL
4379 3 1 # # 2 NULL
4380 -- Check data on secondary - should be as set
4381 select * from test.t1 order by a;
4382 a b
4383 1 Beach house
4384 2 Toast
4385 -- Now restart slave, will re-align row
4386 start slave;
4387 Warnings:
4388 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4389 -- Check that Secondary is re-aligned
4390 select * from test.t1 order by a;
4391 a b
4392 1 Beach house
4393 2 Peters Yard
4394 -------------------------------
4395 Test 5 : Update-Delete conflict
4396 -------------------------------
4397 -- Stop Secondary slave
4398 stop slave;
4399 Warnings:
4400 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4401 -- Update on Primary Master
4402 update test.t1 set b="Pear tree" where a = 2;
4403 -- Delete on Secondary Master
4404 delete from test.t1 where a = 2;
4405 -- Examine data on Primary Master, should be unaffected
4406 select * from test.t1 order by a;
4407 a b
4408 1 Beach house
4409 2 Pear tree
4410 Conflict algorithm counter diff :
4411 ndb_conflict_fn_epoch2
4412 3
4413 Exception table contents
4414 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4415 server_id master_server_id master_epoch count a d
4416 3 1 # 1 # #
4417 3 1 # 2 # #
4418 3 1 # 3 # #
4419 SELECT * FROM `t1$EX` ORDER BY a, d;
4420 server_id master_server_id master_epoch count a d
4421 3 1 # # 2 NULL
4422 3 1 # # 2 NULL
4423 3 1 # # 2 NULL
4424 -- Examine data on Secondary Master before slave restart, still missing
4425 select * from test.t1 order by a;
4426 a b
4427 1 Beach house
4428 -- Restart Secondary Master slave
4429 start slave;
4430 Warnings:
4431 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4432 -- Examine data on Secondary Master after slave restart, aligned with Master
4433 select * from test.t1 order by a;
4434 a b
4435 1 Beach house
4436 2 Pear tree
4437 -------------------------------
4438 Test 6 : Delete-Update conflict
4439 -------------------------------
4440 -- Stop Secondary slave
4441 stop slave;
4442 Warnings:
4443 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4444 -- Delete on Primary Master
4445 delete from test.t1 where a=2;
4446 -- Update on Secondary Master
4447 update test.t1 set b="Black pig" where a=2;
4448 -- Examine data on Primary Master, should be unaffected (no row)
4449 select * from test.t1 order by a;
4450 a b
4451 1 Beach house
4452 Conflict algorithm counter diff :
4453 ndb_conflict_fn_epoch2
4454 4
4455 Exception table contents
4456 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4457 server_id master_server_id master_epoch count a d
4458 3 1 # 1 # #
4459 3 1 # 2 # #
4460 3 1 # 3 # #
4461 3 1 # 4 # #
4462 SELECT * FROM `t1$EX` ORDER BY a, d;
4463 server_id master_server_id master_epoch count a d
4464 3 1 # # 2 NULL
4465 3 1 # # 2 NULL
4466 3 1 # # 2 NULL
4467 3 1 # # 2 NULL
4468 -- Examine data on Secondary Master before slave restart, should be as inserted
4469 select * from test.t1 order by a;
4470 a b
4471 1 Beach house
4472 2 Black pig
4473 -- Restart Secondary Master slave
4474 start slave;
4475 Warnings:
4476 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4477 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
4478 select * from test.t1 order by a;
4479 a b
4480 1 Beach house
4481 -------------------------------
4482 Test 7 : Delete-Delete conflict
4483 -------------------------------
4484 -- Stop Secondary slave
4485 stop slave;
4486 Warnings:
4487 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4488 -- Delete on Primary Master
4489 delete from test.t1 where a=1;
4490 -- Delete on Secondary Master
4491 delete from test.t1 where a=1;
4492 -- Examine data on Primary Master, no row
4493 select * from test.t1 order by a;
4494 a b
4495 Conflict algorithm counter diff :
4496 ndb_conflict_fn_epoch2
4497 5
4498 ndb_epoch_delete_delete
4499 1
4500 Exception table contents
4501 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4502 server_id master_server_id master_epoch count a d
4503 3 1 # 1 # #
4504 3 1 # 2 # #
4505 3 1 # 3 # #
4506 3 1 # 4 # #
4507 3 1 # 5 # #
4508 SELECT * FROM `t1$EX` ORDER BY a, d;
4509 server_id master_server_id master_epoch count a d
4510 3 1 # # 1 NULL
4511 3 1 # # 2 NULL
4512 3 1 # # 2 NULL
4513 3 1 # # 2 NULL
4514 3 1 # # 2 NULL
4515 -- Examine data on Secondary Master before slave restart, no row
4516 select * from test.t1 order by a;
4517 a b
4518 -- Restart Secondary Master slave
4519 start slave;
4520 Warnings:
4521 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4522 -- Examine data on Secondary Master after slave restart, no row
4523 select * from test.t1 order by a;
4524 a b
4525 ------------------------------------------------
4526 Test 8 : Delete-Delete, Insert conflict exposure
4527 ------------------------------------------------
4528 -- Insert a row on Secondary Master
4529 insert into test.t1 values (3, "Espy");
4530 -- Check it's present on Primary Master
4531 select * from test.t1 order by a;
4532 a b
4533 3 Espy
4534 -- Stop replication in both directions
4535 stop slave;
4536 Warnings:
4537 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4538 stop slave;
4539 Warnings:
4540 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4541 -- Delete row from both clusters
4542 delete from test.t1 where a=3;
4543 delete from test.t1 where a=3;
4544 -- Follow up with Insert from Secondary master
4545 insert into test.t1 values (3, "Dalriada");
4546 -- Restart replication in both directions
4547 start slave;
4548 Warnings:
4549 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4550 start slave;
4551 Warnings:
4552 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4553 -- Check data on both sites
4554 -- diverged with NDB$EPOCH
4555 -- consistent with NDB$EPOCH2
4556 -- Secondary master :
4557 select * from test.t1 order by a;
4558 a b
4559 3 Dalriada
4560 -- Primary master :
4561 select * from test.t1 order by a;
4562 a b
4563 3 Dalriada
4564 --Remove extra row
4565 delete from test.t1 where a=3;
4566 -- Note that Delete-Delete conflict detected below
4567 Conflict algorithm counter diff :
4568 ndb_conflict_fn_epoch2
4569 6
4570 ndb_epoch_delete_delete
4571 2
4572 Exception table contents
4573 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4574 server_id master_server_id master_epoch count a d
4575 3 1 # 1 # #
4576 3 1 # 2 # #
4577 3 1 # 3 # #
4578 3 1 # 4 # #
4579 3 1 # 5 # #
4580 3 1 # 6 # #
4581 SELECT * FROM `t1$EX` ORDER BY a, d;
4582 server_id master_server_id master_epoch count a d
4583 3 1 # # 1 NULL
4584 3 1 # # 2 NULL
4585 3 1 # # 2 NULL
4586 3 1 # # 2 NULL
4587 3 1 # # 2 NULL
4588 3 1 # # 3 NULL
4589 ------------------------------------------------
4590 Test 9 : Insert, Insert-Update-Delete conflict
4591 ------------------------------------------------
4592 -- Stop replication on Secondary Master
4593 stop slave;
4594 Warnings:
4595 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4596 -- Insert row on Primary Master
4597 insert into test.t1 values (4, "Haymarket");
4598 -- Insert row on Secondary Master
4599 insert into test.t1 values (4, "Outhouse");
4600 -- Update row on Secondary Master
4601 update test.t1 set b="Mathers" where a = 4;
4602 -- Delete row on Secondary Master
4603 delete from test.t1 where a=4;
4604 -- Examine data (none) on Secondary Master
4605 select * from test.t1 order by a;
4606 a b
4607 -- Examine data on Primary Master, should be unaffected
4608 select * from test.t1 order by a;
4609 a b
4610 4 Haymarket
4611 Conflict algorithm counter diff :
4612 ndb_conflict_fn_epoch2
4613 9
4614 ndb_epoch_delete_delete
4615 2
4616 Exception table contents
4617 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4618 server_id master_server_id master_epoch count a d
4619 3 1 # 1 # #
4620 3 1 # 2 # #
4621 3 1 # 3 # #
4622 3 1 # 4 # #
4623 3 1 # 5 # #
4624 3 1 # 6 # #
4625 3 1 # 7 # #
4626 3 1 # 8 # #
4627 3 1 # 9 # #
4628 SELECT * FROM `t1$EX` ORDER BY a, d;
4629 server_id master_server_id master_epoch count a d
4630 3 1 # # 1 NULL
4631 3 1 # # 2 NULL
4632 3 1 # # 2 NULL
4633 3 1 # # 2 NULL
4634 3 1 # # 2 NULL
4635 3 1 # # 3 NULL
4636 3 1 # # 4 NULL
4637 3 1 # # 4 NULL
4638 3 1 # # 4 NULL
4639 -- Examine data on Secondary Master (none)
4640 select * from test.t1 order by a;
4641 a b
4642 -- Restart Secondary Master's slave
4643 start slave;
4644 Warnings:
4645 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4646 -- Check data on Secondary Master, should be same as Primary Master
4647 select * from test.t1;
4648 a b
4649 4 Haymarket
4650 ------------------------------------------------
4651 Test 10 : Update, Delete-Insert-Update conflict
4652 ------------------------------------------------
4653 -- Stop replication on Secondary Master
4654 stop slave;
4655 Warnings:
4656 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4657 -- Update row on Primary Master
4658 update test.t1 set b="Blind poet" where a=4;
4659 -- Delete row on Secondary Master
4660 delete from test.t1 where a=4;
4661 -- Insert row on Secondary Master
4662 insert into test.t1 values (4, "Drouthy Neebors");
4663 -- Update row on Secondary Master
4664 update test.t1 set b="The Tankard" where a=4;
4665 -- Check data on Primary Master, should be unaffected
4666 select * from test.t1 order by a;
4667 a b
4668 4 Blind poet
4669 Conflict algorithm counter diff :
4670 ndb_conflict_fn_epoch2
4671 12
4672 ndb_epoch_delete_delete
4673 2
4674 Exception table contents
4675 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4676 server_id master_server_id master_epoch count a d
4677 3 1 # 1 # #
4678 3 1 # 2 # #
4679 3 1 # 3 # #
4680 3 1 # 4 # #
4681 3 1 # 5 # #
4682 3 1 # 6 # #
4683 3 1 # 7 # #
4684 3 1 # 8 # #
4685 3 1 # 9 # #
4686 3 1 # 10 # #
4687 3 1 # 11 # #
4688 3 1 # 12 # #
4689 SELECT * FROM `t1$EX` ORDER BY a, d;
4690 server_id master_server_id master_epoch count a d
4691 3 1 # # 1 NULL
4692 3 1 # # 2 NULL
4693 3 1 # # 2 NULL
4694 3 1 # # 2 NULL
4695 3 1 # # 2 NULL
4696 3 1 # # 3 NULL
4697 3 1 # # 4 NULL
4698 3 1 # # 4 NULL
4699 3 1 # # 4 NULL
4700 3 1 # # 4 NULL
4701 3 1 # # 4 NULL
4702 3 1 # # 4 NULL
4703 -- Check data on Secondary Master, as set
4704 select * from test.t1 order by a;
4705 a b
4706 4 The Tankard
4707 -- Restart Secondary Master slave
4708 start slave;
4709 Warnings:
4710 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4711 -- Check data on Secondary Master - should be as Primary
4712 select * from test.t1 order by a;
4713 a b
4714 4 Blind poet
4715 ------------------------------------------------------------------------
4716 Test 11 : Test Secondary insert-update-delete accepted
4717 ------------------------------------------------------------------------
4718 Insert row on Secondary
4719 insert into test.t1 values (5, "Minders");
4720 Update row on Secondary
4721 update test.t1 set b="Southsider" where a=5;
4722 Delete row on Secondary
4723 delete from test.t1 where a=5;
4724 Check data on Primary, should be none. No new conflicts
4725 select * from test.t1 order by a;
4726 a b
4727 4 Blind poet
4728 Conflict algorithm counter diff :
4729 ndb_conflict_fn_epoch2
4730 12
4731 ndb_epoch_delete_delete
4732 2
4733 Exception table contents
4734 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4735 server_id master_server_id master_epoch count a d
4736 3 1 # 1 # #
4737 3 1 # 2 # #
4738 3 1 # 3 # #
4739 3 1 # 4 # #
4740 3 1 # 5 # #
4741 3 1 # 6 # #
4742 3 1 # 7 # #
4743 3 1 # 8 # #
4744 3 1 # 9 # #
4745 3 1 # 10 # #
4746 3 1 # 11 # #
4747 3 1 # 12 # #
4748 SELECT * FROM `t1$EX` ORDER BY a, d;
4749 server_id master_server_id master_epoch count a d
4750 3 1 # # 1 NULL
4751 3 1 # # 2 NULL
4752 3 1 # # 2 NULL
4753 3 1 # # 2 NULL
4754 3 1 # # 2 NULL
4755 3 1 # # 3 NULL
4756 3 1 # # 4 NULL
4757 3 1 # # 4 NULL
4758 3 1 # # 4 NULL
4759 3 1 # # 4 NULL
4760 3 1 # # 4 NULL
4761 3 1 # # 4 NULL
4762 Conflict algorithm counter diff :
4763 ndb_conflict_fn_epoch2
4764 12
4765 ndb_epoch_delete_delete
4766 2
4767 Exception table contents
4768 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4769 server_id master_server_id master_epoch count a d
4770 3 1 # 1 # #
4771 3 1 # 2 # #
4772 3 1 # 3 # #
4773 3 1 # 4 # #
4774 3 1 # 5 # #
4775 3 1 # 6 # #
4776 3 1 # 7 # #
4777 3 1 # 8 # #
4778 3 1 # 9 # #
4779 3 1 # 10 # #
4780 3 1 # 11 # #
4781 3 1 # 12 # #
4782 SELECT * FROM `t1$EX` ORDER BY a, d;
4783 server_id master_server_id master_epoch count a d
4784 3 1 # # 1 NULL
4785 3 1 # # 2 NULL
4786 3 1 # # 2 NULL
4787 3 1 # # 2 NULL
4788 3 1 # # 2 NULL
4789 3 1 # # 3 NULL
4790 3 1 # # 4 NULL
4791 3 1 # # 4 NULL
4792 3 1 # # 4 NULL
4793 3 1 # # 4 NULL
4794 3 1 # # 4 NULL
4795 3 1 # # 4 NULL
4796 drop table test.t1;
4797 drop table test.t2;
4798 drop table test.t1$EX;
4799 Now test batched conflict detection/handling
4800 create table test.t1 (
4801 a int primary key,
4802 b int,
4803 c varchar(2000)) engine=ndb;
4804 -- Stop replication from Primary -> Secondary
4805 stop slave;
4806 Warnings:
4807 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4808 -- Insert a row on Primary
4809 insert into test.t1 values (1,1,repeat('B', 2000));
4810 -- Generate a large batch of inserts with early + late conflicts
4811 create procedure test.doit (`rows` int)
4812 begin
4813 set @x = 0;
4814 START TRANSACTION;
4815 repeat
4816 insert into test.t1 values (@x, @x, repeat('B', 2000));
4817 set @x = @x + 1;
4818 until @x = `rows`
4819 end repeat;
4820 COMMIT;
4821 START TRANSACTION;
4822 update test.t1 set b=999, c=repeat('E',2000) where a=1;
4823 COMMIT;
4824 START TRANSACTION;
4825 delete from test.t1 where a=1;
4826 COMMIT;
4827 START TRANSACTION;
4828 insert into test.t1 values (1,1,'A');
4829 COMMIT;
4830 end%
4831 call test.doit(100);
4832 drop procedure test.doit;
4833 -- Look at Primary status, expect 4 conflicts
4834 Conflict algorithm counter diff :
4835 ndb_conflict_fn_epoch2
4836 4
4837 Exception table contents
4838 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
4839 SELECT * FROM `t1$EX` ORDER BY a, d;
4840 start slave;
4841 Warnings:
4842 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4843 -- Look at Seconday row
4844 select a,b,sha1(c) from test.t1 where a=1;
4845 a b sha1(c)
4846 1 1 6d41e1d402596dff36396d1f0f288d17a4b9800a
4847 -- Check it's the same on the Secondary
4848 select a,b,sha1(c) from test.t1 where a=1;
4849 a b sha1(c)
4850 1 1 6d41e1d402596dff36396d1f0f288d17a4b9800a
4851 Test batching of DELETE vs DELETE with following INSERT
4852 delete from test.t1;
4853 insert into test.t1 values (1, 1, "Ma Brows");
4854 -- Stop Slave in both directions
4855 stop slave;
4856 Warnings:
4857 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4858 stop slave;
4859 Warnings:
4860 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
4861 -- Delete row on Primary Cluster
4862 delete from test.t1 where a=1;
4863 -- Delete row on Secondary Cluster, followed by Insert in later 'batch'
4864 create procedure test.doit (`rows` int)
4865 begin
4866 set @x = 2;
4867 START TRANSACTION;
4868 delete from test.t1 where a=1;
4869 repeat
4870 insert into test.t1 values (@x, @x, repeat('B', 2000));
4871 set @x = @x + 1;
4872 until @x = (`rows` + 2)
4873 end repeat;
4874 COMMIT;
4875 START TRANSACTION;
4876 insert into test.t1 values (1, 1, 'Malleny arms');
4877 COMMIT;
4878 end%
4879 call test.doit(200);
4880 -- Restart slave on Primary Cluster
4881 start replica;
4882 -- Show data on Primary Cluster (should have row inserted on Secondary)
4883 select * from test.t1 where a=1;
4884 a b c
4885 1 1 Malleny arms
4886 -- Show data on Secondary Cluster (should have row inserted on Secondary)
4887 select * from test.t1 where a=1;
4888 a b c
4889 1 1 Malleny arms
4890 --Restart slave on Secondary Cluster
4891 start slave;
4892 Warnings:
4893 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
4894 -- Show data on Clusters after slaves restarted+synced
4895 For NDB$EPOCH, the data will be missing
4896 as expected with delete vs delete conflict
4897 followed closely by Insert
4898 For NDB$EPOCH2, the data will be present on both
4899 clusters.
4900 PRIMARY
4901 select * from test.t1 where a=1;
4902 a b c
4903 1 1 Malleny arms
4904 SECONDARY
4905 select * from test.t1 where a=1;
4906 a b c
4907 1 1 Malleny arms
4908 -- Force wait for master to be in-sync with slave
4909 To avoid race between DML and table drop
4910 flush logs;
4911 drop procedure test.doit;
4912 drop table test.t1;
4913 delete from mysql.ndb_replication;
4914 insert into mysql.ndb_replication values
4915 ("test", "t3", 0, 7, "NDB$EPOCH2(32)"),
4916 ("test", "t4", 0, 7, "NDB$EPOCH2(-1)");
4917 create table test.t3 (a int primary key) engine=ndb;
4918 ERROR HY000: Error in parsing conflict function. Message: NDB$EPOCH2(32), Too many extra Gci bits at ')'
4919 show warnings;
4920 Level Code Message
4921 Error 1626 Error in parsing conflict function. Message: NDB$EPOCH2(32), Too many extra Gci bits at ')'
4922 Error 1031 Table storage engine for 't3' doesn't have this option
4923 create table test.t4 (a int primary key) engine=ndb;
4924 ERROR HY000: Error in parsing conflict function. Message: NDB$EPOCH2(-1), Too many extra Gci bits at ')'
4925 show warnings;
4926 Level Code Message
4927 Error 1626 Error in parsing conflict function. Message: NDB$EPOCH2(-1), Too many extra Gci bits at ')'
4928 Error 1031 Table storage engine for 't4' doesn't have this option
4929 -- Force sync before dropping table to avoid race
4930 flush logs;
4931 flush logs;
4932 drop table mysql.ndb_replication;
4933 -- Attempt to get system back in pre-test state
4934 stop replica;
4935 reset replica;
4936 include/rpl_end.inc
4937 SET GLOBAL ndb_applier_conflict_role="NONE";
4938 SET GLOBAL ndb_applier_conflict_role="NONE";