Debian Codegraph @

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

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 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.
5 [connection master]
6 Setup circular replication
7 Setup ndb_replication and t1 exceptions table
8 Populate ndb_replication table as necessary
9 replace into mysql.ndb_replication values
10 ("test", "t1", 3, 7, NULL),
11 ("test", "t1", 1, 7, "NDB$EPOCH_TRANS()");
12 replace into mysql.ndb_replication values
13 ("test", "t2", 3, 7, NULL),
14 ("test", "t2", 1, 7, "NDB$EPOCH_TRANS()");
15 create table test.t1 (
16 a int primary key,
17 b varchar(2000)) engine=ndb;
18 create table test.t2 (
19 a int primary key,
20 b varchar(2000)) engine=ndb;
21 Add some data
22 insert into test.t1 values
23 (1, "Initial data 1"),
24 (2, "Initial data 2"),
25 (3, "Initial data 3"),
26 (4, "Initial data 4"),
27 (5, "Initial data 5"),
28 (6, "Initial data 6"),
29 (7, "Initial data 7"),
30 (8, "Initial data 8"),
31 (9, "Initial data 9"),
32 (10, "Initial data 10");
33 Show basic row-level conflict detection
34 ---------------------------------------
35 stop slave;
36 Warnings:
37 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
38 update t1 set b="Primary first change 2" where a=2;
39 select * from test.t1 order by a;
40 a b
41 1 Initial data 1
42 2 Primary first change 2
43 3 Initial data 3
44 4 Initial data 4
45 5 Initial data 5
46 6 Initial data 6
47 7 Initial data 7
48 8 Initial data 8
49 9 Initial data 9
50 10 Initial data 10
51 update t1 set b="Secondary first change 2" where a=2;
52 select * from test.t1 order by a;
53 a b
54 1 Initial data 1
55 2 Secondary first change 2
56 3 Initial data 3
57 4 Initial data 4
58 5 Initial data 5
59 6 Initial data 6
60 7 Initial data 7
61 8 Initial data 8
62 9 Initial data 9
63 10 Initial data 10
64 Primary should have rejected change from Secondary, keeping its value
65 select * from t1 order by a;
66 a b
67 1 Initial data 1
68 2 Primary first change 2
69 3 Initial data 3
70 4 Initial data 4
71 5 Initial data 5
72 6 Initial data 6
73 7 Initial data 7
74 8 Initial data 8
75 9 Initial data 9
76 10 Initial data 10
77 ndb_conflict_fn_epoch_trans
78 1
79 ndb_conflict_fn_epoch2_trans
80 0
81 ndb_conflict_trans_row_conflict_count
82 1
83 ndb_conflict_trans_row_reject_count
84 1
85 ndb_conflict_trans_reject_count
86 1
87 ndb_conflict_trans_detect_iter_count
88 1
89 ndb_conflict_trans_conflict_commit_count
90 1
91 ndb_epoch_delete_delete_count
92 0
93 start slave;
94 Warnings:
95 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
96 Secondary should have been realigned to Primary
97 select * from t1 order by a;
98 a b
99 1 Initial data 1
100 2 Primary first change 2
101 3 Initial data 3
102 4 Initial data 4
103 5 Initial data 5
104 6 Initial data 6
105 7 Initial data 7
106 8 Initial data 8
107 9 Initial data 9
108 10 Initial data 10
109 Show rollback of whole secondary transaction
110 --------------------------------------------
111 stop slave;
112 Warnings:
113 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
114 update t1 set b="Primary second change 4" where a=4;
115 select * from test.t1 order by a;
116 a b
117 1 Initial data 1
118 2 Primary first change 2
119 3 Initial data 3
120 4 Primary second change 4
121 5 Initial data 5
122 6 Initial data 6
123 7 Initial data 7
124 8 Initial data 8
125 9 Initial data 9
126 10 Initial data 10
127 begin;
128 update t1 set b="Secondary second change 4" where a=4;
129 update t1 set b="Secondary second change 5" where a=5;
130 commit;
131 select * from test.t1 order by a;
132 a b
133 1 Initial data 1
134 2 Primary first change 2
135 3 Initial data 3
136 4 Secondary second change 4
137 5 Secondary second change 5
138 6 Initial data 6
139 7 Initial data 7
140 8 Initial data 8
141 9 Initial data 9
142 10 Initial data 10
143 Primary should have rejected secondary changes on both rows
144 select * from test.t1 order by a;
145 a b
146 1 Initial data 1
147 2 Primary first change 2
148 3 Initial data 3
149 4 Primary second change 4
150 5 Initial data 5
151 6 Initial data 6
152 7 Initial data 7
153 8 Initial data 8
154 9 Initial data 9
155 10 Initial data 10
156 ndb_conflict_fn_epoch_trans
157 1
158 ndb_conflict_fn_epoch2_trans
159 0
160 ndb_conflict_trans_row_conflict_count
161 1
162 ndb_conflict_trans_row_reject_count
163 2
164 ndb_conflict_trans_reject_count
165 1
166 ndb_conflict_trans_detect_iter_count
167 1
168 ndb_conflict_trans_conflict_commit_count
169 1
170 ndb_epoch_delete_delete_count
171 0
172 start slave;
173 Warnings:
174 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
175 Secondary should have been realigned to Primary
176 select * from test.t1 order by a;
177 a b
178 1 Initial data 1
179 2 Primary first change 2
180 3 Initial data 3
181 4 Primary second change 4
182 5 Initial data 5
183 6 Initial data 6
184 7 Initial data 7
185 8 Initial data 8
186 9 Initial data 9
187 10 Initial data 10
188 Show rollback of dependent transaction as well
189 ----------------------------------------------
190 stop slave;
191 Warnings:
192 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
193 update t1 set b="Primary third change 1" where a=1;
194 select * from test.t1 order by a;
195 a b
196 1 Primary third change 1
197 2 Primary first change 2
198 3 Initial data 3
199 4 Primary second change 4
200 5 Initial data 5
201 6 Initial data 6
202 7 Initial data 7
203 8 Initial data 8
204 9 Initial data 9
205 10 Initial data 10
206 begin;
207 update t1 set b="Secondary third change 3" where a=3;
208 update t1 set b="Secondary third change 1" where a=1;
209 commit;
210 begin;
211 update t1 set b="Secondary fourth change 3" where a=3;
212 insert into t1 values (11,"Secondary fourth change 11");
213 commit;
214 select * from test.t1 order by a;
215 a b
216 1 Secondary third change 1
217 2 Primary first change 2
218 3 Secondary fourth change 3
219 4 Primary second change 4
220 5 Initial data 5
221 6 Initial data 6
222 7 Initial data 7
223 8 Initial data 8
224 9 Initial data 9
225 10 Initial data 10
226 11 Secondary fourth change 11
227 Primary should have rejected all secondary changes
228 select * from test.t1 order by a;
229 a b
230 1 Primary third change 1
231 2 Primary first change 2
232 3 Initial data 3
233 4 Primary second change 4
234 5 Initial data 5
235 6 Initial data 6
236 7 Initial data 7
237 8 Initial data 8
238 9 Initial data 9
239 10 Initial data 10
240 ndb_conflict_trans_row_reject_count
241 4
242 ndb_conflict_trans_reject_count
243 2
244 ndb_epoch_delete_delete_count
245 0
246 start slave;
247 Warnings:
248 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
249 Secondary should have been realigned to Primary
250 select * from test.t1 order by a;
251 a b
252 1 Primary third change 1
253 2 Primary first change 2
254 3 Initial data 3
255 4 Primary second change 4
256 5 Initial data 5
257 6 Initial data 6
258 7 Initial data 7
259 8 Initial data 8
260 9 Initial data 9
261 10 Initial data 10
262 Show rollback of dependent transaction across different tables
263 --------------------------------------------------------------
264 stop slave;
265 Warnings:
266 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
267 update t1 set b="Primary fifth change 6" where a=6;
268 select * from test.t1 order by a;
269 a b
270 1 Primary third change 1
271 2 Primary first change 2
272 3 Initial data 3
273 4 Primary second change 4
274 5 Initial data 5
275 6 Primary fifth change 6
276 7 Initial data 7
277 8 Initial data 8
278 9 Initial data 9
279 10 Initial data 10
280 begin;
281 update t1 set b="Secondary fifth change 6" where a=6;
282 insert into t2 values (1, "Secondary fifth change 1");
283 insert into t2 values (2, "Secondary fifth change 2");
284 commit;
285 begin;
286 update t2 set b="Secondary sixth change 1" where a=2;
287 insert into t2 values (3, "Secondary sixth change 2");
288 commit;
289 select * from test.t1 order by a;
290 a b
291 1 Primary third change 1
292 2 Primary first change 2
293 3 Initial data 3
294 4 Primary second change 4
295 5 Initial data 5
296 6 Secondary fifth change 6
297 7 Initial data 7
298 8 Initial data 8
299 9 Initial data 9
300 10 Initial data 10
301 select * from test.t2 order by a;
302 a b
303 1 Secondary fifth change 1
304 2 Secondary sixth change 1
305 3 Secondary sixth change 2
306 Primary should have rejected all secondary changes
307 select * from test.t1 order by a;
308 a b
309 1 Primary third change 1
310 2 Primary first change 2
311 3 Initial data 3
312 4 Primary second change 4
313 5 Initial data 5
314 6 Primary fifth change 6
315 7 Initial data 7
316 8 Initial data 8
317 9 Initial data 9
318 10 Initial data 10
319 select * from test.t2 order by a;
320 a b
321 ndb_conflict_trans_row_reject_count
322 5
323 ndb_conflict_trans_reject_count
324 2
325 ndb_epoch_delete_delete_count
326 0
327 start slave;
328 Warnings:
329 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
330 Secondary should have been realigned to primary
331 select * from test.t1 order by a;
332 a b
333 1 Primary third change 1
334 2 Primary first change 2
335 3 Initial data 3
336 4 Primary second change 4
337 5 Initial data 5
338 6 Primary fifth change 6
339 7 Initial data 7
340 8 Initial data 8
341 9 Initial data 9
342 10 Initial data 10
343 select * from test.t2 order by a;
344 a b
345 Show that whole epoch is not rolled back
346 ----------------------------------------
347 stop slave;
348 Warnings:
349 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
350 update t1 set b="Primary is king" where a=10;
351 begin;
352 update t1 set b="Secondary is emperor" where a=10;
353 insert into t1 values (11, "Secondary is pleni-potentiary");
354 commit;
355 begin;
356 insert into t1 values (12, "Secondary ruled once");
357 insert into t1 values (13, "This history will not be lost");
358 insert into t1 values (14, "Look on my works ye mighty, and despair");
359 commit;
360 Primary should have rejected conflicting trans (emperor, pleni-potentiary)
361 but accepted unrelated trans (history)
362 select * from t1 order by a;
363 a b
364 1 Primary third change 1
365 2 Primary first change 2
366 3 Initial data 3
367 4 Primary second change 4
368 5 Initial data 5
369 6 Primary fifth change 6
370 7 Initial data 7
371 8 Initial data 8
372 9 Initial data 9
373 10 Primary is king
374 12 Secondary ruled once
375 13 This history will not be lost
376 14 Look on my works ye mighty, and despair
377 ndb_conflict_fn_epoch_trans
378 1
379 ndb_conflict_fn_epoch2_trans
380 0
381 ndb_conflict_trans_row_conflict_count
382 1
383 ndb_conflict_trans_row_reject_count
384 2
385 ndb_conflict_trans_reject_count
386 1
387 ndb_conflict_trans_detect_iter_count
388 1
389 ndb_conflict_trans_conflict_commit_count
390 1
391 ndb_epoch_delete_delete_count
392 0
393 start slave;
394 Warnings:
395 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
396 Secondary should be aligned with Primary
397 select * from t1 order by a;
398 a b
399 1 Primary third change 1
400 2 Primary first change 2
401 3 Initial data 3
402 4 Primary second change 4
403 5 Initial data 5
404 6 Primary fifth change 6
405 7 Initial data 7
406 8 Initial data 8
407 9 Initial data 9
408 10 Primary is king
409 12 Secondary ruled once
410 13 This history will not be lost
411 14 Look on my works ye mighty, and despair
412 Show that non-conflicting ancestors are not implicated
413 ------------------------------------------------------
414 stop slave;
415 Warnings:
416 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
417 update t1 set b="7 : Primary is king" where a=7;
418 Primary state
419 select * from test.t1 order by a;
420 a b
421 1 Primary third change 1
422 2 Primary first change 2
423 3 Initial data 3
424 4 Primary second change 4
425 5 Initial data 5
426 6 Primary fifth change 6
427 7 7 : Primary is king
428 8 Initial data 8
429 9 Initial data 9
430 10 Primary is king
431 12 Secondary ruled once
432 13 This history will not be lost
433 14 Look on my works ye mighty, and despair
434 begin;
435 update t1 set b="8 : Secondary innocent" where a=8;
436 update t1 set b="9 : Secondary innocent" where a=9;
437 commit;
438 Secondary with innocent
439 select * from test.t1 order by a;
440 a b
441 1 Primary third change 1
442 2 Primary first change 2
443 3 Initial data 3
444 4 Primary second change 4
445 5 Initial data 5
446 6 Primary fifth change 6
447 7 Initial data 7
448 8 8 : Secondary innocent
449 9 9 : Secondary innocent
450 10 Primary is king
451 12 Secondary ruled once
452 13 This history will not be lost
453 14 Look on my works ye mighty, and despair
454 begin;
455 update t1 set b="9 : Secondary guilty" where a=9;
456 update t1 set b="7 : Secondary guilty" where a=7;
457 commit;
458 Secondary with guilty overlaid
459 select * from test.t1 order by a;
460 a b
461 1 Primary third change 1
462 2 Primary first change 2
463 3 Initial data 3
464 4 Primary second change 4
465 5 Initial data 5
466 6 Primary fifth change 6
467 7 7 : Secondary guilty
468 8 8 : Secondary innocent
469 9 9 : Secondary guilty
470 10 Primary is king
471 12 Secondary ruled once
472 13 This history will not be lost
473 14 Look on my works ye mighty, and despair
474 Primary cluster should have rejected 'guilty' secondary transaction, but
475 accepted 'innocent' secondary transaction.
476 select * from test.t1 order by a;
477 a b
478 1 Primary third change 1
479 2 Primary first change 2
480 3 Initial data 3
481 4 Primary second change 4
482 5 Initial data 5
483 6 Primary fifth change 6
484 7 7 : Primary is king
485 8 8 : Secondary innocent
486 9 9 : Secondary innocent
487 10 Primary is king
488 12 Secondary ruled once
489 13 This history will not be lost
490 14 Look on my works ye mighty, and despair
491 ndb_conflict_fn_epoch_trans
492 1
493 ndb_conflict_fn_epoch2_trans
494 0
495 ndb_conflict_trans_row_conflict_count
496 1
497 ndb_conflict_trans_row_reject_count
498 2
499 ndb_conflict_trans_reject_count
500 1
501 ndb_conflict_trans_detect_iter_count
502 1
503 ndb_conflict_trans_conflict_commit_count
504 1
505 ndb_epoch_delete_delete_count
506 0
507 start slave;
508 Warnings:
509 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
510 Secondary cluster should be realigned with Primary
511 select * from test.t1 order by a;
512 a b
513 1 Primary third change 1
514 2 Primary first change 2
515 3 Initial data 3
516 4 Primary second change 4
517 5 Initial data 5
518 6 Primary fifth change 6
519 7 7 : Primary is king
520 8 8 : Secondary innocent
521 9 9 : Secondary innocent
522 10 Primary is king
523 12 Secondary ruled once
524 13 This history will not be lost
525 14 Look on my works ye mighty, and despair
526 Classic banking example
527 -----------------------
528 replace into mysql.ndb_replication values
529 ("test", "balances", 3, 7, NULL),
530 ("test", "balances", 1, 7, "NDB$EPOCH_TRANS()");
531 replace into mysql.ndb_replication values
532 ("test", "transactions", 3, 7, NULL),
533 ("test", "transactions", 1, 7, "NDB$EPOCH_TRANS()");
534 create table test.balances
535 (name varchar(100) primary key,
536 balance int) engine=ndb;
537 create table test.transactions$EX
538 (server_id int unsigned,
539 master_server_id int unsigned,
540 master_epoch bigint unsigned,
541 count int unsigned,
542 auto_key int not null,
543 from_name varchar(100) not null,
544 to_name varchar(100) not null,
545 detail varchar(100) not null,
546 primary key(server_id, master_server_id, master_epoch, count))
547 engine=ndb;
548 create table test.transactions
549 (auto_key int auto_increment,
550 from_name varchar(100),
551 to_name varchar(100),
552 detail varchar(100),
553 amount int,
554 primary key(auto_key, from_name, to_name, detail)) engine=ndb;
555 Initialise balances across both bank sites
556 insert into test.balances values
557 ("Larry", 100),
558 ("Employee-1", 0),
559 ("Employee-2", 0),
560 ("Yacht dealer", 0),
561 ("Newsagent", 0);
562 FLUSH LOGS;
563 Bank sites are disconnected
564 stop slave;
565 Warnings:
566 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
567 Larry buys a yacht using Primary bank site
568 begin;
569 insert into test.transactions (from_name, to_name, detail, amount)
570 values ("Larry", "Yacht dealer", "Yacht purchase", 50);
571 update test.balances set balance = balance - 50 where name = "Larry";
572 update test.balances set balance = balance + 50 where name = "Yacht dealer";
573 commit;
574 Show yacht transaction records
575 select * from test.transactions order by auto_key;
576 auto_key from_name to_name detail amount
577 1 Larry Yacht dealer Yacht purchase 50
578 select * from test.balances order by name;
579 name balance
580 Employee-1 0
581 Employee-2 0
582 Larry 50
583 Newsagent 0
584 Yacht dealer 50
585 Larry pays employees using Secondary bank site
586 begin;
587 insert into test.transactions (from_name, to_name, detail, amount)
588 values ("Larry", "Employee-1", "Payment to Employee-1", 1);
589 update test.balances set balance = balance - 1 where name = "Larry";
590 update test.balances set balance = balance + 1 where name = "Employee-1";
591 commit;
592 begin;
593 insert into test.transactions (from_name, to_name, detail, amount)
594 values ("Larry", "Employee-2", "Payment to Employee-2", 1);
595 update test.balances set balance = balance - 1 where name = "Larry";
596 update test.balances set balance = balance + 1 where name = "Employee-2";
597 commit;
598 Employee-2 buys yacht magazine using Secondary bank site
599 begin;
600 insert into test.transactions (from_name, to_name, detail, amount)
601 values ("Employee-2", "Newsagent", "Buy yacht magazine", 1);
602 update test.balances set balance = balance - 1 where name = "Employee-2";
603 update test.balances set balance = balance + 1 where name = "Newsagent";
604 commit;
605 Show employee transactions
606 select * from test.transactions order by auto_key;
607 auto_key from_name to_name detail amount
608 1 Larry Employee-1 Payment to Employee-1 1
609 2 Larry Employee-2 Payment to Employee-2 1
610 3 Employee-2 Newsagent Buy yacht magazine 1
611 select * from test.balances order by name;
612 name balance
613 Employee-1 1
614 Employee-2 0
615 Larry 98
616 Newsagent 1
617 Yacht dealer 0
618 Bank sites re-connected
619 start slave;
620 Warnings:
621 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
622 Records at Primary bank site
623 select * from test.transactions order by auto_key;
624 auto_key from_name to_name detail amount
625 1 Larry Yacht dealer Yacht purchase 50
626 select * from test.balances order by name;
627 name balance
628 Employee-1 0
629 Employee-2 0
630 Larry 50
631 Newsagent 0
632 Yacht dealer 50
633 Exceptions at Primary bank site
634 select server_id, master_server_id, auto_key, from_name, to_name, detail
635 from test.transactions$EX order by auto_key, from_name, to_name, detail;
636 server_id master_server_id auto_key from_name to_name detail
637 1 3 1 Larry Employee-1 Payment to Employee-1
638 1 3 2 Larry Employee-2 Payment to Employee-2
639 1 3 3 Employee-2 Newsagent Buy yacht magazine
640 Conflict handling activity at Primary bank site
641 Expect :
642 1 conflict from slave T1 on Larry's balance
643 1 conflict from slave T2 on Larry's balance
644 =2 row conflicts
645
646 3 (user) transactions rejected
647 9 rows rejected (3 per transaction)
648 Variability : # epoch transactions, # row conflicts detected
649 1-3 2-3
650 # detect_iter_count
651 1-3
652 We only check stable values
653 ndb_conflict_trans_row_reject_count
654 9
655 ndb_conflict_trans_reject_count
656 3
657 ndb_epoch_delete_delete_count
658 0
659 Records at Secondary bank site
660 select * from test.transactions order by auto_key;
661 auto_key from_name to_name detail amount
662 1 Larry Yacht dealer Yacht purchase 50
663 select * from test.balances order by name;
664 name balance
665 Employee-1 0
666 Employee-2 0
667 Larry 50
668 Newsagent 0
669 Yacht dealer 50
670 drop table test.balances;
671 drop table test.transactions;
672 drop table test.transactions$EX;
673 Test mixing transactional and non transactional
674 -----------------------------------------------
675 Remove old data from t1
676 delete from test.t1;
677 Define table with row-based epoch detection
678 replace into mysql.ndb_replication values
679 ("test", "t3", 3, 7, NULL),
680 ("test", "t3", 1, 7, 'NDB$EPOCH()');
681 create table t3 (a int primary key, b int) engine=ndb;
682 create table t4 (a int primary key, b int) engine=ndb;
683 create table t5 (a int primary key, b longtext) engine=ndb;
684 Insert some data
685 insert into test.t1 values
686 (1,1),
687 (2,2),
688 (3,3),
689 (4,4),
690 (5,5),
691 (6,6);
692 insert into test.t3 values
693 (11,11),
694 (12,12),
695 (13,13),
696 (14,14),
697 (15,15),
698 (16,16);
699 insert into test.t4 values
700 (21,21),
701 (22,22),
702 (23,23),
703 (24,24),
704 (25,25),
705 (26,26);
706 insert into test.t5 values
707 (1, REPEAT("B", 10000)),
708 (2, REPEAT("E", 10000)),
709 (3, REPEAT("A", 10000));
710 Allow to propagate
711 FLUSH LOGS;
712 Case 1 : Transactional detection affects row - based entries in same trans
713 stop slave;
714 Warnings:
715 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
716 update test.t1 set b=100 where a=1;
717 begin;
718 update test.t3 set b=1100 where a=11;
719 update test.t4 set b=2100 where a=21;
720 update test.t1 set b=1000 where a=1;
721 commit;
722 Show slave transaction effect
723 select * from test.t1 order by a;
724 a b
725 1 1000
726 2 2
727 3 3
728 4 4
729 5 5
730 6 6
731 select * from test.t3 order by a;
732 a b
733 11 1100
734 12 12
735 13 13
736 14 14
737 15 15
738 16 16
739 select * from test.t4 order by a;
740 a b
741 21 2100
742 22 22
743 23 23
744 24 24
745 25 25
746 26 26
747 Expect Primary to have rejected whole trans across 3 tables
748 select * from test.t1 order by a;
749 a b
750 1 100
751 2 2
752 3 3
753 4 4
754 5 5
755 6 6
756 select * from test.t3 order by a;
757 a b
758 11 11
759 12 12
760 13 13
761 14 14
762 15 15
763 16 16
764 select * from test.t4 order by a;
765 a b
766 21 21
767 22 22
768 23 23
769 24 24
770 25 25
771 26 26
772 Expect 1 transaction rejected, 3 rows rejected
773 1 conflict row, 1 epoch, 1 iteration
774 ndb_conflict_fn_epoch_trans
775 1
776 ndb_conflict_fn_epoch2_trans
777 0
778 ndb_conflict_trans_row_conflict_count
779 1
780 ndb_conflict_trans_row_reject_count
781 3
782 ndb_conflict_trans_reject_count
783 1
784 ndb_conflict_trans_detect_iter_count
785 1
786 ndb_conflict_trans_conflict_commit_count
787 1
788 ndb_epoch_delete_delete_count
789 0
790 Now restart rep to Secondary, and check realignment
791 start slave;
792 Warnings:
793 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
794 select * from test.t1 order by a;
795 a b
796 1 100
797 2 2
798 3 3
799 4 4
800 5 5
801 6 6
802 select * from test.t3 order by a;
803 a b
804 11 11
805 12 12
806 13 13
807 14 14
808 15 15
809 16 16
810 select * from test.t4 order by a;
811 a b
812 21 21
813 22 22
814 23 23
815 24 24
816 25 25
817 26 26
818 Case 2 : Row based detection does not affect other transaction entries
819 stop slave;
820 Warnings:
821 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
822 update test.t3 set b=1200 where a=12;
823 begin;
824 update test.t3 set b=1201 where a=12;
825 update test.t4 set b=2200 where a=22;
826 update test.t1 set b=2000 where a=2;
827 commit;
828 Show effect of transaction on Secondary
829 select * from test.t1 order by a;
830 a b
831 1 100
832 2 2000
833 3 3
834 4 4
835 5 5
836 6 6
837 select * from test.t3 order by a;
838 a b
839 11 11
840 12 1201
841 13 13
842 14 14
843 15 15
844 16 16
845 select * from test.t4 order by a;
846 a b
847 21 21
848 22 2200
849 23 23
850 24 24
851 25 25
852 26 26
853 Show effect of transaction on Primary
854 Only t3 should have been reverted
855 select * from test.t1 order by a;
856 a b
857 1 100
858 2 2000
859 3 3
860 4 4
861 5 5
862 6 6
863 select * from test.t3 order by a;
864 a b
865 11 11
866 12 1200
867 13 13
868 14 14
869 15 15
870 16 16
871 select * from test.t4 order by a;
872 a b
873 21 21
874 22 2200
875 23 23
876 24 24
877 25 25
878 26 26
879 Expect all counters to be zero
880 ndb_conflict_fn_epoch_trans
881 0
882 ndb_conflict_fn_epoch2_trans
883 0
884 ndb_conflict_trans_row_conflict_count
885 0
886 ndb_conflict_trans_row_reject_count
887 0
888 ndb_conflict_trans_reject_count
889 0
890 ndb_conflict_trans_detect_iter_count
891 0
892 ndb_conflict_trans_conflict_commit_count
893 0
894 ndb_epoch_delete_delete_count
895 0
896 Show effect of transaction on Secondary
897 start slave;
898 Warnings:
899 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
900 select * from test.t1 order by a;
901 a b
902 1 100
903 2 2000
904 3 3
905 4 4
906 5 5
907 6 6
908 select * from test.t3 order by a;
909 a b
910 11 11
911 12 1200
912 13 13
913 14 14
914 15 15
915 16 16
916 select * from test.t4 order by a;
917 a b
918 21 21
919 22 2200
920 23 23
921 24 24
922 25 25
923 26 26
924 flush logs;
925 Case 3 : Check behaviour where table with Blob is implicated
926 in transactional conflict. Should result in Slave
927 stopping with an error.
928 STOP SLAVE;
929 Warnings:
930 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
931 Setup warning suppression
932 begin;
933 update t1 set b= 11 where a=1;
934 commit;
935 begin;
936 update t1 set b= 111 where a=1;
937 update t1 set b= 222 where a=2;
938 update t5 set b= REPEAT("T", 10000) where a=3;
939 commit;
940 Show effect of transaction on Secondary
941 select * from test.t1 order by a;
942 a b
943 1 111
944 2 222
945 3 3
946 4 4
947 5 5
948 6 6
949 select left(b,1), length(b) from test.t5 order by a;
950 left(b,1) length(b)
951 B 10000
952 E 10000
953 T 10000
954 Check that Primary Slave has stopped
955 include/wait_for_slave_sql_error.inc [errno=1296]
956 Restart Primary Slave
957 set global sql_replica_skip_counter=1;
958 START SLAVE;
959 Warnings:
960 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
961 Restart Secondary Slave
962 START SLAVE;
963 Warnings:
964 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
965 flush logs;
966 drop table test.t3;
967 drop table test.t4;
968 drop table test.t5;
969 Fix misalignment caused above
970 Makes following result sets look nicer
971 update test.t1 set b=2 where a=2;
972 Show a simple row-level delete-delete conflict
973 PRIMARY:
974 insert into test.t1 values (77, "A good year");
975 select * from test.t1 order by a;
976 a b
977 1 11
978 2 2
979 3 3
980 4 4
981 5 5
982 6 6
983 77 A good year
984 SECONDARY:
985 stop slave;
986 Warnings:
987 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
988 PRIMARY:
989 Primary Delete
990 delete from test.t1 where a=77;
991 Show Primary contents
992 select * from test.t1 order by a;
993 a b
994 1 11
995 2 2
996 3 3
997 4 4
998 5 5
999 6 6
1000 SECONDARY:
1001 select * from test.t1 order by a;
1002 a b
1003 1 11
1004 2 2
1005 3 3
1006 4 4
1007 5 5
1008 6 6
1009 77 A good year
1010 Secondary Delete->Insert in separate transactions
1011 and potentially different epochs
1012 Delete part with related transaction op
1013 begin;
1014 delete from test.t1 where a=77;
1015 insert into test.t1 values (73, "A superb year");
1016 commit;
1017 Unrelated transaction from Secondary
1018 begin;
1019 insert into test.t1 values (84, "A good vintage");
1020 commit;
1021 Optionally flush logs
1022 Following Insert from Secondary - potentially in different epoch
1023 begin;
1024 insert into test.t1 values (77, "Exceptional");
1025 insert into test.t1 values (80, "Poor");
1026 commit;
1027 Show Secondary contents
1028 select * from test.t1 order by a;
1029 a b
1030 1 11
1031 2 2
1032 3 3
1033 4 4
1034 5 5
1035 6 6
1036 73 A superb year
1037 77 Exceptional
1038 80 Poor
1039 84 A good vintage
1040 Allow propagation to Primary
1041 PRIMARY:
1042 Expect:
1043 - NDB$EPOCH_TRANS :
1044 - Will not detect delete-delete conflict, so first
1045 Secondary transaction will apply row 73.
1046 - Unrelated transaction from Secondary (84) is present
1047 - Following insert on delete-delete conflict row (77) is not in conflict,
1048 so both it and the related row from the same transaction (80) are present
1049 This applies whether the following insert is in the same or a different
1050 epoch
1051 Divergence:
1052 - Primary will have 73,84,77,80
1053 - Secondary will have 73,84,80, no 77
1054
1055 - NDB$EPOCH_TRANS2 :
1056 - Will detect delete-delete conflict, so first
1057 Secondary transaction will not apply (no row 73)
1058 77 and 73 will be refreshed (DEL)
1059 - Unrelated transaction from Secondary (84) is present and applied
1060 84 will be reflected (INS)
1061 - Following insert on delete-delete conflict row (77) will be found in
1062 conflict iff this transaction is in the same epoch as the first
1063 conflicting transaction. If it is in a different epoch then it will
1064 not be found in conflict, as there will be no state available to
1065 use.
1066 - If it is in-conflict, then 77 and 80 will be refreshed (DEL)
1067 Result on both should be :
1068 84
1069 - If it is not in-conflict, then 77 and 80 will be reflected (INS)
1070 Result on both should be :
1071 84, 77, 80
1072 No divergence
1073 set @primary_ok:=
1074 ((@epoch_trans XOR
1075 @epoch_trans2_same_epoch) XOR
1076 @epoch_trans2_diff_epoch);
1077 select @primary_ok;
1078 @primary_ok
1079 1
1080 EPOCH_TRANS has stable results independent of
1081 epoch boundaries:
1082 select * from test.t1 order by a;
1083 a b
1084 1 11
1085 2 2
1086 3 3
1087 4 4
1088 5 5
1089 6 6
1090 73 A superb year
1091 77 Exceptional
1092 80 Poor
1093 84 A good vintage
1094 ndb_conflict_trans_row_reject_count
1095 0
1096 ndb_conflict_trans_reject_count
1097 0
1098 ndb_epoch_delete_delete_count
1099 1
1100 Now sync with Secondary to show outcomes
1101 start slave;
1102 Warnings:
1103 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1104 SECONDARY:
1105
1106 set @secondary_ok:=
1107 ((@epoch_trans XOR
1108 @epoch_trans2_same_epoch) XOR
1109 @epoch_trans2_diff_epoch);
1110 select @secondary_ok;
1111 @secondary_ok
1112 1
1113 EPOCH_TRANS has stable results independent of
1114 epoch boundaries:
1115 select * from test.t1 order by a;
1116 a b
1117 1 11
1118 2 2
1119 3 3
1120 4 4
1121 5 5
1122 6 6
1123 73 A superb year
1124 80 Poor
1125 84 A good vintage
1126 PRIMARY
1127 Cleanup
1128 delete from test.t1 where a != 6;
1129 PRIMARY
1130 Transactional Delete testcase
1131 STOP slave on SECONDARY
1132 stop slave;
1133 Warnings:
1134 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1135 PRIMARY
1136 Primary updates row with pk 6
1137 update test.t1 set b="Pentland" where a=6;
1138 PRIMARY content
1139 Row 6 updated
1140 select * from test.t1 order by a;
1141 a b
1142 6 Pentland
1143 SECONDARY
1144 Transaction deleting pk 6, inserting pk 7
1145 begin;
1146 delete from test.t1 where a = 6;
1147 insert into test.t1 values (7, "Mendip");
1148 commit;
1149 Optionally flush logs
1150 Transaction deleting pk 7, inserting pk 8
1151 begin;
1152 delete from test.t1 where a = 7;
1153 insert into test.t1 values (8, "Ochil");
1154 commit;
1155 SECONDARY content
1156 Rows 6 + 7 deleted. Row 8 present.
1157 select * from test.t1 order by a;
1158 a b
1159 8 Ochil
1160 Sync SECONDARY -> PRIMARY
1161 Expected Behaviour
1162 EPOCH_TRANS
1163
1164 1. Conflict between Primary update to 6 + Secondary
1165 [Delete of 6 + Insert of 7] is detected by Primary
1166 and transaction is not applied at the Primary.
1167 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
1168 epoch updated.
1169
1170 2. Following Secondary transaction, [delete of 7
1171 + insert of 8] behaviour depends on whether it
1172 arrives in the same or a different epoch.
1173 Same epoch :
1174 Will conflict with refreshed row 7, and so will
1175 not be applied. Row 8 will be refreshed (DEL)
1176 Different epoch :
1177 Will not conflict with Not-present row 7, and so
1178 will be applied.
1179
1180 3. On the Secondary, the observed sequence will be :
1181 - Update 6 from PRIMARY : (Idempotent insert)
1182 - Refresh (INS) 6 from PRIMARY
1183 - Refresh (DEL) 7 from PRIMARY
1184 Same epoch :
1185 - Refresh (DEL) 8 from PRIMARY
1186 Different epoch :
1187 - Nothing
1188
1189 Results (no divergence) :
1190 Same epoch :
1191 PRIMARY : 6
1192 SECONDARY : 6
1193 Diff epoch :
1194 PRIMARY : 6,8
1195 SECONDARY : 6,8
1196
1197
1198 EPOCH_TRANS2
1199
1200 1. Conflict between Primary update to 6 + Secondary
1201 [Delete of 6 + Insert of 7] is detected by Primary
1202 and transaction is not applied at the Primary.
1203 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
1204 epoch updated.
1205
1206 2. Following Secondary transaction [delete of 7 +
1207 insert of 8] will conflict with the first
1208 transaction as EPOCH_TRANS2 does not allow
1209 a delete of an already deleted row.
1210 Row 7 will be refreshed (DEL) and row 8 will be
1211 refreshed (DEL)
1212
1213 3. On the Secondary, the observed sequence will be :
1214 - Update 6 from PRIMARY : (Idempotent insert)
1215 - Refresh (INS) 6 from PRIMARY
1216 - Refresh (DEL) 7 from PRIMARY
1217 - [Refresh (DEL) 7 from PRIMARY] - diff epoch
1218 - Refresh (DEL) 8 from PRIMARY
1219
1220 Results (no divergence) :
1221 PRIMARY : 6
1222 SECONDARY : 6
1223
1224 PRIMARY content
1225 set @primary_ok:=
1226 ((@epoch_trans_same_epoch XOR
1227 @epoch_trans_diff_epoch) XOR
1228 @epoch2_trans);
1229 select @primary_ok;
1230 @primary_ok
1231 1
1232 Restart SECONDARY Slave
1233 START SLAVE;
1234 Warnings:
1235 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1236 Sync PRIMARY -> SECONDARY
1237 SECONDARY content
1238 set @secondary_ok:=
1239 ((@epoch_trans_same_epoch XOR
1240 @epoch_trans_diff_epoch) XOR
1241 @epoch2_trans);
1242 select @secondary_ok;
1243 @secondary_ok
1244 1
1245 STABILISE
1246 drop table mysql.ndb_replication;
1247 drop table test.t1;
1248 drop table test.t2;
1249 flush logs;
1250 stop slave;
1251 Warnings:
1252 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1253 reset slave;
1254 Warnings:
1255 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
1256 include/rpl_end.inc