Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_conflict_basic.test

raw
1 #
2 # Test engine native conflict resolution for ndb
3 #
4 #
5 --source include/have_multi_ndb.inc
6 --source include/have_binlog_format_mixed_or_row.inc
7 --source suite/ndb_rpl/ndb_master-slave.inc
8
9 --disable_query_log
10 --connection master
11 CREATE TABLE mysql.ndb_replication
12 (db VARBINARY(63),
13 table_name VARBINARY(63),
14 server_id INT UNSIGNED,
15 binlog_type INT UNSIGNED,
16 conflict_fn VARBINARY(128),
17 PRIMARY KEY USING HASH (db,table_name,server_id))
18 ENGINE=NDB PARTITION BY KEY(db,table_name);
19 --enable_query_log
20
21 # only test with binlog_type = 7, as everything else feels insane in context of conflict resolution
22 insert into mysql.ndb_replication values ("test", "t1_old", 0, 7, "NDB$OLD(X)");
23 insert into mysql.ndb_replication values ("test", "t1_max", 0, 7, "NDB$MAX(X)");
24 insert into mysql.ndb_replication values ("test", "t1_max_delete_win", 0, 7, "NDB$MAX_DELETE_WIN(X)");
25
26 create table `t1_old$EX`
27 (server_id int unsigned,
28 master_server_id int unsigned,
29 master_epoch bigint unsigned,
30 count int unsigned,
31 a int not null,
32 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
33
34 create table `t1_max$EX`
35 (server_id int unsigned,
36 master_server_id int unsigned,
37 master_epoch bigint unsigned,
38 count int unsigned,
39 a int not null,
40 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
41
42 create table `t1_max_delete_win$EX`
43 (server_id int unsigned,
44 master_server_id int unsigned,
45 master_epoch bigint unsigned,
46 count int unsigned,
47 a int not null,
48 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
49
50 create table t1_old (a int primary key, b varchar(32), X int unsigned) engine = ndb;
51 create table t1_max (a int primary key, b varchar(32), X int unsigned) engine = ndb;
52 create table t1_max_delete_win (a int primary key, b varchar(32), X int unsigned) engine = ndb;
53 --sync_slave_with_master
54
55 ###############
56 --echo "Test 1"
57
58 --connection master
59 insert into t1_old values (1, 'Initial X=1', 1);
60 insert into t1_max values (1, 'Initial X=1', 1);
61 insert into t1_max_delete_win values (1, 'Initial X=1', 1);
62 --sync_slave_with_master
63
64 --connection slave
65 update t1_old set X = 2, b='Slave X=2';
66 update t1_max set X = 2, b='Slave X=2';
67 update t1_max_delete_win set X = 2, b='Slave X=2';
68
69 --connection master
70 update t1_old set X = 3, b='Master X=3';
71 update t1_max set X = 3, b='Master X=3';
72 update t1_max_delete_win set X = 3, b='Master X=3';
73 --sync_slave_with_master
74
75 --connection slave
76 --echo "Expect t1_old to contain slave row, and t1_max* to contain master row"
77 select * from t1_old;
78 select * from t1_max;
79 select * from t1_max_delete_win;
80
81 # syncronize
82 update t1_old set X = 3, b='Master X=3';
83
84 ###############
85 --echo "Test 2"
86
87 --connection slave
88 update t1_old set X = 4, b='Slave X=4';
89 update t1_max set X = 4, b='Slave X=4';
90 update t1_max_delete_win set X = 4, b='Slave X=4';
91
92 --connection master
93 delete from t1_old;
94 delete from t1_max;
95 delete from t1_max_delete_win;
96 --sync_slave_with_master
97
98 --connection slave
99 --echo "Expect t1_old and t1_max to contain slave row, and t1_max_delete_win to be empty(as master)"
100 select * from t1_old;
101 select * from t1_max;
102 select * from t1_max_delete_win;
103
104 # syncronize
105 delete from t1_old;
106 delete from t1_max;
107
108 --connection master
109
110 # Now test with Blobs
111 drop table t1_old, t1_max, t1_max_delete_win;
112 delete from t1_old$EX;
113 delete from t1_max$EX;
114 delete from t1_max_delete_win$EX;
115
116 --sync_slave_with_master
117 --connection slave
118 # Delete on slave, as $EX table ops don't replicate
119 delete from t1_old$EX;
120 delete from t1_max$EX;
121 delete from t1_max_delete_win$EX;
122
123 --connection master
124
125 create table t1_old (a int primary key, b longtext, X int unsigned) engine = ndb;
126 create table t1_max (a int primary key, b longtext, X int unsigned) engine = ndb;
127 create table t1_max_delete_win (a int primary key, b longtext, X int unsigned) engine = ndb;
128
129 --sync_slave_with_master
130
131 ###############
132 --echo "Test 3"
133
134 --connection master
135 insert into t1_old values (1, repeat('Initial X=1',1000), 1);
136 insert into t1_max values (1, repeat('Initial X=1',1000), 1);
137 insert into t1_max_delete_win values (1, repeat('Initial X=1',1000), 1);
138 --sync_slave_with_master
139
140 --connection slave
141 update t1_old set X = 2, b=repeat('Slave X=2',1001);
142 update t1_max set X = 2, b=repeat('Slave X=2',1001);
143 update t1_max_delete_win set X = 2, b=repeat('Slave X=2',1001);
144
145 --connection master
146 update t1_old set X = 3, b=repeat('Master X=3',1002);
147 update t1_max set X = 3, b=repeat('Master X=3',1002);
148 update t1_max_delete_win set X = 3, b=repeat('Master X=3',1002);
149 --sync_slave_with_master
150
151 --connection slave
152 --echo "Expect t1_old to contain slave row, and t1_max* to contain master row"
153 select a, left(b, 20), length(b), X from t1_old;
154 select a, left(b, 20), length(b), X from t1_max;
155 select a, left(b, 20), length(b), X from t1_max_delete_win;
156
157 --echo Expect t1_old to have 1 entry, and t1_max* to have no entries
158 select server_id, master_server_id, count, a from t1_old$EX order by count;
159 select server_id, master_server_id, count, a from t1_max$EX order by count;
160 select server_id, master_server_id, count, a from t1_max_delete_win$EX order by count;
161
162 delete from t1_max$EX;
163 delete from t1_max_delete_win$EX;
164 delete from t1_old$EX;
165
166 # syncronize
167 update t1_old set X = 3, b=repeat('Master X=3', 1002);
168
169 ###############
170 --echo "Test 4"
171
172 --connection slave
173 update t1_old set X = 4, b=repeat('Slave X=4',2000);
174 update t1_max set X = 4, b=repeat('Slave X=4',2000);
175 update t1_max_delete_win set X = 4, b=repeat('Slave X=4',2000);
176
177 --connection master
178 delete from t1_old;
179 delete from t1_max;
180 delete from t1_max_delete_win;
181 --sync_slave_with_master
182
183 --connection slave
184 --echo "Expect t1_old and t1_max to contain slave row, and t1_max_delete_win to be empty(as master)"
185 select a, left(b, 20), length(b), X from t1_old;
186 select a, left(b, 20), length(b), X from t1_max;
187 select a, left(b, 20), length(b), X from t1_max_delete_win;
188
189 --echo Expect t1_old and t1_max to contain 1 entry, and t1_max_delete_win to be empty
190 select server_id, master_server_id, count, a from t1_old$EX order by count;
191 select server_id, master_server_id, count, a from t1_max$EX order by count;
192 select server_id, master_server_id, count, a from t1_max_delete_win$EX order by count;
193
194 delete from t1_max$EX;
195 delete from t1_max_delete_win$EX;
196 delete from t1_old$EX;
197
198 delete from t1_old;
199 delete from t1_max;
200 delete from t1_max_delete_win;
201
202 --connection master
203 delete from t1_old;
204 delete from t1_max;
205 delete from t1_max_delete_win;
206
207 #################
208 --echo "Test 5"
209
210 --echo Test that Updates affecting Blobs are rejected
211 --echo correctly on the slave
212 drop table t1_max;
213 create table t1_max (a int primary key, b int, c longtext, d longtext, X int unsigned) engine = ndb;
214
215 insert into t1_max values (1, 1, repeat("B", 10000), repeat("E", 10001), 1);
216 insert into t1_max values (2, 2, repeat("A", 10002), repeat("T", 10003), 1);
217
218 --sync_slave_with_master
219 --connection slave
220
221 # Bump up tuple versions
222 update t1_max set X=20;
223
224 --echo Initial values on Slave
225 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
226
227 --connection master
228 --echo Originate update which will be rejected
229 update t1_max set c=repeat("Z", 10006), d=repeat("I", 10005), X=2 where a=1;
230
231 --sync_slave_with_master
232 --connection slave
233 --echo Check slave has rejected due to lower version
234 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
235
236 --connection master
237 --echo Originate delete which will be rejected (due to NDB-OLD) algorith
238 delete from t1_max where a=1;
239
240 --sync_slave_with_master
241 --connection slave
242 --echo Check slave has rejected due to before image mismatch
243 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
244
245 --connection master
246 --echo Originate insert which will be rejected (as row exists)
247 insert into t1_max values (1, 1, repeat("R", 10004), repeat("A", 10007), 1);
248
249 --sync_slave_with_master
250 --connection slave
251 --echo Check slave has rejected due to row existing already
252 select a,b,SHA1(c),length(c), SHA1(d), length(d), X from t1_max order by a;
253
254 --echo Expect t1_max to have 3 entries
255 select server_id, master_server_id, count, a from t1_old$EX order by count;
256 select server_id, master_server_id, count, a from t1_max$EX order by count;
257 select server_id, master_server_id, count, a from t1_max_delete_win$EX order by count;
258
259 delete from t1_max$EX;
260 delete from t1_max_delete_win$EX;
261 delete from t1_old$EX;
262
263 --connection master
264
265 #######
266 --echo Test 6
267 --echo Check that non-Blob related operations in a batch with a Blob
268 --echo operation are still subject to conflict detection.
269 --echo
270 insert into mysql.ndb_replication values ("test", "t2_max", 0, 7, "NDB$MAX(X)");
271
272 create table `t2_max$EX`
273 (server_id int unsigned,
274 master_server_id int unsigned,
275 master_epoch bigint unsigned,
276 count int unsigned,
277 a int not null,
278 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
279
280 create table t2_max (a int primary key, b int, X bigint unsigned) engine=ndb;
281
282 insert into t2_max values (1,1,10), (2,2,10), (3,3,10), (4,4,10), (5,5,10);
283
284 --sync_slave_with_master
285
286 --connection master
287 --echo Now issue a transaction with a successful Blob op, and unsuccessful
288 --echo non-Blob op. Check that the Blob op succeeds, and the unsuccessful
289 --echo non-Blob op is handled as expected.
290
291 begin;
292 update t2_max set b=b+1, X=1 where a=3; # conflicts
293 update t1_max set c=repeat("R", 10008), d=repeat("A", 10009), X = 21 where a=1; # ok
294 commit;
295
296 --sync_slave_with_master
297
298 --connection slave
299 --echo Contents on Slave
300 --echo Expect Blob data applied to t1_max, no update applied to t2_max
301 select a,b,left(c,1), length(c), left(d,1), length(d), X from t1_max where a=1;
302 select * from t2_max order by a;
303
304 --echo Expect No conflict in t1_max, 1 conflict in t2_max$EX
305 select server_id, master_server_id, count, a from t1_max$EX order by count;
306 select server_id, master_server_id, count, a from t2_max$EX order by count;
307
308 --connection master
309 drop table t2_max, t2_max$EX;
310 drop table t1_old, `t1_old$EX`, t1_max, `t1_max$EX`, t1_max_delete_win, `t1_max_delete_win$EX`;
311 delete from mysql.ndb_replication;
312
313 --echo Test that online table distribution sets up conflict functions and exceptions tables
314
315 # t1allsame - Same on all servers, no exceptions table
316 insert into mysql.ndb_replication values ("test", "t1allsame", 0, 7, "NDB$MAX(X)");
317
318 # t2diffex - Different on each server with an exceptions table
319 # Not a recommended configuration!
320 insert into mysql.ndb_replication values ("test", "t2diffex", 1, 7, "NDB$OLD(X)");
321 insert into mysql.ndb_replication values ("test", "t2diffex", 3, 7, "NDB$MAX(X)");
322
323 # t3oneex - Only on one server with an exceptions table
324 # Note that it's not defined on the server where it's created (not recommended)
325 # so on the server where it's defined, we get an error due to having no extra
326 # author bits
327 #
328 insert into mysql.ndb_replication values ("test", "t3oneex", 3, 7, "NDB$EPOCH()");
329
330 # Create exception tables
331 create table t2diffex$EX (
332 server_id int unsigned,
333 master_server_id int unsigned,
334 master_epoch bigint unsigned,
335 count int unsigned,
336 a int not null,
337 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
338
339 create table t3oneex$EX (
340 server_id int unsigned,
341 master_server_id int unsigned,
342 master_epoch bigint unsigned,
343 count int unsigned,
344 a int not null,
345 primary key(server_id, master_server_id, master_epoch, count)) engine ndb;
346
347 # Now create actual tables on this server (id 1)
348 create table t1allsame(a int primary key, b varchar(200), X int unsigned) engine=ndb;
349 create table t2diffex(a int primary key, b varchar(200), X int unsigned) engine=ndb;
350 --replace_regex /Node [0-9]+:/Node <nodeid>/
351 create table t3oneex(a int primary key, b varchar(200)) engine=ndb;
352
353 # Now examine server logs to see that conflict detection and
354 # exceptions tables were setup as expected
355 show variables like 'server_id';
356 --let $server_num=1.1
357 --let $pattern=%Replica:%
358 --let $limit=4
359 --source suite/ndb_rpl/t/show_mysqld_warnings.inc
360
361
362 --connection server2
363 --disable_query_log
364 call mtr.add_suppression("Replica: .* No extra row author bits in table.*");
365 call mtr.add_suppression("Failed to setup binlogging for table 'test.t3oneex'");
366 call mtr.add_suppression("Distribution of CREATE TABLE 'test.t3oneex' failed");
367 --enable_query_log
368
369 show variables like 'server_id';
370 --let $server_num=2.1
371 --let $pattern=%Replica:%
372 --let $limit=6
373
374 --source suite/ndb_rpl/t/show_mysqld_warnings.inc
375
376 --connection master
377 drop table t3oneex, t2diffex, t1allsame, t3oneex$EX, t2diffex$EX;
378
379 delete from mysql.ndb_replication;
380
381 --echo Test exceptions table schema flexibility
382 #
383 # An exceptions table should be able to have the mandatory columns with
384 # different names, as long as the types match.
385 # Also, not all main table primary key parts need be present
386 # Finally, arbitrary extra columns should be allowed, as long as
387 # they can be defaulted.
388 #
389
390 insert into mysql.ndb_replication values ("test", "t1", 0, 7, "NDB$MAX(X)");
391
392 --echo Test 'normal' mandatory column names + all table pks
393
394 create table test.t1$EX(
395 server_id int unsigned,
396 master_server_id int unsigned,
397 master_epoch bigint unsigned,
398 count int unsigned,
399 a int not null,
400 b int not null,
401 c int not null,
402 primary key (server_id, master_server_id, master_epoch, count)) engine=ndb;
403
404 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
405 primary key(a,b,c)) engine=ndb;
406
407 --echo Generate a conflict on the slave
408
409 insert into test.t1 values (1,1,1,1,1,1);
410 --sync_slave_with_master slave
411 --connection master
412 update test.t1 set X=0 where a=1 and b=1 and c=1;
413 --sync_slave_with_master slave
414 --connection slave
415
416 --echo Check that conflict has been recorded.
417 --replace_column 3 <epoch_num>
418 select * from test.t1$EX;
419
420 --connection master
421 drop table test.t1;
422 drop table test.t1$EX;
423
424 --echo Test 'normal' mandatory column names + all table pks +
425 --echo extra columns with same and different names to main table columns
426 --echo Also a defaulted extra column.
427
428 create table test.t1$EX(
429 server_id int unsigned,
430 master_server_id int unsigned,
431 master_epoch bigint unsigned,
432 count int unsigned,
433 a int not null,
434 b int not null,
435 c int not null,
436 d int, # Same name as main table, but user defined
437 lilljeholmen varchar(50) default 'Slussen',
438 # Separate, user defined
439 primary key (server_id, master_server_id, master_epoch, count)) engine=ndb;
440
441 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
442 primary key(a,b,c)) engine=ndb;
443
444 --echo Generate a conflict on the slave
445
446 insert into test.t1 values (1,1,1,1,1,1);
447 --sync_slave_with_master slave
448 --connection master
449 update test.t1 set X=0 where a=1 and b=1 and c=1;
450 --sync_slave_with_master slave
451 --connection slave
452
453 --echo Check that conflict has been recorded.
454 --replace_column 3 <epoch_num>
455 select * from test.t1$EX;
456
457 --connection master
458 drop table test.t1;
459 drop table test.t1$EX;
460
461 --echo Test unusual mandatory column names + all table pks +
462 --echo extra columns with same and different names to main table columns
463 --echo Also a defaulted extra column.
464
465 create table test.t1$EX(
466 monteverdi int unsigned,
467 asparagi int unsigned,
468 plenipotentiary bigint unsigned,
469 mountebank int unsigned,
470 a int not null,
471 b int not null,
472 c int not null,
473 d int, # Same name as main table, but user defined
474 lilljeholmen varchar(50) default 'Slussen',
475 # Separate, user defined
476 primary key (monteverdi, asparagi, plenipotentiary, mountebank)) engine=ndb;
477
478 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
479 primary key(a,b,c)) engine=ndb;
480
481 --echo Generate a conflict on the slave
482
483 insert into test.t1 values (1,1,1,1,1,1);
484 --sync_slave_with_master slave
485 --connection master
486 update test.t1 set X=0 where a=1 and b=1 and c=1;
487 --sync_slave_with_master slave
488 --connection slave
489
490 --echo Check that conflict has been recorded.
491 --replace_column 3 <epoch_num>
492 select * from test.t1$EX;
493
494 --connection master
495 drop table test.t1;
496 drop table test.t1$EX;
497
498 --echo Test unusual mandatory column names + all table pks which are same
499 --echo as 'normal' exceptions table column names plus extra columns with
500 --echo same and different names to main table columns
501 --echo Also a defaulted extra column.
502
503 create table test.t1$EX(
504 monteverdi int unsigned,
505 asparagi int unsigned,
506 plenipotentiary bigint unsigned,
507 mountebank int unsigned,
508 server_id int unsigned not null,
509 master_server_id int unsigned not null,
510 master_epoch bigint unsigned not null,
511 count int unsigned not null,
512 d int, # Same name as main table, but user defined
513 lilljeholmen varchar(50) default 'Slussen',
514 # Separate, user defined
515 primary key (monteverdi, asparagi, plenipotentiary, mountebank)) engine=ndb;
516
517 create table test.t1 (server_id int unsigned,
518 master_server_id int unsigned,
519 master_epoch bigint unsigned,
520 count int unsigned,
521 d int, e int, X int unsigned,
522 primary key(server_id, master_server_id,
523 master_epoch, count)) engine=ndb;
524
525 --echo Generate a conflict on the slave
526
527 insert into test.t1 values (1,1,1,1,1,1,1);
528 --sync_slave_with_master slave
529 --connection master
530 update test.t1 set X=0 where server_id=1 and master_server_id=1 and master_epoch=1 and count=1;
531 --sync_slave_with_master slave
532 --connection slave
533
534 --echo Check that conflict has been recorded.
535 --replace_column 3 <epoch_num>
536 select * from test.t1$EX;
537
538 --connection master
539 drop table test.t1;
540 drop table test.t1$EX;
541
542 --disable_query_log
543 --connection server1
544 call mtr.add_suppression("Replica: exceptions table .* has wrong definition .*");
545 --connection server2
546 call mtr.add_suppression("Replica: exceptions table .* has wrong definition .*");
547 --connection slave
548 call mtr.add_suppression("Replica: exceptions table .* has wrong definition .*");
549 --connection master
550 --enable_query_log
551
552 --echo And some bad exceptions table schemata
553 --echo Keys in wrong positions
554 create table test.t1$EX(
555 a int not null,
556 b int not null,
557 c int not null,
558 d int, # Same name as main table, but user defined
559 lilljeholmen varchar(50) default 'Slussen',
560 # Separate, user defined
561 server_id int unsigned,
562 master_server_id int unsigned,
563 master_epoch bigint unsigned,
564 count int unsigned,
565 primary key (server_id, master_server_id, master_epoch, count)) engine=ndb;
566
567 create table test.t1 (a int, b int, c int, d int, e int, X int unsigned,
568 primary key(a,b,c)) engine=ndb;
569 show warnings;
570
571 --let $server_num=1.1
572 --let $pattern=%Replica\:%
573 --let $limit=2
574
575 --source suite/ndb_rpl/t/show_mysqld_warnings.inc
576
577 drop table test.t1;
578 drop table test.t1$EX;
579
580 --echo Test conflict_fn definitions in ndb_replication which cover
581 --echo exceptions tables and verify that conflict functions are not
582 --echo set up for exceptions tables.
583
584 --connection master
585 insert into mysql.ndb_replication values ("test", "t2%", 1, 7, "NDB$EPOCH()");
586
587 create table test.t2$EX (
588 server_id int unsigned NOT NULL,
589 master_server_id int unsigned NOT NULL,
590 master_epoch bigint unsigned NOT NULL,
591 count int unsigned NOT NULL,
592 a int NOT NULL,
593 PRIMARY KEY (server_id,master_server_id,master_epoch,count)
594 ) ENGINE=ndbcluster;
595
596 create table test.t2 (
597 a int primary key,
598 b varchar(100)) engine=ndb;
599
600 --let mysqld_name=mysqld.1.1
601 --source ndb_rpl_get_server_error_log.inc
602 --disable_query_log
603 # t2$EX is exceptions table, conflict function should not be used
604 select count(*) from test.server_error_log where line like '%Replica%t2$EX is exceptions table: not using conflict function%';
605 # normal table test.t2 is not exceptions table, should have conflict function
606 select count(*) from test.server_error_log where line like '%Replica%test.t2 using conflict_fn NDB$EPOCH%';
607 --enable_query_log
608
609 drop table test.server_error_log;
610 drop table test.t2;
611 drop table test.t2$EX;
612
613 ###############
614 --echo "Cleanup"
615
616 --connection master
617 drop table mysql.ndb_replication;
618 --sync_slave_with_master
619
620 --source include/rpl_end.inc