Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_conflict_epoch_trans_ext.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 (ndb$server_id int unsigned,
539 ndb$master_server_id int unsigned,
540 ndb$master_epoch bigint unsigned,
541 ndb$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 amount$old int,
547 amount$new int,
548 ndb$op_type enum('write_row','update_row', 'delete_row') not null,
549 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null,
550 ndb$orig_transid bigint unsigned not null,
551 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$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 ndb$server_id, ndb$master_server_id, auto_key, from_name, to_name, detail
640 amount$old, amount$new, ndb$op_type, ndb$cft_cause from test.transactions$EX order by ndb$orig_transid, ndb$count;
641 ndb$server_id ndb$master_server_id auto_key from_name to_name amount$old amount$new ndb$op_type ndb$cft_cause
642 1 3 1 Larry Employee-1 Payment to Employee-1 1 write_row trans_in_conflict
643 1 3 2 Larry Employee-2 Payment to Employee-2 1 write_row trans_in_conflict
644 1 3 3 Employee-2 Newsagent Buy yacht magazine 1 write_row trans_in_conflict
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 1
781 ndb_conflict_fn_epoch2_trans
782 0
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 EPOCH_TRANS has stable results independent of
1086 epoch boundaries:
1087 select * from test.t1 order by a;
1088 a b
1089 1 11
1090 2 2
1091 3 3
1092 4 4
1093 5 5
1094 6 6
1095 73 A superb year
1096 77 Exceptional
1097 80 Poor
1098 84 A good vintage
1099 ndb_conflict_trans_row_reject_count
1100 0
1101 ndb_conflict_trans_reject_count
1102 0
1103 ndb_epoch_delete_delete_count
1104 1
1105 Now sync with Secondary to show outcomes
1106 start slave;
1107 Warnings:
1108 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1109 SECONDARY:
1110
1111 set @secondary_ok:=
1112 ((@epoch_trans XOR
1113 @epoch_trans2_same_epoch) XOR
1114 @epoch_trans2_diff_epoch);
1115 select @secondary_ok;
1116 @secondary_ok
1117 1
1118 EPOCH_TRANS has stable results independent of
1119 epoch boundaries:
1120 select * from test.t1 order by a;
1121 a b
1122 1 11
1123 2 2
1124 3 3
1125 4 4
1126 5 5
1127 6 6
1128 73 A superb year
1129 80 Poor
1130 84 A good vintage
1131 PRIMARY
1132 Cleanup
1133 delete from test.t1 where a != 6;
1134 PRIMARY
1135 Transactional Delete testcase
1136 STOP slave on SECONDARY
1137 stop slave;
1138 Warnings:
1139 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1140 PRIMARY
1141 Primary updates row with pk 6
1142 update test.t1 set b="Pentland" where a=6;
1143 PRIMARY content
1144 Row 6 updated
1145 select * from test.t1 order by a;
1146 a b
1147 6 Pentland
1148 SECONDARY
1149 Transaction deleting pk 6, inserting pk 7
1150 begin;
1151 delete from test.t1 where a = 6;
1152 insert into test.t1 values (7, "Mendip");
1153 commit;
1154 Optionally flush logs
1155 Transaction deleting pk 7, inserting pk 8
1156 begin;
1157 delete from test.t1 where a = 7;
1158 insert into test.t1 values (8, "Ochil");
1159 commit;
1160 SECONDARY content
1161 Rows 6 + 7 deleted. Row 8 present.
1162 select * from test.t1 order by a;
1163 a b
1164 8 Ochil
1165 Sync SECONDARY -> PRIMARY
1166 Expected Behaviour
1167 EPOCH_TRANS
1168
1169 1. Conflict between Primary update to 6 + Secondary
1170 [Delete of 6 + Insert of 7] is detected by Primary
1171 and transaction is not applied at the Primary.
1172 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
1173 epoch updated.
1174
1175 2. Following Secondary transaction, [delete of 7
1176 + insert of 8] behaviour depends on whether it
1177 arrives in the same or a different epoch.
1178 Same epoch :
1179 Will conflict with refreshed row 7, and so will
1180 not be applied. Row 8 will be refreshed (DEL)
1181 Different epoch :
1182 Will not conflict with Not-present row 7, and so
1183 will be applied.
1184
1185 3. On the Secondary, the observed sequence will be :
1186 - Update 6 from PRIMARY : (Idempotent insert)
1187 - Refresh (INS) 6 from PRIMARY
1188 - Refresh (DEL) 7 from PRIMARY
1189 Same epoch :
1190 - Refresh (DEL) 8 from PRIMARY
1191 Different epoch :
1192 - Nothing
1193
1194 Results (no divergence) :
1195 Same epoch :
1196 PRIMARY : 6
1197 SECONDARY : 6
1198 Diff epoch :
1199 PRIMARY : 6,8
1200 SECONDARY : 6,8
1201
1202
1203 EPOCH_TRANS2
1204
1205 1. Conflict between Primary update to 6 + Secondary
1206 [Delete of 6 + Insert of 7] is detected by Primary
1207 and transaction is not applied at the Primary.
1208 Rows 6 (INS) and 7 (DEL) are refreshed. Row 6 has its
1209 epoch updated.
1210
1211 2. Following Secondary transaction [delete of 7 +
1212 insert of 8] will conflict with the first
1213 transaction as EPOCH_TRANS2 does not allow
1214 a delete of an already deleted row.
1215 Row 7 will be refreshed (DEL) and row 8 will be
1216 refreshed (DEL)
1217
1218 3. On the Secondary, the observed sequence will be :
1219 - Update 6 from PRIMARY : (Idempotent insert)
1220 - Refresh (INS) 6 from PRIMARY
1221 - Refresh (DEL) 7 from PRIMARY
1222 - [Refresh (DEL) 7 from PRIMARY] - diff epoch
1223 - Refresh (DEL) 8 from PRIMARY
1224
1225 Results (no divergence) :
1226 PRIMARY : 6
1227 SECONDARY : 6
1228
1229 PRIMARY content
1230 set @primary_ok:=
1231 ((@epoch_trans_same_epoch XOR
1232 @epoch_trans_diff_epoch) XOR
1233 @epoch2_trans);
1234 select @primary_ok;
1235 @primary_ok
1236 1
1237 Restart SECONDARY Slave
1238 START SLAVE;
1239 Warnings:
1240 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1241 Sync PRIMARY -> SECONDARY
1242 SECONDARY content
1243 set @secondary_ok:=
1244 ((@epoch_trans_same_epoch XOR
1245 @epoch_trans_diff_epoch) XOR
1246 @epoch2_trans);
1247 select @secondary_ok;
1248 @secondary_ok
1249 1
1250 STABILISE
1251 drop table mysql.ndb_replication;
1252 drop table test.t1;
1253 drop table test.t2;
1254 flush logs;
1255 stop slave;
1256 Warnings:
1257 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1258 reset slave;
1259 Warnings:
1260 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
1261 include/rpl_end.inc