1include/master-slave.inc 2[connection master] 3Setup circular replication 4RESET MASTER; 5select @slave_server_id:=(variable_value+0) 6from information_schema.global_variables 7where variable_name like 'server_id'; 8@slave_server_id:=(variable_value+0) 93 10CHANGE MASTER TO master_host="127.0.0.1",master_port=SLAVE_PORT,master_user="root"; 11START SLAVE; 12select @master_server_id:=(variable_value+0) 13from information_schema.global_variables 14where variable_name like 'server_id'; 15@master_server_id:=(variable_value+0) 161 17Setup ndb_replication and t1 exceptions table 18Populate ndb_replication table as necessary 19-- 0 extra gci bits 20replace into mysql.ndb_replication values 21("test", "t1", 3, 7, NULL), 22("test", "t1", 1, 7, "NDB$EPOCH(0)"); 23create table `test`.`t1$EX` 24 (server_id int unsigned, 25master_server_id int unsigned, 26master_epoch bigint unsigned, 27count int unsigned, 28a int not null, 29d int, 30primary key(server_id, master_server_id, master_epoch, count)) engine ndb; 31Create table 32create table test.t1(a int primary key, b varchar(255)) engine = ndb; 33Create other table 34create table test.t2(a int primary key, b int) engine = ndb; 35---------------------------------- 36Test 1 : Basic two-way replication 37---------------------------------- 38insert into test.t1 values (1, "Metropole"); 39-- Give time for a new epoch on the Master 40-- Insert something to ensure the new epoch is noticed 41replace into test.t2 values (2, 1); 42-- Flushed to slave 43select * from test.t1 order by a; 44a b 451 Metropole 46-- Flushed back to Master 47select * from test.t1 order by a; 48a b 491 Metropole 50-- Now update data on slave 51update test.t1 set b="Favorit" where a=1; 52-- Now check data on master 53select * from test.t1 order by a; 54a b 551 Favorit 56SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 57VARIABLE_VALUE-@init_ndb_conflict_fn_max 580 59SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 60VARIABLE_VALUE-@init_ndb_conflict_fn_old 610 62SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 63VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 640 65SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 66VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 670 68SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 69server_id master_server_id master_epoch count a d 70SELECT * FROM `t1$EX` ORDER BY a, d; 71server_id master_server_id master_epoch count a d 72SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 73SELECT * FROM `t2$EX` ORDER BY a, d; 74-- Now perform multiple consecutive updates on the slave 75update test.t1 set b="Elephant house" where a=1; 76update test.t1 set b="Beach house" where a=1; 77select * from test.t1 order by a; 78a b 791 Beach house 80-- Now check they've applied on the master 81select * from test.t1 order by a; 82a b 831 Beach house 84SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 85VARIABLE_VALUE-@init_ndb_conflict_fn_max 860 87SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 88VARIABLE_VALUE-@init_ndb_conflict_fn_old 890 90SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 91VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 920 93SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 94VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 950 96SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 97server_id master_server_id master_epoch count a d 98SELECT * FROM `t1$EX` ORDER BY a, d; 99server_id master_server_id master_epoch count a d 100SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 101SELECT * FROM `t2$EX` ORDER BY a, d; 102-------------------------------------------- 103Test 2 : Normal Insert from Secondary Master 104-------------------------------------------- 105-- Insert a new row on the Slave 106insert into test.t1 values (2, "Forrest"); 107-- Check it exists on the Master 108select * from test.t1 order by a; 109a b 1101 Beach house 1112 Forrest 112-- Update from the slave 113update test.t1 set b="Reds" where a=2; 114select * from test.t1 order by a; 115a b 1161 Beach house 1172 Reds 118delete from test.t1 where a=2; 119select * from test.t1 order by a; 120a b 1211 Beach house 122SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 123VARIABLE_VALUE-@init_ndb_conflict_fn_max 1240 125SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 126VARIABLE_VALUE-@init_ndb_conflict_fn_old 1270 128SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 129VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 1300 131SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 132VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 1330 134SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 135server_id master_server_id master_epoch count a d 136SELECT * FROM `t1$EX` ORDER BY a, d; 137server_id master_server_id master_epoch count a d 138SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 139SELECT * FROM `t2$EX` ORDER BY a, d; 140------------------------------- 141Test 3 : Insert-Insert conflict 142------------------------------- 143stop slave; 144-- Insert a row on the Primary Master 145insert into test.t1 values (2, "Loopy Lornas"); 146-- Insert a row on the secondary Master 147insert into test.t1 values (2, "Cloisters"); 148-- Examine data on Primary Master (should be unaffected) 149select * from test.t1 order by a; 150a b 1511 Beach house 1522 Loopy Lornas 153-- Examine conflict indicators on Primary Master 154SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 155VARIABLE_VALUE-@init_ndb_conflict_fn_max 1560 157SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 158VARIABLE_VALUE-@init_ndb_conflict_fn_old 1590 160SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 161VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 1620 163SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 164VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 1651 166SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 167server_id master_server_id master_epoch count a d 1681 3 # 1 # # 169SELECT * FROM `t1$EX` ORDER BY a, d; 170server_id master_server_id master_epoch count a d 1711 3 # # 2 NULL 172SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 173SELECT * FROM `t2$EX` ORDER BY a, d; 174-- Examine data on isolated secondary Master (should be as-set) 175select * from test.t1 order by a; 176a b 1771 Beach house 1782 Cloisters 179-- Restart secondary Masters slave 180start slave; 181-- Reexamine secondary Master's data (should be same as Primary Masters) 182select * from test.t1 order by a; 183a b 1841 Beach house 1852 Loopy Lornas 186------------------------------- 187Test 4 : Update-Update conflict 188------------------------------- 189-- Stop replication to secondary master 190stop slave; 191-- Update row on Primary Master 192update test.t1 set b="Peters Yard" where a=2; 193-- Show data on Primary Master 194select * from test.t1 order by a; 195a b 1961 Beach house 1972 Peters Yard 198SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 199VARIABLE_VALUE-@init_ndb_conflict_fn_max 2000 201SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 202VARIABLE_VALUE-@init_ndb_conflict_fn_old 2030 204SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 205VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 2060 207SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 208VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 2091 210SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 211server_id master_server_id master_epoch count a d 2121 3 # 1 # # 213SELECT * FROM `t1$EX` ORDER BY a, d; 214server_id master_server_id master_epoch count a d 2151 3 # # 2 NULL 216SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 217SELECT * FROM `t2$EX` ORDER BY a, d; 218-- Update row on Secondary Master 219update test.t1 set b="Toast" where a=2; 220-- Examine data on Primary Master - should be unaffected 221select * from test.t1 order by a; 222a b 2231 Beach house 2242 Peters Yard 225SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 226VARIABLE_VALUE-@init_ndb_conflict_fn_max 2270 228SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 229VARIABLE_VALUE-@init_ndb_conflict_fn_old 2300 231SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 232VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 2330 234SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 235VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 2362 237SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 238server_id master_server_id master_epoch count a d 2391 3 # 1 # # 2401 3 # 2 # # 241SELECT * FROM `t1$EX` ORDER BY a, d; 242server_id master_server_id master_epoch count a d 2431 3 # # 2 NULL 2441 3 # # 2 NULL 245SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 246SELECT * FROM `t2$EX` ORDER BY a, d; 247-- Check data on secondary - should be as set 248select * from test.t1 order by a; 249a b 2501 Beach house 2512 Toast 252-- Now restart slave, will re-align row 253start slave; 254-- Check that Secondary is re-aligned 255select * from test.t1 order by a; 256a b 2571 Beach house 2582 Peters Yard 259------------------------------- 260Test 5 : Update-Delete conflict 261------------------------------- 262-- Stop Secondary slave 263stop slave; 264-- Update on Primary Master 265update test.t1 set b="Pear tree" where a = 2; 266-- Delete on Secondary Master 267delete from test.t1 where a = 2; 268-- Examine data on Primary Master, should be unaffected 269select * from test.t1 order by a; 270a b 2711 Beach house 2722 Pear tree 273SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 274VARIABLE_VALUE-@init_ndb_conflict_fn_max 2750 276SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 277VARIABLE_VALUE-@init_ndb_conflict_fn_old 2780 279SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 280VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 2810 282SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 283VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 2843 285SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 286server_id master_server_id master_epoch count a d 2871 3 # 1 # # 2881 3 # 2 # # 2891 3 # 3 # # 290SELECT * FROM `t1$EX` ORDER BY a, d; 291server_id master_server_id master_epoch count a d 2921 3 # # 2 NULL 2931 3 # # 2 NULL 2941 3 # # 2 NULL 295SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 296SELECT * FROM `t2$EX` ORDER BY a, d; 297-- Examine data on Secondary Master before slave restart, still missing 298select * from test.t1 order by a; 299a b 3001 Beach house 301-- Restart Secondary Master slave 302start slave; 303-- Examine data on Secondary Master after slave restart, aligned with Master 304select * from test.t1 order by a; 305a b 3061 Beach house 3072 Pear tree 308------------------------------- 309Test 6 : Delete-Update conflict 310------------------------------- 311-- Stop Secondary slave 312stop slave; 313-- Delete on Primary Master 314delete from test.t1 where a=2; 315-- Update on Secondary Master 316update test.t1 set b="Black pig" where a=2; 317-- Examine data on Primary Master, should be unaffected (no row) 318select * from test.t1 order by a; 319a b 3201 Beach house 321SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 322VARIABLE_VALUE-@init_ndb_conflict_fn_max 3230 324SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 325VARIABLE_VALUE-@init_ndb_conflict_fn_old 3260 327SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 328VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 3290 330SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 331VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 3324 333SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 334server_id master_server_id master_epoch count a d 3351 3 # 1 # # 3361 3 # 2 # # 3371 3 # 3 # # 3381 3 # 4 # # 339SELECT * FROM `t1$EX` ORDER BY a, d; 340server_id master_server_id master_epoch count a d 3411 3 # # 2 NULL 3421 3 # # 2 NULL 3431 3 # # 2 NULL 3441 3 # # 2 NULL 345SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 346SELECT * FROM `t2$EX` ORDER BY a, d; 347-- Examine data on Secondary Master before slave restart, should be as inserted 348select * from test.t1 order by a; 349a b 3501 Beach house 3512 Black pig 352-- Restart Secondary Master slave 353start slave; 354-- Examine data on Secondary Master after slave restart, aligned with Master (deleted) 355select * from test.t1 order by a; 356a b 3571 Beach house 358------------------------------- 359Test 7 : Delete-Delete conflict 360------------------------------- 361-- Stop Secondary slave 362stop slave; 363-- Delete on Primary Master 364delete from test.t1 where a=1; 365-- Delete on Secondary Master 366delete from test.t1 where a=1; 367-- Examine data on Primary Master, no row 368select * from test.t1 order by a; 369a b 370SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 371VARIABLE_VALUE-@init_ndb_conflict_fn_max 3720 373SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 374VARIABLE_VALUE-@init_ndb_conflict_fn_old 3750 376SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 377VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 3780 379SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 380VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 3815 382SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 383server_id master_server_id master_epoch count a d 3841 3 # 1 # # 3851 3 # 2 # # 3861 3 # 3 # # 3871 3 # 4 # # 3881 3 # 5 # # 389SELECT * FROM `t1$EX` ORDER BY a, d; 390server_id master_server_id master_epoch count a d 3911 3 # # 1 NULL 3921 3 # # 2 NULL 3931 3 # # 2 NULL 3941 3 # # 2 NULL 3951 3 # # 2 NULL 396SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 397SELECT * FROM `t2$EX` ORDER BY a, d; 398-- Examine data on Secondary Master before slave restart, no row 399select * from test.t1 order by a; 400a b 401-- Restart Secondary Master slave 402start slave; 403-- Examine data on Secondary Master after slave restart, no row 404select * from test.t1 order by a; 405a b 406------------------------------------------------ 407Test 8 : Delete-Delete, Insert conflict exposure 408------------------------------------------------ 409-- Insert a row on Secondary Master 410insert into test.t1 values (3, "Espy"); 411-- Check it's present on Primary Master 412select * from test.t1 order by a; 413a b 4143 Espy 415-- Stop replication in both directions 416stop slave; 417stop slave; 418-- Delete row from both clusters 419delete from test.t1 where a=3; 420delete from test.t1 where a=3; 421-- Follow up with Insert from Secondary master 422insert into test.t1 values (3, "Dalriada"); 423-- Restart replication in both directions 424start slave; 425start slave; 426-- Check data on both sites - diverged 427-- Secondary master : 428select * from test.t1 order by a; 429a b 430-- Primary master : 431select * from test.t1 order by a; 432a b 4333 Dalriada 434--Remove extra row 435delete from test.t1 where a=3; 436-- Note that Delete-Delete conflict detected below 437SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 438VARIABLE_VALUE-@init_ndb_conflict_fn_max 4390 440SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 441VARIABLE_VALUE-@init_ndb_conflict_fn_old 4420 443SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 444VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 4450 446SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 447VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 4486 449SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 450server_id master_server_id master_epoch count a d 4511 3 # 1 # # 4521 3 # 2 # # 4531 3 # 3 # # 4541 3 # 4 # # 4551 3 # 5 # # 4561 3 # 6 # # 457SELECT * FROM `t1$EX` ORDER BY a, d; 458server_id master_server_id master_epoch count a d 4591 3 # # 1 NULL 4601 3 # # 2 NULL 4611 3 # # 2 NULL 4621 3 # # 2 NULL 4631 3 # # 2 NULL 4641 3 # # 3 NULL 465SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 466SELECT * FROM `t2$EX` ORDER BY a, d; 467------------------------------------------------ 468Test 9 : Insert, Insert-Update-Delete conflict 469------------------------------------------------ 470-- Stop replication on Secondary Master 471stop slave; 472-- Insert row on Primary Master 473insert into test.t1 values (4, "Haymarket"); 474-- Insert row on Secondary Master 475insert into test.t1 values (4, "Outhouse"); 476-- Update row on Secondary Master 477update test.t1 set b="Mathers" where a = 4; 478-- Delete row on Secondary Master 479delete from test.t1 where a=4; 480-- Examine data (none) on Secondary Master 481select * from test.t1 order by a; 482a b 483-- Examine data on Primary Master, should be unaffected 484select * from test.t1 order by a; 485a b 4864 Haymarket 487SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 488VARIABLE_VALUE-@init_ndb_conflict_fn_max 4890 490SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 491VARIABLE_VALUE-@init_ndb_conflict_fn_old 4920 493SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 494VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 4950 496SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 497VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 4989 499SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 500server_id master_server_id master_epoch count a d 5011 3 # 1 # # 5021 3 # 2 # # 5031 3 # 3 # # 5041 3 # 4 # # 5051 3 # 5 # # 5061 3 # 6 # # 5071 3 # 7 # # 5081 3 # 8 # # 5091 3 # 9 # # 510SELECT * FROM `t1$EX` ORDER BY a, d; 511server_id master_server_id master_epoch count a d 5121 3 # # 1 NULL 5131 3 # # 2 NULL 5141 3 # # 2 NULL 5151 3 # # 2 NULL 5161 3 # # 2 NULL 5171 3 # # 3 NULL 5181 3 # # 4 NULL 5191 3 # # 4 NULL 5201 3 # # 4 NULL 521SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 522SELECT * FROM `t2$EX` ORDER BY a, d; 523-- Examine data on Secondary Master (none) 524select * from test.t1 order by a; 525a b 526-- Restart Secondary Master's slave 527start slave; 528-- Check data on Secondary Master, should be same as Primary Master 529select * from test.t1; 530a b 5314 Haymarket 532------------------------------------------------ 533Test 10 : Update, Delete-Insert-Update conflict 534------------------------------------------------ 535-- Stop replication on Secondary Master 536stop slave; 537-- Update row on Primary Master 538update test.t1 set b="Blind poet" where a=4; 539-- Delete row on Secondary Master 540delete from test.t1 where a=4; 541-- Insert row on Secondary Master 542insert into test.t1 values (4, "Drouthy Neebors"); 543-- Update row on Secondary Master 544update test.t1 set b="The Tankard" where a=4; 545-- Check data on Primary Master, should be unaffected 546select * from test.t1 order by a; 547a b 5484 Blind poet 549SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 550VARIABLE_VALUE-@init_ndb_conflict_fn_max 5510 552SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 553VARIABLE_VALUE-@init_ndb_conflict_fn_old 5540 555SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 556VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 5570 558SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 559VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 56012 561SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 562server_id master_server_id master_epoch count a d 5631 3 # 1 # # 5641 3 # 2 # # 5651 3 # 3 # # 5661 3 # 4 # # 5671 3 # 5 # # 5681 3 # 6 # # 5691 3 # 7 # # 5701 3 # 8 # # 5711 3 # 9 # # 5721 3 # 10 # # 5731 3 # 11 # # 5741 3 # 12 # # 575SELECT * FROM `t1$EX` ORDER BY a, d; 576server_id master_server_id master_epoch count a d 5771 3 # # 1 NULL 5781 3 # # 2 NULL 5791 3 # # 2 NULL 5801 3 # # 2 NULL 5811 3 # # 2 NULL 5821 3 # # 3 NULL 5831 3 # # 4 NULL 5841 3 # # 4 NULL 5851 3 # # 4 NULL 5861 3 # # 4 NULL 5871 3 # # 4 NULL 5881 3 # # 4 NULL 589SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 590SELECT * FROM `t2$EX` ORDER BY a, d; 591-- Check data on Secondary Master, as set 592select * from test.t1 order by a; 593a b 5944 The Tankard 595-- Restart Secondary Master slave 596start slave; 597-- Check data on Secondary Master - should be as Primary 598select * from test.t1 order by a; 599a b 6004 Blind poet 601------------------------------------------------------------------------ 602Test 11 : Test Secondary insert-update-delete accepted 603------------------------------------------------------------------------ 604Insert row on Secondary 605insert into test.t1 values (5, "Minders"); 606Update row on Secondary 607update test.t1 set b="Southsider" where a=5; 608Delete row on Secondary 609delete from test.t1 where a=5; 610Check data on Primary, should be none. No new conflicts 611select * from test.t1 order by a; 612a b 6134 Blind poet 614SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 615VARIABLE_VALUE-@init_ndb_conflict_fn_max 6160 617SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 618VARIABLE_VALUE-@init_ndb_conflict_fn_old 6190 620SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 621VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 6220 623SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 624VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 62512 626SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 627server_id master_server_id master_epoch count a d 6281 3 # 1 # # 6291 3 # 2 # # 6301 3 # 3 # # 6311 3 # 4 # # 6321 3 # 5 # # 6331 3 # 6 # # 6341 3 # 7 # # 6351 3 # 8 # # 6361 3 # 9 # # 6371 3 # 10 # # 6381 3 # 11 # # 6391 3 # 12 # # 640SELECT * FROM `t1$EX` ORDER BY a, d; 641server_id master_server_id master_epoch count a d 6421 3 # # 1 NULL 6431 3 # # 2 NULL 6441 3 # # 2 NULL 6451 3 # # 2 NULL 6461 3 # # 2 NULL 6471 3 # # 3 NULL 6481 3 # # 4 NULL 6491 3 # # 4 NULL 6501 3 # # 4 NULL 6511 3 # # 4 NULL 6521 3 # # 4 NULL 6531 3 # # 4 NULL 654SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 655SELECT * FROM `t2$EX` ORDER BY a, d; 656SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 657VARIABLE_VALUE-@init_ndb_conflict_fn_max 6580 659SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 660VARIABLE_VALUE-@init_ndb_conflict_fn_old 6610 662SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 663VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 6640 665SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 666VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 66712 668SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 669server_id master_server_id master_epoch count a d 6701 3 # 1 # # 6711 3 # 2 # # 6721 3 # 3 # # 6731 3 # 4 # # 6741 3 # 5 # # 6751 3 # 6 # # 6761 3 # 7 # # 6771 3 # 8 # # 6781 3 # 9 # # 6791 3 # 10 # # 6801 3 # 11 # # 6811 3 # 12 # # 682SELECT * FROM `t1$EX` ORDER BY a, d; 683server_id master_server_id master_epoch count a d 6841 3 # # 1 NULL 6851 3 # # 2 NULL 6861 3 # # 2 NULL 6871 3 # # 2 NULL 6881 3 # # 2 NULL 6891 3 # # 3 NULL 6901 3 # # 4 NULL 6911 3 # # 4 NULL 6921 3 # # 4 NULL 6931 3 # # 4 NULL 6941 3 # # 4 NULL 6951 3 # # 4 NULL 696SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 697SELECT * FROM `t2$EX` ORDER BY a, d; 698drop table test.t1; 699drop table test.t2; 700drop table test.t1$EX; 701Populate ndb_replication table as necessary 702-- 1 extra gci bits 703replace into mysql.ndb_replication values 704("test", "t1", 3, 7, NULL), 705("test", "t1", 1, 7, "NDB$EPOCH(1)"); 706create table `test`.`t1$EX` 707 (server_id int unsigned, 708master_server_id int unsigned, 709master_epoch bigint unsigned, 710count int unsigned, 711a int not null, 712d int, 713primary key(server_id, master_server_id, master_epoch, count)) engine ndb; 714Create table 715create table test.t1(a int primary key, b varchar(255)) engine = ndb; 716Create other table 717create table test.t2(a int primary key, b int) engine = ndb; 718---------------------------------- 719Test 1 : Basic two-way replication 720---------------------------------- 721insert into test.t1 values (1, "Metropole"); 722-- Give time for a new epoch on the Master 723-- Insert something to ensure the new epoch is noticed 724replace into test.t2 values (2, 1); 725-- Flushed to slave 726select * from test.t1 order by a; 727a b 7281 Metropole 729-- Flushed back to Master 730select * from test.t1 order by a; 731a b 7321 Metropole 733-- Now update data on slave 734update test.t1 set b="Favorit" where a=1; 735-- Now check data on master 736select * from test.t1 order by a; 737a b 7381 Favorit 739SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 740VARIABLE_VALUE-@init_ndb_conflict_fn_max 7410 742SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 743VARIABLE_VALUE-@init_ndb_conflict_fn_old 7440 745SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 746VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 7470 748SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 749VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 7500 751SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 752server_id master_server_id master_epoch count a d 753SELECT * FROM `t1$EX` ORDER BY a, d; 754server_id master_server_id master_epoch count a d 755SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 756SELECT * FROM `t2$EX` ORDER BY a, d; 757-- Now perform multiple consecutive updates on the slave 758update test.t1 set b="Elephant house" where a=1; 759update test.t1 set b="Beach house" where a=1; 760select * from test.t1 order by a; 761a b 7621 Beach house 763-- Now check they've applied on the master 764select * from test.t1 order by a; 765a b 7661 Beach house 767SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 768VARIABLE_VALUE-@init_ndb_conflict_fn_max 7690 770SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 771VARIABLE_VALUE-@init_ndb_conflict_fn_old 7720 773SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 774VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 7750 776SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 777VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 7780 779SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 780server_id master_server_id master_epoch count a d 781SELECT * FROM `t1$EX` ORDER BY a, d; 782server_id master_server_id master_epoch count a d 783SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 784SELECT * FROM `t2$EX` ORDER BY a, d; 785-------------------------------------------- 786Test 2 : Normal Insert from Secondary Master 787-------------------------------------------- 788-- Insert a new row on the Slave 789insert into test.t1 values (2, "Forrest"); 790-- Check it exists on the Master 791select * from test.t1 order by a; 792a b 7931 Beach house 7942 Forrest 795-- Update from the slave 796update test.t1 set b="Reds" where a=2; 797select * from test.t1 order by a; 798a b 7991 Beach house 8002 Reds 801delete from test.t1 where a=2; 802select * from test.t1 order by a; 803a b 8041 Beach house 805SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 806VARIABLE_VALUE-@init_ndb_conflict_fn_max 8070 808SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 809VARIABLE_VALUE-@init_ndb_conflict_fn_old 8100 811SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 812VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 8130 814SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 815VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 8160 817SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 818server_id master_server_id master_epoch count a d 819SELECT * FROM `t1$EX` ORDER BY a, d; 820server_id master_server_id master_epoch count a d 821SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 822SELECT * FROM `t2$EX` ORDER BY a, d; 823------------------------------- 824Test 3 : Insert-Insert conflict 825------------------------------- 826stop slave; 827-- Insert a row on the Primary Master 828insert into test.t1 values (2, "Loopy Lornas"); 829-- Insert a row on the secondary Master 830insert into test.t1 values (2, "Cloisters"); 831-- Examine data on Primary Master (should be unaffected) 832select * from test.t1 order by a; 833a b 8341 Beach house 8352 Loopy Lornas 836-- Examine conflict indicators on Primary Master 837SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 838VARIABLE_VALUE-@init_ndb_conflict_fn_max 8390 840SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 841VARIABLE_VALUE-@init_ndb_conflict_fn_old 8420 843SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 844VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 8450 846SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 847VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 8481 849SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 850server_id master_server_id master_epoch count a d 8511 3 # 1 # # 852SELECT * FROM `t1$EX` ORDER BY a, d; 853server_id master_server_id master_epoch count a d 8541 3 # # 2 NULL 855SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 856SELECT * FROM `t2$EX` ORDER BY a, d; 857-- Examine data on isolated secondary Master (should be as-set) 858select * from test.t1 order by a; 859a b 8601 Beach house 8612 Cloisters 862-- Restart secondary Masters slave 863start slave; 864-- Reexamine secondary Master's data (should be same as Primary Masters) 865select * from test.t1 order by a; 866a b 8671 Beach house 8682 Loopy Lornas 869------------------------------- 870Test 4 : Update-Update conflict 871------------------------------- 872-- Stop replication to secondary master 873stop slave; 874-- Update row on Primary Master 875update test.t1 set b="Peters Yard" where a=2; 876-- Show data on Primary Master 877select * from test.t1 order by a; 878a b 8791 Beach house 8802 Peters Yard 881SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 882VARIABLE_VALUE-@init_ndb_conflict_fn_max 8830 884SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 885VARIABLE_VALUE-@init_ndb_conflict_fn_old 8860 887SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 888VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 8890 890SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 891VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 8921 893SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 894server_id master_server_id master_epoch count a d 8951 3 # 1 # # 896SELECT * FROM `t1$EX` ORDER BY a, d; 897server_id master_server_id master_epoch count a d 8981 3 # # 2 NULL 899SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 900SELECT * FROM `t2$EX` ORDER BY a, d; 901-- Update row on Secondary Master 902update test.t1 set b="Toast" where a=2; 903-- Examine data on Primary Master - should be unaffected 904select * from test.t1 order by a; 905a b 9061 Beach house 9072 Peters Yard 908SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 909VARIABLE_VALUE-@init_ndb_conflict_fn_max 9100 911SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 912VARIABLE_VALUE-@init_ndb_conflict_fn_old 9130 914SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 915VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 9160 917SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 918VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 9192 920SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 921server_id master_server_id master_epoch count a d 9221 3 # 1 # # 9231 3 # 2 # # 924SELECT * FROM `t1$EX` ORDER BY a, d; 925server_id master_server_id master_epoch count a d 9261 3 # # 2 NULL 9271 3 # # 2 NULL 928SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 929SELECT * FROM `t2$EX` ORDER BY a, d; 930-- Check data on secondary - should be as set 931select * from test.t1 order by a; 932a b 9331 Beach house 9342 Toast 935-- Now restart slave, will re-align row 936start slave; 937-- Check that Secondary is re-aligned 938select * from test.t1 order by a; 939a b 9401 Beach house 9412 Peters Yard 942------------------------------- 943Test 5 : Update-Delete conflict 944------------------------------- 945-- Stop Secondary slave 946stop slave; 947-- Update on Primary Master 948update test.t1 set b="Pear tree" where a = 2; 949-- Delete on Secondary Master 950delete from test.t1 where a = 2; 951-- Examine data on Primary Master, should be unaffected 952select * from test.t1 order by a; 953a b 9541 Beach house 9552 Pear tree 956SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 957VARIABLE_VALUE-@init_ndb_conflict_fn_max 9580 959SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 960VARIABLE_VALUE-@init_ndb_conflict_fn_old 9610 962SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 963VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 9640 965SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 966VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 9673 968SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 969server_id master_server_id master_epoch count a d 9701 3 # 1 # # 9711 3 # 2 # # 9721 3 # 3 # # 973SELECT * FROM `t1$EX` ORDER BY a, d; 974server_id master_server_id master_epoch count a d 9751 3 # # 2 NULL 9761 3 # # 2 NULL 9771 3 # # 2 NULL 978SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 979SELECT * FROM `t2$EX` ORDER BY a, d; 980-- Examine data on Secondary Master before slave restart, still missing 981select * from test.t1 order by a; 982a b 9831 Beach house 984-- Restart Secondary Master slave 985start slave; 986-- Examine data on Secondary Master after slave restart, aligned with Master 987select * from test.t1 order by a; 988a b 9891 Beach house 9902 Pear tree 991------------------------------- 992Test 6 : Delete-Update conflict 993------------------------------- 994-- Stop Secondary slave 995stop slave; 996-- Delete on Primary Master 997delete from test.t1 where a=2; 998-- Update on Secondary Master 999update test.t1 set b="Black pig" where a=2; 1000-- Examine data on Primary Master, should be unaffected (no row) 1001select * from test.t1 order by a; 1002a b 10031 Beach house 1004SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1005VARIABLE_VALUE-@init_ndb_conflict_fn_max 10060 1007SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1008VARIABLE_VALUE-@init_ndb_conflict_fn_old 10090 1010SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1011VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 10120 1013SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1014VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 10154 1016SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1017server_id master_server_id master_epoch count a d 10181 3 # 1 # # 10191 3 # 2 # # 10201 3 # 3 # # 10211 3 # 4 # # 1022SELECT * FROM `t1$EX` ORDER BY a, d; 1023server_id master_server_id master_epoch count a d 10241 3 # # 2 NULL 10251 3 # # 2 NULL 10261 3 # # 2 NULL 10271 3 # # 2 NULL 1028SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1029SELECT * FROM `t2$EX` ORDER BY a, d; 1030-- Examine data on Secondary Master before slave restart, should be as inserted 1031select * from test.t1 order by a; 1032a b 10331 Beach house 10342 Black pig 1035-- Restart Secondary Master slave 1036start slave; 1037-- Examine data on Secondary Master after slave restart, aligned with Master (deleted) 1038select * from test.t1 order by a; 1039a b 10401 Beach house 1041------------------------------- 1042Test 7 : Delete-Delete conflict 1043------------------------------- 1044-- Stop Secondary slave 1045stop slave; 1046-- Delete on Primary Master 1047delete from test.t1 where a=1; 1048-- Delete on Secondary Master 1049delete from test.t1 where a=1; 1050-- Examine data on Primary Master, no row 1051select * from test.t1 order by a; 1052a b 1053SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1054VARIABLE_VALUE-@init_ndb_conflict_fn_max 10550 1056SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1057VARIABLE_VALUE-@init_ndb_conflict_fn_old 10580 1059SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1060VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 10610 1062SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1063VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 10645 1065SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1066server_id master_server_id master_epoch count a d 10671 3 # 1 # # 10681 3 # 2 # # 10691 3 # 3 # # 10701 3 # 4 # # 10711 3 # 5 # # 1072SELECT * FROM `t1$EX` ORDER BY a, d; 1073server_id master_server_id master_epoch count a d 10741 3 # # 1 NULL 10751 3 # # 2 NULL 10761 3 # # 2 NULL 10771 3 # # 2 NULL 10781 3 # # 2 NULL 1079SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1080SELECT * FROM `t2$EX` ORDER BY a, d; 1081-- Examine data on Secondary Master before slave restart, no row 1082select * from test.t1 order by a; 1083a b 1084-- Restart Secondary Master slave 1085start slave; 1086-- Examine data on Secondary Master after slave restart, no row 1087select * from test.t1 order by a; 1088a b 1089------------------------------------------------ 1090Test 8 : Delete-Delete, Insert conflict exposure 1091------------------------------------------------ 1092-- Insert a row on Secondary Master 1093insert into test.t1 values (3, "Espy"); 1094-- Check it's present on Primary Master 1095select * from test.t1 order by a; 1096a b 10973 Espy 1098-- Stop replication in both directions 1099stop slave; 1100stop slave; 1101-- Delete row from both clusters 1102delete from test.t1 where a=3; 1103delete from test.t1 where a=3; 1104-- Follow up with Insert from Secondary master 1105insert into test.t1 values (3, "Dalriada"); 1106-- Restart replication in both directions 1107start slave; 1108start slave; 1109-- Check data on both sites - diverged 1110-- Secondary master : 1111select * from test.t1 order by a; 1112a b 1113-- Primary master : 1114select * from test.t1 order by a; 1115a b 11163 Dalriada 1117--Remove extra row 1118delete from test.t1 where a=3; 1119-- Note that Delete-Delete conflict detected below 1120SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1121VARIABLE_VALUE-@init_ndb_conflict_fn_max 11220 1123SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1124VARIABLE_VALUE-@init_ndb_conflict_fn_old 11250 1126SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1127VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 11280 1129SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1130VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 11316 1132SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1133server_id master_server_id master_epoch count a d 11341 3 # 1 # # 11351 3 # 2 # # 11361 3 # 3 # # 11371 3 # 4 # # 11381 3 # 5 # # 11391 3 # 6 # # 1140SELECT * FROM `t1$EX` ORDER BY a, d; 1141server_id master_server_id master_epoch count a d 11421 3 # # 1 NULL 11431 3 # # 2 NULL 11441 3 # # 2 NULL 11451 3 # # 2 NULL 11461 3 # # 2 NULL 11471 3 # # 3 NULL 1148SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1149SELECT * FROM `t2$EX` ORDER BY a, d; 1150------------------------------------------------ 1151Test 9 : Insert, Insert-Update-Delete conflict 1152------------------------------------------------ 1153-- Stop replication on Secondary Master 1154stop slave; 1155-- Insert row on Primary Master 1156insert into test.t1 values (4, "Haymarket"); 1157-- Insert row on Secondary Master 1158insert into test.t1 values (4, "Outhouse"); 1159-- Update row on Secondary Master 1160update test.t1 set b="Mathers" where a = 4; 1161-- Delete row on Secondary Master 1162delete from test.t1 where a=4; 1163-- Examine data (none) on Secondary Master 1164select * from test.t1 order by a; 1165a b 1166-- Examine data on Primary Master, should be unaffected 1167select * from test.t1 order by a; 1168a b 11694 Haymarket 1170SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1171VARIABLE_VALUE-@init_ndb_conflict_fn_max 11720 1173SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1174VARIABLE_VALUE-@init_ndb_conflict_fn_old 11750 1176SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1177VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 11780 1179SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1180VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 11819 1182SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1183server_id master_server_id master_epoch count a d 11841 3 # 1 # # 11851 3 # 2 # # 11861 3 # 3 # # 11871 3 # 4 # # 11881 3 # 5 # # 11891 3 # 6 # # 11901 3 # 7 # # 11911 3 # 8 # # 11921 3 # 9 # # 1193SELECT * FROM `t1$EX` ORDER BY a, d; 1194server_id master_server_id master_epoch count a d 11951 3 # # 1 NULL 11961 3 # # 2 NULL 11971 3 # # 2 NULL 11981 3 # # 2 NULL 11991 3 # # 2 NULL 12001 3 # # 3 NULL 12011 3 # # 4 NULL 12021 3 # # 4 NULL 12031 3 # # 4 NULL 1204SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1205SELECT * FROM `t2$EX` ORDER BY a, d; 1206-- Examine data on Secondary Master (none) 1207select * from test.t1 order by a; 1208a b 1209-- Restart Secondary Master's slave 1210start slave; 1211-- Check data on Secondary Master, should be same as Primary Master 1212select * from test.t1; 1213a b 12144 Haymarket 1215------------------------------------------------ 1216Test 10 : Update, Delete-Insert-Update conflict 1217------------------------------------------------ 1218-- Stop replication on Secondary Master 1219stop slave; 1220-- Update row on Primary Master 1221update test.t1 set b="Blind poet" where a=4; 1222-- Delete row on Secondary Master 1223delete from test.t1 where a=4; 1224-- Insert row on Secondary Master 1225insert into test.t1 values (4, "Drouthy Neebors"); 1226-- Update row on Secondary Master 1227update test.t1 set b="The Tankard" where a=4; 1228-- Check data on Primary Master, should be unaffected 1229select * from test.t1 order by a; 1230a b 12314 Blind poet 1232SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1233VARIABLE_VALUE-@init_ndb_conflict_fn_max 12340 1235SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1236VARIABLE_VALUE-@init_ndb_conflict_fn_old 12370 1238SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1239VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 12400 1241SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1242VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 124312 1244SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1245server_id master_server_id master_epoch count a d 12461 3 # 1 # # 12471 3 # 2 # # 12481 3 # 3 # # 12491 3 # 4 # # 12501 3 # 5 # # 12511 3 # 6 # # 12521 3 # 7 # # 12531 3 # 8 # # 12541 3 # 9 # # 12551 3 # 10 # # 12561 3 # 11 # # 12571 3 # 12 # # 1258SELECT * FROM `t1$EX` ORDER BY a, d; 1259server_id master_server_id master_epoch count a d 12601 3 # # 1 NULL 12611 3 # # 2 NULL 12621 3 # # 2 NULL 12631 3 # # 2 NULL 12641 3 # # 2 NULL 12651 3 # # 3 NULL 12661 3 # # 4 NULL 12671 3 # # 4 NULL 12681 3 # # 4 NULL 12691 3 # # 4 NULL 12701 3 # # 4 NULL 12711 3 # # 4 NULL 1272SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1273SELECT * FROM `t2$EX` ORDER BY a, d; 1274-- Check data on Secondary Master, as set 1275select * from test.t1 order by a; 1276a b 12774 The Tankard 1278-- Restart Secondary Master slave 1279start slave; 1280-- Check data on Secondary Master - should be as Primary 1281select * from test.t1 order by a; 1282a b 12834 Blind poet 1284------------------------------------------------------------------------ 1285Test 11 : Test Secondary insert-update-delete accepted 1286------------------------------------------------------------------------ 1287Insert row on Secondary 1288insert into test.t1 values (5, "Minders"); 1289Update row on Secondary 1290update test.t1 set b="Southsider" where a=5; 1291Delete row on Secondary 1292delete from test.t1 where a=5; 1293Check data on Primary, should be none. No new conflicts 1294select * from test.t1 order by a; 1295a b 12964 Blind poet 1297SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1298VARIABLE_VALUE-@init_ndb_conflict_fn_max 12990 1300SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1301VARIABLE_VALUE-@init_ndb_conflict_fn_old 13020 1303SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1304VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 13050 1306SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1307VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 130812 1309SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1310server_id master_server_id master_epoch count a d 13111 3 # 1 # # 13121 3 # 2 # # 13131 3 # 3 # # 13141 3 # 4 # # 13151 3 # 5 # # 13161 3 # 6 # # 13171 3 # 7 # # 13181 3 # 8 # # 13191 3 # 9 # # 13201 3 # 10 # # 13211 3 # 11 # # 13221 3 # 12 # # 1323SELECT * FROM `t1$EX` ORDER BY a, d; 1324server_id master_server_id master_epoch count a d 13251 3 # # 1 NULL 13261 3 # # 2 NULL 13271 3 # # 2 NULL 13281 3 # # 2 NULL 13291 3 # # 2 NULL 13301 3 # # 3 NULL 13311 3 # # 4 NULL 13321 3 # # 4 NULL 13331 3 # # 4 NULL 13341 3 # # 4 NULL 13351 3 # # 4 NULL 13361 3 # # 4 NULL 1337SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1338SELECT * FROM `t2$EX` ORDER BY a, d; 1339SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1340VARIABLE_VALUE-@init_ndb_conflict_fn_max 13410 1342SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1343VARIABLE_VALUE-@init_ndb_conflict_fn_old 13440 1345SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1346VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 13470 1348SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1349VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 135012 1351SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1352server_id master_server_id master_epoch count a d 13531 3 # 1 # # 13541 3 # 2 # # 13551 3 # 3 # # 13561 3 # 4 # # 13571 3 # 5 # # 13581 3 # 6 # # 13591 3 # 7 # # 13601 3 # 8 # # 13611 3 # 9 # # 13621 3 # 10 # # 13631 3 # 11 # # 13641 3 # 12 # # 1365SELECT * FROM `t1$EX` ORDER BY a, d; 1366server_id master_server_id master_epoch count a d 13671 3 # # 1 NULL 13681 3 # # 2 NULL 13691 3 # # 2 NULL 13701 3 # # 2 NULL 13711 3 # # 2 NULL 13721 3 # # 3 NULL 13731 3 # # 4 NULL 13741 3 # # 4 NULL 13751 3 # # 4 NULL 13761 3 # # 4 NULL 13771 3 # # 4 NULL 13781 3 # # 4 NULL 1379SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1380SELECT * FROM `t2$EX` ORDER BY a, d; 1381drop table test.t1; 1382drop table test.t2; 1383drop table test.t1$EX; 1384Populate ndb_replication table as necessary 1385-- 31 extra gci bits 1386replace into mysql.ndb_replication values 1387("test", "t1", 3, 7, NULL), 1388("test", "t1", 1, 7, "NDB$EPOCH(31)"); 1389create table `test`.`t1$EX` 1390 (server_id int unsigned, 1391master_server_id int unsigned, 1392master_epoch bigint unsigned, 1393count int unsigned, 1394a int not null, 1395d int, 1396primary key(server_id, master_server_id, master_epoch, count)) engine ndb; 1397Create table 1398create table test.t1(a int primary key, b varchar(255)) engine = ndb; 1399Create other table 1400create table test.t2(a int primary key, b int) engine = ndb; 1401---------------------------------- 1402Test 1 : Basic two-way replication 1403---------------------------------- 1404insert into test.t1 values (1, "Metropole"); 1405-- Give time for a new epoch on the Master 1406-- Insert something to ensure the new epoch is noticed 1407replace into test.t2 values (2, 1); 1408-- Flushed to slave 1409select * from test.t1 order by a; 1410a b 14111 Metropole 1412-- Flushed back to Master 1413select * from test.t1 order by a; 1414a b 14151 Metropole 1416-- Now update data on slave 1417update test.t1 set b="Favorit" where a=1; 1418-- Now check data on master 1419select * from test.t1 order by a; 1420a b 14211 Favorit 1422SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1423VARIABLE_VALUE-@init_ndb_conflict_fn_max 14240 1425SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1426VARIABLE_VALUE-@init_ndb_conflict_fn_old 14270 1428SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1429VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 14300 1431SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1432VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 14330 1434SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1435server_id master_server_id master_epoch count a d 1436SELECT * FROM `t1$EX` ORDER BY a, d; 1437server_id master_server_id master_epoch count a d 1438SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1439SELECT * FROM `t2$EX` ORDER BY a, d; 1440-- Now perform multiple consecutive updates on the slave 1441update test.t1 set b="Elephant house" where a=1; 1442update test.t1 set b="Beach house" where a=1; 1443select * from test.t1 order by a; 1444a b 14451 Beach house 1446-- Now check they've applied on the master 1447select * from test.t1 order by a; 1448a b 14491 Beach house 1450SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1451VARIABLE_VALUE-@init_ndb_conflict_fn_max 14520 1453SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1454VARIABLE_VALUE-@init_ndb_conflict_fn_old 14550 1456SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1457VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 14580 1459SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1460VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 14610 1462SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1463server_id master_server_id master_epoch count a d 1464SELECT * FROM `t1$EX` ORDER BY a, d; 1465server_id master_server_id master_epoch count a d 1466SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1467SELECT * FROM `t2$EX` ORDER BY a, d; 1468-------------------------------------------- 1469Test 2 : Normal Insert from Secondary Master 1470-------------------------------------------- 1471-- Insert a new row on the Slave 1472insert into test.t1 values (2, "Forrest"); 1473-- Check it exists on the Master 1474select * from test.t1 order by a; 1475a b 14761 Beach house 14772 Forrest 1478-- Update from the slave 1479update test.t1 set b="Reds" where a=2; 1480select * from test.t1 order by a; 1481a b 14821 Beach house 14832 Reds 1484delete from test.t1 where a=2; 1485select * from test.t1 order by a; 1486a b 14871 Beach house 1488SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1489VARIABLE_VALUE-@init_ndb_conflict_fn_max 14900 1491SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1492VARIABLE_VALUE-@init_ndb_conflict_fn_old 14930 1494SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1495VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 14960 1497SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1498VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 14990 1500SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1501server_id master_server_id master_epoch count a d 1502SELECT * FROM `t1$EX` ORDER BY a, d; 1503server_id master_server_id master_epoch count a d 1504SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1505SELECT * FROM `t2$EX` ORDER BY a, d; 1506------------------------------- 1507Test 3 : Insert-Insert conflict 1508------------------------------- 1509stop slave; 1510-- Insert a row on the Primary Master 1511insert into test.t1 values (2, "Loopy Lornas"); 1512-- Insert a row on the secondary Master 1513insert into test.t1 values (2, "Cloisters"); 1514-- Examine data on Primary Master (should be unaffected) 1515select * from test.t1 order by a; 1516a b 15171 Beach house 15182 Loopy Lornas 1519-- Examine conflict indicators on Primary Master 1520SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1521VARIABLE_VALUE-@init_ndb_conflict_fn_max 15220 1523SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1524VARIABLE_VALUE-@init_ndb_conflict_fn_old 15250 1526SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1527VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 15280 1529SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1530VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 15311 1532SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1533server_id master_server_id master_epoch count a d 15341 3 # 1 # # 1535SELECT * FROM `t1$EX` ORDER BY a, d; 1536server_id master_server_id master_epoch count a d 15371 3 # # 2 NULL 1538SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1539SELECT * FROM `t2$EX` ORDER BY a, d; 1540-- Examine data on isolated secondary Master (should be as-set) 1541select * from test.t1 order by a; 1542a b 15431 Beach house 15442 Cloisters 1545-- Restart secondary Masters slave 1546start slave; 1547-- Reexamine secondary Master's data (should be same as Primary Masters) 1548select * from test.t1 order by a; 1549a b 15501 Beach house 15512 Loopy Lornas 1552------------------------------- 1553Test 4 : Update-Update conflict 1554------------------------------- 1555-- Stop replication to secondary master 1556stop slave; 1557-- Update row on Primary Master 1558update test.t1 set b="Peters Yard" where a=2; 1559-- Show data on Primary Master 1560select * from test.t1 order by a; 1561a b 15621 Beach house 15632 Peters Yard 1564SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1565VARIABLE_VALUE-@init_ndb_conflict_fn_max 15660 1567SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1568VARIABLE_VALUE-@init_ndb_conflict_fn_old 15690 1570SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1571VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 15720 1573SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1574VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 15751 1576SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1577server_id master_server_id master_epoch count a d 15781 3 # 1 # # 1579SELECT * FROM `t1$EX` ORDER BY a, d; 1580server_id master_server_id master_epoch count a d 15811 3 # # 2 NULL 1582SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1583SELECT * FROM `t2$EX` ORDER BY a, d; 1584-- Update row on Secondary Master 1585update test.t1 set b="Toast" where a=2; 1586-- Examine data on Primary Master - should be unaffected 1587select * from test.t1 order by a; 1588a b 15891 Beach house 15902 Peters Yard 1591SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1592VARIABLE_VALUE-@init_ndb_conflict_fn_max 15930 1594SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1595VARIABLE_VALUE-@init_ndb_conflict_fn_old 15960 1597SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1598VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 15990 1600SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1601VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 16022 1603SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1604server_id master_server_id master_epoch count a d 16051 3 # 1 # # 16061 3 # 2 # # 1607SELECT * FROM `t1$EX` ORDER BY a, d; 1608server_id master_server_id master_epoch count a d 16091 3 # # 2 NULL 16101 3 # # 2 NULL 1611SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1612SELECT * FROM `t2$EX` ORDER BY a, d; 1613-- Check data on secondary - should be as set 1614select * from test.t1 order by a; 1615a b 16161 Beach house 16172 Toast 1618-- Now restart slave, will re-align row 1619start slave; 1620-- Check that Secondary is re-aligned 1621select * from test.t1 order by a; 1622a b 16231 Beach house 16242 Peters Yard 1625------------------------------- 1626Test 5 : Update-Delete conflict 1627------------------------------- 1628-- Stop Secondary slave 1629stop slave; 1630-- Update on Primary Master 1631update test.t1 set b="Pear tree" where a = 2; 1632-- Delete on Secondary Master 1633delete from test.t1 where a = 2; 1634-- Examine data on Primary Master, should be unaffected 1635select * from test.t1 order by a; 1636a b 16371 Beach house 16382 Pear tree 1639SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1640VARIABLE_VALUE-@init_ndb_conflict_fn_max 16410 1642SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1643VARIABLE_VALUE-@init_ndb_conflict_fn_old 16440 1645SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1646VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 16470 1648SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1649VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 16503 1651SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1652server_id master_server_id master_epoch count a d 16531 3 # 1 # # 16541 3 # 2 # # 16551 3 # 3 # # 1656SELECT * FROM `t1$EX` ORDER BY a, d; 1657server_id master_server_id master_epoch count a d 16581 3 # # 2 NULL 16591 3 # # 2 NULL 16601 3 # # 2 NULL 1661SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1662SELECT * FROM `t2$EX` ORDER BY a, d; 1663-- Examine data on Secondary Master before slave restart, still missing 1664select * from test.t1 order by a; 1665a b 16661 Beach house 1667-- Restart Secondary Master slave 1668start slave; 1669-- Examine data on Secondary Master after slave restart, aligned with Master 1670select * from test.t1 order by a; 1671a b 16721 Beach house 16732 Pear tree 1674------------------------------- 1675Test 6 : Delete-Update conflict 1676------------------------------- 1677-- Stop Secondary slave 1678stop slave; 1679-- Delete on Primary Master 1680delete from test.t1 where a=2; 1681-- Update on Secondary Master 1682update test.t1 set b="Black pig" where a=2; 1683-- Examine data on Primary Master, should be unaffected (no row) 1684select * from test.t1 order by a; 1685a b 16861 Beach house 1687SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1688VARIABLE_VALUE-@init_ndb_conflict_fn_max 16890 1690SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1691VARIABLE_VALUE-@init_ndb_conflict_fn_old 16920 1693SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1694VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 16950 1696SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1697VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 16984 1699SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1700server_id master_server_id master_epoch count a d 17011 3 # 1 # # 17021 3 # 2 # # 17031 3 # 3 # # 17041 3 # 4 # # 1705SELECT * FROM `t1$EX` ORDER BY a, d; 1706server_id master_server_id master_epoch count a d 17071 3 # # 2 NULL 17081 3 # # 2 NULL 17091 3 # # 2 NULL 17101 3 # # 2 NULL 1711SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1712SELECT * FROM `t2$EX` ORDER BY a, d; 1713-- Examine data on Secondary Master before slave restart, should be as inserted 1714select * from test.t1 order by a; 1715a b 17161 Beach house 17172 Black pig 1718-- Restart Secondary Master slave 1719start slave; 1720-- Examine data on Secondary Master after slave restart, aligned with Master (deleted) 1721select * from test.t1 order by a; 1722a b 17231 Beach house 1724------------------------------- 1725Test 7 : Delete-Delete conflict 1726------------------------------- 1727-- Stop Secondary slave 1728stop slave; 1729-- Delete on Primary Master 1730delete from test.t1 where a=1; 1731-- Delete on Secondary Master 1732delete from test.t1 where a=1; 1733-- Examine data on Primary Master, no row 1734select * from test.t1 order by a; 1735a b 1736SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1737VARIABLE_VALUE-@init_ndb_conflict_fn_max 17380 1739SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1740VARIABLE_VALUE-@init_ndb_conflict_fn_old 17410 1742SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1743VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 17440 1745SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1746VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 17475 1748SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1749server_id master_server_id master_epoch count a d 17501 3 # 1 # # 17511 3 # 2 # # 17521 3 # 3 # # 17531 3 # 4 # # 17541 3 # 5 # # 1755SELECT * FROM `t1$EX` ORDER BY a, d; 1756server_id master_server_id master_epoch count a d 17571 3 # # 1 NULL 17581 3 # # 2 NULL 17591 3 # # 2 NULL 17601 3 # # 2 NULL 17611 3 # # 2 NULL 1762SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1763SELECT * FROM `t2$EX` ORDER BY a, d; 1764-- Examine data on Secondary Master before slave restart, no row 1765select * from test.t1 order by a; 1766a b 1767-- Restart Secondary Master slave 1768start slave; 1769-- Examine data on Secondary Master after slave restart, no row 1770select * from test.t1 order by a; 1771a b 1772------------------------------------------------ 1773Test 8 : Delete-Delete, Insert conflict exposure 1774------------------------------------------------ 1775-- Insert a row on Secondary Master 1776insert into test.t1 values (3, "Espy"); 1777-- Check it's present on Primary Master 1778select * from test.t1 order by a; 1779a b 17803 Espy 1781-- Stop replication in both directions 1782stop slave; 1783stop slave; 1784-- Delete row from both clusters 1785delete from test.t1 where a=3; 1786delete from test.t1 where a=3; 1787-- Follow up with Insert from Secondary master 1788insert into test.t1 values (3, "Dalriada"); 1789-- Restart replication in both directions 1790start slave; 1791start slave; 1792-- Check data on both sites - diverged 1793-- Secondary master : 1794select * from test.t1 order by a; 1795a b 1796-- Primary master : 1797select * from test.t1 order by a; 1798a b 17993 Dalriada 1800--Remove extra row 1801delete from test.t1 where a=3; 1802-- Note that Delete-Delete conflict detected below 1803SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1804VARIABLE_VALUE-@init_ndb_conflict_fn_max 18050 1806SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1807VARIABLE_VALUE-@init_ndb_conflict_fn_old 18080 1809SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1810VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 18110 1812SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1813VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 18146 1815SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1816server_id master_server_id master_epoch count a d 18171 3 # 1 # # 18181 3 # 2 # # 18191 3 # 3 # # 18201 3 # 4 # # 18211 3 # 5 # # 18221 3 # 6 # # 1823SELECT * FROM `t1$EX` ORDER BY a, d; 1824server_id master_server_id master_epoch count a d 18251 3 # # 1 NULL 18261 3 # # 2 NULL 18271 3 # # 2 NULL 18281 3 # # 2 NULL 18291 3 # # 2 NULL 18301 3 # # 3 NULL 1831SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1832SELECT * FROM `t2$EX` ORDER BY a, d; 1833------------------------------------------------ 1834Test 9 : Insert, Insert-Update-Delete conflict 1835------------------------------------------------ 1836-- Stop replication on Secondary Master 1837stop slave; 1838-- Insert row on Primary Master 1839insert into test.t1 values (4, "Haymarket"); 1840-- Insert row on Secondary Master 1841insert into test.t1 values (4, "Outhouse"); 1842-- Update row on Secondary Master 1843update test.t1 set b="Mathers" where a = 4; 1844-- Delete row on Secondary Master 1845delete from test.t1 where a=4; 1846-- Examine data (none) on Secondary Master 1847select * from test.t1 order by a; 1848a b 1849-- Examine data on Primary Master, should be unaffected 1850select * from test.t1 order by a; 1851a b 18524 Haymarket 1853SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1854VARIABLE_VALUE-@init_ndb_conflict_fn_max 18550 1856SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1857VARIABLE_VALUE-@init_ndb_conflict_fn_old 18580 1859SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1860VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 18610 1862SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1863VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 18649 1865SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1866server_id master_server_id master_epoch count a d 18671 3 # 1 # # 18681 3 # 2 # # 18691 3 # 3 # # 18701 3 # 4 # # 18711 3 # 5 # # 18721 3 # 6 # # 18731 3 # 7 # # 18741 3 # 8 # # 18751 3 # 9 # # 1876SELECT * FROM `t1$EX` ORDER BY a, d; 1877server_id master_server_id master_epoch count a d 18781 3 # # 1 NULL 18791 3 # # 2 NULL 18801 3 # # 2 NULL 18811 3 # # 2 NULL 18821 3 # # 2 NULL 18831 3 # # 3 NULL 18841 3 # # 4 NULL 18851 3 # # 4 NULL 18861 3 # # 4 NULL 1887SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1888SELECT * FROM `t2$EX` ORDER BY a, d; 1889-- Examine data on Secondary Master (none) 1890select * from test.t1 order by a; 1891a b 1892-- Restart Secondary Master's slave 1893start slave; 1894-- Check data on Secondary Master, should be same as Primary Master 1895select * from test.t1; 1896a b 18974 Haymarket 1898------------------------------------------------ 1899Test 10 : Update, Delete-Insert-Update conflict 1900------------------------------------------------ 1901-- Stop replication on Secondary Master 1902stop slave; 1903-- Update row on Primary Master 1904update test.t1 set b="Blind poet" where a=4; 1905-- Delete row on Secondary Master 1906delete from test.t1 where a=4; 1907-- Insert row on Secondary Master 1908insert into test.t1 values (4, "Drouthy Neebors"); 1909-- Update row on Secondary Master 1910update test.t1 set b="The Tankard" where a=4; 1911-- Check data on Primary Master, should be unaffected 1912select * from test.t1 order by a; 1913a b 19144 Blind poet 1915SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1916VARIABLE_VALUE-@init_ndb_conflict_fn_max 19170 1918SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1919VARIABLE_VALUE-@init_ndb_conflict_fn_old 19200 1921SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1922VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 19230 1924SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1925VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 192612 1927SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1928server_id master_server_id master_epoch count a d 19291 3 # 1 # # 19301 3 # 2 # # 19311 3 # 3 # # 19321 3 # 4 # # 19331 3 # 5 # # 19341 3 # 6 # # 19351 3 # 7 # # 19361 3 # 8 # # 19371 3 # 9 # # 19381 3 # 10 # # 19391 3 # 11 # # 19401 3 # 12 # # 1941SELECT * FROM `t1$EX` ORDER BY a, d; 1942server_id master_server_id master_epoch count a d 19431 3 # # 1 NULL 19441 3 # # 2 NULL 19451 3 # # 2 NULL 19461 3 # # 2 NULL 19471 3 # # 2 NULL 19481 3 # # 3 NULL 19491 3 # # 4 NULL 19501 3 # # 4 NULL 19511 3 # # 4 NULL 19521 3 # # 4 NULL 19531 3 # # 4 NULL 19541 3 # # 4 NULL 1955SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1956SELECT * FROM `t2$EX` ORDER BY a, d; 1957-- Check data on Secondary Master, as set 1958select * from test.t1 order by a; 1959a b 19604 The Tankard 1961-- Restart Secondary Master slave 1962start slave; 1963-- Check data on Secondary Master - should be as Primary 1964select * from test.t1 order by a; 1965a b 19664 Blind poet 1967------------------------------------------------------------------------ 1968Test 11 : Test Secondary insert-update-delete accepted 1969------------------------------------------------------------------------ 1970Insert row on Secondary 1971insert into test.t1 values (5, "Minders"); 1972Update row on Secondary 1973update test.t1 set b="Southsider" where a=5; 1974Delete row on Secondary 1975delete from test.t1 where a=5; 1976Check data on Primary, should be none. No new conflicts 1977select * from test.t1 order by a; 1978a b 19794 Blind poet 1980SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 1981VARIABLE_VALUE-@init_ndb_conflict_fn_max 19820 1983SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 1984VARIABLE_VALUE-@init_ndb_conflict_fn_old 19850 1986SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 1987VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 19880 1989SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 1990VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 199112 1992SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 1993server_id master_server_id master_epoch count a d 19941 3 # 1 # # 19951 3 # 2 # # 19961 3 # 3 # # 19971 3 # 4 # # 19981 3 # 5 # # 19991 3 # 6 # # 20001 3 # 7 # # 20011 3 # 8 # # 20021 3 # 9 # # 20031 3 # 10 # # 20041 3 # 11 # # 20051 3 # 12 # # 2006SELECT * FROM `t1$EX` ORDER BY a, d; 2007server_id master_server_id master_epoch count a d 20081 3 # # 1 NULL 20091 3 # # 2 NULL 20101 3 # # 2 NULL 20111 3 # # 2 NULL 20121 3 # # 2 NULL 20131 3 # # 3 NULL 20141 3 # # 4 NULL 20151 3 # # 4 NULL 20161 3 # # 4 NULL 20171 3 # # 4 NULL 20181 3 # # 4 NULL 20191 3 # # 4 NULL 2020SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2021SELECT * FROM `t2$EX` ORDER BY a, d; 2022SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2023VARIABLE_VALUE-@init_ndb_conflict_fn_max 20240 2025SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2026VARIABLE_VALUE-@init_ndb_conflict_fn_old 20270 2028SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2029VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 20300 2031SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2032VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 203312 2034SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2035server_id master_server_id master_epoch count a d 20361 3 # 1 # # 20371 3 # 2 # # 20381 3 # 3 # # 20391 3 # 4 # # 20401 3 # 5 # # 20411 3 # 6 # # 20421 3 # 7 # # 20431 3 # 8 # # 20441 3 # 9 # # 20451 3 # 10 # # 20461 3 # 11 # # 20471 3 # 12 # # 2048SELECT * FROM `t1$EX` ORDER BY a, d; 2049server_id master_server_id master_epoch count a d 20501 3 # # 1 NULL 20511 3 # # 2 NULL 20521 3 # # 2 NULL 20531 3 # # 2 NULL 20541 3 # # 2 NULL 20551 3 # # 3 NULL 20561 3 # # 4 NULL 20571 3 # # 4 NULL 20581 3 # # 4 NULL 20591 3 # # 4 NULL 20601 3 # # 4 NULL 20611 3 # # 4 NULL 2062SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2063SELECT * FROM `t2$EX` ORDER BY a, d; 2064drop table test.t1; 2065drop table test.t2; 2066drop table test.t1$EX; 2067Populate ndb_replication table as necessary 2068-- Default extra Gci bits 2069replace into mysql.ndb_replication values 2070("test", "t1", 3, 7, NULL), 2071("test", "t1", 1, 7, "NDB$EPOCH()"); 2072create table `test`.`t1$EX` 2073 (server_id int unsigned, 2074master_server_id int unsigned, 2075master_epoch bigint unsigned, 2076count int unsigned, 2077a int not null, 2078d int, 2079primary key(server_id, master_server_id, master_epoch, count)) engine ndb; 2080Create table 2081create table test.t1(a int primary key, b varchar(255)) engine = ndb; 2082Create other table 2083create table test.t2(a int primary key, b int) engine = ndb; 2084---------------------------------- 2085Test 1 : Basic two-way replication 2086---------------------------------- 2087insert into test.t1 values (1, "Metropole"); 2088-- Give time for a new epoch on the Master 2089-- Insert something to ensure the new epoch is noticed 2090replace into test.t2 values (2, 1); 2091-- Flushed to slave 2092select * from test.t1 order by a; 2093a b 20941 Metropole 2095-- Flushed back to Master 2096select * from test.t1 order by a; 2097a b 20981 Metropole 2099-- Now update data on slave 2100update test.t1 set b="Favorit" where a=1; 2101-- Now check data on master 2102select * from test.t1 order by a; 2103a b 21041 Favorit 2105SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2106VARIABLE_VALUE-@init_ndb_conflict_fn_max 21070 2108SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2109VARIABLE_VALUE-@init_ndb_conflict_fn_old 21100 2111SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2112VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 21130 2114SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2115VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 21160 2117SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2118server_id master_server_id master_epoch count a d 2119SELECT * FROM `t1$EX` ORDER BY a, d; 2120server_id master_server_id master_epoch count a d 2121SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2122SELECT * FROM `t2$EX` ORDER BY a, d; 2123-- Now perform multiple consecutive updates on the slave 2124update test.t1 set b="Elephant house" where a=1; 2125update test.t1 set b="Beach house" where a=1; 2126select * from test.t1 order by a; 2127a b 21281 Beach house 2129-- Now check they've applied on the master 2130select * from test.t1 order by a; 2131a b 21321 Beach house 2133SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2134VARIABLE_VALUE-@init_ndb_conflict_fn_max 21350 2136SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2137VARIABLE_VALUE-@init_ndb_conflict_fn_old 21380 2139SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2140VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 21410 2142SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2143VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 21440 2145SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2146server_id master_server_id master_epoch count a d 2147SELECT * FROM `t1$EX` ORDER BY a, d; 2148server_id master_server_id master_epoch count a d 2149SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2150SELECT * FROM `t2$EX` ORDER BY a, d; 2151-------------------------------------------- 2152Test 2 : Normal Insert from Secondary Master 2153-------------------------------------------- 2154-- Insert a new row on the Slave 2155insert into test.t1 values (2, "Forrest"); 2156-- Check it exists on the Master 2157select * from test.t1 order by a; 2158a b 21591 Beach house 21602 Forrest 2161-- Update from the slave 2162update test.t1 set b="Reds" where a=2; 2163select * from test.t1 order by a; 2164a b 21651 Beach house 21662 Reds 2167delete from test.t1 where a=2; 2168select * from test.t1 order by a; 2169a b 21701 Beach house 2171SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2172VARIABLE_VALUE-@init_ndb_conflict_fn_max 21730 2174SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2175VARIABLE_VALUE-@init_ndb_conflict_fn_old 21760 2177SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2178VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 21790 2180SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2181VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 21820 2183SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2184server_id master_server_id master_epoch count a d 2185SELECT * FROM `t1$EX` ORDER BY a, d; 2186server_id master_server_id master_epoch count a d 2187SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2188SELECT * FROM `t2$EX` ORDER BY a, d; 2189------------------------------- 2190Test 3 : Insert-Insert conflict 2191------------------------------- 2192stop slave; 2193-- Insert a row on the Primary Master 2194insert into test.t1 values (2, "Loopy Lornas"); 2195-- Insert a row on the secondary Master 2196insert into test.t1 values (2, "Cloisters"); 2197-- Examine data on Primary Master (should be unaffected) 2198select * from test.t1 order by a; 2199a b 22001 Beach house 22012 Loopy Lornas 2202-- Examine conflict indicators on Primary Master 2203SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2204VARIABLE_VALUE-@init_ndb_conflict_fn_max 22050 2206SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2207VARIABLE_VALUE-@init_ndb_conflict_fn_old 22080 2209SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2210VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 22110 2212SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2213VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 22141 2215SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2216server_id master_server_id master_epoch count a d 22171 3 # 1 # # 2218SELECT * FROM `t1$EX` ORDER BY a, d; 2219server_id master_server_id master_epoch count a d 22201 3 # # 2 NULL 2221SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2222SELECT * FROM `t2$EX` ORDER BY a, d; 2223-- Examine data on isolated secondary Master (should be as-set) 2224select * from test.t1 order by a; 2225a b 22261 Beach house 22272 Cloisters 2228-- Restart secondary Masters slave 2229start slave; 2230-- Reexamine secondary Master's data (should be same as Primary Masters) 2231select * from test.t1 order by a; 2232a b 22331 Beach house 22342 Loopy Lornas 2235------------------------------- 2236Test 4 : Update-Update conflict 2237------------------------------- 2238-- Stop replication to secondary master 2239stop slave; 2240-- Update row on Primary Master 2241update test.t1 set b="Peters Yard" where a=2; 2242-- Show data on Primary Master 2243select * from test.t1 order by a; 2244a b 22451 Beach house 22462 Peters Yard 2247SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2248VARIABLE_VALUE-@init_ndb_conflict_fn_max 22490 2250SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2251VARIABLE_VALUE-@init_ndb_conflict_fn_old 22520 2253SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2254VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 22550 2256SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2257VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 22581 2259SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2260server_id master_server_id master_epoch count a d 22611 3 # 1 # # 2262SELECT * FROM `t1$EX` ORDER BY a, d; 2263server_id master_server_id master_epoch count a d 22641 3 # # 2 NULL 2265SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2266SELECT * FROM `t2$EX` ORDER BY a, d; 2267-- Update row on Secondary Master 2268update test.t1 set b="Toast" where a=2; 2269-- Examine data on Primary Master - should be unaffected 2270select * from test.t1 order by a; 2271a b 22721 Beach house 22732 Peters Yard 2274SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2275VARIABLE_VALUE-@init_ndb_conflict_fn_max 22760 2277SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2278VARIABLE_VALUE-@init_ndb_conflict_fn_old 22790 2280SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2281VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 22820 2283SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2284VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 22852 2286SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2287server_id master_server_id master_epoch count a d 22881 3 # 1 # # 22891 3 # 2 # # 2290SELECT * FROM `t1$EX` ORDER BY a, d; 2291server_id master_server_id master_epoch count a d 22921 3 # # 2 NULL 22931 3 # # 2 NULL 2294SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2295SELECT * FROM `t2$EX` ORDER BY a, d; 2296-- Check data on secondary - should be as set 2297select * from test.t1 order by a; 2298a b 22991 Beach house 23002 Toast 2301-- Now restart slave, will re-align row 2302start slave; 2303-- Check that Secondary is re-aligned 2304select * from test.t1 order by a; 2305a b 23061 Beach house 23072 Peters Yard 2308------------------------------- 2309Test 5 : Update-Delete conflict 2310------------------------------- 2311-- Stop Secondary slave 2312stop slave; 2313-- Update on Primary Master 2314update test.t1 set b="Pear tree" where a = 2; 2315-- Delete on Secondary Master 2316delete from test.t1 where a = 2; 2317-- Examine data on Primary Master, should be unaffected 2318select * from test.t1 order by a; 2319a b 23201 Beach house 23212 Pear tree 2322SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2323VARIABLE_VALUE-@init_ndb_conflict_fn_max 23240 2325SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2326VARIABLE_VALUE-@init_ndb_conflict_fn_old 23270 2328SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2329VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 23300 2331SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2332VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 23333 2334SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2335server_id master_server_id master_epoch count a d 23361 3 # 1 # # 23371 3 # 2 # # 23381 3 # 3 # # 2339SELECT * FROM `t1$EX` ORDER BY a, d; 2340server_id master_server_id master_epoch count a d 23411 3 # # 2 NULL 23421 3 # # 2 NULL 23431 3 # # 2 NULL 2344SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2345SELECT * FROM `t2$EX` ORDER BY a, d; 2346-- Examine data on Secondary Master before slave restart, still missing 2347select * from test.t1 order by a; 2348a b 23491 Beach house 2350-- Restart Secondary Master slave 2351start slave; 2352-- Examine data on Secondary Master after slave restart, aligned with Master 2353select * from test.t1 order by a; 2354a b 23551 Beach house 23562 Pear tree 2357------------------------------- 2358Test 6 : Delete-Update conflict 2359------------------------------- 2360-- Stop Secondary slave 2361stop slave; 2362-- Delete on Primary Master 2363delete from test.t1 where a=2; 2364-- Update on Secondary Master 2365update test.t1 set b="Black pig" where a=2; 2366-- Examine data on Primary Master, should be unaffected (no row) 2367select * from test.t1 order by a; 2368a b 23691 Beach house 2370SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2371VARIABLE_VALUE-@init_ndb_conflict_fn_max 23720 2373SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2374VARIABLE_VALUE-@init_ndb_conflict_fn_old 23750 2376SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2377VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 23780 2379SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2380VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 23814 2382SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2383server_id master_server_id master_epoch count a d 23841 3 # 1 # # 23851 3 # 2 # # 23861 3 # 3 # # 23871 3 # 4 # # 2388SELECT * FROM `t1$EX` ORDER BY a, d; 2389server_id master_server_id master_epoch count a d 23901 3 # # 2 NULL 23911 3 # # 2 NULL 23921 3 # # 2 NULL 23931 3 # # 2 NULL 2394SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2395SELECT * FROM `t2$EX` ORDER BY a, d; 2396-- Examine data on Secondary Master before slave restart, should be as inserted 2397select * from test.t1 order by a; 2398a b 23991 Beach house 24002 Black pig 2401-- Restart Secondary Master slave 2402start slave; 2403-- Examine data on Secondary Master after slave restart, aligned with Master (deleted) 2404select * from test.t1 order by a; 2405a b 24061 Beach house 2407------------------------------- 2408Test 7 : Delete-Delete conflict 2409------------------------------- 2410-- Stop Secondary slave 2411stop slave; 2412-- Delete on Primary Master 2413delete from test.t1 where a=1; 2414-- Delete on Secondary Master 2415delete from test.t1 where a=1; 2416-- Examine data on Primary Master, no row 2417select * from test.t1 order by a; 2418a b 2419SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2420VARIABLE_VALUE-@init_ndb_conflict_fn_max 24210 2422SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2423VARIABLE_VALUE-@init_ndb_conflict_fn_old 24240 2425SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2426VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 24270 2428SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2429VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 24305 2431SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2432server_id master_server_id master_epoch count a d 24331 3 # 1 # # 24341 3 # 2 # # 24351 3 # 3 # # 24361 3 # 4 # # 24371 3 # 5 # # 2438SELECT * FROM `t1$EX` ORDER BY a, d; 2439server_id master_server_id master_epoch count a d 24401 3 # # 1 NULL 24411 3 # # 2 NULL 24421 3 # # 2 NULL 24431 3 # # 2 NULL 24441 3 # # 2 NULL 2445SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2446SELECT * FROM `t2$EX` ORDER BY a, d; 2447-- Examine data on Secondary Master before slave restart, no row 2448select * from test.t1 order by a; 2449a b 2450-- Restart Secondary Master slave 2451start slave; 2452-- Examine data on Secondary Master after slave restart, no row 2453select * from test.t1 order by a; 2454a b 2455------------------------------------------------ 2456Test 8 : Delete-Delete, Insert conflict exposure 2457------------------------------------------------ 2458-- Insert a row on Secondary Master 2459insert into test.t1 values (3, "Espy"); 2460-- Check it's present on Primary Master 2461select * from test.t1 order by a; 2462a b 24633 Espy 2464-- Stop replication in both directions 2465stop slave; 2466stop slave; 2467-- Delete row from both clusters 2468delete from test.t1 where a=3; 2469delete from test.t1 where a=3; 2470-- Follow up with Insert from Secondary master 2471insert into test.t1 values (3, "Dalriada"); 2472-- Restart replication in both directions 2473start slave; 2474start slave; 2475-- Check data on both sites - diverged 2476-- Secondary master : 2477select * from test.t1 order by a; 2478a b 2479-- Primary master : 2480select * from test.t1 order by a; 2481a b 24823 Dalriada 2483--Remove extra row 2484delete from test.t1 where a=3; 2485-- Note that Delete-Delete conflict detected below 2486SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2487VARIABLE_VALUE-@init_ndb_conflict_fn_max 24880 2489SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2490VARIABLE_VALUE-@init_ndb_conflict_fn_old 24910 2492SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2493VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 24940 2495SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2496VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 24976 2498SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2499server_id master_server_id master_epoch count a d 25001 3 # 1 # # 25011 3 # 2 # # 25021 3 # 3 # # 25031 3 # 4 # # 25041 3 # 5 # # 25051 3 # 6 # # 2506SELECT * FROM `t1$EX` ORDER BY a, d; 2507server_id master_server_id master_epoch count a d 25081 3 # # 1 NULL 25091 3 # # 2 NULL 25101 3 # # 2 NULL 25111 3 # # 2 NULL 25121 3 # # 2 NULL 25131 3 # # 3 NULL 2514SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2515SELECT * FROM `t2$EX` ORDER BY a, d; 2516------------------------------------------------ 2517Test 9 : Insert, Insert-Update-Delete conflict 2518------------------------------------------------ 2519-- Stop replication on Secondary Master 2520stop slave; 2521-- Insert row on Primary Master 2522insert into test.t1 values (4, "Haymarket"); 2523-- Insert row on Secondary Master 2524insert into test.t1 values (4, "Outhouse"); 2525-- Update row on Secondary Master 2526update test.t1 set b="Mathers" where a = 4; 2527-- Delete row on Secondary Master 2528delete from test.t1 where a=4; 2529-- Examine data (none) on Secondary Master 2530select * from test.t1 order by a; 2531a b 2532-- Examine data on Primary Master, should be unaffected 2533select * from test.t1 order by a; 2534a b 25354 Haymarket 2536SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2537VARIABLE_VALUE-@init_ndb_conflict_fn_max 25380 2539SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2540VARIABLE_VALUE-@init_ndb_conflict_fn_old 25410 2542SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2543VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 25440 2545SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2546VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 25479 2548SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2549server_id master_server_id master_epoch count a d 25501 3 # 1 # # 25511 3 # 2 # # 25521 3 # 3 # # 25531 3 # 4 # # 25541 3 # 5 # # 25551 3 # 6 # # 25561 3 # 7 # # 25571 3 # 8 # # 25581 3 # 9 # # 2559SELECT * FROM `t1$EX` ORDER BY a, d; 2560server_id master_server_id master_epoch count a d 25611 3 # # 1 NULL 25621 3 # # 2 NULL 25631 3 # # 2 NULL 25641 3 # # 2 NULL 25651 3 # # 2 NULL 25661 3 # # 3 NULL 25671 3 # # 4 NULL 25681 3 # # 4 NULL 25691 3 # # 4 NULL 2570SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2571SELECT * FROM `t2$EX` ORDER BY a, d; 2572-- Examine data on Secondary Master (none) 2573select * from test.t1 order by a; 2574a b 2575-- Restart Secondary Master's slave 2576start slave; 2577-- Check data on Secondary Master, should be same as Primary Master 2578select * from test.t1; 2579a b 25804 Haymarket 2581------------------------------------------------ 2582Test 10 : Update, Delete-Insert-Update conflict 2583------------------------------------------------ 2584-- Stop replication on Secondary Master 2585stop slave; 2586-- Update row on Primary Master 2587update test.t1 set b="Blind poet" where a=4; 2588-- Delete row on Secondary Master 2589delete from test.t1 where a=4; 2590-- Insert row on Secondary Master 2591insert into test.t1 values (4, "Drouthy Neebors"); 2592-- Update row on Secondary Master 2593update test.t1 set b="The Tankard" where a=4; 2594-- Check data on Primary Master, should be unaffected 2595select * from test.t1 order by a; 2596a b 25974 Blind poet 2598SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2599VARIABLE_VALUE-@init_ndb_conflict_fn_max 26000 2601SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2602VARIABLE_VALUE-@init_ndb_conflict_fn_old 26030 2604SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2605VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 26060 2607SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2608VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 260912 2610SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2611server_id master_server_id master_epoch count a d 26121 3 # 1 # # 26131 3 # 2 # # 26141 3 # 3 # # 26151 3 # 4 # # 26161 3 # 5 # # 26171 3 # 6 # # 26181 3 # 7 # # 26191 3 # 8 # # 26201 3 # 9 # # 26211 3 # 10 # # 26221 3 # 11 # # 26231 3 # 12 # # 2624SELECT * FROM `t1$EX` ORDER BY a, d; 2625server_id master_server_id master_epoch count a d 26261 3 # # 1 NULL 26271 3 # # 2 NULL 26281 3 # # 2 NULL 26291 3 # # 2 NULL 26301 3 # # 2 NULL 26311 3 # # 3 NULL 26321 3 # # 4 NULL 26331 3 # # 4 NULL 26341 3 # # 4 NULL 26351 3 # # 4 NULL 26361 3 # # 4 NULL 26371 3 # # 4 NULL 2638SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2639SELECT * FROM `t2$EX` ORDER BY a, d; 2640-- Check data on Secondary Master, as set 2641select * from test.t1 order by a; 2642a b 26434 The Tankard 2644-- Restart Secondary Master slave 2645start slave; 2646-- Check data on Secondary Master - should be as Primary 2647select * from test.t1 order by a; 2648a b 26494 Blind poet 2650------------------------------------------------------------------------ 2651Test 11 : Test Secondary insert-update-delete accepted 2652------------------------------------------------------------------------ 2653Insert row on Secondary 2654insert into test.t1 values (5, "Minders"); 2655Update row on Secondary 2656update test.t1 set b="Southsider" where a=5; 2657Delete row on Secondary 2658delete from test.t1 where a=5; 2659Check data on Primary, should be none. No new conflicts 2660select * from test.t1 order by a; 2661a b 26624 Blind poet 2663SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2664VARIABLE_VALUE-@init_ndb_conflict_fn_max 26650 2666SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2667VARIABLE_VALUE-@init_ndb_conflict_fn_old 26680 2669SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2670VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 26710 2672SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2673VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 267412 2675SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2676server_id master_server_id master_epoch count a d 26771 3 # 1 # # 26781 3 # 2 # # 26791 3 # 3 # # 26801 3 # 4 # # 26811 3 # 5 # # 26821 3 # 6 # # 26831 3 # 7 # # 26841 3 # 8 # # 26851 3 # 9 # # 26861 3 # 10 # # 26871 3 # 11 # # 26881 3 # 12 # # 2689SELECT * FROM `t1$EX` ORDER BY a, d; 2690server_id master_server_id master_epoch count a d 26911 3 # # 1 NULL 26921 3 # # 2 NULL 26931 3 # # 2 NULL 26941 3 # # 2 NULL 26951 3 # # 2 NULL 26961 3 # # 3 NULL 26971 3 # # 4 NULL 26981 3 # # 4 NULL 26991 3 # # 4 NULL 27001 3 # # 4 NULL 27011 3 # # 4 NULL 27021 3 # # 4 NULL 2703SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2704SELECT * FROM `t2$EX` ORDER BY a, d; 2705SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2706VARIABLE_VALUE-@init_ndb_conflict_fn_max 27070 2708SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2709VARIABLE_VALUE-@init_ndb_conflict_fn_old 27100 2711SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2712VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 27130 2714SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2715VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 271612 2717SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2718server_id master_server_id master_epoch count a d 27191 3 # 1 # # 27201 3 # 2 # # 27211 3 # 3 # # 27221 3 # 4 # # 27231 3 # 5 # # 27241 3 # 6 # # 27251 3 # 7 # # 27261 3 # 8 # # 27271 3 # 9 # # 27281 3 # 10 # # 27291 3 # 11 # # 27301 3 # 12 # # 2731SELECT * FROM `t1$EX` ORDER BY a, d; 2732server_id master_server_id master_epoch count a d 27331 3 # # 1 NULL 27341 3 # # 2 NULL 27351 3 # # 2 NULL 27361 3 # # 2 NULL 27371 3 # # 2 NULL 27381 3 # # 3 NULL 27391 3 # # 4 NULL 27401 3 # # 4 NULL 27411 3 # # 4 NULL 27421 3 # # 4 NULL 27431 3 # # 4 NULL 27441 3 # # 4 NULL 2745SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2746SELECT * FROM `t2$EX` ORDER BY a, d; 2747drop table test.t1; 2748drop table test.t2; 2749drop table test.t1$EX; 2750Now test batched conflict detection/handling 2751create table test.t1 ( 2752a int primary key, 2753b int, 2754c varchar(2000)) engine=ndb; 2755-- Stop replication from Primary -> Secondary 2756stop slave; 2757-- Insert a row on Primary 2758insert into test.t1 values (1,1,repeat('B', 2000)); 2759-- Generate a large batch of inserts with early + late conflicts 2760create procedure test.doit (rows int) 2761begin 2762set @x = 0; 2763START TRANSACTION; 2764repeat 2765insert into test.t1 values (@x, @x, repeat('B', 2000)); 2766set @x = @x + 1; 2767until @x = rows 2768end repeat; 2769COMMIT; 2770START TRANSACTION; 2771update test.t1 set b=999, c=repeat('E',2000) where a=1; 2772COMMIT; 2773START TRANSACTION; 2774delete from test.t1 where a=1; 2775COMMIT; 2776START TRANSACTION; 2777insert into test.t1 values (1,1,'A'); 2778COMMIT; 2779end% 2780call test.doit(100); 2781drop procedure test.doit; 2782-- Look at Primary status, expect 4 conflicts 2783SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX"; 2784VARIABLE_VALUE-@init_ndb_conflict_fn_max 27850 2786SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_old FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_OLD"; 2787VARIABLE_VALUE-@init_ndb_conflict_fn_old 27880 2789SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_MAX_DEL_WIN"; 2790VARIABLE_VALUE-@init_ndb_conflict_fn_max_del_win 27910 2792SELECT VARIABLE_VALUE-@init_ndb_conflict_fn_epoch FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE "NDB_CONFLICT_FN_EPOCH"; 2793VARIABLE_VALUE-@init_ndb_conflict_fn_epoch 27944 2795SELECT server_id, master_server_id, master_epoch, count, a, d FROM `t1$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2796SELECT * FROM `t1$EX` ORDER BY a, d; 2797SELECT * FROM `t2$EX` ORDER BY server_id, master_server_id, master_epoch, count; 2798SELECT * FROM `t2$EX` ORDER BY a, d; 2799start slave; 2800-- Look at Seconday row 2801select a,b,sha1(c) from test.t1 where a=1; 2802a b sha1(c) 28031 1 6d41e1d402596dff36396d1f0f288d17a4b9800a 2804-- Check it's the same on the Secondary 2805select a,b,sha1(c) from test.t1 where a=1; 2806a b sha1(c) 28071 1 6d41e1d402596dff36396d1f0f288d17a4b9800a 2808Test batching of DELETE vs DELETE with following INSERT 2809delete from test.t1; 2810insert into test.t1 values (1, 1, "Ma Brows"); 2811-- Stop Slave in both directions 2812stop slave; 2813stop slave; 2814-- Delete row on Primary Cluster 2815delete from test.t1 where a=1; 2816-- Delete row on Secondary Cluster, followed by Insert in later 'batch' 2817create procedure test.doit (rows int) 2818begin 2819set @x = 2; 2820START TRANSACTION; 2821delete from test.t1 where a=1; 2822repeat 2823insert into test.t1 values (@x, @x, repeat('B', 2000)); 2824set @x = @x + 1; 2825until @x = (rows + 2) 2826end repeat; 2827COMMIT; 2828START TRANSACTION; 2829insert into test.t1 values (1, 1, 'Malleny arms'); 2830COMMIT; 2831end% 2832call test.doit(200); 2833-- Restart slave on Primary Cluster 2834start slave; 2835-- Show data on Primary Cluster (should have row inserted on Secondary) 2836select * from test.t1 where a=1; 2837a b c 28381 1 Malleny arms 2839-- Show data on Secondary Cluster (should have row inserted on Secondary) 2840select * from test.t1 where a=1; 2841a b c 28421 1 Malleny arms 2843--Restart slave on Secondary Cluster 2844start slave; 2845-- Show data on Secondary Cluster (should now be missing) 2846Clusters are diverged as expected with delete vs delete conflict 2847followed closely by Insert 2848select * from test.t1 where a=1; 2849a b c 2850-- Force wait for master to be in-sync with slave 2851To avoid race between DML and table drop 2852flush logs; 2853drop procedure test.doit; 2854drop table test.t1; 2855delete from mysql.ndb_replication; 2856insert into mysql.ndb_replication values 2857("test", "t3", 0, 7, "NDB\$EPOCH(32)"), 2858("test", "t4", 0, 7, "NDB\$EPOCH(-1)"); 2859create table test.t3 (a int primary key) engine=ndb; 2860ERROR HY000: Can't create table 'test.t3' (errno: 1626) 2861show warnings; 2862Level Code Message 2863Warning 1626 Error in parsing conflict function. Message: NDB$EPOCH(32), Too many extra Gci bits at ')' 2864Error 1005 Can't create table 'test.t3' (errno: 1626) 2865create table test.t4 (a int primary key) engine=ndb; 2866ERROR HY000: Can't create table 'test.t4' (errno: 1626) 2867show warnings; 2868Level Code Message 2869Warning 1626 Error in parsing conflict function. Message: NDB$EPOCH(-1), Too many extra Gci bits at ')' 2870Error 1005 Can't create table 'test.t4' (errno: 1626) 2871-- Force sync before dropping table to avoid race 2872flush logs; 2873flush logs; 2874drop table mysql.ndb_replication; 2875-- Attempt to get system back in pre-test state 2876stop slave; 2877reset slave; 2878include/rpl_end.inc 2879