Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_conflict_old.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 *** Test 1 ********************************************************
7 * test native NDB$OLD() conflict resolution
8 * test with both allowed "conflict column" types, uint32 and uint64
9 *******************************************************************
10 set @save_luaw = @@global.ndb_log_update_as_write;
11 set @save_luo = @@global.ndb_log_updated_only;
12 set global ndb_log_update_as_write=0;
13 set global ndb_log_updated_only=0;
14 insert into mysql.ndb_replication values ("test", "t1", 0, NULL, "NDB$OLD(X)");
15 create table t1 (a int, b varchar(32), X int unsigned, d varchar(9), primary key (a, d)) engine ndb;
16 create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb;
17 ************************************************
18 * some basic positive testing, i.e. no conflicts
19 ************************************************
20 *** insert some data
21 begin;
22 insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1');
23 insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12');
24 insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123');
25 insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1');
26 insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12');
27 insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123');
28 commit;
29 *** master - update all
30 begin;
31 update t1 set b="Master t1 X=2", X=X+1;
32 update t2 set b="Master t2 c=2", c=c+1;
33 commit;
34 *** slave - check update all
35 select * from t1 order by a, d;
36 a b X d
37 1 Master t1 X=2 2 1
38 2 Master t1 X=2 2 12
39 3 Master t1 X=2 2 123
40 select * from t2 order by a, d;
41 a b c d
42 1 Master t2 c=2 2 1
43 2 Master t2 c=2 2 12
44 3 Master t2 c=2 2 123
45 Conflict algorithm counter diff :
46 Exception table contents
47 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
48 server_id master_server_id master_epoch count a d
49 SELECT * FROM `t1$EX` ORDER BY a, d;
50 server_id master_server_id master_epoch count a d
51 *** master - update 2 rows in different tables
52 update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2;
53 update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3;
54 *** slave - check update 2 rows in different tables
55 select * from t1 order by a, d;
56 a b X d
57 1 Master t1 X=2 2 1
58 2 Master t1 a=2 at X=3 3 12
59 3 Master t1 X=2 2 123
60 select * from t2 order by a, d;
61 a b c d
62 1 Master t2 c=2 2 1
63 2 Master t2 c=2 2 12
64 3 Master t2 a=3 at c=3 3 123
65 Conflict algorithm counter diff :
66 Exception table contents
67 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
68 server_id master_server_id master_epoch count a d
69 SELECT * FROM `t1$EX` ORDER BY a, d;
70 server_id master_server_id master_epoch count a d
71 *** master - delete all
72 delete from t1;
73 delete from t2;
74 *** slave - check delete all
75 select * from t1 order by a, d;
76 a b X d
77 select * from t2 order by a, d;
78 a b c d
79 Conflict algorithm counter diff :
80 Exception table contents
81 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
82 server_id master_server_id master_epoch count a d
83 SELECT * FROM `t1$EX` ORDER BY a, d;
84 server_id master_server_id master_epoch count a d
85 ************************
86 * start conflict testing
87 ************************
88 *** master - insert some data
89 begin;
90 insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111');
91 insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222');
92 insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333');
93 insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333');
94 insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111');
95 insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222');
96 insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333');
97 insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333');
98 commit;
99 *** slave - check conflict info, no conflicts yet
100 Conflict algorithm counter diff :
101 Exception table contents
102 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
103 server_id master_server_id master_epoch count a d
104 SELECT * FROM `t1$EX` ORDER BY a, d;
105 server_id master_server_id master_epoch count a d
106 *** slave - check insert some data
107 select * from t1 order by a, d;
108 a b X d
109 1 Master t1 a=1 at X=10 10 111
110 2 Master t1 a=2 at X=10 10 111222
111 3 Master t1 a=3 at X=10 10 111222333
112 4 Master t1 a=4 at X=10 10 111222333
113 select * from t2 order by a, d;
114 a b c d
115 1 Master t2 a=1 at c=10 10 111
116 2 Master t2 a=2 at c=10 10 111222
117 3 Master t2 a=3 at c=10 10 111222333
118 4 Master t2 a=4 at c=10 10 111222333
119 *** slave - update some data to cause conflicts
120 begin;
121 update t1 set b="Slave t1 a=1 at X=12", X=12 where a=1 and d='111';
122 update t1 set b="Slave t1 a=2 at X=12", X=12 where a=2 and d='111222';
123 update t1 set b="Slave t1 a=3 at X=12", X=12 where a=3 and d='111222333';
124 update t1 set b="Slave t1 a=4 at X=12", X=12 where a=4 and d='111222333';
125 update t2 set b="Slave t2 a=1 at c=12", c=12 where a=1;
126 update t2 set b="Slave t2 a=2 at c=12", c=12 where a=2;
127 update t2 set b="Slave t2 a=3 at c=12", c=12 where a=3;
128 update t2 set b="Slave t2 a=4 at c=12", c=12 where a=4;
129 commit;
130 *** master - update some data to cause conflicts
131 begin;
132 update t1 set b="Master t1 a=1 at X=11", X=11 where a=1 and d='111';
133 update t1 set b="Master t1 a=2 at X=11", X=11 where a=2 and d='111222';
134 update t1 set b="Master t1 a=3 at X=11", X=11 where a=3 and d='111222333';
135 delete from t1 where a = 4;
136 update t2 set b="Master t2 a=1 at c=11", c=11 where a=1;
137 update t2 set b="Master t2 a=2 at c=11", c=11 where a=2;
138 update t2 set b="Master t2 a=3 at c=11", c=11 where a=3;
139 delete from t2 where a = 4;
140 commit;
141 *** slave - check conflict info, there should be some
142 Conflict algorithm counter diff :
143 ndb_conflict_fn_old
144 4
145 Exception table contents
146 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
147 server_id master_server_id master_epoch count a d
148 2 1 # 1 # #
149 2 1 # 2 # #
150 2 1 # 3 # #
151 2 1 # 4 # #
152 SELECT * FROM `t1$EX` ORDER BY a, d;
153 server_id master_server_id master_epoch count a d
154 2 1 # # 1 111
155 2 1 # # 2 111222
156 2 1 # # 3 111222333
157 2 1 # # 4 111222333
158 *** slave - check update some data that causes conflicts
159 select * from t1 order by a, d;
160 a b X d
161 1 Slave t1 a=1 at X=12 12 111
162 2 Slave t1 a=2 at X=12 12 111222
163 3 Slave t1 a=3 at X=12 12 111222333
164 4 Slave t1 a=4 at X=12 12 111222333
165 select * from t2 order by a, d;
166 a b c d
167 1 Master t2 a=1 at c=11 11 111
168 2 Master t2 a=2 at c=11 11 111222
169 3 Master t2 a=3 at c=11 11 111222333
170 *** slave - correct some data to correspond to the master
171 update t1 set b="Slave t1 a=1 at X=11", X=11 where a=1 and d='111';
172 delete from t1 where a = 4;
173 *** master - update some data, use higher timestamp
174 begin;
175 update t1 set b="Master t1 a=1 at X=13", X=13 where a=1 and d='111';
176 update t1 set b="Master t1 a=2 at X=13", X=13 where a=2 and d='111222';
177 update t1 set b="Master t1 a=3 at X=13", X=13 where a=3 and d='111222333';
178 update t2 set b="Master t2 a=1 at c=13", c=13 where a=1;
179 update t2 set b="Master t2 a=2 at c=13", c=13 where a=2;
180 update t2 set b="Master t2 a=3 at c=13", c=13 where a=3;
181 commit;
182 *** slave - check conflict info, change depends on calling test
183 Conflict algorithm counter diff :
184 ndb_conflict_fn_old
185 6
186 Exception table contents
187 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
188 server_id master_server_id master_epoch count a d
189 2 1 # 1 # #
190 2 1 # 2 # #
191 2 1 # 3 # #
192 2 1 # 4 # #
193 2 1 # 5 # #
194 2 1 # 6 # #
195 SELECT * FROM `t1$EX` ORDER BY a, d;
196 server_id master_server_id master_epoch count a d
197 2 1 # # 1 111
198 2 1 # # 2 111222
199 2 1 # # 2 111222
200 2 1 # # 3 111222333
201 2 1 # # 3 111222333
202 2 1 # # 4 111222333
203 *** slave - check higer timestamp
204 select * from t1 order by a, d;
205 a b X d
206 1 Master t1 a=1 at X=13 13 111
207 2 Slave t1 a=2 at X=12 12 111222
208 3 Slave t1 a=3 at X=12 12 111222333
209 select * from t2 order by a, d;
210 a b c d
211 1 Master t2 a=1 at c=13 13 111
212 2 Master t2 a=2 at c=13 13 111222
213 3 Master t2 a=3 at c=13 13 111222333
214 drop table t1, t2;
215 *** Test 2 ********************************************************
216 * test native NDB$OLD() conflict resolution
217 * test with both allowed "conflict column" types, uint32 and uint64
218 *******************************************************************
219 create table t1 (a int, b varchar(32), X bigint unsigned, d varchar(9), primary key (a, d)) engine ndb;
220 create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb;
221 ************************************************
222 * some basic positive testing, i.e. no conflicts
223 ************************************************
224 *** insert some data
225 begin;
226 insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1');
227 insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12');
228 insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123');
229 insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1');
230 insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12');
231 insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123');
232 commit;
233 *** master - update all
234 begin;
235 update t1 set b="Master t1 X=2", X=X+1;
236 update t2 set b="Master t2 c=2", c=c+1;
237 commit;
238 *** slave - check update all
239 select * from t1 order by a, d;
240 a b X d
241 1 Master t1 X=2 2 1
242 2 Master t1 X=2 2 12
243 3 Master t1 X=2 2 123
244 select * from t2 order by a, d;
245 a b c d
246 1 Master t2 c=2 2 1
247 2 Master t2 c=2 2 12
248 3 Master t2 c=2 2 123
249 Conflict algorithm counter diff :
250 Exception table contents
251 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
252 server_id master_server_id master_epoch count a d
253 SELECT * FROM `t1$EX` ORDER BY a, d;
254 server_id master_server_id master_epoch count a d
255 *** master - update 2 rows in different tables
256 update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2;
257 update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3;
258 *** slave - check update 2 rows in different tables
259 select * from t1 order by a, d;
260 a b X d
261 1 Master t1 X=2 2 1
262 2 Master t1 a=2 at X=3 3 12
263 3 Master t1 X=2 2 123
264 select * from t2 order by a, d;
265 a b c d
266 1 Master t2 c=2 2 1
267 2 Master t2 c=2 2 12
268 3 Master t2 a=3 at c=3 3 123
269 Conflict algorithm counter diff :
270 Exception table contents
271 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
272 server_id master_server_id master_epoch count a d
273 SELECT * FROM `t1$EX` ORDER BY a, d;
274 server_id master_server_id master_epoch count a d
275 *** master - delete all
276 delete from t1;
277 delete from t2;
278 *** slave - check delete all
279 select * from t1 order by a, d;
280 a b X d
281 select * from t2 order by a, d;
282 a b c d
283 Conflict algorithm counter diff :
284 Exception table contents
285 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
286 server_id master_server_id master_epoch count a d
287 SELECT * FROM `t1$EX` ORDER BY a, d;
288 server_id master_server_id master_epoch count a d
289 ************************
290 * start conflict testing
291 ************************
292 *** master - insert some data
293 begin;
294 insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111');
295 insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222');
296 insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333');
297 insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333');
298 insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111');
299 insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222');
300 insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333');
301 insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333');
302 commit;
303 *** slave - check conflict info, no conflicts yet
304 Conflict algorithm counter diff :
305 Exception table contents
306 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
307 server_id master_server_id master_epoch count a d
308 SELECT * FROM `t1$EX` ORDER BY a, d;
309 server_id master_server_id master_epoch count a d
310 *** slave - check insert some data
311 select * from t1 order by a, d;
312 a b X d
313 1 Master t1 a=1 at X=10 10 111
314 2 Master t1 a=2 at X=10 10 111222
315 3 Master t1 a=3 at X=10 10 111222333
316 4 Master t1 a=4 at X=10 10 111222333
317 select * from t2 order by a, d;
318 a b c d
319 1 Master t2 a=1 at c=10 10 111
320 2 Master t2 a=2 at c=10 10 111222
321 3 Master t2 a=3 at c=10 10 111222333
322 4 Master t2 a=4 at c=10 10 111222333
323 *** slave - update some data to cause conflicts
324 begin;
325 update t1 set b="Slave t1 a=1 at X=12", X=12 where a=1 and d='111';
326 update t1 set b="Slave t1 a=2 at X=12", X=12 where a=2 and d='111222';
327 update t1 set b="Slave t1 a=3 at X=12", X=12 where a=3 and d='111222333';
328 update t1 set b="Slave t1 a=4 at X=12", X=12 where a=4 and d='111222333';
329 update t2 set b="Slave t2 a=1 at c=12", c=12 where a=1;
330 update t2 set b="Slave t2 a=2 at c=12", c=12 where a=2;
331 update t2 set b="Slave t2 a=3 at c=12", c=12 where a=3;
332 update t2 set b="Slave t2 a=4 at c=12", c=12 where a=4;
333 commit;
334 *** master - update some data to cause conflicts
335 begin;
336 update t1 set b="Master t1 a=1 at X=11", X=11 where a=1 and d='111';
337 update t1 set b="Master t1 a=2 at X=11", X=11 where a=2 and d='111222';
338 update t1 set b="Master t1 a=3 at X=11", X=11 where a=3 and d='111222333';
339 delete from t1 where a = 4;
340 update t2 set b="Master t2 a=1 at c=11", c=11 where a=1;
341 update t2 set b="Master t2 a=2 at c=11", c=11 where a=2;
342 update t2 set b="Master t2 a=3 at c=11", c=11 where a=3;
343 delete from t2 where a = 4;
344 commit;
345 *** slave - check conflict info, there should be some
346 Conflict algorithm counter diff :
347 ndb_conflict_fn_old
348 4
349 Exception table contents
350 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
351 server_id master_server_id master_epoch count a d
352 2 1 # 1 # #
353 2 1 # 2 # #
354 2 1 # 3 # #
355 2 1 # 4 # #
356 SELECT * FROM `t1$EX` ORDER BY a, d;
357 server_id master_server_id master_epoch count a d
358 2 1 # # 1 111
359 2 1 # # 2 111222
360 2 1 # # 3 111222333
361 2 1 # # 4 111222333
362 *** slave - check update some data that causes conflicts
363 select * from t1 order by a, d;
364 a b X d
365 1 Slave t1 a=1 at X=12 12 111
366 2 Slave t1 a=2 at X=12 12 111222
367 3 Slave t1 a=3 at X=12 12 111222333
368 4 Slave t1 a=4 at X=12 12 111222333
369 select * from t2 order by a, d;
370 a b c d
371 1 Master t2 a=1 at c=11 11 111
372 2 Master t2 a=2 at c=11 11 111222
373 3 Master t2 a=3 at c=11 11 111222333
374 *** slave - correct some data to correspond to the master
375 update t1 set b="Slave t1 a=1 at X=11", X=11 where a=1 and d='111';
376 delete from t1 where a = 4;
377 *** master - update some data, use higher timestamp
378 begin;
379 update t1 set b="Master t1 a=1 at X=13", X=13 where a=1 and d='111';
380 update t1 set b="Master t1 a=2 at X=13", X=13 where a=2 and d='111222';
381 update t1 set b="Master t1 a=3 at X=13", X=13 where a=3 and d='111222333';
382 update t2 set b="Master t2 a=1 at c=13", c=13 where a=1;
383 update t2 set b="Master t2 a=2 at c=13", c=13 where a=2;
384 update t2 set b="Master t2 a=3 at c=13", c=13 where a=3;
385 commit;
386 *** slave - check conflict info, change depends on calling test
387 Conflict algorithm counter diff :
388 ndb_conflict_fn_old
389 6
390 Exception table contents
391 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
392 server_id master_server_id master_epoch count a d
393 2 1 # 1 # #
394 2 1 # 2 # #
395 2 1 # 3 # #
396 2 1 # 4 # #
397 2 1 # 5 # #
398 2 1 # 6 # #
399 SELECT * FROM `t1$EX` ORDER BY a, d;
400 server_id master_server_id master_epoch count a d
401 2 1 # # 1 111
402 2 1 # # 2 111222
403 2 1 # # 2 111222
404 2 1 # # 3 111222333
405 2 1 # # 3 111222333
406 2 1 # # 4 111222333
407 *** slave - check higer timestamp
408 select * from t1 order by a, d;
409 a b X d
410 1 Master t1 a=1 at X=13 13 111
411 2 Slave t1 a=2 at X=12 12 111222
412 3 Slave t1 a=3 at X=12 12 111222333
413 select * from t2 order by a, d;
414 a b c d
415 1 Master t2 a=1 at c=13 13 111
416 2 Master t2 a=2 at c=13 13 111222
417 3 Master t2 a=3 at c=13 13 111222333
418 drop table t1, t2;
419 *** Test 3 ********************************************************
420 * test native NDB$OLD() conflict resolution
421 * test that setting binlog update option via table works equally well
422 *******************************************************************
423 set global ndb_log_update_as_write=1;
424 set global ndb_log_updated_only=1;
425 update mysql.ndb_replication set binlog_type=7;
426 create table t1 (a int, b varchar(32), X bigint unsigned, d varchar(9), primary key (a, d)) engine ndb;
427 create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb;
428 ************************************************
429 * some basic positive testing, i.e. no conflicts
430 ************************************************
431 *** insert some data
432 begin;
433 insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1');
434 insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12');
435 insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123');
436 insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1');
437 insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12');
438 insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123');
439 commit;
440 *** master - update all
441 begin;
442 update t1 set b="Master t1 X=2", X=X+1;
443 update t2 set b="Master t2 c=2", c=c+1;
444 commit;
445 *** slave - check update all
446 select * from t1 order by a, d;
447 a b X d
448 1 Master t1 X=2 2 1
449 2 Master t1 X=2 2 12
450 3 Master t1 X=2 2 123
451 select * from t2 order by a, d;
452 a b c d
453 1 Master t2 c=2 2 1
454 2 Master t2 c=2 2 12
455 3 Master t2 c=2 2 123
456 Conflict algorithm counter diff :
457 Exception table contents
458 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
459 server_id master_server_id master_epoch count a d
460 SELECT * FROM `t1$EX` ORDER BY a, d;
461 server_id master_server_id master_epoch count a d
462 *** master - update 2 rows in different tables
463 update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2;
464 update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3;
465 *** slave - check update 2 rows in different tables
466 select * from t1 order by a, d;
467 a b X d
468 1 Master t1 X=2 2 1
469 2 Master t1 a=2 at X=3 3 12
470 3 Master t1 X=2 2 123
471 select * from t2 order by a, d;
472 a b c d
473 1 Master t2 c=2 2 1
474 2 Master t2 c=2 2 12
475 3 Master t2 a=3 at c=3 3 123
476 Conflict algorithm counter diff :
477 Exception table contents
478 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
479 server_id master_server_id master_epoch count a d
480 SELECT * FROM `t1$EX` ORDER BY a, d;
481 server_id master_server_id master_epoch count a d
482 *** master - delete all
483 delete from t1;
484 delete from t2;
485 *** slave - check delete all
486 select * from t1 order by a, d;
487 a b X d
488 select * from t2 order by a, d;
489 a b c d
490 Conflict algorithm counter diff :
491 Exception table contents
492 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
493 server_id master_server_id master_epoch count a d
494 SELECT * FROM `t1$EX` ORDER BY a, d;
495 server_id master_server_id master_epoch count a d
496 ************************
497 * start conflict testing
498 ************************
499 *** master - insert some data
500 begin;
501 insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111');
502 insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222');
503 insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333');
504 insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333');
505 insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111');
506 insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222');
507 insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333');
508 insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333');
509 commit;
510 *** slave - check conflict info, no conflicts yet
511 Conflict algorithm counter diff :
512 Exception table contents
513 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
514 server_id master_server_id master_epoch count a d
515 SELECT * FROM `t1$EX` ORDER BY a, d;
516 server_id master_server_id master_epoch count a d
517 *** slave - check insert some data
518 select * from t1 order by a, d;
519 a b X d
520 1 Master t1 a=1 at X=10 10 111
521 2 Master t1 a=2 at X=10 10 111222
522 3 Master t1 a=3 at X=10 10 111222333
523 4 Master t1 a=4 at X=10 10 111222333
524 select * from t2 order by a, d;
525 a b c d
526 1 Master t2 a=1 at c=10 10 111
527 2 Master t2 a=2 at c=10 10 111222
528 3 Master t2 a=3 at c=10 10 111222333
529 4 Master t2 a=4 at c=10 10 111222333
530 *** slave - update some data to cause conflicts
531 begin;
532 update t1 set b="Slave t1 a=1 at X=12", X=12 where a=1 and d='111';
533 update t1 set b="Slave t1 a=2 at X=12", X=12 where a=2 and d='111222';
534 update t1 set b="Slave t1 a=3 at X=12", X=12 where a=3 and d='111222333';
535 update t1 set b="Slave t1 a=4 at X=12", X=12 where a=4 and d='111222333';
536 update t2 set b="Slave t2 a=1 at c=12", c=12 where a=1;
537 update t2 set b="Slave t2 a=2 at c=12", c=12 where a=2;
538 update t2 set b="Slave t2 a=3 at c=12", c=12 where a=3;
539 update t2 set b="Slave t2 a=4 at c=12", c=12 where a=4;
540 commit;
541 *** master - update some data to cause conflicts
542 begin;
543 update t1 set b="Master t1 a=1 at X=11", X=11 where a=1 and d='111';
544 update t1 set b="Master t1 a=2 at X=11", X=11 where a=2 and d='111222';
545 update t1 set b="Master t1 a=3 at X=11", X=11 where a=3 and d='111222333';
546 delete from t1 where a = 4;
547 update t2 set b="Master t2 a=1 at c=11", c=11 where a=1;
548 update t2 set b="Master t2 a=2 at c=11", c=11 where a=2;
549 update t2 set b="Master t2 a=3 at c=11", c=11 where a=3;
550 delete from t2 where a = 4;
551 commit;
552 *** slave - check conflict info, there should be some
553 Conflict algorithm counter diff :
554 ndb_conflict_fn_old
555 4
556 Exception table contents
557 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
558 server_id master_server_id master_epoch count a d
559 2 1 # 1 # #
560 2 1 # 2 # #
561 2 1 # 3 # #
562 2 1 # 4 # #
563 SELECT * FROM `t1$EX` ORDER BY a, d;
564 server_id master_server_id master_epoch count a d
565 2 1 # # 1 111
566 2 1 # # 2 111222
567 2 1 # # 3 111222333
568 2 1 # # 4 111222333
569 *** slave - check update some data that causes conflicts
570 select * from t1 order by a, d;
571 a b X d
572 1 Slave t1 a=1 at X=12 12 111
573 2 Slave t1 a=2 at X=12 12 111222
574 3 Slave t1 a=3 at X=12 12 111222333
575 4 Slave t1 a=4 at X=12 12 111222333
576 select * from t2 order by a, d;
577 a b c d
578 1 Master t2 a=1 at c=11 11 111
579 2 Master t2 a=2 at c=11 11 111222
580 3 Master t2 a=3 at c=11 11 111222333
581 *** slave - correct some data to correspond to the master
582 update t1 set b="Slave t1 a=1 at X=11", X=11 where a=1 and d='111';
583 delete from t1 where a = 4;
584 *** master - update some data, use higher timestamp
585 begin;
586 update t1 set b="Master t1 a=1 at X=13", X=13 where a=1 and d='111';
587 update t1 set b="Master t1 a=2 at X=13", X=13 where a=2 and d='111222';
588 update t1 set b="Master t1 a=3 at X=13", X=13 where a=3 and d='111222333';
589 update t2 set b="Master t2 a=1 at c=13", c=13 where a=1;
590 update t2 set b="Master t2 a=2 at c=13", c=13 where a=2;
591 update t2 set b="Master t2 a=3 at c=13", c=13 where a=3;
592 commit;
593 *** slave - check conflict info, change depends on calling test
594 Conflict algorithm counter diff :
595 ndb_conflict_fn_old
596 6
597 Exception table contents
598 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
599 server_id master_server_id master_epoch count a d
600 2 1 # 1 # #
601 2 1 # 2 # #
602 2 1 # 3 # #
603 2 1 # 4 # #
604 2 1 # 5 # #
605 2 1 # 6 # #
606 SELECT * FROM `t1$EX` ORDER BY a, d;
607 server_id master_server_id master_epoch count a d
608 2 1 # # 1 111
609 2 1 # # 2 111222
610 2 1 # # 2 111222
611 2 1 # # 3 111222333
612 2 1 # # 3 111222333
613 2 1 # # 4 111222333
614 *** slave - check higer timestamp
615 select * from t1 order by a, d;
616 a b X d
617 1 Master t1 a=1 at X=13 13 111
618 2 Slave t1 a=2 at X=12 12 111222
619 3 Slave t1 a=3 at X=12 12 111222333
620 select * from t2 order by a, d;
621 a b c d
622 1 Master t2 a=1 at c=13 13 111
623 2 Master t2 a=2 at c=13 13 111222
624 3 Master t2 a=3 at c=13 13 111222333
625 drop table t1, t2;
626 *** Test 4 ***********************************************************
627 * test behaviour with updates logged as writes, but all columns present
628 * Should succeed, but miss update conflict detection as no updates are
629 * recorded. Bit of a weird test...
630 * ********************************************************************
631 update mysql.ndb_replication set binlog_type=3;
632 create table t1 (a int, b varchar(32), X int unsigned, d varchar(9), primary key (a, d)) engine ndb;
633 create table t2 (a int, b varchar(32), c int unsigned, d varchar(9), primary key (a, d)) engine ndb;
634 ************************************************
635 * some basic positive testing, i.e. no conflicts
636 ************************************************
637 *** insert some data
638 begin;
639 insert into t1 values (1, "Master t1 a=1 at X=1", 1, '1');
640 insert into t1 values (2, "Master t1 a=2 at X=1", 1, '12');
641 insert into t1 values (3, "Master t1 a=3 at X=1", 1, '123');
642 insert into t2 values (1, "Master t2 a=1 at c=1", 1, '1');
643 insert into t2 values (2, "Master t2 a=2 at c=1", 1, '12');
644 insert into t2 values (3, "Master t2 a=3 at c=1", 1, '123');
645 commit;
646 *** master - update all
647 begin;
648 update t1 set b="Master t1 X=2", X=X+1;
649 update t2 set b="Master t2 c=2", c=c+1;
650 commit;
651 *** slave - check update all
652 select * from t1 order by a, d;
653 a b X d
654 1 Master t1 a=1 at X=1 1 1
655 2 Master t1 a=2 at X=1 1 12
656 3 Master t1 a=3 at X=1 1 123
657 select * from t2 order by a, d;
658 a b c d
659 1 Master t2 c=2 2 1
660 2 Master t2 c=2 2 12
661 3 Master t2 c=2 2 123
662 Conflict algorithm counter diff :
663 ndb_conflict_fn_old
664 3
665 Exception table contents
666 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
667 server_id master_server_id master_epoch count a d
668 2 1 # 1 # #
669 2 1 # 2 # #
670 2 1 # 3 # #
671 SELECT * FROM `t1$EX` ORDER BY a, d;
672 server_id master_server_id master_epoch count a d
673 2 1 # # 1 1
674 2 1 # # 2 12
675 2 1 # # 3 123
676 *** master - update 2 rows in different tables
677 update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2;
678 update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3;
679 *** slave - check update 2 rows in different tables
680 select * from t1 order by a, d;
681 a b X d
682 1 Master t1 a=1 at X=1 1 1
683 2 Master t1 a=2 at X=1 1 12
684 3 Master t1 a=3 at X=1 1 123
685 select * from t2 order by a, d;
686 a b c d
687 1 Master t2 c=2 2 1
688 2 Master t2 c=2 2 12
689 3 Master t2 a=3 at c=3 3 123
690 Conflict algorithm counter diff :
691 ndb_conflict_fn_old
692 4
693 Exception table contents
694 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
695 server_id master_server_id master_epoch count a d
696 2 1 # 1 # #
697 2 1 # 2 # #
698 2 1 # 3 # #
699 2 1 # 4 # #
700 SELECT * FROM `t1$EX` ORDER BY a, d;
701 server_id master_server_id master_epoch count a d
702 2 1 # # 1 1
703 2 1 # # 2 12
704 2 1 # # 2 12
705 2 1 # # 3 123
706 *** master - delete all
707 delete from t1;
708 delete from t2;
709 *** slave - check delete all
710 select * from t1 order by a, d;
711 a b X d
712 1 Master t1 a=1 at X=1 1 1
713 2 Master t1 a=2 at X=1 1 12
714 3 Master t1 a=3 at X=1 1 123
715 select * from t2 order by a, d;
716 a b c d
717 Conflict algorithm counter diff :
718 ndb_conflict_fn_old
719 7
720 Exception table contents
721 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
722 server_id master_server_id master_epoch count a d
723 2 1 # 1 # #
724 2 1 # 2 # #
725 2 1 # 3 # #
726 2 1 # 4 # #
727 2 1 # 5 # #
728 2 1 # 6 # #
729 2 1 # 7 # #
730 SELECT * FROM `t1$EX` ORDER BY a, d;
731 server_id master_server_id master_epoch count a d
732 2 1 # # 1 1
733 2 1 # # 1 1
734 2 1 # # 2 12
735 2 1 # # 2 12
736 2 1 # # 2 12
737 2 1 # # 3 123
738 2 1 # # 3 123
739 ************************
740 * start conflict testing
741 ************************
742 *** master - insert some data
743 begin;
744 insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111');
745 insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222');
746 insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333');
747 insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333');
748 insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111');
749 insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222');
750 insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333');
751 insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333');
752 commit;
753 *** slave - check conflict info, no conflicts yet
754 Conflict algorithm counter diff :
755 Exception table contents
756 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
757 server_id master_server_id master_epoch count a d
758 SELECT * FROM `t1$EX` ORDER BY a, d;
759 server_id master_server_id master_epoch count a d
760 *** slave - check insert some data
761 select * from t1 order by a, d;
762 a b X d
763 1 Master t1 a=1 at X=1 1 1
764 1 Master t1 a=1 at X=10 10 111
765 2 Master t1 a=2 at X=10 10 111222
766 2 Master t1 a=2 at X=1 1 12
767 3 Master t1 a=3 at X=10 10 111222333
768 3 Master t1 a=3 at X=1 1 123
769 4 Master t1 a=4 at X=10 10 111222333
770 select * from t2 order by a, d;
771 a b c d
772 1 Master t2 a=1 at c=10 10 111
773 2 Master t2 a=2 at c=10 10 111222
774 3 Master t2 a=3 at c=10 10 111222333
775 4 Master t2 a=4 at c=10 10 111222333
776 *** slave - update some data to cause conflicts
777 begin;
778 update t1 set b="Slave t1 a=1 at X=12", X=12 where a=1 and d='111';
779 update t1 set b="Slave t1 a=2 at X=12", X=12 where a=2 and d='111222';
780 update t1 set b="Slave t1 a=3 at X=12", X=12 where a=3 and d='111222333';
781 update t1 set b="Slave t1 a=4 at X=12", X=12 where a=4 and d='111222333';
782 update t2 set b="Slave t2 a=1 at c=12", c=12 where a=1;
783 update t2 set b="Slave t2 a=2 at c=12", c=12 where a=2;
784 update t2 set b="Slave t2 a=3 at c=12", c=12 where a=3;
785 update t2 set b="Slave t2 a=4 at c=12", c=12 where a=4;
786 commit;
787 *** master - update some data to cause conflicts
788 begin;
789 update t1 set b="Master t1 a=1 at X=11", X=11 where a=1 and d='111';
790 update t1 set b="Master t1 a=2 at X=11", X=11 where a=2 and d='111222';
791 update t1 set b="Master t1 a=3 at X=11", X=11 where a=3 and d='111222333';
792 delete from t1 where a = 4;
793 update t2 set b="Master t2 a=1 at c=11", c=11 where a=1;
794 update t2 set b="Master t2 a=2 at c=11", c=11 where a=2;
795 update t2 set b="Master t2 a=3 at c=11", c=11 where a=3;
796 delete from t2 where a = 4;
797 commit;
798 *** slave - check conflict info, there should be some
799 Conflict algorithm counter diff :
800 ndb_conflict_fn_old
801 4
802 Exception table contents
803 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
804 server_id master_server_id master_epoch count a d
805 2 1 # 8 # #
806 2 1 # 9 # #
807 2 1 # 10 # #
808 2 1 # 11 # #
809 SELECT * FROM `t1$EX` ORDER BY a, d;
810 server_id master_server_id master_epoch count a d
811 2 1 # # 1 111
812 2 1 # # 2 111222
813 2 1 # # 3 111222333
814 2 1 # # 4 111222333
815 *** slave - check update some data that causes conflicts
816 select * from t1 order by a, d;
817 a b X d
818 1 Master t1 a=1 at X=1 1 1
819 1 Slave t1 a=1 at X=12 12 111
820 2 Slave t1 a=2 at X=12 12 111222
821 2 Master t1 a=2 at X=1 1 12
822 3 Slave t1 a=3 at X=12 12 111222333
823 3 Master t1 a=3 at X=1 1 123
824 4 Slave t1 a=4 at X=12 12 111222333
825 select * from t2 order by a, d;
826 a b c d
827 1 Master t2 a=1 at c=11 11 111
828 2 Master t2 a=2 at c=11 11 111222
829 3 Master t2 a=3 at c=11 11 111222333
830 *** slave - correct some data to correspond to the master
831 update t1 set b="Slave t1 a=1 at X=11", X=11 where a=1 and d='111';
832 delete from t1 where a = 4;
833 *** master - update some data, use higher timestamp
834 begin;
835 update t1 set b="Master t1 a=1 at X=13", X=13 where a=1 and d='111';
836 update t1 set b="Master t1 a=2 at X=13", X=13 where a=2 and d='111222';
837 update t1 set b="Master t1 a=3 at X=13", X=13 where a=3 and d='111222333';
838 update t2 set b="Master t2 a=1 at c=13", c=13 where a=1;
839 update t2 set b="Master t2 a=2 at c=13", c=13 where a=2;
840 update t2 set b="Master t2 a=3 at c=13", c=13 where a=3;
841 commit;
842 *** slave - check conflict info, change depends on calling test
843 Conflict algorithm counter diff :
844 ndb_conflict_fn_old
845 7
846 Exception table contents
847 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
848 server_id master_server_id master_epoch count a d
849 2 1 # 8 # #
850 2 1 # 9 # #
851 2 1 # 10 # #
852 2 1 # 11 # #
853 2 1 # 12 # #
854 2 1 # 13 # #
855 2 1 # 14 # #
856 SELECT * FROM `t1$EX` ORDER BY a, d;
857 server_id master_server_id master_epoch count a d
858 2 1 # # 1 111
859 2 1 # # 1 111
860 2 1 # # 2 111222
861 2 1 # # 2 111222
862 2 1 # # 3 111222333
863 2 1 # # 3 111222333
864 2 1 # # 4 111222333
865 *** slave - check higer timestamp
866 select * from t1 order by a, d;
867 a b X d
868 1 Master t1 a=1 at X=1 1 1
869 1 Slave t1 a=1 at X=11 11 111
870 2 Slave t1 a=2 at X=12 12 111222
871 2 Master t1 a=2 at X=1 1 12
872 3 Slave t1 a=3 at X=12 12 111222333
873 3 Master t1 a=3 at X=1 1 123
874 select * from t2 order by a, d;
875 a b c d
876 1 Master t2 a=1 at c=13 13 111
877 2 Master t2 a=2 at c=13 13 111222
878 3 Master t2 a=3 at c=13 13 111222333
879 drop table t1, t2;
880 *** Test 5 ***********************************************************
881 * test that missing timestamp column in after-image for update, or
882 * test before image for delete results in slave stopping
883 **********************************************************************
884 update mysql.ndb_replication set binlog_type=6;
885 create table t1 (a int, b varchar(32), X bigint unsigned, d varchar(9), primary key (a, d)) engine ndb;
886 insert into t1 values (1, "Master row 1 at X=1", 1, "1:1");
887 insert into t1 values (2, "Master row 2 at X=1", 1, "2:1");
888 insert into t1 values (3, "Master row 3 at X=1", 1, "3:1");
889 Show update missing timestamp column in before image causes slave failure
890 update t1 set b='Blah' where a=1 and d="1:1";
891 call mtr.add_suppression(".*missing info necessary for conflict detection.*");
892 call mtr.add_suppression(".Could not execute.*rows event on table.*");
893 call mtr.add_suppression(".*error 1610.*");
894 call mtr.add_suppression(".*coordinator and worker threads are stopped.*");
895 Wait for slave to choke on that event.
896 include/wait_for_slave_sql_error.inc [errno=1296]
897 include/stop_slave_io.inc
898 RESET REPLICA;
899 include/rpl_reset.inc
900 Show delete missing timestamp column (in before image) causes
901 slave failure
902 delete from t1 where a=3 and d="3:1";
903 Wait for slave to choke on that event.
904 include/wait_for_slave_sql_error.inc [errno=1296]
905 include/stop_slave_io.inc
906 RESET REPLICA;
907 include/rpl_reset.inc
908 drop table t1;
909 *** test cleanup
910 set global ndb_log_update_as_write= @save_luaw;
911 set global ndb_log_updated_only = @save_luo;
912 include/rpl_end.inc