Debian Codegraph @

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