Debian Codegraph @

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

raw
1 Testing NDB algorithm
2 Setup connections for use in this file
3 First we have Cluster A as Primary, Cluster B as Secondary
4 SET GLOBAL ndb_applier_conflict_role="PRIMARY";
5 SET GLOBAL ndb_applier_conflict_role="SECONDARY";
6 include/master-slave.inc
7 Warnings:
8 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
9 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.
10 [connection master]
11 Setup circular replication
12 Setup ndb_replication and t1 exceptions table
13 Populate ndb_replication table as necessary
14 replace into mysql.ndb_replication values
15 ("test", "t1", 3, 7, "NDB$EPOCH2_TRANS()"),
16 ("test", "t1", 1, 7, "NDB$EPOCH2_TRANS()");
17 replace into mysql.ndb_replication values
18 ("test", "t2", 3, 7, "NDB$EPOCH2_TRANS()"),
19 ("test", "t2", 1, 7, "NDB$EPOCH2_TRANS()");
20 create table test.t1 (
21 a int primary key,
22 b varchar(2000)) engine=ndb;
23 create table test.t2 (
24 a int primary key,
25 b varchar(2000)) engine=ndb;
26 Add some data
27 insert into test.t1 values
28 (1, "Initial data 1"),
29 (2, "Initial data 2"),
30 (3, "Initial data 3"),
31 (4, "Initial data 4"),
32 (5, "Initial data 5"),
33 (6, "Initial data 6"),
34 (7, "Initial data 7"),
35 (8, "Initial data 8"),
36 (9, "Initial data 9"),
37 (10, "Initial data 10");
38 Show basic row-level conflict detection
39 ---------------------------------------
40 stop slave;
41 Warnings:
42 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
43 update t1 set b="Primary first change 2" where a=2;
44 select * from test.t1 order by a;
45 a b
46 1 Initial data 1
47 2 Primary first change 2
48 3 Initial data 3
49 4 Initial data 4
50 5 Initial data 5
51 6 Initial data 6
52 7 Initial data 7
53 8 Initial data 8
54 9 Initial data 9
55 10 Initial data 10
56 update t1 set b="Secondary first change 2" where a=2;
57 select * from test.t1 order by a;
58 a b
59 1 Initial data 1
60 2 Secondary first change 2
61 3 Initial data 3
62 4 Initial data 4
63 5 Initial data 5
64 6 Initial data 6
65 7 Initial data 7
66 8 Initial data 8
67 9 Initial data 9
68 10 Initial data 10
69 Primary should have rejected change from Secondary, keeping its value
70 select * from t1 order by a;
71 a b
72 1 Initial data 1
73 2 Primary first change 2
74 3 Initial data 3
75 4 Initial data 4
76 5 Initial data 5
77 6 Initial data 6
78 7 Initial data 7
79 8 Initial data 8
80 9 Initial data 9
81 10 Initial data 10
82 ndb_conflict_fn_epoch_trans
83 0
84 ndb_conflict_fn_epoch2_trans
85 1
86 ndb_conflict_trans_row_conflict_count
87 1
88 ndb_conflict_trans_row_reject_count
89 1
90 ndb_conflict_trans_reject_count
91 1
92 ndb_conflict_trans_detect_iter_count
93 1
94 ndb_conflict_trans_conflict_commit_count
95 1
96 ndb_epoch_delete_delete_count
97 0
98 start slave;
99 Warnings:
100 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
101 Secondary should have been realigned to Primary
102 select * from t1 order by a;
103 a b
104 1 Initial data 1
105 2 Primary first change 2
106 3 Initial data 3
107 4 Initial data 4
108 5 Initial data 5
109 6 Initial data 6
110 7 Initial data 7
111 8 Initial data 8
112 9 Initial data 9
113 10 Initial data 10
114 Show rollback of whole secondary transaction
115 --------------------------------------------
116 stop slave;
117 Warnings:
118 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
119 update t1 set b="Primary second change 4" where a=4;
120 select * from test.t1 order by a;
121 a b
122 1 Initial data 1
123 2 Primary first change 2
124 3 Initial data 3
125 4 Primary second change 4
126 5 Initial data 5
127 6 Initial data 6
128 7 Initial data 7
129 8 Initial data 8
130 9 Initial data 9
131 10 Initial data 10
132 begin;
133 update t1 set b="Secondary second change 4" where a=4;
134 update t1 set b="Secondary second change 5" where a=5;
135 commit;
136 select * from test.t1 order by a;
137 a b
138 1 Initial data 1
139 2 Primary first change 2
140 3 Initial data 3
141 4 Secondary second change 4
142 5 Secondary second change 5
143 6 Initial data 6
144 7 Initial data 7
145 8 Initial data 8
146 9 Initial data 9
147 10 Initial data 10
148 Primary should have rejected secondary changes on both rows
149 select * from test.t1 order by a;
150 a b
151 1 Initial data 1
152 2 Primary first change 2
153 3 Initial data 3
154 4 Primary second change 4
155 5 Initial data 5
156 6 Initial data 6
157 7 Initial data 7
158 8 Initial data 8
159 9 Initial data 9
160 10 Initial data 10
161 ndb_conflict_fn_epoch_trans
162 0
163 ndb_conflict_fn_epoch2_trans
164 1
165 ndb_conflict_trans_row_conflict_count
166 1
167 ndb_conflict_trans_row_reject_count
168 2
169 ndb_conflict_trans_reject_count
170 1
171 ndb_conflict_trans_detect_iter_count
172 1
173 ndb_conflict_trans_conflict_commit_count
174 1
175 ndb_epoch_delete_delete_count
176 0
177 start slave;
178 Warnings:
179 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
180 Secondary should have been realigned to Primary
181 select * from test.t1 order by a;
182 a b
183 1 Initial data 1
184 2 Primary first change 2
185 3 Initial data 3
186 4 Primary second change 4
187 5 Initial data 5
188 6 Initial data 6
189 7 Initial data 7
190 8 Initial data 8
191 9 Initial data 9
192 10 Initial data 10
193 Show rollback of dependent transaction as well
194 ----------------------------------------------
195 stop slave;
196 Warnings:
197 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
198 update t1 set b="Primary third change 1" where a=1;
199 select * from test.t1 order by a;
200 a b
201 1 Primary third change 1
202 2 Primary first change 2
203 3 Initial data 3
204 4 Primary second change 4
205 5 Initial data 5
206 6 Initial data 6
207 7 Initial data 7
208 8 Initial data 8
209 9 Initial data 9
210 10 Initial data 10
211 begin;
212 update t1 set b="Secondary third change 3" where a=3;
213 update t1 set b="Secondary third change 1" where a=1;
214 commit;
215 begin;
216 update t1 set b="Secondary fourth change 3" where a=3;
217 insert into t1 values (11,"Secondary fourth change 11");
218 commit;
219 select * from test.t1 order by a;
220 a b
221 1 Secondary third change 1
222 2 Primary first change 2
223 3 Secondary fourth change 3
224 4 Primary second change 4
225 5 Initial data 5
226 6 Initial data 6
227 7 Initial data 7
228 8 Initial data 8
229 9 Initial data 9
230 10 Initial data 10
231 11 Secondary fourth change 11
232 Primary should have rejected all secondary changes
233 select * from test.t1 order by a;
234 a b
235 1 Primary third change 1
236 2 Primary first change 2
237 3 Initial data 3
238 4 Primary second change 4
239 5 Initial data 5
240 6 Initial data 6
241 7 Initial data 7
242 8 Initial data 8
243 9 Initial data 9
244 10 Initial data 10
245 ndb_conflict_trans_row_reject_count
246 4
247 ndb_conflict_trans_reject_count
248 2
249 ndb_epoch_delete_delete_count
250 0
251 start slave;
252 Warnings:
253 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
254 Secondary should have been realigned to Primary
255 select * from test.t1 order by a;
256 a b
257 1 Primary third change 1
258 2 Primary first change 2
259 3 Initial data 3
260 4 Primary second change 4
261 5 Initial data 5
262 6 Initial data 6
263 7 Initial data 7
264 8 Initial data 8
265 9 Initial data 9
266 10 Initial data 10
267 Show rollback of dependent transaction across different tables
268 --------------------------------------------------------------
269 stop slave;
270 Warnings:
271 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
272 update t1 set b="Primary fifth change 6" where a=6;
273 select * from test.t1 order by a;
274 a b
275 1 Primary third change 1
276 2 Primary first change 2
277 3 Initial data 3
278 4 Primary second change 4
279 5 Initial data 5
280 6 Primary fifth change 6
281 7 Initial data 7
282 8 Initial data 8
283 9 Initial data 9
284 10 Initial data 10
285 begin;
286 update t1 set b="Secondary fifth change 6" where a=6;
287 insert into t2 values (1, "Secondary fifth change 1");
288 insert into t2 values (2, "Secondary fifth change 2");
289 commit;
290 begin;
291 update t2 set b="Secondary sixth change 1" where a=2;
292 insert into t2 values (3, "Secondary sixth change 2");
293 commit;
294 select * from test.t1 order by a;
295 a b
296 1 Primary third change 1
297 2 Primary first change 2
298 3 Initial data 3
299 4 Primary second change 4
300 5 Initial data 5
301 6 Secondary fifth change 6
302 7 Initial data 7
303 8 Initial data 8
304 9 Initial data 9
305 10 Initial data 10
306 select * from test.t2 order by a;
307 a b
308 1 Secondary fifth change 1
309 2 Secondary sixth change 1
310 3 Secondary sixth change 2
311 Primary should have rejected all secondary changes
312 select * from test.t1 order by a;
313 a b
314 1 Primary third change 1
315 2 Primary first change 2
316 3 Initial data 3
317 4 Primary second change 4
318 5 Initial data 5
319 6 Primary fifth change 6
320 7 Initial data 7
321 8 Initial data 8
322 9 Initial data 9
323 10 Initial data 10
324 select * from test.t2 order by a;
325 a b
326 ndb_conflict_trans_row_reject_count
327 5
328 ndb_conflict_trans_reject_count
329 2
330 ndb_epoch_delete_delete_count
331 0
332 start slave;
333 Warnings:
334 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
335 Secondary should have been realigned to primary
336 select * from test.t1 order by a;
337 a b
338 1 Primary third change 1
339 2 Primary first change 2
340 3 Initial data 3
341 4 Primary second change 4
342 5 Initial data 5
343 6 Primary fifth change 6
344 7 Initial data 7
345 8 Initial data 8
346 9 Initial data 9
347 10 Initial data 10
348 select * from test.t2 order by a;
349 a b
350 Show that whole epoch is not rolled back
351 ----------------------------------------
352 stop slave;
353 Warnings:
354 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
355 update t1 set b="Primary is king" where a=10;
356 begin;
357 update t1 set b="Secondary is emperor" where a=10;
358 insert into t1 values (11, "Secondary is pleni-potentiary");
359 commit;
360 begin;
361 insert into t1 values (12, "Secondary ruled once");
362 insert into t1 values (13, "This history will not be lost");
363 insert into t1 values (14, "Look on my works ye mighty, and despair");
364 commit;
365 Primary should have rejected conflicting trans (emperor, pleni-potentiary)
366 but accepted unrelated trans (history)
367 select * from t1 order by a;
368 a b
369 1 Primary third change 1
370 2 Primary first change 2
371 3 Initial data 3
372 4 Primary second change 4
373 5 Initial data 5
374 6 Primary fifth change 6
375 7 Initial data 7
376 8 Initial data 8
377 9 Initial data 9
378 10 Primary is king
379 12 Secondary ruled once
380 13 This history will not be lost
381 14 Look on my works ye mighty, and despair
382 ndb_conflict_fn_epoch_trans
383 0
384 ndb_conflict_fn_epoch2_trans
385 1
386 ndb_conflict_trans_row_conflict_count
387 1
388 ndb_conflict_trans_row_reject_count
389 2
390 ndb_conflict_trans_reject_count
391 1
392 ndb_conflict_trans_detect_iter_count
393 1
394 ndb_conflict_trans_conflict_commit_count
395 1
396 ndb_epoch_delete_delete_count
397 0
398 start slave;
399 Warnings:
400 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
401 Secondary should be aligned with Primary
402 select * from t1 order by a;
403 a b
404 1 Primary third change 1
405 2 Primary first change 2
406 3 Initial data 3
407 4 Primary second change 4
408 5 Initial data 5
409 6 Primary fifth change 6
410 7 Initial data 7
411 8 Initial data 8
412 9 Initial data 9
413 10 Primary is king
414 12 Secondary ruled once
415 13 This history will not be lost
416 14 Look on my works ye mighty, and despair
417 Show that non-conflicting ancestors are not implicated
418 ------------------------------------------------------
419 stop slave;
420 Warnings:
421 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
422 update t1 set b="7 : Primary is king" where a=7;
423 Primary state
424 select * from test.t1 order by a;
425 a b
426 1 Primary third change 1
427 2 Primary first change 2
428 3 Initial data 3
429 4 Primary second change 4
430 5 Initial data 5
431 6 Primary fifth change 6
432 7 7 : Primary is king
433 8 Initial data 8
434 9 Initial data 9
435 10 Primary is king
436 12 Secondary ruled once
437 13 This history will not be lost
438 14 Look on my works ye mighty, and despair
439 begin;
440 update t1 set b="8 : Secondary innocent" where a=8;
441 update t1 set b="9 : Secondary innocent" where a=9;
442 commit;
443 Secondary with innocent
444 select * from test.t1 order by a;
445 a b
446 1 Primary third change 1
447 2 Primary first change 2
448 3 Initial data 3
449 4 Primary second change 4
450 5 Initial data 5
451 6 Primary fifth change 6
452 7 Initial data 7
453 8 8 : Secondary innocent
454 9 9 : Secondary innocent
455 10 Primary is king
456 12 Secondary ruled once
457 13 This history will not be lost
458 14 Look on my works ye mighty, and despair
459 begin;
460 update t1 set b="9 : Secondary guilty" where a=9;
461 update t1 set b="7 : Secondary guilty" where a=7;
462 commit;
463 Secondary with guilty overlaid
464 select * from test.t1 order by a;
465 a b
466 1 Primary third change 1
467 2 Primary first change 2
468 3 Initial data 3
469 4 Primary second change 4
470 5 Initial data 5
471 6 Primary fifth change 6
472 7 7 : Secondary guilty
473 8 8 : Secondary innocent
474 9 9 : Secondary guilty
475 10 Primary is king
476 12 Secondary ruled once
477 13 This history will not be lost
478 14 Look on my works ye mighty, and despair
479 Primary cluster should have rejected 'guilty' secondary transaction, but
480 accepted 'innocent' secondary transaction.
481 select * from test.t1 order by a;
482 a b
483 1 Primary third change 1
484 2 Primary first change 2
485 3 Initial data 3
486 4 Primary second change 4
487 5 Initial data 5
488 6 Primary fifth change 6
489 7 7 : Primary is king
490 8 8 : Secondary innocent
491 9 9 : Secondary innocent
492 10 Primary is king
493 12 Secondary ruled once
494 13 This history will not be lost
495 14 Look on my works ye mighty, and despair
496 ndb_conflict_fn_epoch_trans
497 0
498 ndb_conflict_fn_epoch2_trans
499 1
500 ndb_conflict_trans_row_conflict_count
501 1
502 ndb_conflict_trans_row_reject_count
503 2
504 ndb_conflict_trans_reject_count
505 1
506 ndb_conflict_trans_detect_iter_count
507 1
508 ndb_conflict_trans_conflict_commit_count
509 1
510 ndb_epoch_delete_delete_count
511 0
512 start slave;
513 Warnings:
514 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
515 Secondary cluster should be realigned with Primary
516 select * from test.t1 order by a;
517 a b
518 1 Primary third change 1
519 2 Primary first change 2
520 3 Initial data 3
521 4 Primary second change 4
522 5 Initial data 5
523 6 Primary fifth change 6
524 7 7 : Primary is king
525 8 8 : Secondary innocent
526 9 9 : Secondary innocent
527 10 Primary is king
528 12 Secondary ruled once
529 13 This history will not be lost
530 14 Look on my works ye mighty, and despair
531 Classic banking example
532 -----------------------
533 replace into mysql.ndb_replication values
534 ("test", "balances", 3, 7, "NDB$EPOCH2_TRANS()"),
535 ("test", "balances", 1, 7, "NDB$EPOCH2_TRANS()");
536 replace into mysql.ndb_replication values
537 ("test", "transactions", 3, 7, "NDB$EPOCH2_TRANS()"),
538 ("test", "transactions", 1, 7, "NDB$EPOCH2_TRANS()");
539 create table test.balances
540 (name varchar(100) primary key,
541 balance int) engine=ndb;
542 create table test.transactions$EX
543 (server_id int unsigned,
544 master_server_id int unsigned,
545 master_epoch bigint unsigned,
546 count int unsigned,
547 auto_key int not null,
548 from_name varchar(100) not null,
549 to_name varchar(100) not null,
550 detail varchar(100) not null,
551 primary key(server_id, master_server_id, master_epoch, count))
552 engine=ndb;
553 create table test.transactions
554 (auto_key int auto_increment,
555 from_name varchar(100),
556 to_name varchar(100),
557 detail varchar(100),
558 amount int,
559 primary key(auto_key, from_name, to_name, detail)) engine=ndb;
560 Initialise balances across both bank sites
561 insert into test.balances values
562 ("Larry", 100),
563 ("Employee-1", 0),
564 ("Employee-2", 0),
565 ("Yacht dealer", 0),
566 ("Newsagent", 0);
567 FLUSH LOGS;
568 Bank sites are disconnected
569 stop slave;
570 Warnings:
571 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
572 Larry buys a yacht using Primary bank site
573 begin;
574 insert into test.transactions (from_name, to_name, detail, amount)
575 values ("Larry", "Yacht dealer", "Yacht purchase", 50);
576 update test.balances set balance = balance - 50 where name = "Larry";
577 update test.balances set balance = balance + 50 where name = "Yacht dealer";
578 commit;
579 Show yacht transaction records
580 select * from test.transactions order by auto_key;
581 auto_key from_name to_name detail amount
582 1 Larry Yacht dealer Yacht purchase 50
583 select * from test.balances order by name;
584 name balance
585 Employee-1 0
586 Employee-2 0
587 Larry 50
588 Newsagent 0
589 Yacht dealer 50
590 Larry pays employees using Secondary bank site
591 begin;
592 insert into test.transactions (from_name, to_name, detail, amount)
593 values ("Larry", "Employee-1", "Payment to Employee-1", 1);
594 update test.balances set balance = balance - 1 where name = "Larry";
595 update test.balances set balance = balance + 1 where name = "Employee-1";
596 commit;
597 begin;
598 insert into test.transactions (from_name, to_name, detail, amount)
599 values ("Larry", "Employee-2", "Payment to Employee-2", 1);
600 update test.balances set balance = balance - 1 where name = "Larry";
601 update test.balances set balance = balance + 1 where name = "Employee-2";
602 commit;
603 Employee-2 buys yacht magazine using Secondary bank site
604 begin;
605 insert into test.transactions (from_name, to_name, detail, amount)
606 values ("Employee-2", "Newsagent", "Buy yacht magazine", 1);
607 update test.balances set balance = balance - 1 where name = "Employee-2";
608 update test.balances set balance = balance + 1 where name = "Newsagent";
609 commit;
610 Show employee transactions
611 select * from test.transactions order by auto_key;
612 auto_key from_name to_name detail amount
613 1 Larry Employee-1 Payment to Employee-1 1
614 2 Larry Employee-2 Payment to Employee-2 1
615 3 Employee-2 Newsagent Buy yacht magazine 1
616 select * from test.balances order by name;
617 name balance
618 Employee-1 1
619 Employee-2 0
620 Larry 98
621 Newsagent 1
622 Yacht dealer 0
623 Bank sites re-connected
624 start slave;
625 Warnings:
626 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
627 Records at Primary bank site
628 select * from test.transactions order by auto_key;
629 auto_key from_name to_name detail amount
630 1 Larry Yacht dealer Yacht purchase 50
631 select * from test.balances order by name;
632 name balance
633 Employee-1 0
634 Employee-2 0
635 Larry 50
636 Newsagent 0
637 Yacht dealer 50
638 Exceptions at Primary bank site
639 select server_id, master_server_id, auto_key, from_name, to_name, detail
640 from test.transactions$EX order by auto_key, from_name, to_name, detail;
641 server_id master_server_id auto_key from_name to_name detail
642 1 3 1 Larry Employee-1 Payment to Employee-1
643 1 3 2 Larry Employee-2 Payment to Employee-2
644 1 3 3 Employee-2 Newsagent Buy yacht magazine
645 Conflict handling activity at Primary bank site
646 Expect :
647 1 conflict from slave T1 on Larry's balance
648 1 conflict from slave T2 on Larry's balance
649 =2 row conflicts
650
651 3 (user) transactions rejected
652 9 rows rejected (3 per transaction)
653 Variability : # epoch transactions, # row conflicts detected
654 1-3 2-3
655 # detect_iter_count
656 1-3
657 We only check stable values
658 ndb_conflict_trans_row_reject_count
659 9
660 ndb_conflict_trans_reject_count
661 3
662 ndb_epoch_delete_delete_count
663 0
664 Records at Secondary bank site
665 select * from test.transactions order by auto_key;
666 auto_key from_name to_name detail amount
667 1 Larry Yacht dealer Yacht purchase 50
668 select * from test.balances order by name;
669 name balance
670 Employee-1 0
671 Employee-2 0
672 Larry 50
673 Newsagent 0
674 Yacht dealer 50
675 drop table test.balances;
676 drop table test.transactions;
677 drop table test.transactions$EX;
678 Test mixing transactional and non transactional
679 -----------------------------------------------
680 Remove old data from t1
681 delete from test.t1;
682 Define table with row-based epoch detection
683 replace into mysql.ndb_replication values
684 ("test", "t3", 3, 7, NULL),
685 ("test", "t3", 1, 7, 'NDB$EPOCH()');
686 create table t3 (a int primary key, b int) engine=ndb;
687 create table t4 (a int primary key, b int) engine=ndb;
688 create table t5 (a int primary key, b longtext) engine=ndb;
689 Insert some data
690 insert into test.t1 values
691 (1,1),
692 (2,2),
693 (3,3),
694 (4,4),
695 (5,5),
696 (6,6);
697 insert into test.t3 values
698 (11,11),
699 (12,12),
700 (13,13),
701 (14,14),
702 (15,15),
703 (16,16);
704 insert into test.t4 values
705 (21,21),
706 (22,22),
707 (23,23),
708 (24,24),
709 (25,25),
710 (26,26);
711 insert into test.t5 values
712 (1, REPEAT("B", 10000)),
713 (2, REPEAT("E", 10000)),
714 (3, REPEAT("A", 10000));
715 Allow to propagate
716 FLUSH LOGS;
717 Case 1 : Transactional detection affects row - based entries in same trans
718 stop slave;
719 Warnings:
720 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
721 update test.t1 set b=100 where a=1;
722 begin;
723 update test.t3 set b=1100 where a=11;
724 update test.t4 set b=2100 where a=21;
725 update test.t1 set b=1000 where a=1;
726 commit;
727 Show slave transaction effect
728 select * from test.t1 order by a;
729 a b
730 1 1000
731 2 2
732 3 3
733 4 4
734 5 5
735 6 6
736 select * from test.t3 order by a;
737 a b
738 11 1100
739 12 12
740 13 13
741 14 14
742 15 15
743 16 16
744 select * from test.t4 order by a;
745 a b
746 21 2100
747 22 22
748 23 23
749 24 24
750 25 25
751 26 26
752 Expect Primary to have rejected whole trans across 3 tables
753 select * from test.t1 order by a;
754 a b
755 1 100
756 2 2
757 3 3
758 4 4
759 5 5
760 6 6
761 select * from test.t3 order by a;
762 a b
763 11 11
764 12 12
765 13 13
766 14 14
767 15 15
768 16 16
769 select * from test.t4 order by a;
770 a b
771 21 21
772 22 22
773 23 23
774 24 24
775 25 25
776 26 26
777 Expect 1 transaction rejected, 3 rows rejected
778 1 conflict row, 1 epoch, 1 iteration
779 ndb_conflict_fn_epoch_trans
780 0
781 ndb_conflict_fn_epoch2_trans
782 1
783 ndb_conflict_trans_row_conflict_count
784 1
785 ndb_conflict_trans_row_reject_count
786 3
787 ndb_conflict_trans_reject_count
788 1
789 ndb_conflict_trans_detect_iter_count
790 1
791 ndb_conflict_trans_conflict_commit_count
792 1
793 ndb_epoch_delete_delete_count
794 0
795 Now restart rep to Secondary, and check realignment
796 start slave;
797 Warnings:
798 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
799 select * from test.t1 order by a;
800 a b
801 1 100
802 2 2
803 3 3
804 4 4
805 5 5
806 6 6
807 select * from test.t3 order by a;
808 a b
809 11 11
810 12 12
811 13 13
812 14 14
813 15 15
814 16 16
815 select * from test.t4 order by a;
816 a b
817 21 21
818 22 22
819 23 23
820 24 24
821 25 25
822 26 26
823 Case 2 : Row based detection does not affect other transaction entries
824 stop slave;
825 Warnings:
826 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
827 update test.t3 set b=1200 where a=12;
828 begin;
829 update test.t3 set b=1201 where a=12;
830 update test.t4 set b=2200 where a=22;
831 update test.t1 set b=2000 where a=2;
832 commit;
833 Show effect of transaction on Secondary
834 select * from test.t1 order by a;
835 a b
836 1 100
837 2 2000
838 3 3
839 4 4
840 5 5
841 6 6
842 select * from test.t3 order by a;
843 a b
844 11 11
845 12 1201
846 13 13
847 14 14
848 15 15
849 16 16
850 select * from test.t4 order by a;
851 a b
852 21 21
853 22 2200
854 23 23
855 24 24
856 25 25
857 26 26
858 Show effect of transaction on Primary
859 Only t3 should have been reverted
860 select * from test.t1 order by a;
861 a b
862 1 100
863 2 2000
864 3 3
865 4 4
866 5 5
867 6 6
868 select * from test.t3 order by a;
869 a b
870 11 11
871 12 1200
872 13 13
873 14 14
874 15 15
875 16 16
876 select * from test.t4 order by a;
877 a b
878 21 21
879 22 2200
880 23 23
881 24 24
882 25 25
883 26 26
884 Expect all counters to be zero
885 ndb_conflict_fn_epoch_trans
886 0
887 ndb_conflict_fn_epoch2_trans
888 0
889 ndb_conflict_trans_row_conflict_count
890 0
891 ndb_conflict_trans_row_reject_count
892 0
893 ndb_conflict_trans_reject_count
894 0
895 ndb_conflict_trans_detect_iter_count
896 0
897 ndb_conflict_trans_conflict_commit_count
898 0
899 ndb_epoch_delete_delete_count
900 0
901 Show effect of transaction on Secondary
902 start slave;
903 Warnings:
904 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
905 select * from test.t1 order by a;
906 a b
907 1 100
908 2 2000
909 3 3
910 4 4
911 5 5
912 6 6
913 select * from test.t3 order by a;
914 a b
915 11 11
916 12 1200
917 13 13
918 14 14
919 15 15
920 16 16
921 select * from test.t4 order by a;
922 a b
923 21 21
924 22 2200
925 23 23
926 24 24
927 25 25
928 26 26
929 flush logs;
930 Case 3 : Check behaviour where table with Blob is implicated
931 in transactional conflict. Should result in Slave
932 stopping with an error.
933 STOP SLAVE;
934 Warnings:
935 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
936 Setup warning suppression
937 begin;
938 update t1 set b= 11 where a=1;
939 commit;
940 begin;
941 update t1 set b= 111 where a=1;
942 update t1 set b= 222 where a=2;
943 update t5 set b= REPEAT("T", 10000) where a=3;
944 commit;
945 Show effect of transaction on Secondary
946 select * from test.t1 order by a;
947 a b
948 1 111
949 2 222
950 3 3
951 4 4
952 5 5
953 6 6
954 select left(b,1), length(b) from test.t5 order by a;
955 left(b,1) length(b)
956 B 10000
957 E 10000
958 T 10000
959 Check that Primary Slave has stopped
960 include/wait_for_slave_sql_error.inc [errno=1296]
961 Restart Primary Slave
962 set global sql_replica_skip_counter=1;
963 START SLAVE;
964 Warnings:
965 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
966 Restart Secondary Slave
967 START SLAVE;
968 Warnings:
969 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
970 flush logs;
971 drop table test.t3;
972 drop table test.t4;
973 drop table test.t5;
974 Fix misalignment caused above
975 Makes following result sets look nicer
976 update test.t1 set b=2 where a=2;
977 Show a simple row-level delete-delete conflict
978 PRIMARY:
979 insert into test.t1 values (77, "A good year");
980 select * from test.t1 order by a;
981 a b
982 1 11
983 2 2
984 3 3
985 4 4
986 5 5
987 6 6
988 77 A good year
989 SECONDARY:
990 stop slave;
991 Warnings:
992 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
993 PRIMARY:
994 Primary Delete
995 delete from test.t1 where a=77;
996 Show Primary contents
997 select * from test.t1 order by a;
998 a b
999 1 11
1000 2 2
1001 3 3
1002 4 4
1003 5 5
1004 6 6
1005 SECONDARY:
1006 select * from test.t1 order by a;
1007 a b
1008 1 11
1009 2 2
1010 3 3
1011 4 4
1012 5 5
1013 6 6
1014 77 A good year
1015 Secondary Delete->Insert in separate transactions
1016 and potentially different epochs
1017 Delete part with related transaction op
1018 begin;
1019 delete from test.t1 where a=77;
1020 insert into test.t1 values (73, "A superb year");
1021 commit;
1022 Unrelated transaction from Secondary
1023 begin;
1024 insert into test.t1 values (84, "A good vintage");
1025 commit;
1026 Optionally flush logs
1027 Following Insert from Secondary - potentially in different epoch
1028 begin;
1029 insert into test.t1 values (77, "Exceptional");
1030 insert into test.t1 values (80, "Poor");
1031 commit;
1032 Show Secondary contents
1033 select * from test.t1 order by a;
1034 a b
1035 1 11
1036 2 2
1037 3 3
1038 4 4
1039 5 5
1040 6 6
1041 73 A superb year
1042 77 Exceptional
1043 80 Poor
1044 84 A good vintage
1045 Allow propagation to Primary
1046 PRIMARY:
1047 Expect:
1048 - NDB$EPOCH_TRANS :
1049 - Will not detect delete-delete conflict, so first
1050 Secondary transaction will apply row 73.
1051 - Unrelated transaction from Secondary (84) is present
1052 - Following insert on delete-delete conflict row (77) is not in conflict,
1053 so both it and the related row from the same transaction (80) are present
1054 This applies whether the following insert is in the same or a different
1055 epoch
1056 Divergence:
1057 - Primary will have 73,84,77,80
1058 - Secondary will have 73,84,80, no 77
1059
1060 - NDB$EPOCH_TRANS2 :
1061 - Will detect delete-delete conflict, so first
1062 Secondary transaction will not apply (no row 73)
1063 77 and 73 will be refreshed (DEL)
1064 - Unrelated transaction from Secondary (84) is present and applied
1065 84 will be reflected (INS)
1066 - Following insert on delete-delete conflict row (77) will be found in
1067 conflict iff this transaction is in the same epoch as the first
1068 conflicting transaction. If it is in a different epoch then it will
1069 not be found in conflict, as there will be no state available to
1070 use.
1071 - If it is in-conflict, then 77 and 80 will be refreshed (DEL)
1072 Result on both should be :
1073 84
1074 - If it is not in-conflict, then 77 and 80 will be reflected (INS)
1075 Result on both should be :
1076 84, 77, 80
1077 No divergence
1078 set @primary_ok:=
1079 ((@epoch_trans XOR
1080 @epoch_trans2_same_epoch) XOR
1081 @epoch_trans2_diff_epoch);
1082 select @primary_ok;
1083 @primary_ok
1084 1
1085 EPOCH2_TRANS results are dependent on epoch boundaries
1086 Now sync with Secondary to show outcomes
1087 start slave;
1088 Warnings:
1089 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1090 SECONDARY:
1091
1092 set @secondary_ok:=
1093 ((@epoch_trans XOR
1094 @epoch_trans2_same_epoch) XOR
1095 @epoch_trans2_diff_epoch);
1096 select @secondary_ok;
1097 @secondary_ok
1098 1
1099 EPOCH2_TRANS results are dependent on epoch boundaries
1100 PRIMARY
1101 Cleanup
1102 delete from test.t1 where a != 6;
1103 PRIMARY
1104 Transactional Delete testcase
1105 STOP slave on SECONDARY
1106 stop slave;
1107 Warnings:
1108 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1109 PRIMARY
1110 Primary updates row with pk 6
1111 update test.t1 set b="Pentland" where a=6;
1112 PRIMARY content
1113 Row 6 updated
1114 select * from test.t1 order by a;
1115 a b
1116 6 Pentland
1117 SECONDARY
1118 Transaction deleting pk 6, inserting pk 7
1119 begin;
1120 delete from test.t1 where a = 6;
1121 insert into test.t1 values (7, "Mendip");
1122 commit;
1123 Optionally flush logs
1124 Transaction deleting pk 7, inserting pk 8
1125 begin;
1126 delete from test.t1 where a = 7;
1127 insert into test.t1 values (8, "Ochil");
1128 commit;
1129 SECONDARY content
1130 Rows 6 + 7 deleted. Row 8 present.
1131 select * from test.t1 order by a;
1132 a b
1133 8 Ochil
1134 Sync SECONDARY -> PRIMARY
1135 Expected Behaviour
1136 EPOCH_TRANS
1137
1138 1. Conflict between Primary update to 6 + Secondary
1139 [Delete of 6 + Insert of 7] is detected by Primary
1140 and transaction is not applied at the Primary.
1141 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
1142 epoch updated.
1143
1144 2. Following Secondary transaction, [delete of 7
1145 + insert of 8] behaviour depends on whether it
1146 arrives in the same or a different epoch.
1147 Same epoch :
1148 Will conflict with refreshed row 7, and so will
1149 not be applied. Row 8 will be refreshed (DEL)
1150 Different epoch :
1151 Will not conflict with Not-present row 7, and so
1152 will be applied.
1153
1154 3. On the Secondary, the observed sequence will be :
1155 - Update 6 from PRIMARY : (Idempotent insert)
1156 - Refresh (INS) 6 from PRIMARY
1157 - Refresh (DEL) 7 from PRIMARY
1158 Same epoch :
1159 - Refresh (DEL) 8 from PRIMARY
1160 Different epoch :
1161 - Nothing
1162
1163 Results (no divergence) :
1164 Same epoch :
1165 PRIMARY : 6
1166 SECONDARY : 6
1167 Diff epoch :
1168 PRIMARY : 6,8
1169 SECONDARY : 6,8
1170
1171
1172 EPOCH_TRANS2
1173
1174 1. Conflict between Primary update to 6 + Secondary
1175 [Delete of 6 + Insert of 7] is detected by Primary
1176 and transaction is not applied at the Primary.
1177 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
1178 epoch updated.
1179
1180 2. Following Secondary transaction [delete of 7 +
1181 insert of 8] will conflict with the first
1182 transaction as EPOCH_TRANS2 does not allow
1183 a delete of an already deleted row.
1184 Row 7 will be refreshed (DEL) and row 8 will be
1185 refreshed (DEL)
1186
1187 3. On the Secondary, the observed sequence will be :
1188 - Update 6 from PRIMARY : (Idempotent insert)
1189 - Refresh (INS) 6 from PRIMARY
1190 - Refresh (DEL) 7 from PRIMARY
1191 - [Refresh (DEL) 7 from PRIMARY] - diff epoch
1192 - Refresh (DEL) 8 from PRIMARY
1193
1194 Results (no divergence) :
1195 PRIMARY : 6
1196 SECONDARY : 6
1197
1198 PRIMARY content
1199 set @primary_ok:=
1200 ((@epoch_trans_same_epoch XOR
1201 @epoch_trans_diff_epoch) XOR
1202 @epoch2_trans);
1203 select @primary_ok;
1204 @primary_ok
1205 1
1206 select * from test.t1 order by a;
1207 a b
1208 6 Pentland
1209 Restart SECONDARY Slave
1210 START SLAVE;
1211 Warnings:
1212 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1213 Sync PRIMARY -> SECONDARY
1214 SECONDARY content
1215 set @secondary_ok:=
1216 ((@epoch_trans_same_epoch XOR
1217 @epoch_trans_diff_epoch) XOR
1218 @epoch2_trans);
1219 select @secondary_ok;
1220 @secondary_ok
1221 1
1222 select * from test.t1 order by a;
1223 a b
1224 6 Pentland
1225 STABILISE
1226 drop table mysql.ndb_replication;
1227 drop table test.t1;
1228 drop table test.t2;
1229 flush logs;
1230 stop slave;
1231 Warnings:
1232 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1233 reset slave;
1234 Warnings:
1235 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
1236 include/rpl_end.inc
1237 Next we have Cluster A as Secondary, Cluster B as Primary
1238 SET GLOBAL ndb_applier_conflict_role="NONE";
1239 SET GLOBAL ndb_applier_conflict_role="SECONDARY";
1240 SET GLOBAL ndb_applier_conflict_role="NONE";
1241 SET GLOBAL ndb_applier_conflict_role="PRIMARY";
1242 We set a special control var to allow the generic scripts
1243 to handle this topology
1244 Remove some connections which can't be setup twice (ugly)
1245 include/master-slave.inc [rpl_server_count=2]
1246 Warnings:
1247 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
1248 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.
1249 [connection master]
1250 Setup circular replication
1251 Setup ndb_replication and t1 exceptions table
1252 Populate ndb_replication table as necessary
1253 replace into mysql.ndb_replication values
1254 ("test", "t1", 1, 7, "NDB$EPOCH2_TRANS()"),
1255 ("test", "t1", 3, 7, "NDB$EPOCH2_TRANS()");
1256 replace into mysql.ndb_replication values
1257 ("test", "t2", 1, 7, "NDB$EPOCH2_TRANS()"),
1258 ("test", "t2", 3, 7, "NDB$EPOCH2_TRANS()");
1259 create table test.t1 (
1260 a int primary key,
1261 b varchar(2000)) engine=ndb;
1262 create table test.t2 (
1263 a int primary key,
1264 b varchar(2000)) engine=ndb;
1265 Add some data
1266 insert into test.t1 values
1267 (1, "Initial data 1"),
1268 (2, "Initial data 2"),
1269 (3, "Initial data 3"),
1270 (4, "Initial data 4"),
1271 (5, "Initial data 5"),
1272 (6, "Initial data 6"),
1273 (7, "Initial data 7"),
1274 (8, "Initial data 8"),
1275 (9, "Initial data 9"),
1276 (10, "Initial data 10");
1277 Show basic row-level conflict detection
1278 ---------------------------------------
1279 stop slave;
1280 Warnings:
1281 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1282 update t1 set b="Primary first change 2" where a=2;
1283 select * from test.t1 order by a;
1284 a b
1285 1 Initial data 1
1286 2 Primary first change 2
1287 3 Initial data 3
1288 4 Initial data 4
1289 5 Initial data 5
1290 6 Initial data 6
1291 7 Initial data 7
1292 8 Initial data 8
1293 9 Initial data 9
1294 10 Initial data 10
1295 update t1 set b="Secondary first change 2" where a=2;
1296 select * from test.t1 order by a;
1297 a b
1298 1 Initial data 1
1299 2 Secondary first change 2
1300 3 Initial data 3
1301 4 Initial data 4
1302 5 Initial data 5
1303 6 Initial data 6
1304 7 Initial data 7
1305 8 Initial data 8
1306 9 Initial data 9
1307 10 Initial data 10
1308 Primary should have rejected change from Secondary, keeping its value
1309 select * from t1 order by a;
1310 a b
1311 1 Initial data 1
1312 2 Primary first change 2
1313 3 Initial data 3
1314 4 Initial data 4
1315 5 Initial data 5
1316 6 Initial data 6
1317 7 Initial data 7
1318 8 Initial data 8
1319 9 Initial data 9
1320 10 Initial data 10
1321 ndb_conflict_fn_epoch_trans
1322 NULL
1323 ndb_conflict_fn_epoch2_trans
1324 NULL
1325 ndb_conflict_trans_row_conflict_count
1326 NULL
1327 ndb_conflict_trans_row_reject_count
1328 NULL
1329 ndb_conflict_trans_reject_count
1330 NULL
1331 ndb_conflict_trans_detect_iter_count
1332 NULL
1333 ndb_conflict_trans_conflict_commit_count
1334 NULL
1335 ndb_epoch_delete_delete_count
1336 NULL
1337 start slave;
1338 Warnings:
1339 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1340 Secondary should have been realigned to Primary
1341 select * from t1 order by a;
1342 a b
1343 1 Initial data 1
1344 2 Primary first change 2
1345 3 Initial data 3
1346 4 Initial data 4
1347 5 Initial data 5
1348 6 Initial data 6
1349 7 Initial data 7
1350 8 Initial data 8
1351 9 Initial data 9
1352 10 Initial data 10
1353 Show rollback of whole secondary transaction
1354 --------------------------------------------
1355 stop slave;
1356 Warnings:
1357 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1358 update t1 set b="Primary second change 4" where a=4;
1359 select * from test.t1 order by a;
1360 a b
1361 1 Initial data 1
1362 2 Primary first change 2
1363 3 Initial data 3
1364 4 Primary second change 4
1365 5 Initial data 5
1366 6 Initial data 6
1367 7 Initial data 7
1368 8 Initial data 8
1369 9 Initial data 9
1370 10 Initial data 10
1371 begin;
1372 update t1 set b="Secondary second change 4" where a=4;
1373 update t1 set b="Secondary second change 5" where a=5;
1374 commit;
1375 select * from test.t1 order by a;
1376 a b
1377 1 Initial data 1
1378 2 Primary first change 2
1379 3 Initial data 3
1380 4 Secondary second change 4
1381 5 Secondary second change 5
1382 6 Initial data 6
1383 7 Initial data 7
1384 8 Initial data 8
1385 9 Initial data 9
1386 10 Initial data 10
1387 Primary should have rejected secondary changes on both rows
1388 select * from test.t1 order by a;
1389 a b
1390 1 Initial data 1
1391 2 Primary first change 2
1392 3 Initial data 3
1393 4 Primary second change 4
1394 5 Initial data 5
1395 6 Initial data 6
1396 7 Initial data 7
1397 8 Initial data 8
1398 9 Initial data 9
1399 10 Initial data 10
1400 ndb_conflict_fn_epoch_trans
1401 0
1402 ndb_conflict_fn_epoch2_trans
1403 1
1404 ndb_conflict_trans_row_conflict_count
1405 1
1406 ndb_conflict_trans_row_reject_count
1407 2
1408 ndb_conflict_trans_reject_count
1409 1
1410 ndb_conflict_trans_detect_iter_count
1411 1
1412 ndb_conflict_trans_conflict_commit_count
1413 1
1414 ndb_epoch_delete_delete_count
1415 0
1416 start slave;
1417 Warnings:
1418 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1419 Secondary should have been realigned to Primary
1420 select * from test.t1 order by a;
1421 a b
1422 1 Initial data 1
1423 2 Primary first change 2
1424 3 Initial data 3
1425 4 Primary second change 4
1426 5 Initial data 5
1427 6 Initial data 6
1428 7 Initial data 7
1429 8 Initial data 8
1430 9 Initial data 9
1431 10 Initial data 10
1432 Show rollback of dependent transaction as well
1433 ----------------------------------------------
1434 stop slave;
1435 Warnings:
1436 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1437 update t1 set b="Primary third change 1" where a=1;
1438 select * from test.t1 order by a;
1439 a b
1440 1 Primary third change 1
1441 2 Primary first change 2
1442 3 Initial data 3
1443 4 Primary second change 4
1444 5 Initial data 5
1445 6 Initial data 6
1446 7 Initial data 7
1447 8 Initial data 8
1448 9 Initial data 9
1449 10 Initial data 10
1450 begin;
1451 update t1 set b="Secondary third change 3" where a=3;
1452 update t1 set b="Secondary third change 1" where a=1;
1453 commit;
1454 begin;
1455 update t1 set b="Secondary fourth change 3" where a=3;
1456 insert into t1 values (11,"Secondary fourth change 11");
1457 commit;
1458 select * from test.t1 order by a;
1459 a b
1460 1 Secondary third change 1
1461 2 Primary first change 2
1462 3 Secondary fourth change 3
1463 4 Primary second change 4
1464 5 Initial data 5
1465 6 Initial data 6
1466 7 Initial data 7
1467 8 Initial data 8
1468 9 Initial data 9
1469 10 Initial data 10
1470 11 Secondary fourth change 11
1471 Primary should have rejected all secondary changes
1472 select * from test.t1 order by a;
1473 a b
1474 1 Primary third change 1
1475 2 Primary first change 2
1476 3 Initial data 3
1477 4 Primary second change 4
1478 5 Initial data 5
1479 6 Initial data 6
1480 7 Initial data 7
1481 8 Initial data 8
1482 9 Initial data 9
1483 10 Initial data 10
1484 ndb_conflict_trans_row_reject_count
1485 4
1486 ndb_conflict_trans_reject_count
1487 2
1488 ndb_epoch_delete_delete_count
1489 0
1490 start slave;
1491 Warnings:
1492 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1493 Secondary should have been realigned to Primary
1494 select * from test.t1 order by a;
1495 a b
1496 1 Primary third change 1
1497 2 Primary first change 2
1498 3 Initial data 3
1499 4 Primary second change 4
1500 5 Initial data 5
1501 6 Initial data 6
1502 7 Initial data 7
1503 8 Initial data 8
1504 9 Initial data 9
1505 10 Initial data 10
1506 Show rollback of dependent transaction across different tables
1507 --------------------------------------------------------------
1508 stop slave;
1509 Warnings:
1510 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1511 update t1 set b="Primary fifth change 6" where a=6;
1512 select * from test.t1 order by a;
1513 a b
1514 1 Primary third change 1
1515 2 Primary first change 2
1516 3 Initial data 3
1517 4 Primary second change 4
1518 5 Initial data 5
1519 6 Primary fifth change 6
1520 7 Initial data 7
1521 8 Initial data 8
1522 9 Initial data 9
1523 10 Initial data 10
1524 begin;
1525 update t1 set b="Secondary fifth change 6" where a=6;
1526 insert into t2 values (1, "Secondary fifth change 1");
1527 insert into t2 values (2, "Secondary fifth change 2");
1528 commit;
1529 begin;
1530 update t2 set b="Secondary sixth change 1" where a=2;
1531 insert into t2 values (3, "Secondary sixth change 2");
1532 commit;
1533 select * from test.t1 order by a;
1534 a b
1535 1 Primary third change 1
1536 2 Primary first change 2
1537 3 Initial data 3
1538 4 Primary second change 4
1539 5 Initial data 5
1540 6 Secondary fifth change 6
1541 7 Initial data 7
1542 8 Initial data 8
1543 9 Initial data 9
1544 10 Initial data 10
1545 select * from test.t2 order by a;
1546 a b
1547 1 Secondary fifth change 1
1548 2 Secondary sixth change 1
1549 3 Secondary sixth change 2
1550 Primary should have rejected all secondary changes
1551 select * from test.t1 order by a;
1552 a b
1553 1 Primary third change 1
1554 2 Primary first change 2
1555 3 Initial data 3
1556 4 Primary second change 4
1557 5 Initial data 5
1558 6 Primary fifth change 6
1559 7 Initial data 7
1560 8 Initial data 8
1561 9 Initial data 9
1562 10 Initial data 10
1563 select * from test.t2 order by a;
1564 a b
1565 ndb_conflict_trans_row_reject_count
1566 5
1567 ndb_conflict_trans_reject_count
1568 2
1569 ndb_epoch_delete_delete_count
1570 0
1571 start slave;
1572 Warnings:
1573 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1574 Secondary should have been realigned to primary
1575 select * from test.t1 order by a;
1576 a b
1577 1 Primary third change 1
1578 2 Primary first change 2
1579 3 Initial data 3
1580 4 Primary second change 4
1581 5 Initial data 5
1582 6 Primary fifth change 6
1583 7 Initial data 7
1584 8 Initial data 8
1585 9 Initial data 9
1586 10 Initial data 10
1587 select * from test.t2 order by a;
1588 a b
1589 Show that whole epoch is not rolled back
1590 ----------------------------------------
1591 stop slave;
1592 Warnings:
1593 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1594 update t1 set b="Primary is king" where a=10;
1595 begin;
1596 update t1 set b="Secondary is emperor" where a=10;
1597 insert into t1 values (11, "Secondary is pleni-potentiary");
1598 commit;
1599 begin;
1600 insert into t1 values (12, "Secondary ruled once");
1601 insert into t1 values (13, "This history will not be lost");
1602 insert into t1 values (14, "Look on my works ye mighty, and despair");
1603 commit;
1604 Primary should have rejected conflicting trans (emperor, pleni-potentiary)
1605 but accepted unrelated trans (history)
1606 select * from t1 order by a;
1607 a b
1608 1 Primary third change 1
1609 2 Primary first change 2
1610 3 Initial data 3
1611 4 Primary second change 4
1612 5 Initial data 5
1613 6 Primary fifth change 6
1614 7 Initial data 7
1615 8 Initial data 8
1616 9 Initial data 9
1617 10 Primary is king
1618 12 Secondary ruled once
1619 13 This history will not be lost
1620 14 Look on my works ye mighty, and despair
1621 ndb_conflict_fn_epoch_trans
1622 0
1623 ndb_conflict_fn_epoch2_trans
1624 1
1625 ndb_conflict_trans_row_conflict_count
1626 1
1627 ndb_conflict_trans_row_reject_count
1628 2
1629 ndb_conflict_trans_reject_count
1630 1
1631 ndb_conflict_trans_detect_iter_count
1632 1
1633 ndb_conflict_trans_conflict_commit_count
1634 1
1635 ndb_epoch_delete_delete_count
1636 0
1637 start slave;
1638 Warnings:
1639 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1640 Secondary should be aligned with Primary
1641 select * from t1 order by a;
1642 a b
1643 1 Primary third change 1
1644 2 Primary first change 2
1645 3 Initial data 3
1646 4 Primary second change 4
1647 5 Initial data 5
1648 6 Primary fifth change 6
1649 7 Initial data 7
1650 8 Initial data 8
1651 9 Initial data 9
1652 10 Primary is king
1653 12 Secondary ruled once
1654 13 This history will not be lost
1655 14 Look on my works ye mighty, and despair
1656 Show that non-conflicting ancestors are not implicated
1657 ------------------------------------------------------
1658 stop slave;
1659 Warnings:
1660 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1661 update t1 set b="7 : Primary is king" where a=7;
1662 Primary state
1663 select * from test.t1 order by a;
1664 a b
1665 1 Primary third change 1
1666 2 Primary first change 2
1667 3 Initial data 3
1668 4 Primary second change 4
1669 5 Initial data 5
1670 6 Primary fifth change 6
1671 7 7 : Primary is king
1672 8 Initial data 8
1673 9 Initial data 9
1674 10 Primary is king
1675 12 Secondary ruled once
1676 13 This history will not be lost
1677 14 Look on my works ye mighty, and despair
1678 begin;
1679 update t1 set b="8 : Secondary innocent" where a=8;
1680 update t1 set b="9 : Secondary innocent" where a=9;
1681 commit;
1682 Secondary with innocent
1683 select * from test.t1 order by a;
1684 a b
1685 1 Primary third change 1
1686 2 Primary first change 2
1687 3 Initial data 3
1688 4 Primary second change 4
1689 5 Initial data 5
1690 6 Primary fifth change 6
1691 7 Initial data 7
1692 8 8 : Secondary innocent
1693 9 9 : Secondary innocent
1694 10 Primary is king
1695 12 Secondary ruled once
1696 13 This history will not be lost
1697 14 Look on my works ye mighty, and despair
1698 begin;
1699 update t1 set b="9 : Secondary guilty" where a=9;
1700 update t1 set b="7 : Secondary guilty" where a=7;
1701 commit;
1702 Secondary with guilty overlaid
1703 select * from test.t1 order by a;
1704 a b
1705 1 Primary third change 1
1706 2 Primary first change 2
1707 3 Initial data 3
1708 4 Primary second change 4
1709 5 Initial data 5
1710 6 Primary fifth change 6
1711 7 7 : Secondary guilty
1712 8 8 : Secondary innocent
1713 9 9 : Secondary guilty
1714 10 Primary is king
1715 12 Secondary ruled once
1716 13 This history will not be lost
1717 14 Look on my works ye mighty, and despair
1718 Primary cluster should have rejected 'guilty' secondary transaction, but
1719 accepted 'innocent' secondary transaction.
1720 select * from test.t1 order by a;
1721 a b
1722 1 Primary third change 1
1723 2 Primary first change 2
1724 3 Initial data 3
1725 4 Primary second change 4
1726 5 Initial data 5
1727 6 Primary fifth change 6
1728 7 7 : Primary is king
1729 8 8 : Secondary innocent
1730 9 9 : Secondary innocent
1731 10 Primary is king
1732 12 Secondary ruled once
1733 13 This history will not be lost
1734 14 Look on my works ye mighty, and despair
1735 ndb_conflict_fn_epoch_trans
1736 0
1737 ndb_conflict_fn_epoch2_trans
1738 1
1739 ndb_conflict_trans_row_conflict_count
1740 1
1741 ndb_conflict_trans_row_reject_count
1742 2
1743 ndb_conflict_trans_reject_count
1744 1
1745 ndb_conflict_trans_detect_iter_count
1746 1
1747 ndb_conflict_trans_conflict_commit_count
1748 1
1749 ndb_epoch_delete_delete_count
1750 0
1751 start slave;
1752 Warnings:
1753 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1754 Secondary cluster should be realigned with Primary
1755 select * from test.t1 order by a;
1756 a b
1757 1 Primary third change 1
1758 2 Primary first change 2
1759 3 Initial data 3
1760 4 Primary second change 4
1761 5 Initial data 5
1762 6 Primary fifth change 6
1763 7 7 : Primary is king
1764 8 8 : Secondary innocent
1765 9 9 : Secondary innocent
1766 10 Primary is king
1767 12 Secondary ruled once
1768 13 This history will not be lost
1769 14 Look on my works ye mighty, and despair
1770 Classic banking example
1771 -----------------------
1772 replace into mysql.ndb_replication values
1773 ("test", "balances", 1, 7, "NDB$EPOCH2_TRANS()"),
1774 ("test", "balances", 3, 7, "NDB$EPOCH2_TRANS()");
1775 replace into mysql.ndb_replication values
1776 ("test", "transactions", 1, 7, "NDB$EPOCH2_TRANS()"),
1777 ("test", "transactions", 3, 7, "NDB$EPOCH2_TRANS()");
1778 create table test.balances
1779 (name varchar(100) primary key,
1780 balance int) engine=ndb;
1781 create table test.transactions$EX
1782 (server_id int unsigned,
1783 master_server_id int unsigned,
1784 master_epoch bigint unsigned,
1785 count int unsigned,
1786 auto_key int not null,
1787 from_name varchar(100) not null,
1788 to_name varchar(100) not null,
1789 detail varchar(100) not null,
1790 primary key(server_id, master_server_id, master_epoch, count))
1791 engine=ndb;
1792 create table test.transactions
1793 (auto_key int auto_increment,
1794 from_name varchar(100),
1795 to_name varchar(100),
1796 detail varchar(100),
1797 amount int,
1798 primary key(auto_key, from_name, to_name, detail)) engine=ndb;
1799 Initialise balances across both bank sites
1800 insert into test.balances values
1801 ("Larry", 100),
1802 ("Employee-1", 0),
1803 ("Employee-2", 0),
1804 ("Yacht dealer", 0),
1805 ("Newsagent", 0);
1806 FLUSH LOGS;
1807 Bank sites are disconnected
1808 stop slave;
1809 Warnings:
1810 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1811 Larry buys a yacht using Primary bank site
1812 begin;
1813 insert into test.transactions (from_name, to_name, detail, amount)
1814 values ("Larry", "Yacht dealer", "Yacht purchase", 50);
1815 update test.balances set balance = balance - 50 where name = "Larry";
1816 update test.balances set balance = balance + 50 where name = "Yacht dealer";
1817 commit;
1818 Show yacht transaction records
1819 select * from test.transactions order by auto_key;
1820 auto_key from_name to_name detail amount
1821 1 Larry Yacht dealer Yacht purchase 50
1822 select * from test.balances order by name;
1823 name balance
1824 Employee-1 0
1825 Employee-2 0
1826 Larry 50
1827 Newsagent 0
1828 Yacht dealer 50
1829 Larry pays employees using Secondary bank site
1830 begin;
1831 insert into test.transactions (from_name, to_name, detail, amount)
1832 values ("Larry", "Employee-1", "Payment to Employee-1", 1);
1833 update test.balances set balance = balance - 1 where name = "Larry";
1834 update test.balances set balance = balance + 1 where name = "Employee-1";
1835 commit;
1836 begin;
1837 insert into test.transactions (from_name, to_name, detail, amount)
1838 values ("Larry", "Employee-2", "Payment to Employee-2", 1);
1839 update test.balances set balance = balance - 1 where name = "Larry";
1840 update test.balances set balance = balance + 1 where name = "Employee-2";
1841 commit;
1842 Employee-2 buys yacht magazine using Secondary bank site
1843 begin;
1844 insert into test.transactions (from_name, to_name, detail, amount)
1845 values ("Employee-2", "Newsagent", "Buy yacht magazine", 1);
1846 update test.balances set balance = balance - 1 where name = "Employee-2";
1847 update test.balances set balance = balance + 1 where name = "Newsagent";
1848 commit;
1849 Show employee transactions
1850 select * from test.transactions order by auto_key;
1851 auto_key from_name to_name detail amount
1852 1 Larry Employee-1 Payment to Employee-1 1
1853 2 Larry Employee-2 Payment to Employee-2 1
1854 3 Employee-2 Newsagent Buy yacht magazine 1
1855 select * from test.balances order by name;
1856 name balance
1857 Employee-1 1
1858 Employee-2 0
1859 Larry 98
1860 Newsagent 1
1861 Yacht dealer 0
1862 Bank sites re-connected
1863 start slave;
1864 Warnings:
1865 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1866 Records at Primary bank site
1867 select * from test.transactions order by auto_key;
1868 auto_key from_name to_name detail amount
1869 1 Larry Yacht dealer Yacht purchase 50
1870 select * from test.balances order by name;
1871 name balance
1872 Employee-1 0
1873 Employee-2 0
1874 Larry 50
1875 Newsagent 0
1876 Yacht dealer 50
1877 Exceptions at Primary bank site
1878 select server_id, master_server_id, auto_key, from_name, to_name, detail
1879 from test.transactions$EX order by auto_key, from_name, to_name, detail;
1880 server_id master_server_id auto_key from_name to_name detail
1881 3 1 1 Larry Employee-1 Payment to Employee-1
1882 3 1 2 Larry Employee-2 Payment to Employee-2
1883 3 1 3 Employee-2 Newsagent Buy yacht magazine
1884 Conflict handling activity at Primary bank site
1885 Expect :
1886 1 conflict from slave T1 on Larry's balance
1887 1 conflict from slave T2 on Larry's balance
1888 =2 row conflicts
1889
1890 3 (user) transactions rejected
1891 9 rows rejected (3 per transaction)
1892 Variability : # epoch transactions, # row conflicts detected
1893 1-3 2-3
1894 # detect_iter_count
1895 1-3
1896 We only check stable values
1897 ndb_conflict_trans_row_reject_count
1898 9
1899 ndb_conflict_trans_reject_count
1900 3
1901 ndb_epoch_delete_delete_count
1902 0
1903 Records at Secondary bank site
1904 select * from test.transactions order by auto_key;
1905 auto_key from_name to_name detail amount
1906 1 Larry Yacht dealer Yacht purchase 50
1907 select * from test.balances order by name;
1908 name balance
1909 Employee-1 0
1910 Employee-2 0
1911 Larry 50
1912 Newsagent 0
1913 Yacht dealer 50
1914 drop table test.balances;
1915 drop table test.transactions;
1916 drop table test.transactions$EX;
1917 Test mixing transactional and non transactional
1918 -----------------------------------------------
1919 Remove old data from t1
1920 delete from test.t1;
1921 Define table with row-based epoch detection
1922 replace into mysql.ndb_replication values
1923 ("test", "t3", 1, 7, NULL),
1924 ("test", "t3", 3, 7, 'NDB$EPOCH()');
1925 create table t3 (a int primary key, b int) engine=ndb;
1926 create table t4 (a int primary key, b int) engine=ndb;
1927 create table t5 (a int primary key, b longtext) engine=ndb;
1928 Insert some data
1929 insert into test.t1 values
1930 (1,1),
1931 (2,2),
1932 (3,3),
1933 (4,4),
1934 (5,5),
1935 (6,6);
1936 insert into test.t3 values
1937 (11,11),
1938 (12,12),
1939 (13,13),
1940 (14,14),
1941 (15,15),
1942 (16,16);
1943 insert into test.t4 values
1944 (21,21),
1945 (22,22),
1946 (23,23),
1947 (24,24),
1948 (25,25),
1949 (26,26);
1950 insert into test.t5 values
1951 (1, REPEAT("B", 10000)),
1952 (2, REPEAT("E", 10000)),
1953 (3, REPEAT("A", 10000));
1954 Allow to propagate
1955 FLUSH LOGS;
1956 Case 1 : Transactional detection affects row - based entries in same trans
1957 stop slave;
1958 Warnings:
1959 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1960 update test.t1 set b=100 where a=1;
1961 begin;
1962 update test.t3 set b=1100 where a=11;
1963 update test.t4 set b=2100 where a=21;
1964 update test.t1 set b=1000 where a=1;
1965 commit;
1966 Show slave transaction effect
1967 select * from test.t1 order by a;
1968 a b
1969 1 1000
1970 2 2
1971 3 3
1972 4 4
1973 5 5
1974 6 6
1975 select * from test.t3 order by a;
1976 a b
1977 11 1100
1978 12 12
1979 13 13
1980 14 14
1981 15 15
1982 16 16
1983 select * from test.t4 order by a;
1984 a b
1985 21 2100
1986 22 22
1987 23 23
1988 24 24
1989 25 25
1990 26 26
1991 Expect Primary to have rejected whole trans across 3 tables
1992 select * from test.t1 order by a;
1993 a b
1994 1 100
1995 2 2
1996 3 3
1997 4 4
1998 5 5
1999 6 6
2000 select * from test.t3 order by a;
2001 a b
2002 11 11
2003 12 12
2004 13 13
2005 14 14
2006 15 15
2007 16 16
2008 select * from test.t4 order by a;
2009 a b
2010 21 21
2011 22 22
2012 23 23
2013 24 24
2014 25 25
2015 26 26
2016 Expect 1 transaction rejected, 3 rows rejected
2017 1 conflict row, 1 epoch, 1 iteration
2018 ndb_conflict_fn_epoch_trans
2019 0
2020 ndb_conflict_fn_epoch2_trans
2021 1
2022 ndb_conflict_trans_row_conflict_count
2023 1
2024 ndb_conflict_trans_row_reject_count
2025 3
2026 ndb_conflict_trans_reject_count
2027 1
2028 ndb_conflict_trans_detect_iter_count
2029 1
2030 ndb_conflict_trans_conflict_commit_count
2031 1
2032 ndb_epoch_delete_delete_count
2033 0
2034 Now restart rep to Secondary, and check realignment
2035 start slave;
2036 Warnings:
2037 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2038 select * from test.t1 order by a;
2039 a b
2040 1 100
2041 2 2
2042 3 3
2043 4 4
2044 5 5
2045 6 6
2046 select * from test.t3 order by a;
2047 a b
2048 11 11
2049 12 12
2050 13 13
2051 14 14
2052 15 15
2053 16 16
2054 select * from test.t4 order by a;
2055 a b
2056 21 21
2057 22 22
2058 23 23
2059 24 24
2060 25 25
2061 26 26
2062 Case 2 : Row based detection does not affect other transaction entries
2063 stop slave;
2064 Warnings:
2065 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2066 update test.t3 set b=1200 where a=12;
2067 begin;
2068 update test.t3 set b=1201 where a=12;
2069 update test.t4 set b=2200 where a=22;
2070 update test.t1 set b=2000 where a=2;
2071 commit;
2072 Show effect of transaction on Secondary
2073 select * from test.t1 order by a;
2074 a b
2075 1 100
2076 2 2000
2077 3 3
2078 4 4
2079 5 5
2080 6 6
2081 select * from test.t3 order by a;
2082 a b
2083 11 11
2084 12 1201
2085 13 13
2086 14 14
2087 15 15
2088 16 16
2089 select * from test.t4 order by a;
2090 a b
2091 21 21
2092 22 2200
2093 23 23
2094 24 24
2095 25 25
2096 26 26
2097 Show effect of transaction on Primary
2098 Only t3 should have been reverted
2099 select * from test.t1 order by a;
2100 a b
2101 1 100
2102 2 2000
2103 3 3
2104 4 4
2105 5 5
2106 6 6
2107 select * from test.t3 order by a;
2108 a b
2109 11 11
2110 12 1200
2111 13 13
2112 14 14
2113 15 15
2114 16 16
2115 select * from test.t4 order by a;
2116 a b
2117 21 21
2118 22 2200
2119 23 23
2120 24 24
2121 25 25
2122 26 26
2123 Expect all counters to be zero
2124 ndb_conflict_fn_epoch_trans
2125 0
2126 ndb_conflict_fn_epoch2_trans
2127 0
2128 ndb_conflict_trans_row_conflict_count
2129 0
2130 ndb_conflict_trans_row_reject_count
2131 0
2132 ndb_conflict_trans_reject_count
2133 0
2134 ndb_conflict_trans_detect_iter_count
2135 0
2136 ndb_conflict_trans_conflict_commit_count
2137 0
2138 ndb_epoch_delete_delete_count
2139 0
2140 Show effect of transaction on Secondary
2141 start slave;
2142 Warnings:
2143 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2144 select * from test.t1 order by a;
2145 a b
2146 1 100
2147 2 2000
2148 3 3
2149 4 4
2150 5 5
2151 6 6
2152 select * from test.t3 order by a;
2153 a b
2154 11 11
2155 12 1200
2156 13 13
2157 14 14
2158 15 15
2159 16 16
2160 select * from test.t4 order by a;
2161 a b
2162 21 21
2163 22 2200
2164 23 23
2165 24 24
2166 25 25
2167 26 26
2168 flush logs;
2169 Case 3 : Check behaviour where table with Blob is implicated
2170 in transactional conflict. Should result in Slave
2171 stopping with an error.
2172 STOP SLAVE;
2173 Warnings:
2174 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2175 Setup warning suppression
2176 begin;
2177 update t1 set b= 11 where a=1;
2178 commit;
2179 begin;
2180 update t1 set b= 111 where a=1;
2181 update t1 set b= 222 where a=2;
2182 update t5 set b= REPEAT("T", 10000) where a=3;
2183 commit;
2184 Show effect of transaction on Secondary
2185 select * from test.t1 order by a;
2186 a b
2187 1 111
2188 2 222
2189 3 3
2190 4 4
2191 5 5
2192 6 6
2193 select left(b,1), length(b) from test.t5 order by a;
2194 left(b,1) length(b)
2195 B 10000
2196 E 10000
2197 T 10000
2198 Check that Primary Slave has stopped
2199 include/wait_for_slave_sql_error.inc [errno=1296]
2200 Restart Primary Slave
2201 set global sql_replica_skip_counter=1;
2202 START SLAVE;
2203 Warnings:
2204 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2205 Restart Secondary Slave
2206 START SLAVE;
2207 Warnings:
2208 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2209 flush logs;
2210 drop table test.t3;
2211 drop table test.t4;
2212 drop table test.t5;
2213 Fix misalignment caused above
2214 Makes following result sets look nicer
2215 update test.t1 set b=2 where a=2;
2216 Show a simple row-level delete-delete conflict
2217 PRIMARY:
2218 insert into test.t1 values (77, "A good year");
2219 select * from test.t1 order by a;
2220 a b
2221 1 11
2222 2 2
2223 3 3
2224 4 4
2225 5 5
2226 6 6
2227 77 A good year
2228 SECONDARY:
2229 stop slave;
2230 Warnings:
2231 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2232 PRIMARY:
2233 Primary Delete
2234 delete from test.t1 where a=77;
2235 Show Primary contents
2236 select * from test.t1 order by a;
2237 a b
2238 1 11
2239 2 2
2240 3 3
2241 4 4
2242 5 5
2243 6 6
2244 SECONDARY:
2245 select * from test.t1 order by a;
2246 a b
2247 1 11
2248 2 2
2249 3 3
2250 4 4
2251 5 5
2252 6 6
2253 77 A good year
2254 Secondary Delete->Insert in separate transactions
2255 and potentially different epochs
2256 Delete part with related transaction op
2257 begin;
2258 delete from test.t1 where a=77;
2259 insert into test.t1 values (73, "A superb year");
2260 commit;
2261 Unrelated transaction from Secondary
2262 begin;
2263 insert into test.t1 values (84, "A good vintage");
2264 commit;
2265 Optionally flush logs
2266 Following Insert from Secondary - potentially in different epoch
2267 begin;
2268 insert into test.t1 values (77, "Exceptional");
2269 insert into test.t1 values (80, "Poor");
2270 commit;
2271 Show Secondary contents
2272 select * from test.t1 order by a;
2273 a b
2274 1 11
2275 2 2
2276 3 3
2277 4 4
2278 5 5
2279 6 6
2280 73 A superb year
2281 77 Exceptional
2282 80 Poor
2283 84 A good vintage
2284 Allow propagation to Primary
2285 PRIMARY:
2286 Expect:
2287 - NDB$EPOCH_TRANS :
2288 - Will not detect delete-delete conflict, so first
2289 Secondary transaction will apply row 73.
2290 - Unrelated transaction from Secondary (84) is present
2291 - Following insert on delete-delete conflict row (77) is not in conflict,
2292 so both it and the related row from the same transaction (80) are present
2293 This applies whether the following insert is in the same or a different
2294 epoch
2295 Divergence:
2296 - Primary will have 73,84,77,80
2297 - Secondary will have 73,84,80, no 77
2298
2299 - NDB$EPOCH_TRANS2 :
2300 - Will detect delete-delete conflict, so first
2301 Secondary transaction will not apply (no row 73)
2302 77 and 73 will be refreshed (DEL)
2303 - Unrelated transaction from Secondary (84) is present and applied
2304 84 will be reflected (INS)
2305 - Following insert on delete-delete conflict row (77) will be found in
2306 conflict iff this transaction is in the same epoch as the first
2307 conflicting transaction. If it is in a different epoch then it will
2308 not be found in conflict, as there will be no state available to
2309 use.
2310 - If it is in-conflict, then 77 and 80 will be refreshed (DEL)
2311 Result on both should be :
2312 84
2313 - If it is not in-conflict, then 77 and 80 will be reflected (INS)
2314 Result on both should be :
2315 84, 77, 80
2316 No divergence
2317 set @primary_ok:=
2318 ((@epoch_trans XOR
2319 @epoch_trans2_same_epoch) XOR
2320 @epoch_trans2_diff_epoch);
2321 select @primary_ok;
2322 @primary_ok
2323 1
2324 EPOCH2_TRANS results are dependent on epoch boundaries
2325 Now sync with Secondary to show outcomes
2326 start slave;
2327 Warnings:
2328 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2329 SECONDARY:
2330
2331 set @secondary_ok:=
2332 ((@epoch_trans XOR
2333 @epoch_trans2_same_epoch) XOR
2334 @epoch_trans2_diff_epoch);
2335 select @secondary_ok;
2336 @secondary_ok
2337 1
2338 EPOCH2_TRANS results are dependent on epoch boundaries
2339 PRIMARY
2340 Cleanup
2341 delete from test.t1 where a != 6;
2342 PRIMARY
2343 Transactional Delete testcase
2344 STOP slave on SECONDARY
2345 stop slave;
2346 Warnings:
2347 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2348 PRIMARY
2349 Primary updates row with pk 6
2350 update test.t1 set b="Pentland" where a=6;
2351 PRIMARY content
2352 Row 6 updated
2353 select * from test.t1 order by a;
2354 a b
2355 6 Pentland
2356 SECONDARY
2357 Transaction deleting pk 6, inserting pk 7
2358 begin;
2359 delete from test.t1 where a = 6;
2360 insert into test.t1 values (7, "Mendip");
2361 commit;
2362 Optionally flush logs
2363 Transaction deleting pk 7, inserting pk 8
2364 begin;
2365 delete from test.t1 where a = 7;
2366 insert into test.t1 values (8, "Ochil");
2367 commit;
2368 SECONDARY content
2369 Rows 6 + 7 deleted. Row 8 present.
2370 select * from test.t1 order by a;
2371 a b
2372 8 Ochil
2373 Sync SECONDARY -> PRIMARY
2374 Expected Behaviour
2375 EPOCH_TRANS
2376
2377 1. Conflict between Primary update to 6 + Secondary
2378 [Delete of 6 + Insert of 7] is detected by Primary
2379 and transaction is not applied at the Primary.
2380 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
2381 epoch updated.
2382
2383 2. Following Secondary transaction, [delete of 7
2384 + insert of 8] behaviour depends on whether it
2385 arrives in the same or a different epoch.
2386 Same epoch :
2387 Will conflict with refreshed row 7, and so will
2388 not be applied. Row 8 will be refreshed (DEL)
2389 Different epoch :
2390 Will not conflict with Not-present row 7, and so
2391 will be applied.
2392
2393 3. On the Secondary, the observed sequence will be :
2394 - Update 6 from PRIMARY : (Idempotent insert)
2395 - Refresh (INS) 6 from PRIMARY
2396 - Refresh (DEL) 7 from PRIMARY
2397 Same epoch :
2398 - Refresh (DEL) 8 from PRIMARY
2399 Different epoch :
2400 - Nothing
2401
2402 Results (no divergence) :
2403 Same epoch :
2404 PRIMARY : 6
2405 SECONDARY : 6
2406 Diff epoch :
2407 PRIMARY : 6,8
2408 SECONDARY : 6,8
2409
2410
2411 EPOCH_TRANS2
2412
2413 1. Conflict between Primary update to 6 + Secondary
2414 [Delete of 6 + Insert of 7] is detected by Primary
2415 and transaction is not applied at the Primary.
2416 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
2417 epoch updated.
2418
2419 2. Following Secondary transaction [delete of 7 +
2420 insert of 8] will conflict with the first
2421 transaction as EPOCH_TRANS2 does not allow
2422 a delete of an already deleted row.
2423 Row 7 will be refreshed (DEL) and row 8 will be
2424 refreshed (DEL)
2425
2426 3. On the Secondary, the observed sequence will be :
2427 - Update 6 from PRIMARY : (Idempotent insert)
2428 - Refresh (INS) 6 from PRIMARY
2429 - Refresh (DEL) 7 from PRIMARY
2430 - [Refresh (DEL) 7 from PRIMARY] - diff epoch
2431 - Refresh (DEL) 8 from PRIMARY
2432
2433 Results (no divergence) :
2434 PRIMARY : 6
2435 SECONDARY : 6
2436
2437 PRIMARY content
2438 set @primary_ok:=
2439 ((@epoch_trans_same_epoch XOR
2440 @epoch_trans_diff_epoch) XOR
2441 @epoch2_trans);
2442 select @primary_ok;
2443 @primary_ok
2444 1
2445 select * from test.t1 order by a;
2446 a b
2447 6 Pentland
2448 Restart SECONDARY Slave
2449 START SLAVE;
2450 Warnings:
2451 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2452 Sync PRIMARY -> SECONDARY
2453 SECONDARY content
2454 set @secondary_ok:=
2455 ((@epoch_trans_same_epoch XOR
2456 @epoch_trans_diff_epoch) XOR
2457 @epoch2_trans);
2458 select @secondary_ok;
2459 @secondary_ok
2460 1
2461 select * from test.t1 order by a;
2462 a b
2463 6 Pentland
2464 STABILISE
2465 drop table mysql.ndb_replication;
2466 drop table test.t1;
2467 drop table test.t2;
2468 flush logs;
2469 stop slave;
2470 Warnings:
2471 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2472 reset slave;
2473 Warnings:
2474 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
2475 include/rpl_end.inc
2476 SET GLOBAL ndb_applier_conflict_role="NONE";
2477 SET GLOBAL ndb_applier_conflict_role="NONE";