Debian Codegraph @

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