Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_conflict_epoch_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 The circle is setup between Primary and Secondary
8 Setup ndb_replication and t1 exceptions table
9 Populate ndb_replication table as necessary
10 -- 0 extra gci bits
11 create table `test`.`t1$EX`
12 (ndb$server_id int unsigned,
13 ndb$master_server_id int unsigned,
14 ndb$master_epoch bigint unsigned,
15 ndb$count int unsigned,
16 a int not null,
17 b$old varchar(255),
18 b$new varchar(255),
19 d int,
20 ndb$op_type enum('write_row','update_row', 'delete_row') not null,
21 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null,
22 ndb$orig_transid bigint unsigned not null,
23 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) engine ndb;
24 Create table
25 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
26 Create other table
27 create table test.t2(a int primary key, b int) engine = ndb;
28 ----------------------------------
29 Test 1 : Basic two-way replication
30 ----------------------------------
31 insert into test.t1 values (1, "Metropole");
32 FLUSH LOGS;
33 -- Verify that Max rep epoch has been updated
34 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
35 -- Now update data on slave
36 update test.t1 set b="Favorit" where a=1;
37 -- Now check data on Primary
38 select * from test.t1 order by a;
39 a b
40 1 Favorit
41 Conflict algorithm counter diff :
42 Exception table contents
43 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
44 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
45 SELECT * FROM `t1$EX` ORDER BY ndb$count;
46 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
47 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
48 ndb$op_type ndb$cft_cause
49 -- Now perform multiple consecutive updates on the Secondary
50 update test.t1 set b="Elephant house" where a=1;
51 update test.t1 set b="Beach house" where a=1;
52 select * from test.t1 order by a;
53 a b
54 1 Beach house
55 -- Now check they've applied on the Primary
56 select * from test.t1 order by a;
57 a b
58 1 Beach house
59 Conflict algorithm counter diff :
60 Exception table contents
61 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
62 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
63 SELECT * FROM `t1$EX` ORDER BY ndb$count;
64 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
65 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
66 ndb$op_type ndb$cft_cause
67 --------------------------------------------
68 Test 2 : Normal Insert from Secondary Master
69 --------------------------------------------
70 -- Insert a new row on the Secondary
71 insert into test.t1 values (2, "Forrest");
72 -- Check it exists on the Primary
73 select * from test.t1 order by a;
74 a b
75 1 Beach house
76 2 Forrest
77 -- Update from the Secondary
78 update test.t1 set b="Reds" where a=2;
79 select * from test.t1 order by a;
80 a b
81 1 Beach house
82 2 Reds
83 delete from test.t1 where a=2;
84 select * from test.t1 order by a;
85 a b
86 1 Beach house
87 Conflict algorithm counter diff :
88 Exception table contents
89 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
90 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
91 SELECT * FROM `t1$EX` ORDER BY ndb$count;
92 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
93 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
94 ndb$op_type ndb$cft_cause
95 -------------------------------
96 Test 3 : Insert-Insert conflict
97 -------------------------------
98 stop slave;
99 Warnings:
100 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
101 -- Insert a row on the Primary Master
102 insert into test.t1 values (2, "Loopy Lornas");
103 -- Insert a row on the secondary Master
104 insert into test.t1 values (2, "Cloisters");
105 -- Examine data on Primary Master (should be unaffected)
106 select * from test.t1 order by a;
107 a b
108 1 Beach house
109 2 Loopy Lornas
110 -- Examine conflict indicators on Primary Master
111 Conflict algorithm counter diff :
112 ndb_conflict_fn_epoch
113 1
114 Exception table contents
115 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
116 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
117 1 3 # 1 # # # #
118 SELECT * FROM `t1$EX` ORDER BY ndb$count;
119 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
120 1 3 # # 2 NULL Cloisters # # # #
121 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
122 ndb$op_type ndb$cft_cause
123 write_row row_already_exists
124 -- Examine data on isolated secondary Master (should be as-set)
125 select * from test.t1 order by a;
126 a b
127 1 Beach house
128 2 Cloisters
129 -- Restart secondary Masters slave
130 start slave;
131 Warnings:
132 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
133 -- Reexamine secondary Master's data (should be same as Primary Masters)
134 select * from test.t1 order by a;
135 a b
136 1 Beach house
137 2 Loopy Lornas
138 -------------------------------
139 Test 4 : Update-Update conflict
140 -------------------------------
141 -- Stop replication to secondary master
142 stop slave;
143 Warnings:
144 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
145 -- Update row on Primary Master
146 update test.t1 set b="Peters Yard" where a=2;
147 -- Show data on Primary Master
148 select * from test.t1 order by a;
149 a b
150 1 Beach house
151 2 Peters Yard
152 Conflict algorithm counter diff :
153 ndb_conflict_fn_epoch
154 1
155 Exception table contents
156 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
157 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
158 1 3 # 1 # # # #
159 SELECT * FROM `t1$EX` ORDER BY ndb$count;
160 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
161 1 3 # # 2 NULL Cloisters # # # #
162 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
163 ndb$op_type ndb$cft_cause
164 write_row row_already_exists
165 -- Update row on Secondary Master
166 update test.t1 set b="Toast" where a=2;
167 -- Examine data on Primary Master - should be unaffected
168 select * from test.t1 order by a;
169 a b
170 1 Beach house
171 2 Peters Yard
172 Conflict algorithm counter diff :
173 ndb_conflict_fn_epoch
174 2
175 Exception table contents
176 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
177 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
178 1 3 # 1 # # # #
179 1 3 # 2 # # # #
180 SELECT * FROM `t1$EX` ORDER BY ndb$count;
181 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
182 1 3 # # 2 NULL Cloisters # # # #
183 1 3 # # 2 Loopy Lornas Toast # # # #
184 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
185 ndb$op_type ndb$cft_cause
186 write_row row_already_exists
187 update_row data_in_conflict
188 -- Check data on secondary - should be as set
189 select * from test.t1 order by a;
190 a b
191 1 Beach house
192 2 Toast
193 -- Now restart slave, will re-align row
194 start slave;
195 Warnings:
196 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
197 -- Check that Secondary is re-aligned
198 select * from test.t1 order by a;
199 a b
200 1 Beach house
201 2 Peters Yard
202 -------------------------------
203 Test 5 : Update-Delete conflict
204 -------------------------------
205 -- Stop Secondary slave
206 stop slave;
207 Warnings:
208 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
209 -- Update on Primary Master
210 update test.t1 set b="Pear tree" where a = 2;
211 -- Delete on Secondary Master
212 delete from test.t1 where a = 2;
213 -- Examine data on Primary Master, should be unaffected
214 select * from test.t1 order by a;
215 a b
216 1 Beach house
217 2 Pear tree
218 Conflict algorithm counter diff :
219 ndb_conflict_fn_epoch
220 3
221 Exception table contents
222 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
223 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
224 1 3 # 1 # # # #
225 1 3 # 2 # # # #
226 1 3 # 3 # # # #
227 SELECT * FROM `t1$EX` ORDER BY ndb$count;
228 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
229 1 3 # # 2 NULL Cloisters # # # #
230 1 3 # # 2 Loopy Lornas Toast # # # #
231 1 3 # # 2 Peters Yard NULL # # # #
232 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
233 ndb$op_type ndb$cft_cause
234 write_row row_already_exists
235 update_row data_in_conflict
236 delete_row data_in_conflict
237 -- Examine data on Secondary Master before slave restart, still missing
238 select * from test.t1 order by a;
239 a b
240 1 Beach house
241 -- Restart Secondary Master slave
242 start slave;
243 Warnings:
244 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
245 -- Examine data on Secondary Master after slave restart, aligned with Master
246 select * from test.t1 order by a;
247 a b
248 1 Beach house
249 2 Pear tree
250 -------------------------------
251 Test 6 : Delete-Update conflict
252 -------------------------------
253 -- Stop Secondary slave
254 stop slave;
255 Warnings:
256 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
257 -- Delete on Primary Master
258 delete from test.t1 where a=2;
259 -- Update on Secondary Master
260 update test.t1 set b="Black pig" where a=2;
261 -- Examine data on Primary Master, should be unaffected (no row)
262 select * from test.t1 order by a;
263 a b
264 1 Beach house
265 Conflict algorithm counter diff :
266 ndb_conflict_fn_epoch
267 4
268 Exception table contents
269 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
270 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
271 1 3 # 1 # # # #
272 1 3 # 2 # # # #
273 1 3 # 3 # # # #
274 1 3 # 4 # # # #
275 SELECT * FROM `t1$EX` ORDER BY ndb$count;
276 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
277 1 3 # # 2 NULL Cloisters # # # #
278 1 3 # # 2 Loopy Lornas Toast # # # #
279 1 3 # # 2 Peters Yard NULL # # # #
280 1 3 # # 2 Pear tree Black pig # # # #
281 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
282 ndb$op_type ndb$cft_cause
283 write_row row_already_exists
284 update_row row_does_not_exist
285 update_row data_in_conflict
286 delete_row data_in_conflict
287 -- Examine data on Secondary Master before slave restart, should be as inserted
288 select * from test.t1 order by a;
289 a b
290 1 Beach house
291 2 Black pig
292 -- Restart Secondary Master slave
293 start slave;
294 Warnings:
295 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
296 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
297 select * from test.t1 order by a;
298 a b
299 1 Beach house
300 -------------------------------
301 Test 7 : Delete-Delete conflict
302 -------------------------------
303 -- Stop Secondary slave
304 stop slave;
305 Warnings:
306 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
307 -- Delete on Primary Master
308 delete from test.t1 where a=1;
309 -- Delete on Secondary Master
310 delete from test.t1 where a=1;
311 -- Examine data on Primary Master, no row
312 select * from test.t1 order by a;
313 a b
314 Conflict algorithm counter diff :
315 ndb_conflict_fn_epoch
316 5
317 ndb_epoch_delete_delete
318 1
319 Exception table contents
320 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
321 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
322 1 3 # 1 # # # #
323 1 3 # 2 # # # #
324 1 3 # 3 # # # #
325 1 3 # 4 # # # #
326 1 3 # 5 # # # #
327 SELECT * FROM `t1$EX` ORDER BY ndb$count;
328 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
329 1 3 # # 2 NULL Cloisters # # # #
330 1 3 # # 2 Loopy Lornas Toast # # # #
331 1 3 # # 2 Peters Yard NULL # # # #
332 1 3 # # 2 Pear tree Black pig # # # #
333 1 3 # # 1 Beach house NULL # # # #
334 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
335 ndb$op_type ndb$cft_cause
336 write_row row_already_exists
337 update_row row_does_not_exist
338 update_row data_in_conflict
339 delete_row row_does_not_exist
340 delete_row data_in_conflict
341 -- Examine data on Secondary Master before slave restart, no row
342 select * from test.t1 order by a;
343 a b
344 -- Restart Secondary Master slave
345 start slave;
346 Warnings:
347 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
348 -- Examine data on Secondary Master after slave restart, no row
349 select * from test.t1 order by a;
350 a b
351 ------------------------------------------------
352 Test 8 : Delete-Delete, Insert conflict exposure
353 ------------------------------------------------
354 -- Insert a row on Secondary Master
355 insert into test.t1 values (3, "Espy");
356 -- Check it's present on Primary Master
357 select * from test.t1 order by a;
358 a b
359 3 Espy
360 -- Stop replication in both directions
361 stop slave;
362 Warnings:
363 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
364 stop slave;
365 Warnings:
366 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
367 -- Delete row from both clusters
368 delete from test.t1 where a=3;
369 delete from test.t1 where a=3;
370 -- Follow up with Insert from Secondary master
371 insert into test.t1 values (3, "Dalriada");
372 -- Restart replication in both directions
373 start slave;
374 Warnings:
375 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
376 start slave;
377 Warnings:
378 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
379 -- Check data on both sites
380 -- diverged with NDB$EPOCH
381 -- consistent with NDB$EPOCH2
382 -- Secondary master :
383 select * from test.t1 order by a;
384 a b
385 -- Primary master :
386 select * from test.t1 order by a;
387 a b
388 3 Dalriada
389 --Remove extra row
390 delete from test.t1 where a=3;
391 -- Note that Delete-Delete conflict detected below
392 Conflict algorithm counter diff :
393 ndb_conflict_fn_epoch
394 6
395 ndb_epoch_delete_delete
396 2
397 Exception table contents
398 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
399 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
400 1 3 # 1 # # # #
401 1 3 # 2 # # # #
402 1 3 # 3 # # # #
403 1 3 # 4 # # # #
404 1 3 # 5 # # # #
405 1 3 # 6 # # # #
406 SELECT * FROM `t1$EX` ORDER BY ndb$count;
407 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
408 1 3 # # 2 NULL Cloisters # # # #
409 1 3 # # 2 Loopy Lornas Toast # # # #
410 1 3 # # 2 Peters Yard NULL # # # #
411 1 3 # # 2 Pear tree Black pig # # # #
412 1 3 # # 1 Beach house NULL # # # #
413 1 3 # # 3 Espy NULL # # # #
414 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
415 ndb$op_type ndb$cft_cause
416 write_row row_already_exists
417 update_row row_does_not_exist
418 update_row data_in_conflict
419 delete_row row_does_not_exist
420 delete_row row_does_not_exist
421 delete_row data_in_conflict
422 ------------------------------------------------
423 Test 9 : Insert, Insert-Update-Delete conflict
424 ------------------------------------------------
425 -- Stop replication on Secondary Master
426 stop slave;
427 Warnings:
428 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
429 -- Insert row on Primary Master
430 insert into test.t1 values (4, "Haymarket");
431 -- Insert row on Secondary Master
432 insert into test.t1 values (4, "Outhouse");
433 -- Update row on Secondary Master
434 update test.t1 set b="Mathers" where a = 4;
435 -- Delete row on Secondary Master
436 delete from test.t1 where a=4;
437 -- Examine data (none) on Secondary Master
438 select * from test.t1 order by a;
439 a b
440 -- Examine data on Primary Master, should be unaffected
441 select * from test.t1 order by a;
442 a b
443 4 Haymarket
444 Conflict algorithm counter diff :
445 ndb_conflict_fn_epoch
446 9
447 ndb_epoch_delete_delete
448 2
449 Exception table contents
450 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
451 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
452 1 3 # 1 # # # #
453 1 3 # 2 # # # #
454 1 3 # 3 # # # #
455 1 3 # 4 # # # #
456 1 3 # 5 # # # #
457 1 3 # 6 # # # #
458 1 3 # 7 # # # #
459 1 3 # 8 # # # #
460 1 3 # 9 # # # #
461 SELECT * FROM `t1$EX` ORDER BY ndb$count;
462 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
463 1 3 # # 2 NULL Cloisters # # # #
464 1 3 # # 2 Loopy Lornas Toast # # # #
465 1 3 # # 2 Peters Yard NULL # # # #
466 1 3 # # 2 Pear tree Black pig # # # #
467 1 3 # # 1 Beach house NULL # # # #
468 1 3 # # 3 Espy NULL # # # #
469 1 3 # # 4 NULL Outhouse # # # #
470 1 3 # # 4 Outhouse Mathers # # # #
471 1 3 # # 4 Mathers NULL # # # #
472 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
473 ndb$op_type ndb$cft_cause
474 write_row row_already_exists
475 write_row row_already_exists
476 update_row row_does_not_exist
477 update_row data_in_conflict
478 update_row data_in_conflict
479 delete_row row_does_not_exist
480 delete_row row_does_not_exist
481 delete_row data_in_conflict
482 delete_row data_in_conflict
483 -- Examine data on Secondary Master (none)
484 select * from test.t1 order by a;
485 a b
486 -- Restart Secondary Master's slave
487 start slave;
488 Warnings:
489 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
490 -- Check data on Secondary Master, should be same as Primary Master
491 select * from test.t1;
492 a b
493 4 Haymarket
494 ------------------------------------------------
495 Test 10 : Update, Delete-Insert-Update conflict
496 ------------------------------------------------
497 -- Stop replication on Secondary Master
498 stop slave;
499 Warnings:
500 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
501 -- Update row on Primary Master
502 update test.t1 set b="Blind poet" where a=4;
503 -- Delete row on Secondary Master
504 delete from test.t1 where a=4;
505 -- Insert row on Secondary Master
506 insert into test.t1 values (4, "Drouthy Neebors");
507 -- Update row on Secondary Master
508 update test.t1 set b="The Tankard" where a=4;
509 -- Check data on Primary Master, should be unaffected
510 select * from test.t1 order by a;
511 a b
512 4 Blind poet
513 Conflict algorithm counter diff :
514 ndb_conflict_fn_epoch
515 12
516 ndb_epoch_delete_delete
517 2
518 Exception table contents
519 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
520 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
521 1 3 # 1 # # # #
522 1 3 # 2 # # # #
523 1 3 # 3 # # # #
524 1 3 # 4 # # # #
525 1 3 # 5 # # # #
526 1 3 # 6 # # # #
527 1 3 # 7 # # # #
528 1 3 # 8 # # # #
529 1 3 # 9 # # # #
530 1 3 # 10 # # # #
531 1 3 # 11 # # # #
532 1 3 # 12 # # # #
533 SELECT * FROM `t1$EX` ORDER BY ndb$count;
534 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
535 1 3 # # 2 NULL Cloisters # # # #
536 1 3 # # 2 Loopy Lornas Toast # # # #
537 1 3 # # 2 Peters Yard NULL # # # #
538 1 3 # # 2 Pear tree Black pig # # # #
539 1 3 # # 1 Beach house NULL # # # #
540 1 3 # # 3 Espy NULL # # # #
541 1 3 # # 4 NULL Outhouse # # # #
542 1 3 # # 4 Outhouse Mathers # # # #
543 1 3 # # 4 Mathers NULL # # # #
544 1 3 # # 4 Haymarket NULL # # # #
545 1 3 # # 4 NULL Drouthy Neebors # # # #
546 1 3 # # 4 Drouthy Neebors The Tankard # # # #
547 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
548 ndb$op_type ndb$cft_cause
549 write_row row_already_exists
550 write_row row_already_exists
551 write_row row_already_exists
552 update_row row_does_not_exist
553 update_row data_in_conflict
554 update_row data_in_conflict
555 update_row data_in_conflict
556 delete_row row_does_not_exist
557 delete_row row_does_not_exist
558 delete_row data_in_conflict
559 delete_row data_in_conflict
560 delete_row data_in_conflict
561 -- Check data on Secondary Master, as set
562 select * from test.t1 order by a;
563 a b
564 4 The Tankard
565 -- Restart Secondary Master slave
566 start slave;
567 Warnings:
568 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
569 -- Check data on Secondary Master - should be as Primary
570 select * from test.t1 order by a;
571 a b
572 4 Blind poet
573 ------------------------------------------------------------------------
574 Test 11 : Test Secondary insert-update-delete accepted
575 ------------------------------------------------------------------------
576 Insert row on Secondary
577 insert into test.t1 values (5, "Minders");
578 Update row on Secondary
579 update test.t1 set b="Southsider" where a=5;
580 Delete row on Secondary
581 delete from test.t1 where a=5;
582 Check data on Primary, should be none. No new conflicts
583 select * from test.t1 order by a;
584 a b
585 4 Blind poet
586 Conflict algorithm counter diff :
587 ndb_conflict_fn_epoch
588 12
589 ndb_epoch_delete_delete
590 2
591 Exception table contents
592 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
593 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
594 1 3 # 1 # # # #
595 1 3 # 2 # # # #
596 1 3 # 3 # # # #
597 1 3 # 4 # # # #
598 1 3 # 5 # # # #
599 1 3 # 6 # # # #
600 1 3 # 7 # # # #
601 1 3 # 8 # # # #
602 1 3 # 9 # # # #
603 1 3 # 10 # # # #
604 1 3 # 11 # # # #
605 1 3 # 12 # # # #
606 SELECT * FROM `t1$EX` ORDER BY ndb$count;
607 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
608 1 3 # # 2 NULL Cloisters # # # #
609 1 3 # # 2 Loopy Lornas Toast # # # #
610 1 3 # # 2 Peters Yard NULL # # # #
611 1 3 # # 2 Pear tree Black pig # # # #
612 1 3 # # 1 Beach house NULL # # # #
613 1 3 # # 3 Espy NULL # # # #
614 1 3 # # 4 NULL Outhouse # # # #
615 1 3 # # 4 Outhouse Mathers # # # #
616 1 3 # # 4 Mathers NULL # # # #
617 1 3 # # 4 Haymarket NULL # # # #
618 1 3 # # 4 NULL Drouthy Neebors # # # #
619 1 3 # # 4 Drouthy Neebors The Tankard # # # #
620 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
621 ndb$op_type ndb$cft_cause
622 write_row row_already_exists
623 write_row row_already_exists
624 write_row row_already_exists
625 update_row row_does_not_exist
626 update_row data_in_conflict
627 update_row data_in_conflict
628 update_row data_in_conflict
629 delete_row row_does_not_exist
630 delete_row row_does_not_exist
631 delete_row data_in_conflict
632 delete_row data_in_conflict
633 delete_row data_in_conflict
634 Conflict algorithm counter diff :
635 ndb_conflict_fn_epoch
636 12
637 ndb_epoch_delete_delete
638 2
639 Exception table contents
640 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
641 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
642 1 3 # 1 # # # #
643 1 3 # 2 # # # #
644 1 3 # 3 # # # #
645 1 3 # 4 # # # #
646 1 3 # 5 # # # #
647 1 3 # 6 # # # #
648 1 3 # 7 # # # #
649 1 3 # 8 # # # #
650 1 3 # 9 # # # #
651 1 3 # 10 # # # #
652 1 3 # 11 # # # #
653 1 3 # 12 # # # #
654 SELECT * FROM `t1$EX` ORDER BY ndb$count;
655 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
656 1 3 # # 2 NULL Cloisters # # # #
657 1 3 # # 2 Loopy Lornas Toast # # # #
658 1 3 # # 2 Peters Yard NULL # # # #
659 1 3 # # 2 Pear tree Black pig # # # #
660 1 3 # # 1 Beach house NULL # # # #
661 1 3 # # 3 Espy NULL # # # #
662 1 3 # # 4 NULL Outhouse # # # #
663 1 3 # # 4 Outhouse Mathers # # # #
664 1 3 # # 4 Mathers NULL # # # #
665 1 3 # # 4 Haymarket NULL # # # #
666 1 3 # # 4 NULL Drouthy Neebors # # # #
667 1 3 # # 4 Drouthy Neebors The Tankard # # # #
668 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
669 ndb$op_type ndb$cft_cause
670 write_row row_already_exists
671 write_row row_already_exists
672 write_row row_already_exists
673 update_row row_does_not_exist
674 update_row data_in_conflict
675 update_row data_in_conflict
676 update_row data_in_conflict
677 delete_row row_does_not_exist
678 delete_row row_does_not_exist
679 delete_row data_in_conflict
680 delete_row data_in_conflict
681 delete_row data_in_conflict
682 drop table test.t1;
683 drop table test.t2;
684 drop table test.t1$EX;
685 Populate ndb_replication table as necessary
686 -- 1 extra gci bits
687 create table `test`.`t1$EX`
688 (ndb$server_id int unsigned,
689 ndb$master_server_id int unsigned,
690 ndb$master_epoch bigint unsigned,
691 ndb$count int unsigned,
692 a int not null,
693 b$old varchar(255),
694 b$new varchar(255),
695 d int,
696 ndb$op_type enum('write_row','update_row', 'delete_row') not null,
697 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null,
698 ndb$orig_transid bigint unsigned not null,
699 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) engine ndb;
700 Create table
701 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
702 Create other table
703 create table test.t2(a int primary key, b int) engine = ndb;
704 ----------------------------------
705 Test 1 : Basic two-way replication
706 ----------------------------------
707 insert into test.t1 values (1, "Metropole");
708 FLUSH LOGS;
709 -- Verify that Max rep epoch has been updated
710 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
711 -- Now update data on slave
712 update test.t1 set b="Favorit" where a=1;
713 -- Now check data on Primary
714 select * from test.t1 order by a;
715 a b
716 1 Favorit
717 Conflict algorithm counter diff :
718 Exception table contents
719 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
720 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
721 SELECT * FROM `t1$EX` ORDER BY ndb$count;
722 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
723 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
724 ndb$op_type ndb$cft_cause
725 -- Now perform multiple consecutive updates on the Secondary
726 update test.t1 set b="Elephant house" where a=1;
727 update test.t1 set b="Beach house" where a=1;
728 select * from test.t1 order by a;
729 a b
730 1 Beach house
731 -- Now check they've applied on the Primary
732 select * from test.t1 order by a;
733 a b
734 1 Beach house
735 Conflict algorithm counter diff :
736 Exception table contents
737 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
738 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
739 SELECT * FROM `t1$EX` ORDER BY ndb$count;
740 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
741 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
742 ndb$op_type ndb$cft_cause
743 --------------------------------------------
744 Test 2 : Normal Insert from Secondary Master
745 --------------------------------------------
746 -- Insert a new row on the Secondary
747 insert into test.t1 values (2, "Forrest");
748 -- Check it exists on the Primary
749 select * from test.t1 order by a;
750 a b
751 1 Beach house
752 2 Forrest
753 -- Update from the Secondary
754 update test.t1 set b="Reds" where a=2;
755 select * from test.t1 order by a;
756 a b
757 1 Beach house
758 2 Reds
759 delete from test.t1 where a=2;
760 select * from test.t1 order by a;
761 a b
762 1 Beach house
763 Conflict algorithm counter diff :
764 Exception table contents
765 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
766 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
767 SELECT * FROM `t1$EX` ORDER BY ndb$count;
768 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
769 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
770 ndb$op_type ndb$cft_cause
771 -------------------------------
772 Test 3 : Insert-Insert conflict
773 -------------------------------
774 stop slave;
775 Warnings:
776 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
777 -- Insert a row on the Primary Master
778 insert into test.t1 values (2, "Loopy Lornas");
779 -- Insert a row on the secondary Master
780 insert into test.t1 values (2, "Cloisters");
781 -- Examine data on Primary Master (should be unaffected)
782 select * from test.t1 order by a;
783 a b
784 1 Beach house
785 2 Loopy Lornas
786 -- Examine conflict indicators on Primary Master
787 Conflict algorithm counter diff :
788 ndb_conflict_fn_epoch
789 1
790 Exception table contents
791 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
792 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
793 1 3 # 1 # # # #
794 SELECT * FROM `t1$EX` ORDER BY ndb$count;
795 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
796 1 3 # # 2 NULL Cloisters # # # #
797 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
798 ndb$op_type ndb$cft_cause
799 write_row row_already_exists
800 -- Examine data on isolated secondary Master (should be as-set)
801 select * from test.t1 order by a;
802 a b
803 1 Beach house
804 2 Cloisters
805 -- Restart secondary Masters slave
806 start slave;
807 Warnings:
808 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
809 -- Reexamine secondary Master's data (should be same as Primary Masters)
810 select * from test.t1 order by a;
811 a b
812 1 Beach house
813 2 Loopy Lornas
814 -------------------------------
815 Test 4 : Update-Update conflict
816 -------------------------------
817 -- Stop replication to secondary master
818 stop slave;
819 Warnings:
820 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
821 -- Update row on Primary Master
822 update test.t1 set b="Peters Yard" where a=2;
823 -- Show data on Primary Master
824 select * from test.t1 order by a;
825 a b
826 1 Beach house
827 2 Peters Yard
828 Conflict algorithm counter diff :
829 ndb_conflict_fn_epoch
830 1
831 Exception table contents
832 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
833 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
834 1 3 # 1 # # # #
835 SELECT * FROM `t1$EX` ORDER BY ndb$count;
836 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
837 1 3 # # 2 NULL Cloisters # # # #
838 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
839 ndb$op_type ndb$cft_cause
840 write_row row_already_exists
841 -- Update row on Secondary Master
842 update test.t1 set b="Toast" where a=2;
843 -- Examine data on Primary Master - should be unaffected
844 select * from test.t1 order by a;
845 a b
846 1 Beach house
847 2 Peters Yard
848 Conflict algorithm counter diff :
849 ndb_conflict_fn_epoch
850 2
851 Exception table contents
852 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
853 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
854 1 3 # 1 # # # #
855 1 3 # 2 # # # #
856 SELECT * FROM `t1$EX` ORDER BY ndb$count;
857 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
858 1 3 # # 2 NULL Cloisters # # # #
859 1 3 # # 2 Loopy Lornas Toast # # # #
860 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
861 ndb$op_type ndb$cft_cause
862 write_row row_already_exists
863 update_row data_in_conflict
864 -- Check data on secondary - should be as set
865 select * from test.t1 order by a;
866 a b
867 1 Beach house
868 2 Toast
869 -- Now restart slave, will re-align row
870 start slave;
871 Warnings:
872 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
873 -- Check that Secondary is re-aligned
874 select * from test.t1 order by a;
875 a b
876 1 Beach house
877 2 Peters Yard
878 -------------------------------
879 Test 5 : Update-Delete conflict
880 -------------------------------
881 -- Stop Secondary slave
882 stop slave;
883 Warnings:
884 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
885 -- Update on Primary Master
886 update test.t1 set b="Pear tree" where a = 2;
887 -- Delete on Secondary Master
888 delete from test.t1 where a = 2;
889 -- Examine data on Primary Master, should be unaffected
890 select * from test.t1 order by a;
891 a b
892 1 Beach house
893 2 Pear tree
894 Conflict algorithm counter diff :
895 ndb_conflict_fn_epoch
896 3
897 Exception table contents
898 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
899 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
900 1 3 # 1 # # # #
901 1 3 # 2 # # # #
902 1 3 # 3 # # # #
903 SELECT * FROM `t1$EX` ORDER BY ndb$count;
904 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
905 1 3 # # 2 NULL Cloisters # # # #
906 1 3 # # 2 Loopy Lornas Toast # # # #
907 1 3 # # 2 Peters Yard NULL # # # #
908 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
909 ndb$op_type ndb$cft_cause
910 write_row row_already_exists
911 update_row data_in_conflict
912 delete_row data_in_conflict
913 -- Examine data on Secondary Master before slave restart, still missing
914 select * from test.t1 order by a;
915 a b
916 1 Beach house
917 -- Restart Secondary Master slave
918 start slave;
919 Warnings:
920 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
921 -- Examine data on Secondary Master after slave restart, aligned with Master
922 select * from test.t1 order by a;
923 a b
924 1 Beach house
925 2 Pear tree
926 -------------------------------
927 Test 6 : Delete-Update conflict
928 -------------------------------
929 -- Stop Secondary slave
930 stop slave;
931 Warnings:
932 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
933 -- Delete on Primary Master
934 delete from test.t1 where a=2;
935 -- Update on Secondary Master
936 update test.t1 set b="Black pig" where a=2;
937 -- Examine data on Primary Master, should be unaffected (no row)
938 select * from test.t1 order by a;
939 a b
940 1 Beach house
941 Conflict algorithm counter diff :
942 ndb_conflict_fn_epoch
943 4
944 Exception table contents
945 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
946 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
947 1 3 # 1 # # # #
948 1 3 # 2 # # # #
949 1 3 # 3 # # # #
950 1 3 # 4 # # # #
951 SELECT * FROM `t1$EX` ORDER BY ndb$count;
952 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
953 1 3 # # 2 NULL Cloisters # # # #
954 1 3 # # 2 Loopy Lornas Toast # # # #
955 1 3 # # 2 Peters Yard NULL # # # #
956 1 3 # # 2 Pear tree Black pig # # # #
957 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
958 ndb$op_type ndb$cft_cause
959 write_row row_already_exists
960 update_row row_does_not_exist
961 update_row data_in_conflict
962 delete_row data_in_conflict
963 -- Examine data on Secondary Master before slave restart, should be as inserted
964 select * from test.t1 order by a;
965 a b
966 1 Beach house
967 2 Black pig
968 -- Restart Secondary Master slave
969 start slave;
970 Warnings:
971 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
972 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
973 select * from test.t1 order by a;
974 a b
975 1 Beach house
976 -------------------------------
977 Test 7 : Delete-Delete conflict
978 -------------------------------
979 -- Stop Secondary slave
980 stop slave;
981 Warnings:
982 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
983 -- Delete on Primary Master
984 delete from test.t1 where a=1;
985 -- Delete on Secondary Master
986 delete from test.t1 where a=1;
987 -- Examine data on Primary Master, no row
988 select * from test.t1 order by a;
989 a b
990 Conflict algorithm counter diff :
991 ndb_conflict_fn_epoch
992 5
993 ndb_epoch_delete_delete
994 1
995 Exception table contents
996 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
997 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
998 1 3 # 1 # # # #
999 1 3 # 2 # # # #
1000 1 3 # 3 # # # #
1001 1 3 # 4 # # # #
1002 1 3 # 5 # # # #
1003 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1004 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1005 1 3 # # 2 NULL Cloisters # # # #
1006 1 3 # # 2 Loopy Lornas Toast # # # #
1007 1 3 # # 2 Peters Yard NULL # # # #
1008 1 3 # # 2 Pear tree Black pig # # # #
1009 1 3 # # 1 Beach house NULL # # # #
1010 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1011 ndb$op_type ndb$cft_cause
1012 write_row row_already_exists
1013 update_row row_does_not_exist
1014 update_row data_in_conflict
1015 delete_row row_does_not_exist
1016 delete_row data_in_conflict
1017 -- Examine data on Secondary Master before slave restart, no row
1018 select * from test.t1 order by a;
1019 a b
1020 -- Restart Secondary Master slave
1021 start slave;
1022 Warnings:
1023 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1024 -- Examine data on Secondary Master after slave restart, no row
1025 select * from test.t1 order by a;
1026 a b
1027 ------------------------------------------------
1028 Test 8 : Delete-Delete, Insert conflict exposure
1029 ------------------------------------------------
1030 -- Insert a row on Secondary Master
1031 insert into test.t1 values (3, "Espy");
1032 -- Check it's present on Primary Master
1033 select * from test.t1 order by a;
1034 a b
1035 3 Espy
1036 -- Stop replication in both directions
1037 stop slave;
1038 Warnings:
1039 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1040 stop slave;
1041 Warnings:
1042 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1043 -- Delete row from both clusters
1044 delete from test.t1 where a=3;
1045 delete from test.t1 where a=3;
1046 -- Follow up with Insert from Secondary master
1047 insert into test.t1 values (3, "Dalriada");
1048 -- Restart replication in both directions
1049 start slave;
1050 Warnings:
1051 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1052 start slave;
1053 Warnings:
1054 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1055 -- Check data on both sites
1056 -- diverged with NDB$EPOCH
1057 -- consistent with NDB$EPOCH2
1058 -- Secondary master :
1059 select * from test.t1 order by a;
1060 a b
1061 -- Primary master :
1062 select * from test.t1 order by a;
1063 a b
1064 3 Dalriada
1065 --Remove extra row
1066 delete from test.t1 where a=3;
1067 -- Note that Delete-Delete conflict detected below
1068 Conflict algorithm counter diff :
1069 ndb_conflict_fn_epoch
1070 6
1071 ndb_epoch_delete_delete
1072 2
1073 Exception table contents
1074 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1075 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1076 1 3 # 1 # # # #
1077 1 3 # 2 # # # #
1078 1 3 # 3 # # # #
1079 1 3 # 4 # # # #
1080 1 3 # 5 # # # #
1081 1 3 # 6 # # # #
1082 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1083 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1084 1 3 # # 2 NULL Cloisters # # # #
1085 1 3 # # 2 Loopy Lornas Toast # # # #
1086 1 3 # # 2 Peters Yard NULL # # # #
1087 1 3 # # 2 Pear tree Black pig # # # #
1088 1 3 # # 1 Beach house NULL # # # #
1089 1 3 # # 3 Espy NULL # # # #
1090 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1091 ndb$op_type ndb$cft_cause
1092 write_row row_already_exists
1093 update_row row_does_not_exist
1094 update_row data_in_conflict
1095 delete_row row_does_not_exist
1096 delete_row row_does_not_exist
1097 delete_row data_in_conflict
1098 ------------------------------------------------
1099 Test 9 : Insert, Insert-Update-Delete conflict
1100 ------------------------------------------------
1101 -- Stop replication on Secondary Master
1102 stop slave;
1103 Warnings:
1104 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1105 -- Insert row on Primary Master
1106 insert into test.t1 values (4, "Haymarket");
1107 -- Insert row on Secondary Master
1108 insert into test.t1 values (4, "Outhouse");
1109 -- Update row on Secondary Master
1110 update test.t1 set b="Mathers" where a = 4;
1111 -- Delete row on Secondary Master
1112 delete from test.t1 where a=4;
1113 -- Examine data (none) on Secondary Master
1114 select * from test.t1 order by a;
1115 a b
1116 -- Examine data on Primary Master, should be unaffected
1117 select * from test.t1 order by a;
1118 a b
1119 4 Haymarket
1120 Conflict algorithm counter diff :
1121 ndb_conflict_fn_epoch
1122 9
1123 ndb_epoch_delete_delete
1124 2
1125 Exception table contents
1126 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1127 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1128 1 3 # 1 # # # #
1129 1 3 # 2 # # # #
1130 1 3 # 3 # # # #
1131 1 3 # 4 # # # #
1132 1 3 # 5 # # # #
1133 1 3 # 6 # # # #
1134 1 3 # 7 # # # #
1135 1 3 # 8 # # # #
1136 1 3 # 9 # # # #
1137 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1138 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1139 1 3 # # 2 NULL Cloisters # # # #
1140 1 3 # # 2 Loopy Lornas Toast # # # #
1141 1 3 # # 2 Peters Yard NULL # # # #
1142 1 3 # # 2 Pear tree Black pig # # # #
1143 1 3 # # 1 Beach house NULL # # # #
1144 1 3 # # 3 Espy NULL # # # #
1145 1 3 # # 4 NULL Outhouse # # # #
1146 1 3 # # 4 Outhouse Mathers # # # #
1147 1 3 # # 4 Mathers NULL # # # #
1148 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1149 ndb$op_type ndb$cft_cause
1150 write_row row_already_exists
1151 write_row row_already_exists
1152 update_row row_does_not_exist
1153 update_row data_in_conflict
1154 update_row data_in_conflict
1155 delete_row row_does_not_exist
1156 delete_row row_does_not_exist
1157 delete_row data_in_conflict
1158 delete_row data_in_conflict
1159 -- Examine data on Secondary Master (none)
1160 select * from test.t1 order by a;
1161 a b
1162 -- Restart Secondary Master's slave
1163 start slave;
1164 Warnings:
1165 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1166 -- Check data on Secondary Master, should be same as Primary Master
1167 select * from test.t1;
1168 a b
1169 4 Haymarket
1170 ------------------------------------------------
1171 Test 10 : Update, Delete-Insert-Update conflict
1172 ------------------------------------------------
1173 -- Stop replication on Secondary Master
1174 stop slave;
1175 Warnings:
1176 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1177 -- Update row on Primary Master
1178 update test.t1 set b="Blind poet" where a=4;
1179 -- Delete row on Secondary Master
1180 delete from test.t1 where a=4;
1181 -- Insert row on Secondary Master
1182 insert into test.t1 values (4, "Drouthy Neebors");
1183 -- Update row on Secondary Master
1184 update test.t1 set b="The Tankard" where a=4;
1185 -- Check data on Primary Master, should be unaffected
1186 select * from test.t1 order by a;
1187 a b
1188 4 Blind poet
1189 Conflict algorithm counter diff :
1190 ndb_conflict_fn_epoch
1191 12
1192 ndb_epoch_delete_delete
1193 2
1194 Exception table contents
1195 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1196 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1197 1 3 # 1 # # # #
1198 1 3 # 2 # # # #
1199 1 3 # 3 # # # #
1200 1 3 # 4 # # # #
1201 1 3 # 5 # # # #
1202 1 3 # 6 # # # #
1203 1 3 # 7 # # # #
1204 1 3 # 8 # # # #
1205 1 3 # 9 # # # #
1206 1 3 # 10 # # # #
1207 1 3 # 11 # # # #
1208 1 3 # 12 # # # #
1209 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1210 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1211 1 3 # # 2 NULL Cloisters # # # #
1212 1 3 # # 2 Loopy Lornas Toast # # # #
1213 1 3 # # 2 Peters Yard NULL # # # #
1214 1 3 # # 2 Pear tree Black pig # # # #
1215 1 3 # # 1 Beach house NULL # # # #
1216 1 3 # # 3 Espy NULL # # # #
1217 1 3 # # 4 NULL Outhouse # # # #
1218 1 3 # # 4 Outhouse Mathers # # # #
1219 1 3 # # 4 Mathers NULL # # # #
1220 1 3 # # 4 Haymarket NULL # # # #
1221 1 3 # # 4 NULL Drouthy Neebors # # # #
1222 1 3 # # 4 Drouthy Neebors The Tankard # # # #
1223 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1224 ndb$op_type ndb$cft_cause
1225 write_row row_already_exists
1226 write_row row_already_exists
1227 write_row row_already_exists
1228 update_row row_does_not_exist
1229 update_row data_in_conflict
1230 update_row data_in_conflict
1231 update_row data_in_conflict
1232 delete_row row_does_not_exist
1233 delete_row row_does_not_exist
1234 delete_row data_in_conflict
1235 delete_row data_in_conflict
1236 delete_row data_in_conflict
1237 -- Check data on Secondary Master, as set
1238 select * from test.t1 order by a;
1239 a b
1240 4 The Tankard
1241 -- Restart Secondary Master slave
1242 start slave;
1243 Warnings:
1244 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1245 -- Check data on Secondary Master - should be as Primary
1246 select * from test.t1 order by a;
1247 a b
1248 4 Blind poet
1249 ------------------------------------------------------------------------
1250 Test 11 : Test Secondary insert-update-delete accepted
1251 ------------------------------------------------------------------------
1252 Insert row on Secondary
1253 insert into test.t1 values (5, "Minders");
1254 Update row on Secondary
1255 update test.t1 set b="Southsider" where a=5;
1256 Delete row on Secondary
1257 delete from test.t1 where a=5;
1258 Check data on Primary, should be none. No new conflicts
1259 select * from test.t1 order by a;
1260 a b
1261 4 Blind poet
1262 Conflict algorithm counter diff :
1263 ndb_conflict_fn_epoch
1264 12
1265 ndb_epoch_delete_delete
1266 2
1267 Exception table contents
1268 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1269 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1270 1 3 # 1 # # # #
1271 1 3 # 2 # # # #
1272 1 3 # 3 # # # #
1273 1 3 # 4 # # # #
1274 1 3 # 5 # # # #
1275 1 3 # 6 # # # #
1276 1 3 # 7 # # # #
1277 1 3 # 8 # # # #
1278 1 3 # 9 # # # #
1279 1 3 # 10 # # # #
1280 1 3 # 11 # # # #
1281 1 3 # 12 # # # #
1282 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1283 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1284 1 3 # # 2 NULL Cloisters # # # #
1285 1 3 # # 2 Loopy Lornas Toast # # # #
1286 1 3 # # 2 Peters Yard NULL # # # #
1287 1 3 # # 2 Pear tree Black pig # # # #
1288 1 3 # # 1 Beach house NULL # # # #
1289 1 3 # # 3 Espy NULL # # # #
1290 1 3 # # 4 NULL Outhouse # # # #
1291 1 3 # # 4 Outhouse Mathers # # # #
1292 1 3 # # 4 Mathers NULL # # # #
1293 1 3 # # 4 Haymarket NULL # # # #
1294 1 3 # # 4 NULL Drouthy Neebors # # # #
1295 1 3 # # 4 Drouthy Neebors The Tankard # # # #
1296 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1297 ndb$op_type ndb$cft_cause
1298 write_row row_already_exists
1299 write_row row_already_exists
1300 write_row row_already_exists
1301 update_row row_does_not_exist
1302 update_row data_in_conflict
1303 update_row data_in_conflict
1304 update_row data_in_conflict
1305 delete_row row_does_not_exist
1306 delete_row row_does_not_exist
1307 delete_row data_in_conflict
1308 delete_row data_in_conflict
1309 delete_row data_in_conflict
1310 Conflict algorithm counter diff :
1311 ndb_conflict_fn_epoch
1312 12
1313 ndb_epoch_delete_delete
1314 2
1315 Exception table contents
1316 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1317 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1318 1 3 # 1 # # # #
1319 1 3 # 2 # # # #
1320 1 3 # 3 # # # #
1321 1 3 # 4 # # # #
1322 1 3 # 5 # # # #
1323 1 3 # 6 # # # #
1324 1 3 # 7 # # # #
1325 1 3 # 8 # # # #
1326 1 3 # 9 # # # #
1327 1 3 # 10 # # # #
1328 1 3 # 11 # # # #
1329 1 3 # 12 # # # #
1330 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1331 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1332 1 3 # # 2 NULL Cloisters # # # #
1333 1 3 # # 2 Loopy Lornas Toast # # # #
1334 1 3 # # 2 Peters Yard NULL # # # #
1335 1 3 # # 2 Pear tree Black pig # # # #
1336 1 3 # # 1 Beach house NULL # # # #
1337 1 3 # # 3 Espy NULL # # # #
1338 1 3 # # 4 NULL Outhouse # # # #
1339 1 3 # # 4 Outhouse Mathers # # # #
1340 1 3 # # 4 Mathers NULL # # # #
1341 1 3 # # 4 Haymarket NULL # # # #
1342 1 3 # # 4 NULL Drouthy Neebors # # # #
1343 1 3 # # 4 Drouthy Neebors The Tankard # # # #
1344 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1345 ndb$op_type ndb$cft_cause
1346 write_row row_already_exists
1347 write_row row_already_exists
1348 write_row row_already_exists
1349 update_row row_does_not_exist
1350 update_row data_in_conflict
1351 update_row data_in_conflict
1352 update_row data_in_conflict
1353 delete_row row_does_not_exist
1354 delete_row row_does_not_exist
1355 delete_row data_in_conflict
1356 delete_row data_in_conflict
1357 delete_row data_in_conflict
1358 drop table test.t1;
1359 drop table test.t2;
1360 drop table test.t1$EX;
1361 Populate ndb_replication table as necessary
1362 -- 31 extra gci bits
1363 create table `test`.`t1$EX`
1364 (ndb$server_id int unsigned,
1365 ndb$master_server_id int unsigned,
1366 ndb$master_epoch bigint unsigned,
1367 ndb$count int unsigned,
1368 a int not null,
1369 b$old varchar(255),
1370 b$new varchar(255),
1371 d int,
1372 ndb$op_type enum('write_row','update_row', 'delete_row') not null,
1373 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null,
1374 ndb$orig_transid bigint unsigned not null,
1375 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) engine ndb;
1376 Create table
1377 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
1378 Create other table
1379 create table test.t2(a int primary key, b int) engine = ndb;
1380 ----------------------------------
1381 Test 1 : Basic two-way replication
1382 ----------------------------------
1383 insert into test.t1 values (1, "Metropole");
1384 FLUSH LOGS;
1385 -- Verify that Max rep epoch has been updated
1386 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
1387 -- Now update data on slave
1388 update test.t1 set b="Favorit" where a=1;
1389 -- Now check data on Primary
1390 select * from test.t1 order by a;
1391 a b
1392 1 Favorit
1393 Conflict algorithm counter diff :
1394 Exception table contents
1395 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1396 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1397 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1398 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1399 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1400 ndb$op_type ndb$cft_cause
1401 -- Now perform multiple consecutive updates on the Secondary
1402 update test.t1 set b="Elephant house" where a=1;
1403 update test.t1 set b="Beach house" where a=1;
1404 select * from test.t1 order by a;
1405 a b
1406 1 Beach house
1407 -- Now check they've applied on the Primary
1408 select * from test.t1 order by a;
1409 a b
1410 1 Beach house
1411 Conflict algorithm counter diff :
1412 Exception table contents
1413 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1414 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1415 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1416 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1417 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1418 ndb$op_type ndb$cft_cause
1419 --------------------------------------------
1420 Test 2 : Normal Insert from Secondary Master
1421 --------------------------------------------
1422 -- Insert a new row on the Secondary
1423 insert into test.t1 values (2, "Forrest");
1424 -- Check it exists on the Primary
1425 select * from test.t1 order by a;
1426 a b
1427 1 Beach house
1428 2 Forrest
1429 -- Update from the Secondary
1430 update test.t1 set b="Reds" where a=2;
1431 select * from test.t1 order by a;
1432 a b
1433 1 Beach house
1434 2 Reds
1435 delete from test.t1 where a=2;
1436 select * from test.t1 order by a;
1437 a b
1438 1 Beach house
1439 Conflict algorithm counter diff :
1440 Exception table contents
1441 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1442 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1443 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1444 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1445 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1446 ndb$op_type ndb$cft_cause
1447 -------------------------------
1448 Test 3 : Insert-Insert conflict
1449 -------------------------------
1450 stop slave;
1451 Warnings:
1452 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1453 -- Insert a row on the Primary Master
1454 insert into test.t1 values (2, "Loopy Lornas");
1455 -- Insert a row on the secondary Master
1456 insert into test.t1 values (2, "Cloisters");
1457 -- Examine data on Primary Master (should be unaffected)
1458 select * from test.t1 order by a;
1459 a b
1460 1 Beach house
1461 2 Loopy Lornas
1462 -- Examine conflict indicators on Primary Master
1463 Conflict algorithm counter diff :
1464 ndb_conflict_fn_epoch
1465 1
1466 Exception table contents
1467 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1468 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1469 1 3 # 1 # # # #
1470 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1471 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1472 1 3 # # 2 NULL Cloisters # # # #
1473 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1474 ndb$op_type ndb$cft_cause
1475 write_row row_already_exists
1476 -- Examine data on isolated secondary Master (should be as-set)
1477 select * from test.t1 order by a;
1478 a b
1479 1 Beach house
1480 2 Cloisters
1481 -- Restart secondary Masters slave
1482 start slave;
1483 Warnings:
1484 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1485 -- Reexamine secondary Master's data (should be same as Primary Masters)
1486 select * from test.t1 order by a;
1487 a b
1488 1 Beach house
1489 2 Loopy Lornas
1490 -------------------------------
1491 Test 4 : Update-Update conflict
1492 -------------------------------
1493 -- Stop replication to secondary master
1494 stop slave;
1495 Warnings:
1496 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1497 -- Update row on Primary Master
1498 update test.t1 set b="Peters Yard" where a=2;
1499 -- Show data on Primary Master
1500 select * from test.t1 order by a;
1501 a b
1502 1 Beach house
1503 2 Peters Yard
1504 Conflict algorithm counter diff :
1505 ndb_conflict_fn_epoch
1506 1
1507 Exception table contents
1508 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1509 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1510 1 3 # 1 # # # #
1511 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1512 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1513 1 3 # # 2 NULL Cloisters # # # #
1514 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1515 ndb$op_type ndb$cft_cause
1516 write_row row_already_exists
1517 -- Update row on Secondary Master
1518 update test.t1 set b="Toast" where a=2;
1519 -- Examine data on Primary Master - should be unaffected
1520 select * from test.t1 order by a;
1521 a b
1522 1 Beach house
1523 2 Peters Yard
1524 Conflict algorithm counter diff :
1525 ndb_conflict_fn_epoch
1526 2
1527 Exception table contents
1528 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1529 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1530 1 3 # 1 # # # #
1531 1 3 # 2 # # # #
1532 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1533 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1534 1 3 # # 2 NULL Cloisters # # # #
1535 1 3 # # 2 Loopy Lornas Toast # # # #
1536 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1537 ndb$op_type ndb$cft_cause
1538 write_row row_already_exists
1539 update_row data_in_conflict
1540 -- Check data on secondary - should be as set
1541 select * from test.t1 order by a;
1542 a b
1543 1 Beach house
1544 2 Toast
1545 -- Now restart slave, will re-align row
1546 start slave;
1547 Warnings:
1548 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1549 -- Check that Secondary is re-aligned
1550 select * from test.t1 order by a;
1551 a b
1552 1 Beach house
1553 2 Peters Yard
1554 -------------------------------
1555 Test 5 : Update-Delete conflict
1556 -------------------------------
1557 -- Stop Secondary slave
1558 stop slave;
1559 Warnings:
1560 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1561 -- Update on Primary Master
1562 update test.t1 set b="Pear tree" where a = 2;
1563 -- Delete on Secondary Master
1564 delete from test.t1 where a = 2;
1565 -- Examine data on Primary Master, should be unaffected
1566 select * from test.t1 order by a;
1567 a b
1568 1 Beach house
1569 2 Pear tree
1570 Conflict algorithm counter diff :
1571 ndb_conflict_fn_epoch
1572 3
1573 Exception table contents
1574 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1575 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1576 1 3 # 1 # # # #
1577 1 3 # 2 # # # #
1578 1 3 # 3 # # # #
1579 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1580 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1581 1 3 # # 2 NULL Cloisters # # # #
1582 1 3 # # 2 Loopy Lornas Toast # # # #
1583 1 3 # # 2 Peters Yard NULL # # # #
1584 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1585 ndb$op_type ndb$cft_cause
1586 write_row row_already_exists
1587 update_row data_in_conflict
1588 delete_row data_in_conflict
1589 -- Examine data on Secondary Master before slave restart, still missing
1590 select * from test.t1 order by a;
1591 a b
1592 1 Beach house
1593 -- Restart Secondary Master slave
1594 start slave;
1595 Warnings:
1596 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1597 -- Examine data on Secondary Master after slave restart, aligned with Master
1598 select * from test.t1 order by a;
1599 a b
1600 1 Beach house
1601 2 Pear tree
1602 -------------------------------
1603 Test 6 : Delete-Update conflict
1604 -------------------------------
1605 -- Stop Secondary slave
1606 stop slave;
1607 Warnings:
1608 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1609 -- Delete on Primary Master
1610 delete from test.t1 where a=2;
1611 -- Update on Secondary Master
1612 update test.t1 set b="Black pig" where a=2;
1613 -- Examine data on Primary Master, should be unaffected (no row)
1614 select * from test.t1 order by a;
1615 a b
1616 1 Beach house
1617 Conflict algorithm counter diff :
1618 ndb_conflict_fn_epoch
1619 4
1620 Exception table contents
1621 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1622 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1623 1 3 # 1 # # # #
1624 1 3 # 2 # # # #
1625 1 3 # 3 # # # #
1626 1 3 # 4 # # # #
1627 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1628 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1629 1 3 # # 2 NULL Cloisters # # # #
1630 1 3 # # 2 Loopy Lornas Toast # # # #
1631 1 3 # # 2 Peters Yard NULL # # # #
1632 1 3 # # 2 Pear tree Black pig # # # #
1633 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1634 ndb$op_type ndb$cft_cause
1635 write_row row_already_exists
1636 update_row row_does_not_exist
1637 update_row data_in_conflict
1638 delete_row data_in_conflict
1639 -- Examine data on Secondary Master before slave restart, should be as inserted
1640 select * from test.t1 order by a;
1641 a b
1642 1 Beach house
1643 2 Black pig
1644 -- Restart Secondary Master slave
1645 start slave;
1646 Warnings:
1647 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1648 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
1649 select * from test.t1 order by a;
1650 a b
1651 1 Beach house
1652 -------------------------------
1653 Test 7 : Delete-Delete conflict
1654 -------------------------------
1655 -- Stop Secondary slave
1656 stop slave;
1657 Warnings:
1658 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1659 -- Delete on Primary Master
1660 delete from test.t1 where a=1;
1661 -- Delete on Secondary Master
1662 delete from test.t1 where a=1;
1663 -- Examine data on Primary Master, no row
1664 select * from test.t1 order by a;
1665 a b
1666 Conflict algorithm counter diff :
1667 ndb_conflict_fn_epoch
1668 5
1669 ndb_epoch_delete_delete
1670 1
1671 Exception table contents
1672 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1673 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1674 1 3 # 1 # # # #
1675 1 3 # 2 # # # #
1676 1 3 # 3 # # # #
1677 1 3 # 4 # # # #
1678 1 3 # 5 # # # #
1679 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1680 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1681 1 3 # # 2 NULL Cloisters # # # #
1682 1 3 # # 2 Loopy Lornas Toast # # # #
1683 1 3 # # 2 Peters Yard NULL # # # #
1684 1 3 # # 2 Pear tree Black pig # # # #
1685 1 3 # # 1 Beach house NULL # # # #
1686 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1687 ndb$op_type ndb$cft_cause
1688 write_row row_already_exists
1689 update_row row_does_not_exist
1690 update_row data_in_conflict
1691 delete_row row_does_not_exist
1692 delete_row data_in_conflict
1693 -- Examine data on Secondary Master before slave restart, no row
1694 select * from test.t1 order by a;
1695 a b
1696 -- Restart Secondary Master slave
1697 start slave;
1698 Warnings:
1699 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1700 -- Examine data on Secondary Master after slave restart, no row
1701 select * from test.t1 order by a;
1702 a b
1703 ------------------------------------------------
1704 Test 8 : Delete-Delete, Insert conflict exposure
1705 ------------------------------------------------
1706 -- Insert a row on Secondary Master
1707 insert into test.t1 values (3, "Espy");
1708 -- Check it's present on Primary Master
1709 select * from test.t1 order by a;
1710 a b
1711 3 Espy
1712 -- Stop replication in both directions
1713 stop slave;
1714 Warnings:
1715 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1716 stop slave;
1717 Warnings:
1718 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1719 -- Delete row from both clusters
1720 delete from test.t1 where a=3;
1721 delete from test.t1 where a=3;
1722 -- Follow up with Insert from Secondary master
1723 insert into test.t1 values (3, "Dalriada");
1724 -- Restart replication in both directions
1725 start slave;
1726 Warnings:
1727 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1728 start slave;
1729 Warnings:
1730 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1731 -- Check data on both sites
1732 -- diverged with NDB$EPOCH
1733 -- consistent with NDB$EPOCH2
1734 -- Secondary master :
1735 select * from test.t1 order by a;
1736 a b
1737 -- Primary master :
1738 select * from test.t1 order by a;
1739 a b
1740 3 Dalriada
1741 --Remove extra row
1742 delete from test.t1 where a=3;
1743 -- Note that Delete-Delete conflict detected below
1744 Conflict algorithm counter diff :
1745 ndb_conflict_fn_epoch
1746 6
1747 ndb_epoch_delete_delete
1748 2
1749 Exception table contents
1750 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1751 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1752 1 3 # 1 # # # #
1753 1 3 # 2 # # # #
1754 1 3 # 3 # # # #
1755 1 3 # 4 # # # #
1756 1 3 # 5 # # # #
1757 1 3 # 6 # # # #
1758 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1759 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1760 1 3 # # 2 NULL Cloisters # # # #
1761 1 3 # # 2 Loopy Lornas Toast # # # #
1762 1 3 # # 2 Peters Yard NULL # # # #
1763 1 3 # # 2 Pear tree Black pig # # # #
1764 1 3 # # 1 Beach house NULL # # # #
1765 1 3 # # 3 Espy NULL # # # #
1766 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1767 ndb$op_type ndb$cft_cause
1768 write_row row_already_exists
1769 update_row row_does_not_exist
1770 update_row data_in_conflict
1771 delete_row row_does_not_exist
1772 delete_row row_does_not_exist
1773 delete_row data_in_conflict
1774 ------------------------------------------------
1775 Test 9 : Insert, Insert-Update-Delete conflict
1776 ------------------------------------------------
1777 -- Stop replication on Secondary Master
1778 stop slave;
1779 Warnings:
1780 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1781 -- Insert row on Primary Master
1782 insert into test.t1 values (4, "Haymarket");
1783 -- Insert row on Secondary Master
1784 insert into test.t1 values (4, "Outhouse");
1785 -- Update row on Secondary Master
1786 update test.t1 set b="Mathers" where a = 4;
1787 -- Delete row on Secondary Master
1788 delete from test.t1 where a=4;
1789 -- Examine data (none) on Secondary Master
1790 select * from test.t1 order by a;
1791 a b
1792 -- Examine data on Primary Master, should be unaffected
1793 select * from test.t1 order by a;
1794 a b
1795 4 Haymarket
1796 Conflict algorithm counter diff :
1797 ndb_conflict_fn_epoch
1798 9
1799 ndb_epoch_delete_delete
1800 2
1801 Exception table contents
1802 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1803 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1804 1 3 # 1 # # # #
1805 1 3 # 2 # # # #
1806 1 3 # 3 # # # #
1807 1 3 # 4 # # # #
1808 1 3 # 5 # # # #
1809 1 3 # 6 # # # #
1810 1 3 # 7 # # # #
1811 1 3 # 8 # # # #
1812 1 3 # 9 # # # #
1813 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1814 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1815 1 3 # # 2 NULL Cloisters # # # #
1816 1 3 # # 2 Loopy Lornas Toast # # # #
1817 1 3 # # 2 Peters Yard NULL # # # #
1818 1 3 # # 2 Pear tree Black pig # # # #
1819 1 3 # # 1 Beach house NULL # # # #
1820 1 3 # # 3 Espy NULL # # # #
1821 1 3 # # 4 NULL Outhouse # # # #
1822 1 3 # # 4 Outhouse Mathers # # # #
1823 1 3 # # 4 Mathers NULL # # # #
1824 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1825 ndb$op_type ndb$cft_cause
1826 write_row row_already_exists
1827 write_row row_already_exists
1828 update_row row_does_not_exist
1829 update_row data_in_conflict
1830 update_row data_in_conflict
1831 delete_row row_does_not_exist
1832 delete_row row_does_not_exist
1833 delete_row data_in_conflict
1834 delete_row data_in_conflict
1835 -- Examine data on Secondary Master (none)
1836 select * from test.t1 order by a;
1837 a b
1838 -- Restart Secondary Master's slave
1839 start slave;
1840 Warnings:
1841 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1842 -- Check data on Secondary Master, should be same as Primary Master
1843 select * from test.t1;
1844 a b
1845 4 Haymarket
1846 ------------------------------------------------
1847 Test 10 : Update, Delete-Insert-Update conflict
1848 ------------------------------------------------
1849 -- Stop replication on Secondary Master
1850 stop slave;
1851 Warnings:
1852 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
1853 -- Update row on Primary Master
1854 update test.t1 set b="Blind poet" where a=4;
1855 -- Delete row on Secondary Master
1856 delete from test.t1 where a=4;
1857 -- Insert row on Secondary Master
1858 insert into test.t1 values (4, "Drouthy Neebors");
1859 -- Update row on Secondary Master
1860 update test.t1 set b="The Tankard" where a=4;
1861 -- Check data on Primary Master, should be unaffected
1862 select * from test.t1 order by a;
1863 a b
1864 4 Blind poet
1865 Conflict algorithm counter diff :
1866 ndb_conflict_fn_epoch
1867 12
1868 ndb_epoch_delete_delete
1869 2
1870 Exception table contents
1871 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1872 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1873 1 3 # 1 # # # #
1874 1 3 # 2 # # # #
1875 1 3 # 3 # # # #
1876 1 3 # 4 # # # #
1877 1 3 # 5 # # # #
1878 1 3 # 6 # # # #
1879 1 3 # 7 # # # #
1880 1 3 # 8 # # # #
1881 1 3 # 9 # # # #
1882 1 3 # 10 # # # #
1883 1 3 # 11 # # # #
1884 1 3 # 12 # # # #
1885 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1886 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1887 1 3 # # 2 NULL Cloisters # # # #
1888 1 3 # # 2 Loopy Lornas Toast # # # #
1889 1 3 # # 2 Peters Yard NULL # # # #
1890 1 3 # # 2 Pear tree Black pig # # # #
1891 1 3 # # 1 Beach house NULL # # # #
1892 1 3 # # 3 Espy NULL # # # #
1893 1 3 # # 4 NULL Outhouse # # # #
1894 1 3 # # 4 Outhouse Mathers # # # #
1895 1 3 # # 4 Mathers NULL # # # #
1896 1 3 # # 4 Haymarket NULL # # # #
1897 1 3 # # 4 NULL Drouthy Neebors # # # #
1898 1 3 # # 4 Drouthy Neebors The Tankard # # # #
1899 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1900 ndb$op_type ndb$cft_cause
1901 write_row row_already_exists
1902 write_row row_already_exists
1903 write_row row_already_exists
1904 update_row row_does_not_exist
1905 update_row data_in_conflict
1906 update_row data_in_conflict
1907 update_row data_in_conflict
1908 delete_row row_does_not_exist
1909 delete_row row_does_not_exist
1910 delete_row data_in_conflict
1911 delete_row data_in_conflict
1912 delete_row data_in_conflict
1913 -- Check data on Secondary Master, as set
1914 select * from test.t1 order by a;
1915 a b
1916 4 The Tankard
1917 -- Restart Secondary Master slave
1918 start slave;
1919 Warnings:
1920 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
1921 -- Check data on Secondary Master - should be as Primary
1922 select * from test.t1 order by a;
1923 a b
1924 4 Blind poet
1925 ------------------------------------------------------------------------
1926 Test 11 : Test Secondary insert-update-delete accepted
1927 ------------------------------------------------------------------------
1928 Insert row on Secondary
1929 insert into test.t1 values (5, "Minders");
1930 Update row on Secondary
1931 update test.t1 set b="Southsider" where a=5;
1932 Delete row on Secondary
1933 delete from test.t1 where a=5;
1934 Check data on Primary, should be none. No new conflicts
1935 select * from test.t1 order by a;
1936 a b
1937 4 Blind poet
1938 Conflict algorithm counter diff :
1939 ndb_conflict_fn_epoch
1940 12
1941 ndb_epoch_delete_delete
1942 2
1943 Exception table contents
1944 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1945 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1946 1 3 # 1 # # # #
1947 1 3 # 2 # # # #
1948 1 3 # 3 # # # #
1949 1 3 # 4 # # # #
1950 1 3 # 5 # # # #
1951 1 3 # 6 # # # #
1952 1 3 # 7 # # # #
1953 1 3 # 8 # # # #
1954 1 3 # 9 # # # #
1955 1 3 # 10 # # # #
1956 1 3 # 11 # # # #
1957 1 3 # 12 # # # #
1958 SELECT * FROM `t1$EX` ORDER BY ndb$count;
1959 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
1960 1 3 # # 2 NULL Cloisters # # # #
1961 1 3 # # 2 Loopy Lornas Toast # # # #
1962 1 3 # # 2 Peters Yard NULL # # # #
1963 1 3 # # 2 Pear tree Black pig # # # #
1964 1 3 # # 1 Beach house NULL # # # #
1965 1 3 # # 3 Espy NULL # # # #
1966 1 3 # # 4 NULL Outhouse # # # #
1967 1 3 # # 4 Outhouse Mathers # # # #
1968 1 3 # # 4 Mathers NULL # # # #
1969 1 3 # # 4 Haymarket NULL # # # #
1970 1 3 # # 4 NULL Drouthy Neebors # # # #
1971 1 3 # # 4 Drouthy Neebors The Tankard # # # #
1972 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
1973 ndb$op_type ndb$cft_cause
1974 write_row row_already_exists
1975 write_row row_already_exists
1976 write_row row_already_exists
1977 update_row row_does_not_exist
1978 update_row data_in_conflict
1979 update_row data_in_conflict
1980 update_row data_in_conflict
1981 delete_row row_does_not_exist
1982 delete_row row_does_not_exist
1983 delete_row data_in_conflict
1984 delete_row data_in_conflict
1985 delete_row data_in_conflict
1986 Conflict algorithm counter diff :
1987 ndb_conflict_fn_epoch
1988 12
1989 ndb_epoch_delete_delete
1990 2
1991 Exception table contents
1992 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
1993 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
1994 1 3 # 1 # # # #
1995 1 3 # 2 # # # #
1996 1 3 # 3 # # # #
1997 1 3 # 4 # # # #
1998 1 3 # 5 # # # #
1999 1 3 # 6 # # # #
2000 1 3 # 7 # # # #
2001 1 3 # 8 # # # #
2002 1 3 # 9 # # # #
2003 1 3 # 10 # # # #
2004 1 3 # 11 # # # #
2005 1 3 # 12 # # # #
2006 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2007 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2008 1 3 # # 2 NULL Cloisters # # # #
2009 1 3 # # 2 Loopy Lornas Toast # # # #
2010 1 3 # # 2 Peters Yard NULL # # # #
2011 1 3 # # 2 Pear tree Black pig # # # #
2012 1 3 # # 1 Beach house NULL # # # #
2013 1 3 # # 3 Espy NULL # # # #
2014 1 3 # # 4 NULL Outhouse # # # #
2015 1 3 # # 4 Outhouse Mathers # # # #
2016 1 3 # # 4 Mathers NULL # # # #
2017 1 3 # # 4 Haymarket NULL # # # #
2018 1 3 # # 4 NULL Drouthy Neebors # # # #
2019 1 3 # # 4 Drouthy Neebors The Tankard # # # #
2020 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2021 ndb$op_type ndb$cft_cause
2022 write_row row_already_exists
2023 write_row row_already_exists
2024 write_row row_already_exists
2025 update_row row_does_not_exist
2026 update_row data_in_conflict
2027 update_row data_in_conflict
2028 update_row data_in_conflict
2029 delete_row row_does_not_exist
2030 delete_row row_does_not_exist
2031 delete_row data_in_conflict
2032 delete_row data_in_conflict
2033 delete_row data_in_conflict
2034 drop table test.t1;
2035 drop table test.t2;
2036 drop table test.t1$EX;
2037 Populate ndb_replication table as necessary
2038 -- Default extra Gci bits
2039 create table `test`.`t1$EX`
2040 (ndb$server_id int unsigned,
2041 ndb$master_server_id int unsigned,
2042 ndb$master_epoch bigint unsigned,
2043 ndb$count int unsigned,
2044 a int not null,
2045 b$old varchar(255),
2046 b$new varchar(255),
2047 d int,
2048 ndb$op_type enum('write_row','update_row', 'delete_row') not null,
2049 ndb$cft_cause enum('row_does_not_exist','row_already_exists','data_in_conflict','trans_in_conflict') not null,
2050 ndb$orig_transid bigint unsigned not null,
2051 primary key(ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count)) engine ndb;
2052 Create table
2053 create table test.t1(a int primary key, b varchar(255)) engine = ndb;
2054 Create other table
2055 create table test.t2(a int primary key, b int) engine = ndb;
2056 ----------------------------------
2057 Test 1 : Basic two-way replication
2058 ----------------------------------
2059 insert into test.t1 values (1, "Metropole");
2060 FLUSH LOGS;
2061 -- Verify that Max rep epoch has been updated
2062 -- Primary Cluster Max Rep Epoch now beyond the original update epoch
2063 -- Now update data on slave
2064 update test.t1 set b="Favorit" where a=1;
2065 -- Now check data on Primary
2066 select * from test.t1 order by a;
2067 a b
2068 1 Favorit
2069 Conflict algorithm counter diff :
2070 Exception table contents
2071 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2072 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2073 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2074 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2075 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2076 ndb$op_type ndb$cft_cause
2077 -- Now perform multiple consecutive updates on the Secondary
2078 update test.t1 set b="Elephant house" where a=1;
2079 update test.t1 set b="Beach house" where a=1;
2080 select * from test.t1 order by a;
2081 a b
2082 1 Beach house
2083 -- Now check they've applied on the Primary
2084 select * from test.t1 order by a;
2085 a b
2086 1 Beach house
2087 Conflict algorithm counter diff :
2088 Exception table contents
2089 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2090 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2091 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2092 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2093 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2094 ndb$op_type ndb$cft_cause
2095 --------------------------------------------
2096 Test 2 : Normal Insert from Secondary Master
2097 --------------------------------------------
2098 -- Insert a new row on the Secondary
2099 insert into test.t1 values (2, "Forrest");
2100 -- Check it exists on the Primary
2101 select * from test.t1 order by a;
2102 a b
2103 1 Beach house
2104 2 Forrest
2105 -- Update from the Secondary
2106 update test.t1 set b="Reds" where a=2;
2107 select * from test.t1 order by a;
2108 a b
2109 1 Beach house
2110 2 Reds
2111 delete from test.t1 where a=2;
2112 select * from test.t1 order by a;
2113 a b
2114 1 Beach house
2115 Conflict algorithm counter diff :
2116 Exception table contents
2117 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2118 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2119 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2120 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2121 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2122 ndb$op_type ndb$cft_cause
2123 -------------------------------
2124 Test 3 : Insert-Insert conflict
2125 -------------------------------
2126 stop slave;
2127 Warnings:
2128 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2129 -- Insert a row on the Primary Master
2130 insert into test.t1 values (2, "Loopy Lornas");
2131 -- Insert a row on the secondary Master
2132 insert into test.t1 values (2, "Cloisters");
2133 -- Examine data on Primary Master (should be unaffected)
2134 select * from test.t1 order by a;
2135 a b
2136 1 Beach house
2137 2 Loopy Lornas
2138 -- Examine conflict indicators on Primary Master
2139 Conflict algorithm counter diff :
2140 ndb_conflict_fn_epoch
2141 1
2142 Exception table contents
2143 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2144 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2145 1 3 # 1 # # # #
2146 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2147 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2148 1 3 # # 2 NULL Cloisters # # # #
2149 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2150 ndb$op_type ndb$cft_cause
2151 write_row row_already_exists
2152 -- Examine data on isolated secondary Master (should be as-set)
2153 select * from test.t1 order by a;
2154 a b
2155 1 Beach house
2156 2 Cloisters
2157 -- Restart secondary Masters slave
2158 start slave;
2159 Warnings:
2160 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2161 -- Reexamine secondary Master's data (should be same as Primary Masters)
2162 select * from test.t1 order by a;
2163 a b
2164 1 Beach house
2165 2 Loopy Lornas
2166 -------------------------------
2167 Test 4 : Update-Update conflict
2168 -------------------------------
2169 -- Stop replication to secondary master
2170 stop slave;
2171 Warnings:
2172 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2173 -- Update row on Primary Master
2174 update test.t1 set b="Peters Yard" where a=2;
2175 -- Show data on Primary Master
2176 select * from test.t1 order by a;
2177 a b
2178 1 Beach house
2179 2 Peters Yard
2180 Conflict algorithm counter diff :
2181 ndb_conflict_fn_epoch
2182 1
2183 Exception table contents
2184 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2185 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2186 1 3 # 1 # # # #
2187 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2188 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2189 1 3 # # 2 NULL Cloisters # # # #
2190 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2191 ndb$op_type ndb$cft_cause
2192 write_row row_already_exists
2193 -- Update row on Secondary Master
2194 update test.t1 set b="Toast" where a=2;
2195 -- Examine data on Primary Master - should be unaffected
2196 select * from test.t1 order by a;
2197 a b
2198 1 Beach house
2199 2 Peters Yard
2200 Conflict algorithm counter diff :
2201 ndb_conflict_fn_epoch
2202 2
2203 Exception table contents
2204 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2205 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2206 1 3 # 1 # # # #
2207 1 3 # 2 # # # #
2208 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2209 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2210 1 3 # # 2 NULL Cloisters # # # #
2211 1 3 # # 2 Loopy Lornas Toast # # # #
2212 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2213 ndb$op_type ndb$cft_cause
2214 write_row row_already_exists
2215 update_row data_in_conflict
2216 -- Check data on secondary - should be as set
2217 select * from test.t1 order by a;
2218 a b
2219 1 Beach house
2220 2 Toast
2221 -- Now restart slave, will re-align row
2222 start slave;
2223 Warnings:
2224 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2225 -- Check that Secondary is re-aligned
2226 select * from test.t1 order by a;
2227 a b
2228 1 Beach house
2229 2 Peters Yard
2230 -------------------------------
2231 Test 5 : Update-Delete conflict
2232 -------------------------------
2233 -- Stop Secondary slave
2234 stop slave;
2235 Warnings:
2236 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2237 -- Update on Primary Master
2238 update test.t1 set b="Pear tree" where a = 2;
2239 -- Delete on Secondary Master
2240 delete from test.t1 where a = 2;
2241 -- Examine data on Primary Master, should be unaffected
2242 select * from test.t1 order by a;
2243 a b
2244 1 Beach house
2245 2 Pear tree
2246 Conflict algorithm counter diff :
2247 ndb_conflict_fn_epoch
2248 3
2249 Exception table contents
2250 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2251 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2252 1 3 # 1 # # # #
2253 1 3 # 2 # # # #
2254 1 3 # 3 # # # #
2255 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2256 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2257 1 3 # # 2 NULL Cloisters # # # #
2258 1 3 # # 2 Loopy Lornas Toast # # # #
2259 1 3 # # 2 Peters Yard NULL # # # #
2260 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2261 ndb$op_type ndb$cft_cause
2262 write_row row_already_exists
2263 update_row data_in_conflict
2264 delete_row data_in_conflict
2265 -- Examine data on Secondary Master before slave restart, still missing
2266 select * from test.t1 order by a;
2267 a b
2268 1 Beach house
2269 -- Restart Secondary Master slave
2270 start slave;
2271 Warnings:
2272 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2273 -- Examine data on Secondary Master after slave restart, aligned with Master
2274 select * from test.t1 order by a;
2275 a b
2276 1 Beach house
2277 2 Pear tree
2278 -------------------------------
2279 Test 6 : Delete-Update conflict
2280 -------------------------------
2281 -- Stop Secondary slave
2282 stop slave;
2283 Warnings:
2284 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2285 -- Delete on Primary Master
2286 delete from test.t1 where a=2;
2287 -- Update on Secondary Master
2288 update test.t1 set b="Black pig" where a=2;
2289 -- Examine data on Primary Master, should be unaffected (no row)
2290 select * from test.t1 order by a;
2291 a b
2292 1 Beach house
2293 Conflict algorithm counter diff :
2294 ndb_conflict_fn_epoch
2295 4
2296 Exception table contents
2297 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2298 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2299 1 3 # 1 # # # #
2300 1 3 # 2 # # # #
2301 1 3 # 3 # # # #
2302 1 3 # 4 # # # #
2303 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2304 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2305 1 3 # # 2 NULL Cloisters # # # #
2306 1 3 # # 2 Loopy Lornas Toast # # # #
2307 1 3 # # 2 Peters Yard NULL # # # #
2308 1 3 # # 2 Pear tree Black pig # # # #
2309 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2310 ndb$op_type ndb$cft_cause
2311 write_row row_already_exists
2312 update_row row_does_not_exist
2313 update_row data_in_conflict
2314 delete_row data_in_conflict
2315 -- Examine data on Secondary Master before slave restart, should be as inserted
2316 select * from test.t1 order by a;
2317 a b
2318 1 Beach house
2319 2 Black pig
2320 -- Restart Secondary Master slave
2321 start slave;
2322 Warnings:
2323 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2324 -- Examine data on Secondary Master after slave restart, aligned with Master (deleted)
2325 select * from test.t1 order by a;
2326 a b
2327 1 Beach house
2328 -------------------------------
2329 Test 7 : Delete-Delete conflict
2330 -------------------------------
2331 -- Stop Secondary slave
2332 stop slave;
2333 Warnings:
2334 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2335 -- Delete on Primary Master
2336 delete from test.t1 where a=1;
2337 -- Delete on Secondary Master
2338 delete from test.t1 where a=1;
2339 -- Examine data on Primary Master, no row
2340 select * from test.t1 order by a;
2341 a b
2342 Conflict algorithm counter diff :
2343 ndb_conflict_fn_epoch
2344 5
2345 ndb_epoch_delete_delete
2346 1
2347 Exception table contents
2348 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2349 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2350 1 3 # 1 # # # #
2351 1 3 # 2 # # # #
2352 1 3 # 3 # # # #
2353 1 3 # 4 # # # #
2354 1 3 # 5 # # # #
2355 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2356 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2357 1 3 # # 2 NULL Cloisters # # # #
2358 1 3 # # 2 Loopy Lornas Toast # # # #
2359 1 3 # # 2 Peters Yard NULL # # # #
2360 1 3 # # 2 Pear tree Black pig # # # #
2361 1 3 # # 1 Beach house NULL # # # #
2362 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2363 ndb$op_type ndb$cft_cause
2364 write_row row_already_exists
2365 update_row row_does_not_exist
2366 update_row data_in_conflict
2367 delete_row row_does_not_exist
2368 delete_row data_in_conflict
2369 -- Examine data on Secondary Master before slave restart, no row
2370 select * from test.t1 order by a;
2371 a b
2372 -- Restart Secondary Master slave
2373 start slave;
2374 Warnings:
2375 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2376 -- Examine data on Secondary Master after slave restart, no row
2377 select * from test.t1 order by a;
2378 a b
2379 ------------------------------------------------
2380 Test 8 : Delete-Delete, Insert conflict exposure
2381 ------------------------------------------------
2382 -- Insert a row on Secondary Master
2383 insert into test.t1 values (3, "Espy");
2384 -- Check it's present on Primary Master
2385 select * from test.t1 order by a;
2386 a b
2387 3 Espy
2388 -- Stop replication in both directions
2389 stop slave;
2390 Warnings:
2391 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2392 stop slave;
2393 Warnings:
2394 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2395 -- Delete row from both clusters
2396 delete from test.t1 where a=3;
2397 delete from test.t1 where a=3;
2398 -- Follow up with Insert from Secondary master
2399 insert into test.t1 values (3, "Dalriada");
2400 -- Restart replication in both directions
2401 start slave;
2402 Warnings:
2403 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2404 start slave;
2405 Warnings:
2406 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2407 -- Check data on both sites
2408 -- diverged with NDB$EPOCH
2409 -- consistent with NDB$EPOCH2
2410 -- Secondary master :
2411 select * from test.t1 order by a;
2412 a b
2413 -- Primary master :
2414 select * from test.t1 order by a;
2415 a b
2416 3 Dalriada
2417 --Remove extra row
2418 delete from test.t1 where a=3;
2419 -- Note that Delete-Delete conflict detected below
2420 Conflict algorithm counter diff :
2421 ndb_conflict_fn_epoch
2422 6
2423 ndb_epoch_delete_delete
2424 2
2425 Exception table contents
2426 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2427 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2428 1 3 # 1 # # # #
2429 1 3 # 2 # # # #
2430 1 3 # 3 # # # #
2431 1 3 # 4 # # # #
2432 1 3 # 5 # # # #
2433 1 3 # 6 # # # #
2434 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2435 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2436 1 3 # # 2 NULL Cloisters # # # #
2437 1 3 # # 2 Loopy Lornas Toast # # # #
2438 1 3 # # 2 Peters Yard NULL # # # #
2439 1 3 # # 2 Pear tree Black pig # # # #
2440 1 3 # # 1 Beach house NULL # # # #
2441 1 3 # # 3 Espy NULL # # # #
2442 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2443 ndb$op_type ndb$cft_cause
2444 write_row row_already_exists
2445 update_row row_does_not_exist
2446 update_row data_in_conflict
2447 delete_row row_does_not_exist
2448 delete_row row_does_not_exist
2449 delete_row data_in_conflict
2450 ------------------------------------------------
2451 Test 9 : Insert, Insert-Update-Delete conflict
2452 ------------------------------------------------
2453 -- Stop replication on Secondary Master
2454 stop slave;
2455 Warnings:
2456 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2457 -- Insert row on Primary Master
2458 insert into test.t1 values (4, "Haymarket");
2459 -- Insert row on Secondary Master
2460 insert into test.t1 values (4, "Outhouse");
2461 -- Update row on Secondary Master
2462 update test.t1 set b="Mathers" where a = 4;
2463 -- Delete row on Secondary Master
2464 delete from test.t1 where a=4;
2465 -- Examine data (none) on Secondary Master
2466 select * from test.t1 order by a;
2467 a b
2468 -- Examine data on Primary Master, should be unaffected
2469 select * from test.t1 order by a;
2470 a b
2471 4 Haymarket
2472 Conflict algorithm counter diff :
2473 ndb_conflict_fn_epoch
2474 9
2475 ndb_epoch_delete_delete
2476 2
2477 Exception table contents
2478 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2479 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2480 1 3 # 1 # # # #
2481 1 3 # 2 # # # #
2482 1 3 # 3 # # # #
2483 1 3 # 4 # # # #
2484 1 3 # 5 # # # #
2485 1 3 # 6 # # # #
2486 1 3 # 7 # # # #
2487 1 3 # 8 # # # #
2488 1 3 # 9 # # # #
2489 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2490 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2491 1 3 # # 2 NULL Cloisters # # # #
2492 1 3 # # 2 Loopy Lornas Toast # # # #
2493 1 3 # # 2 Peters Yard NULL # # # #
2494 1 3 # # 2 Pear tree Black pig # # # #
2495 1 3 # # 1 Beach house NULL # # # #
2496 1 3 # # 3 Espy NULL # # # #
2497 1 3 # # 4 NULL Outhouse # # # #
2498 1 3 # # 4 Outhouse Mathers # # # #
2499 1 3 # # 4 Mathers NULL # # # #
2500 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2501 ndb$op_type ndb$cft_cause
2502 write_row row_already_exists
2503 write_row row_already_exists
2504 update_row row_does_not_exist
2505 update_row data_in_conflict
2506 update_row data_in_conflict
2507 delete_row row_does_not_exist
2508 delete_row row_does_not_exist
2509 delete_row data_in_conflict
2510 delete_row data_in_conflict
2511 -- Examine data on Secondary Master (none)
2512 select * from test.t1 order by a;
2513 a b
2514 -- Restart Secondary Master's slave
2515 start slave;
2516 Warnings:
2517 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2518 -- Check data on Secondary Master, should be same as Primary Master
2519 select * from test.t1;
2520 a b
2521 4 Haymarket
2522 ------------------------------------------------
2523 Test 10 : Update, Delete-Insert-Update conflict
2524 ------------------------------------------------
2525 -- Stop replication on Secondary Master
2526 stop slave;
2527 Warnings:
2528 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2529 -- Update row on Primary Master
2530 update test.t1 set b="Blind poet" where a=4;
2531 -- Delete row on Secondary Master
2532 delete from test.t1 where a=4;
2533 -- Insert row on Secondary Master
2534 insert into test.t1 values (4, "Drouthy Neebors");
2535 -- Update row on Secondary Master
2536 update test.t1 set b="The Tankard" where a=4;
2537 -- Check data on Primary Master, should be unaffected
2538 select * from test.t1 order by a;
2539 a b
2540 4 Blind poet
2541 Conflict algorithm counter diff :
2542 ndb_conflict_fn_epoch
2543 12
2544 ndb_epoch_delete_delete
2545 2
2546 Exception table contents
2547 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2548 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2549 1 3 # 1 # # # #
2550 1 3 # 2 # # # #
2551 1 3 # 3 # # # #
2552 1 3 # 4 # # # #
2553 1 3 # 5 # # # #
2554 1 3 # 6 # # # #
2555 1 3 # 7 # # # #
2556 1 3 # 8 # # # #
2557 1 3 # 9 # # # #
2558 1 3 # 10 # # # #
2559 1 3 # 11 # # # #
2560 1 3 # 12 # # # #
2561 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2562 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2563 1 3 # # 2 NULL Cloisters # # # #
2564 1 3 # # 2 Loopy Lornas Toast # # # #
2565 1 3 # # 2 Peters Yard NULL # # # #
2566 1 3 # # 2 Pear tree Black pig # # # #
2567 1 3 # # 1 Beach house NULL # # # #
2568 1 3 # # 3 Espy NULL # # # #
2569 1 3 # # 4 NULL Outhouse # # # #
2570 1 3 # # 4 Outhouse Mathers # # # #
2571 1 3 # # 4 Mathers NULL # # # #
2572 1 3 # # 4 Haymarket NULL # # # #
2573 1 3 # # 4 NULL Drouthy Neebors # # # #
2574 1 3 # # 4 Drouthy Neebors The Tankard # # # #
2575 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2576 ndb$op_type ndb$cft_cause
2577 write_row row_already_exists
2578 write_row row_already_exists
2579 write_row row_already_exists
2580 update_row row_does_not_exist
2581 update_row data_in_conflict
2582 update_row data_in_conflict
2583 update_row data_in_conflict
2584 delete_row row_does_not_exist
2585 delete_row row_does_not_exist
2586 delete_row data_in_conflict
2587 delete_row data_in_conflict
2588 delete_row data_in_conflict
2589 -- Check data on Secondary Master, as set
2590 select * from test.t1 order by a;
2591 a b
2592 4 The Tankard
2593 -- Restart Secondary Master slave
2594 start slave;
2595 Warnings:
2596 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2597 -- Check data on Secondary Master - should be as Primary
2598 select * from test.t1 order by a;
2599 a b
2600 4 Blind poet
2601 ------------------------------------------------------------------------
2602 Test 11 : Test Secondary insert-update-delete accepted
2603 ------------------------------------------------------------------------
2604 Insert row on Secondary
2605 insert into test.t1 values (5, "Minders");
2606 Update row on Secondary
2607 update test.t1 set b="Southsider" where a=5;
2608 Delete row on Secondary
2609 delete from test.t1 where a=5;
2610 Check data on Primary, should be none. No new conflicts
2611 select * from test.t1 order by a;
2612 a b
2613 4 Blind poet
2614 Conflict algorithm counter diff :
2615 ndb_conflict_fn_epoch
2616 12
2617 ndb_epoch_delete_delete
2618 2
2619 Exception table contents
2620 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2621 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2622 1 3 # 1 # # # #
2623 1 3 # 2 # # # #
2624 1 3 # 3 # # # #
2625 1 3 # 4 # # # #
2626 1 3 # 5 # # # #
2627 1 3 # 6 # # # #
2628 1 3 # 7 # # # #
2629 1 3 # 8 # # # #
2630 1 3 # 9 # # # #
2631 1 3 # 10 # # # #
2632 1 3 # 11 # # # #
2633 1 3 # 12 # # # #
2634 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2635 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2636 1 3 # # 2 NULL Cloisters # # # #
2637 1 3 # # 2 Loopy Lornas Toast # # # #
2638 1 3 # # 2 Peters Yard NULL # # # #
2639 1 3 # # 2 Pear tree Black pig # # # #
2640 1 3 # # 1 Beach house NULL # # # #
2641 1 3 # # 3 Espy NULL # # # #
2642 1 3 # # 4 NULL Outhouse # # # #
2643 1 3 # # 4 Outhouse Mathers # # # #
2644 1 3 # # 4 Mathers NULL # # # #
2645 1 3 # # 4 Haymarket NULL # # # #
2646 1 3 # # 4 NULL Drouthy Neebors # # # #
2647 1 3 # # 4 Drouthy Neebors The Tankard # # # #
2648 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2649 ndb$op_type ndb$cft_cause
2650 write_row row_already_exists
2651 write_row row_already_exists
2652 write_row row_already_exists
2653 update_row row_does_not_exist
2654 update_row data_in_conflict
2655 update_row data_in_conflict
2656 update_row data_in_conflict
2657 delete_row row_does_not_exist
2658 delete_row row_does_not_exist
2659 delete_row data_in_conflict
2660 delete_row data_in_conflict
2661 delete_row data_in_conflict
2662 Conflict algorithm counter diff :
2663 ndb_conflict_fn_epoch
2664 12
2665 ndb_epoch_delete_delete
2666 2
2667 Exception table contents
2668 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2669 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d
2670 1 3 # 1 # # # #
2671 1 3 # 2 # # # #
2672 1 3 # 3 # # # #
2673 1 3 # 4 # # # #
2674 1 3 # 5 # # # #
2675 1 3 # 6 # # # #
2676 1 3 # 7 # # # #
2677 1 3 # 8 # # # #
2678 1 3 # 9 # # # #
2679 1 3 # 10 # # # #
2680 1 3 # 11 # # # #
2681 1 3 # 12 # # # #
2682 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2683 ndb$server_id ndb$master_server_id ndb$master_epoch ndb$count a b$old b$new d ndb$op_type ndb$cft_cause ndb$orig_transid
2684 1 3 # # 2 NULL Cloisters # # # #
2685 1 3 # # 2 Loopy Lornas Toast # # # #
2686 1 3 # # 2 Peters Yard NULL # # # #
2687 1 3 # # 2 Pear tree Black pig # # # #
2688 1 3 # # 1 Beach house NULL # # # #
2689 1 3 # # 3 Espy NULL # # # #
2690 1 3 # # 4 NULL Outhouse # # # #
2691 1 3 # # 4 Outhouse Mathers # # # #
2692 1 3 # # 4 Mathers NULL # # # #
2693 1 3 # # 4 Haymarket NULL # # # #
2694 1 3 # # 4 NULL Drouthy Neebors # # # #
2695 1 3 # # 4 Drouthy Neebors The Tankard # # # #
2696 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2697 ndb$op_type ndb$cft_cause
2698 write_row row_already_exists
2699 write_row row_already_exists
2700 write_row row_already_exists
2701 update_row row_does_not_exist
2702 update_row data_in_conflict
2703 update_row data_in_conflict
2704 update_row data_in_conflict
2705 delete_row row_does_not_exist
2706 delete_row row_does_not_exist
2707 delete_row data_in_conflict
2708 delete_row data_in_conflict
2709 delete_row data_in_conflict
2710 drop table test.t1;
2711 drop table test.t2;
2712 drop table test.t1$EX;
2713 Now test batched conflict detection/handling
2714 create table test.t1 (
2715 a int primary key,
2716 b int,
2717 c varchar(2000)) engine=ndb;
2718 -- Stop replication from Primary -> Secondary
2719 stop slave;
2720 Warnings:
2721 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2722 -- Insert a row on Primary
2723 insert into test.t1 values (1,1,repeat('B', 2000));
2724 -- Generate a large batch of inserts with early + late conflicts
2725 create procedure test.doit (`rows` int)
2726 begin
2727 set @x = 0;
2728 START TRANSACTION;
2729 repeat
2730 insert into test.t1 values (@x, @x, repeat('B', 2000));
2731 set @x = @x + 1;
2732 until @x = `rows`
2733 end repeat;
2734 COMMIT;
2735 START TRANSACTION;
2736 update test.t1 set b=999, c=repeat('E',2000) where a=1;
2737 COMMIT;
2738 START TRANSACTION;
2739 delete from test.t1 where a=1;
2740 COMMIT;
2741 START TRANSACTION;
2742 insert into test.t1 values (1,1,'A');
2743 COMMIT;
2744 end%
2745 call test.doit(100);
2746 drop procedure test.doit;
2747 -- Look at Primary status, expect 4 conflicts
2748 Conflict algorithm counter diff :
2749 ndb_conflict_fn_epoch
2750 4
2751 Exception table contents
2752 SELECT ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count, a, b$old, b$new, d FROM `t1$EX` ORDER BY ndb$server_id, ndb$master_server_id, ndb$master_epoch, ndb$count;
2753 SELECT * FROM `t1$EX` ORDER BY ndb$count;
2754 SELECT ndb$op_type, ndb$cft_cause FROM `t1$EX` ORDER BY ndb$op_type, ndb$cft_cause;
2755 start slave;
2756 Warnings:
2757 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2758 -- Look at Seconday row
2759 select a,b,sha1(c) from test.t1 where a=1;
2760 a b sha1(c)
2761 1 1 6d41e1d402596dff36396d1f0f288d17a4b9800a
2762 -- Check it's the same on the Secondary
2763 select a,b,sha1(c) from test.t1 where a=1;
2764 a b sha1(c)
2765 1 1 6d41e1d402596dff36396d1f0f288d17a4b9800a
2766 Test batching of DELETE vs DELETE with following INSERT
2767 delete from test.t1;
2768 insert into test.t1 values (1, 1, "Ma Brows");
2769 -- Stop Slave in both directions
2770 stop slave;
2771 Warnings:
2772 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2773 stop slave;
2774 Warnings:
2775 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
2776 -- Delete row on Primary Cluster
2777 delete from test.t1 where a=1;
2778 -- Delete row on Secondary Cluster, followed by Insert in later 'batch'
2779 create procedure test.doit (`rows` int)
2780 begin
2781 set @x = 2;
2782 START TRANSACTION;
2783 delete from test.t1 where a=1;
2784 repeat
2785 insert into test.t1 values (@x, @x, repeat('B', 2000));
2786 set @x = @x + 1;
2787 until @x = (`rows` + 2)
2788 end repeat;
2789 COMMIT;
2790 START TRANSACTION;
2791 insert into test.t1 values (1, 1, 'Malleny arms');
2792 COMMIT;
2793 end%
2794 call test.doit(200);
2795 -- Restart slave on Primary Cluster
2796 start replica;
2797 -- Show data on Primary Cluster (should have row inserted on Secondary)
2798 select * from test.t1 where a=1;
2799 a b c
2800 1 1 Malleny arms
2801 -- Show data on Secondary Cluster (should have row inserted on Secondary)
2802 select * from test.t1 where a=1;
2803 a b c
2804 1 1 Malleny arms
2805 --Restart slave on Secondary Cluster
2806 start slave;
2807 Warnings:
2808 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
2809 -- Show data on Clusters after slaves restarted+synced
2810 For NDB$EPOCH, the data will be missing
2811 as expected with delete vs delete conflict
2812 followed closely by Insert
2813 For NDB$EPOCH2, the data will be present on both
2814 clusters.
2815 PRIMARY
2816 select * from test.t1 where a=1;
2817 a b c
2818 1 1 Malleny arms
2819 SECONDARY
2820 select * from test.t1 where a=1;
2821 a b c
2822 -- Force wait for master to be in-sync with slave
2823 To avoid race between DML and table drop
2824 flush logs;
2825 drop procedure test.doit;
2826 drop table test.t1;
2827 delete from mysql.ndb_replication;
2828 insert into mysql.ndb_replication values
2829 ("test", "t3", 0, 7, "NDB$EPOCH(32)"),
2830 ("test", "t4", 0, 7, "NDB$EPOCH(-1)");
2831 create table test.t3 (a int primary key) engine=ndb;
2832 ERROR HY000: Error in parsing conflict function. Message: NDB$EPOCH(32), Too many extra Gci bits at ')'
2833 show warnings;
2834 Level Code Message
2835 Error 1626 Error in parsing conflict function. Message: NDB$EPOCH(32), Too many extra Gci bits at ')'
2836 Error 1031 Table storage engine for 't3' doesn't have this option
2837 create table test.t4 (a int primary key) engine=ndb;
2838 ERROR HY000: Error in parsing conflict function. Message: NDB$EPOCH(-1), Too many extra Gci bits at ')'
2839 show warnings;
2840 Level Code Message
2841 Error 1626 Error in parsing conflict function. Message: NDB$EPOCH(-1), Too many extra Gci bits at ')'
2842 Error 1031 Table storage engine for 't4' doesn't have this option
2843 -- Force sync before dropping table to avoid race
2844 flush logs;
2845 flush logs;
2846 drop table mysql.ndb_replication;
2847 -- Attempt to get system back in pre-test state
2848 stop replica;
2849 reset replica;
2850 include/rpl_end.inc