include/master-slave.inc Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. [connection master] Setup circular replication RESET MASTER; SLAVE_SERVER_ID: 3 CHANGE REPLICATION SOURCE TO SOURCE_HOST="127.0.0.1",SOURCE_PORT=SLAVE_PORT,SOURCE_USER="root"; Warnings: Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure. Note 1760 Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. START REPLICA; MASTER_SERVER_ID: 1 Setup ndb_replication and t1 exceptions table Populate ndb_replication table as necessary replace into mysql.ndb_replication values ("test", "t1", 3, 7, NULL), ("test", "t1", 1, 7, "NDB$EPOCH_TRANS()"), ("test", "t2", 3, 7, NULL), ("test", "t2", 1, 7, "NDB$EPOCH_TRANS()"); create table test.t1 ( a int primary key, b varchar(2000)) engine=ndb character set latin1; create table test.t2 ( a int primary key, b varchar(2000)) engine=ndb character set latin1; Add some data insert into test.t1 values (1, "Initial data 1"), (2, "Initial data 2"), (3, "Initial data 3"), (4, "Initial data 4"), (5, "Initial data 5"), (6, "Initial data 6"), (7, "Initial data 7"), (8, "Initial data 8"), (9, "Initial data 9"), (10, "Initial data 10"); insert into test.t2 values (1, "Initial data 1"), (2, "Initial data 2"), (3, "Initial data 3"), (4, "Initial data 4"), (5, "Initial data 5"), (6, "Initial data 6"), (7, "Initial data 7"), (8, "Initial data 8"), (9, "Initial data 9"), (10, "Initial data 10"); Sync all changes --------------------------------------- Test 1, read-update conflict without read tracking Stopping secondary replica stop replica; Stopping primary replica stop replica; begin; update test.t1 set b="Primary first change 1" where a=1; update test.t1 set b="Primary first change 2" where a=2; commit; SET ndb_log_exclusive_reads= 0; begin; select * from test.t1 where a = 1 for update; a b 1 Initial data 1 update test.t2 set b="Secondary first change 3" where a=3; commit; Checking tables before conflict resolution --------------------------------------- **** Primary **** select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Starting primary replica start replica; **** Secondary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Primary should not have rejected change from Secondary select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 ndb_conflict_fn_epoch_trans 0 ndb_conflict_fn_epoch2_trans 0 ndb_conflict_trans_row_conflict_count 0 ndb_conflict_trans_row_reject_count 0 ndb_conflict_trans_reject_count 0 ndb_conflict_trans_detect_iter_count 0 ndb_conflict_trans_conflict_commit_count 0 ndb_epoch_delete_delete_count 0 select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t1$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t2$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE starting secondary replica start replica; Secondary should not have been realigned to Primary **** Primary **** select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 **** Secondary **** select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 update test.t1 set b="Initial data 1" where a=1; update test.t1 set b="Initial data 2" where a=2; update test.t2 set b="Initial data 3" where a=3; Sync all changes --------------------------------------- delete from test.t2$EX; Test 2, read-update conflict with read tracking Stopping secondary replica stop replica; Stopping primary replica stop replica; begin; update test.t1 set b="Primary first change 1" where a=1; update test.t1 set b="Primary first change 2" where a=2; commit; SET ndb_log_exclusive_reads= 1; begin; select * from test.t1 where a = 1 for update; a b 1 Initial data 1 update test.t2 set b="Secondary first change 3" where a=3; commit; Checking tables before conflict resolution --------------------------------------- **** Primary **** select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Starting primary replica start replica; **** Secondary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Primary should have rejected change from Secondary, keeping its value select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 ndb_conflict_fn_epoch_trans 1 ndb_conflict_fn_epoch2_trans 0 ndb_conflict_trans_row_conflict_count 1 ndb_conflict_trans_row_reject_count 2 ndb_conflict_trans_reject_count 1 ndb_conflict_trans_detect_iter_count 1 ndb_conflict_trans_conflict_commit_count 1 ndb_epoch_delete_delete_count 0 select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t1$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE READ_ROW Initial data 1 Initial data 1 TRANS_IN_CONFLICT select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t2$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE UPDATE_ROW Initial data 3 Secondary first change 3 TRANS_IN_CONFLICT starting secondary replica start replica; Secondary should have been realigned to Primary **** Primary **** select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 **** Secondary **** select * from test.t1 order by a; a b 1 Primary first change 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 update test.t1 set b="Initial data 1" where a=1; update test.t1 set b="Initial data 2" where a=2; update test.t2 set b="Initial data 3" where a=3; Sync all changes --------------------------------------- delete from test.t1$EX; delete from test.t2$EX; Test 3, read-read conflict without read tracking Stopping secondary replica stop replica; Stopping primary replica stop replica; SET ndb_log_exclusive_reads= 0; begin; select * from test.t1 where a = 1 for update; a b 1 Initial data 1 update test.t1 set b="Primary first change 2" where a=2; commit; SET ndb_log_exclusive_reads= 0; begin; select * from test.t1 where a = 1 for update; a b 1 Initial data 1 update test.t2 set b="Secondary first change 3" where a=3; commit; Checking tables before conflict resolution --------------------------------------- **** Primary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Starting primary replica start replica; **** Secondary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Primary should not have rejected change from Secondary select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 ndb_conflict_fn_epoch_trans 0 ndb_conflict_fn_epoch2_trans 0 ndb_conflict_trans_row_conflict_count 0 ndb_conflict_trans_row_reject_count 0 ndb_conflict_trans_reject_count 0 ndb_conflict_trans_detect_iter_count 0 ndb_conflict_trans_conflict_commit_count 0 ndb_epoch_delete_delete_count 0 select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t1$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t2$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE starting secondary replica start replica; Secondary should not have been realigned to Primary **** Primary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 **** Secondary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 update test.t1 set b="Initial data 1" where a=1; update test.t1 set b="Initial data 2" where a=2; update test.t2 set b="Initial data 3" where a=3; Sync all changes --------------------------------------- delete from test.t1$EX; delete from test.t2$EX; Test 4, read-read conflict with read tracking Stopping secondary replica stop replica; Stopping primary replica stop replica; SET ndb_log_exclusive_reads= 1; begin; select * from test.t1 where a = 1 for update; a b 1 Initial data 1 update test.t1 set b="Primary first change 2" where a=2; commit; SET ndb_log_exclusive_reads= 1; begin; select * from test.t1 where a = 1 for update; a b 1 Initial data 1 update test.t2 set b="Secondary first change 3" where a=3; commit; Checking tables before conflict resolution --------------------------------------- **** Primary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Starting primary replica start replica; **** Secondary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 Primary should have rejected change from Secondary, keeping its value select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 ndb_conflict_fn_epoch_trans 1 ndb_conflict_fn_epoch2_trans 0 ndb_conflict_trans_row_conflict_count 1 ndb_conflict_trans_row_reject_count 2 ndb_conflict_trans_reject_count 1 ndb_conflict_trans_detect_iter_count 1 ndb_conflict_trans_conflict_commit_count 1 ndb_epoch_delete_delete_count 0 select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t1$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE READ_ROW Initial data 1 Initial data 1 TRANS_IN_CONFLICT select NDB$OP_TYPE, b$old, b$new, NDB$CFT_CAUSE from test.t2$EX order by a; NDB$OP_TYPE b$old b$new NDB$CFT_CAUSE UPDATE_ROW Initial data 3 Secondary first change 3 TRANS_IN_CONFLICT starting secondary replica start replica; Secondary should have been realigned to Primary **** Primary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 **** Secondary **** select * from test.t1 order by a; a b 1 Initial data 1 2 Primary first change 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 update test.t1 set b="Initial data 1" where a=1; update test.t1 set b="Initial data 2" where a=2; update test.t2 set b="Initial data 3" where a=3; Sync all changes --------------------------------------- delete from test.t1$EX; delete from test.t2$EX; Test 5, read-update conflict with read tracking on unique secondary index create table test.t3$EX (NDB$SERVER_ID int unsigned, NDB$MASTER_SERVER_ID int unsigned, NDB$MASTER_EPOCH bigint unsigned, NDB$COUNT int unsigned, a int not null, b$old varchar(2000), b$new varchar(2000), c$old int, c$new int, d$old int, d$new int, NDB$OP_TYPE ENUM('WRITE_ROW','UPDATE_ROW','DELETE_ROW','REFRESH_ROW','READ_ROW') NOT NULL, NDB$CFT_CAUSE ENUM('ROW_DOES_NOT_EXIST','ROW_ALREADY_EXISTS','DATA_IN_CONFLICT','TRANS_IN_CONFLICT') NOT NULL, NDB$ORIG_TRANSID BIGINT UNSIGNED NOT NULL, primary key(NDB$SERVER_ID, NDB$MASTER_SERVER_ID, NDB$MASTER_EPOCH, NDB$COUNT)) ENGINE=NDB CHARACTER SET latin1; Populate ndb_replication table as necessary replace into mysql.ndb_replication values ("test", "t3", 3, 7, NULL), ("test", "t3", 1, 7, "NDB$EPOCH_TRANS()"); create table test.t3 ( a int primary key, b varchar(2000), c int not null unique, d int, key(d)) engine=ndb character set latin1; Add some data insert into test.t3 values (1, "Initial data 1", 1, 1), (2, "Initial data 2", 2, 2), (3, "Initial data 3", 3, 3), (4, "Initial data 4", 4, 4), (5, "Initial data 5", 5, 5), (6, "Initial data 6", 6, 6), (7, "Initial data 7", 7, 7), (8, "Initial data 8", 8, 8), (9, "Initial data 9", 9, 9), (10, "Initial data 10", 10, 10); Show basic read tracking --------------------------------------- Stopping secondary replica stop replica; Stopping primary replica stop replica; begin; update test.t3 set b="Primary first change 1" where a=1; update test.t3 set b="Primary first change 2" where a=2; commit; SET ndb_log_exclusive_reads= 1; begin; select * from test.t3 where c = 1 for update; a b c d 1 Initial data 1 1 1 update test.t2 set b="Secondary first change 3" where a=3; commit; Checking tables before conflict resolution --------------------------------------- **** Primary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 Starting primary replica start replica; **** Secondary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Initial data 1 1 1 2 Initial data 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 Primary should have rejected change from Secondary, keeping its value select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 ndb_conflict_fn_epoch_trans 1 ndb_conflict_fn_epoch2_trans 0 ndb_conflict_trans_row_conflict_count 1 ndb_conflict_trans_row_reject_count 2 ndb_conflict_trans_reject_count 1 ndb_conflict_trans_detect_iter_count 1 ndb_conflict_trans_conflict_commit_count 1 ndb_epoch_delete_delete_count 0 select NDB$OP_TYPE, b$old, b$new, c$old, c$new, NDB$CFT_CAUSE from test.t3$EX order by a; NDB$OP_TYPE b$old b$new c$old c$new NDB$CFT_CAUSE READ_ROW Initial data 1 Initial data 1 1 1 TRANS_IN_CONFLICT starting secondary replica start replica; Secondary should have been realigned to Primary **** Primary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 **** Secondary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 update test.t2 set b="Initial data 3" where a=3; update test.t3 set b="Initial data 1" where a=1; update test.t3 set b="Initial data 2" where a=2; Sync all changes --------------------------------------- delete from test.t2$EX; delete from test.t3$EX; Test 6, read-update conflict with read tracking on ordered secondary index Show basic read tracking --------------------------------------- Stopping secondary replica stop replica; Stopping primary replica stop replica; begin; update test.t3 set b="Primary first change 1" where a=1; update test.t3 set b="Primary first change 2" where a=2; commit; SET ndb_log_exclusive_reads= 1; begin; select * from test.t3 where d = 1 for update; a b c d 1 Initial data 1 1 1 update test.t2 set b="Secondary first change 3" where a=3; commit; Checking tables before conflict resolution --------------------------------------- **** Primary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 Starting primary replica start replica; **** Secondary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Initial data 1 1 1 2 Initial data 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 Primary should have rejected change from Secondary, keeping its value select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 ndb_conflict_fn_epoch_trans 1 ndb_conflict_fn_epoch2_trans 0 ndb_conflict_trans_row_conflict_count 1 ndb_conflict_trans_row_reject_count 2 ndb_conflict_trans_reject_count 1 ndb_conflict_trans_detect_iter_count 1 ndb_conflict_trans_conflict_commit_count 1 ndb_epoch_delete_delete_count 0 select NDB$OP_TYPE, b$old, b$new, c$old, c$new, NDB$CFT_CAUSE from test.t3$EX order by a; NDB$OP_TYPE b$old b$new c$old c$new NDB$CFT_CAUSE READ_ROW Initial data 1 Initial data 1 1 1 TRANS_IN_CONFLICT starting secondary replica start replica; Secondary should have been realigned to Primary **** Primary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 **** Secondary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 update test.t2 set b="Initial data 3" where a=3; update test.t3 set b="Initial data 1" where a=1; update test.t3 set b="Initial data 2" where a=2; Sync all changes --------------------------------------- delete from test.t2$EX; delete from test.t3$EX; Test 7, read-update conflict with read tracking on table scan Show basic read tracking --------------------------------------- Stopping secondary replica stop replica; Stopping primary replica stop replica; begin; update test.t3 set b="Primary first change 1" where a=1; update test.t3 set b="Primary first change 2" where a=2; commit; SET ndb_log_exclusive_reads= 1; begin; select * from test.t3 where b like "Initial data 1" for update; a b c d 1 Initial data 1 1 1 update test.t2 set b="Secondary first change 3" where a=3; commit; Checking tables before conflict resolution --------------------------------------- **** Primary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 Starting primary replica start replica; **** Secondary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Secondary first change 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Initial data 1 1 1 2 Initial data 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 Primary should have rejected change from Secondary, keeping its value select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 ndb_conflict_fn_epoch_trans 1 ndb_conflict_fn_epoch2_trans 0 ndb_conflict_trans_row_conflict_count 1 ndb_conflict_trans_row_reject_count 2 ndb_conflict_trans_reject_count 1 ndb_conflict_trans_detect_iter_count 1 ndb_conflict_trans_conflict_commit_count 1 ndb_epoch_delete_delete_count 0 select NDB$OP_TYPE, b$old, b$new, c$old, c$new, NDB$CFT_CAUSE from test.t3$EX order by a; NDB$OP_TYPE b$old b$new c$old c$new NDB$CFT_CAUSE READ_ROW Initial data 1 Initial data 1 1 1 TRANS_IN_CONFLICT starting secondary replica start replica; Secondary should have been realigned to Primary **** Primary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 **** Secondary **** select * from test.t2 order by a; a b 1 Initial data 1 2 Initial data 2 3 Initial data 3 4 Initial data 4 5 Initial data 5 6 Initial data 6 7 Initial data 7 8 Initial data 8 9 Initial data 9 10 Initial data 10 select * from test.t3 order by a; a b c d 1 Primary first change 1 1 1 2 Primary first change 2 2 2 3 Initial data 3 3 3 4 Initial data 4 4 4 5 Initial data 5 5 5 6 Initial data 6 6 6 7 Initial data 7 7 7 8 Initial data 8 8 8 9 Initial data 9 9 9 10 Initial data 10 10 10 update test.t2 set b="Initial data 3" where a=3; update test.t3 set b="Initial data 1" where a=1; update test.t3 set b="Initial data 2" where a=2; Sync all changes --------------------------------------- delete from test.t2$EX; delete from test.t3$EX; drop table mysql.ndb_replication; drop table test.t1, test.t2, test.t3; drop table test.t1$EX, test.t2$EX, test.t3$EX; SET ndb_log_exclusive_reads= 0; flush logs; stop replica; reset replica; include/rpl_end.inc