Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_conflict_max_ins_1.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_INS() 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, 0, "NDB$MAX_INS(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_ins
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_ins
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_INS() 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_ins
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_ins
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_INS() 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_ins
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_ins
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 since the NDB$MAX_INS() supports conflict detection
617 * and resolution for INSERTS.
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 X=2 2 1
643 2 Master t1 X=2 2 12
644 3 Master t1 X=2 2 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 Exception table contents
652 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
653 server_id master_server_id master_epoch count a d
654 SELECT * FROM `t1$EX` ORDER BY a, d;
655 server_id master_server_id master_epoch count a d
656 *** master - update 2 rows in different tables
657 update t1 set b="Master t1 a=2 at X=3", X=X+1 where a=2;
658 update t2 set b="Master t2 a=3 at c=3", c=c+1 where a=3;
659 *** slave - check update 2 rows in different tables
660 select * from t1 order by a, d;
661 a b X d
662 1 Master t1 X=2 2 1
663 2 Master t1 a=2 at X=3 3 12
664 3 Master t1 X=2 2 123
665 select * from t2 order by a, d;
666 a b c d
667 1 Master t2 c=2 2 1
668 2 Master t2 c=2 2 12
669 3 Master t2 a=3 at c=3 3 123
670 Conflict algorithm counter diff :
671 Exception table contents
672 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
673 server_id master_server_id master_epoch count a d
674 SELECT * FROM `t1$EX` ORDER BY a, d;
675 server_id master_server_id master_epoch count a d
676 *** master - delete all
677 delete from t1;
678 delete from t2;
679 *** slave - check delete all
680 select * from t1 order by a, d;
681 a b X d
682 select * from t2 order by a, d;
683 a b c d
684 Conflict algorithm counter diff :
685 Exception table contents
686 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
687 server_id master_server_id master_epoch count a d
688 SELECT * FROM `t1$EX` ORDER BY a, d;
689 server_id master_server_id master_epoch count a d
690 ************************
691 * start conflict testing
692 ************************
693 *** master - insert some data
694 begin;
695 insert into t1 values (1, "Master t1 a=1 at X=10", 10, '111');
696 insert into t1 values (2, "Master t1 a=2 at X=10", 10, '111222');
697 insert into t1 values (3, "Master t1 a=3 at X=10", 10, '111222333');
698 insert into t1 values (4, "Master t1 a=4 at X=10", 10, '111222333');
699 insert into t2 values (1, "Master t2 a=1 at c=10", 10, '111');
700 insert into t2 values (2, "Master t2 a=2 at c=10", 10, '111222');
701 insert into t2 values (3, "Master t2 a=3 at c=10", 10, '111222333');
702 insert into t2 values (4, "Master t2 a=4 at c=10", 10, '111222333');
703 commit;
704 *** slave - check conflict info, no conflicts yet
705 Conflict algorithm counter diff :
706 Exception table contents
707 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
708 server_id master_server_id master_epoch count a d
709 SELECT * FROM `t1$EX` ORDER BY a, d;
710 server_id master_server_id master_epoch count a d
711 *** slave - check insert some data
712 select * from t1 order by a, d;
713 a b X d
714 1 Master t1 a=1 at X=10 10 111
715 2 Master t1 a=2 at X=10 10 111222
716 3 Master t1 a=3 at X=10 10 111222333
717 4 Master t1 a=4 at X=10 10 111222333
718 select * from t2 order by a, d;
719 a b c d
720 1 Master t2 a=1 at c=10 10 111
721 2 Master t2 a=2 at c=10 10 111222
722 3 Master t2 a=3 at c=10 10 111222333
723 4 Master t2 a=4 at c=10 10 111222333
724 *** slave - update some data to cause conflicts
725 begin;
726 update t1 set b="Slave t1 a=1 at X=12", X=12 where a=1 and d='111';
727 update t1 set b="Slave t1 a=2 at X=12", X=12 where a=2 and d='111222';
728 update t1 set b="Slave t1 a=3 at X=12", X=12 where a=3 and d='111222333';
729 update t1 set b="Slave t1 a=4 at X=12", X=12 where a=4 and d='111222333';
730 update t2 set b="Slave t2 a=1 at c=12", c=12 where a=1;
731 update t2 set b="Slave t2 a=2 at c=12", c=12 where a=2;
732 update t2 set b="Slave t2 a=3 at c=12", c=12 where a=3;
733 update t2 set b="Slave t2 a=4 at c=12", c=12 where a=4;
734 commit;
735 *** master - update some data to cause conflicts
736 begin;
737 update t1 set b="Master t1 a=1 at X=11", X=11 where a=1 and d='111';
738 update t1 set b="Master t1 a=2 at X=11", X=11 where a=2 and d='111222';
739 update t1 set b="Master t1 a=3 at X=11", X=11 where a=3 and d='111222333';
740 delete from t1 where a = 4;
741 update t2 set b="Master t2 a=1 at c=11", c=11 where a=1;
742 update t2 set b="Master t2 a=2 at c=11", c=11 where a=2;
743 update t2 set b="Master t2 a=3 at c=11", c=11 where a=3;
744 delete from t2 where a = 4;
745 commit;
746 *** slave - check conflict info, there should be some
747 Conflict algorithm counter diff :
748 ndb_conflict_fn_max_ins
749 4
750 Exception table contents
751 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
752 server_id master_server_id master_epoch count a d
753 2 1 # 1 # #
754 2 1 # 2 # #
755 2 1 # 3 # #
756 2 1 # 4 # #
757 SELECT * FROM `t1$EX` ORDER BY a, d;
758 server_id master_server_id master_epoch count a d
759 2 1 # # 1 111
760 2 1 # # 2 111222
761 2 1 # # 3 111222333
762 2 1 # # 4 111222333
763 *** slave - check update some data that causes conflicts
764 select * from t1 order by a, d;
765 a b X d
766 1 Slave t1 a=1 at X=12 12 111
767 2 Slave t1 a=2 at X=12 12 111222
768 3 Slave t1 a=3 at X=12 12 111222333
769 4 Slave t1 a=4 at X=12 12 111222333
770 select * from t2 order by a, d;
771 a b c d
772 1 Master t2 a=1 at c=11 11 111
773 2 Master t2 a=2 at c=11 11 111222
774 3 Master t2 a=3 at c=11 11 111222333
775 *** slave - correct some data to correspond to the master
776 update t1 set b="Slave t1 a=1 at X=11", X=11 where a=1 and d='111';
777 delete from t1 where a = 4;
778 *** master - update some data, use higher timestamp
779 begin;
780 update t1 set b="Master t1 a=1 at X=13", X=13 where a=1 and d='111';
781 update t1 set b="Master t1 a=2 at X=13", X=13 where a=2 and d='111222';
782 update t1 set b="Master t1 a=3 at X=13", X=13 where a=3 and d='111222333';
783 update t2 set b="Master t2 a=1 at c=13", c=13 where a=1;
784 update t2 set b="Master t2 a=2 at c=13", c=13 where a=2;
785 update t2 set b="Master t2 a=3 at c=13", c=13 where a=3;
786 commit;
787 *** slave - check conflict info, change depends on calling test
788 Conflict algorithm counter diff :
789 ndb_conflict_fn_max_ins
790 4
791 Exception table contents
792 SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count;
793 server_id master_server_id master_epoch count a d
794 2 1 # 1 # #
795 2 1 # 2 # #
796 2 1 # 3 # #
797 2 1 # 4 # #
798 SELECT * FROM `t1$EX` ORDER BY a, d;
799 server_id master_server_id master_epoch count a d
800 2 1 # # 1 111
801 2 1 # # 2 111222
802 2 1 # # 3 111222333
803 2 1 # # 4 111222333
804 *** slave - check higer timestamp
805 select * from t1 order by a, d;
806 a b X d
807 1 Master t1 a=1 at X=13 13 111
808 2 Master t1 a=2 at X=13 13 111222
809 3 Master t1 a=3 at X=13 13 111222333
810 select * from t2 order by a, d;
811 a b c d
812 1 Master t2 a=1 at c=13 13 111
813 2 Master t2 a=2 at c=13 13 111222
814 3 Master t2 a=3 at c=13 13 111222333
815 drop table t1, t2;
816 *** Test 5 ***********************************************************
817 * test that missing timestamp column in after-image for update, or
818 * test before image for delete results in slave stopping
819 **********************************************************************
820 update mysql.ndb_replication set binlog_type=6;
821 create table t1 (a int, b varchar(32), X bigint unsigned, d varchar(9), primary key (a, d)) engine ndb;
822 insert into t1 values (1, "Master row 1 at X=1", 1, "1:1");
823 insert into t1 values (2, "Master row 2 at X=1", 1, "2:1");
824 insert into t1 values (3, "Master row 3 at X=1", 1, "3:1");
825 Show update missing timestamp column causes slave failure
826 update t1 set b='Blah' where a=1 and d="1:1";
827 call mtr.add_suppression(".*missing info necessary for conflict detection.*");
828 call mtr.add_suppression(".Could not execute.*rows event on table.*");
829 call mtr.add_suppression(".*error 1610.*");
830 call mtr.add_suppression(".*coordinator and worker threads are stopped.*");
831 Wait for slave to choke on that event.
832 include/wait_for_slave_sql_error.inc [errno=1296]
833 include/stop_slave_io.inc
834 RESET REPLICA;
835 include/rpl_reset.inc
836 Show delete missing timestamp column (in before image) causes
837 slave failure
838 delete from t1 where a=3 and d="3:1";
839 Wait for slave to choke on that event.
840 include/wait_for_slave_sql_error.inc [errno=1296]
841 include/stop_slave_io.inc
842 RESET REPLICA;
843 include/rpl_reset.inc
844 drop table t1;
845 *** test cleanup
846 set global ndb_log_update_as_write= @save_luaw;
847 set global ndb_log_updated_only = @save_luo;
848 include/rpl_end.inc