| 1 |
# |
| 2 |
# Test replication of changes to a table with |
| 3 |
# - A secondary unique key |
| 4 |
# - All columns (including unchanged columns) are included in UPDATE events |
| 5 |
# (ndb_log_updated_only=0) |
| 6 |
# - Updates are logged as updates |
| 7 |
# (ndb_log_update_as_write=0) |
| 8 |
# - Updates change the value of a column other than the secondary unique column |
| 9 |
# - INSERT and UPDATE events are interleaved |
| 10 |
# |
| 11 |
# Problem occurs as : |
| 12 |
# - Bug in replication code carries INSERT related handler flags over to UPDATE |
| 13 |
# processing |
| 14 |
# - UPDATE processing modifies existing row with secondary unique value |
| 15 |
# - Secondary unique value is present in incoming Binlog |
| 16 |
# - Flag setting causes a 'check for existence' pre check on the secondary value |
| 17 |
# before applying the UPDATE |
| 18 |
# - The secondary value does exist as the row exists and the value is unchanged |
| 19 |
# - This causes a replication failure |
| 20 |
# |
| 21 |
--source include/have_ndb.inc |
| 22 |
--source include/have_binlog_format_mixed_or_row.inc |
| 23 |
--source suite/ndb_rpl/ndb_master-slave.inc |
| 24 |
|
| 25 |
|
| 26 |
drop table if exists mysql.ndb_replication; |
| 27 |
CREATE TABLE mysql.ndb_replication |
| 28 |
(db VARBINARY(63), |
| 29 |
table_name VARBINARY(63), |
| 30 |
server_id INT UNSIGNED, |
| 31 |
binlog_type INT UNSIGNED, |
| 32 |
conflict_fn VARBINARY(128), |
| 33 |
PRIMARY KEY USING HASH (db,table_name,server_id)) |
| 34 |
ENGINE=NDB PARTITION BY KEY(db,table_name); |
| 35 |
|
| 36 |
--echo Populate ndb_replication table as necessary |
| 37 |
eval replace into mysql.ndb_replication values |
| 38 |
("test", "t1", 0, 7, NULL); |
| 39 |
|
| 40 |
create table test.t1 ( |
| 41 |
a int primary key, |
| 42 |
b int, |
| 43 |
c int, |
| 44 |
unique(c)) engine=ndb; |
| 45 |
|
| 46 |
--echo Insert some data |
| 47 |
|
| 48 |
insert into test.t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5); |
| 49 |
|
| 50 |
--sync_slave_with_master slave |
| 51 |
|
| 52 |
--connection master |
| 53 |
--echo Now try to show problem |
| 54 |
--echo Generate inserts and updates interleaved |
| 55 |
--echo Updates are on existing keys, but do not affect existing key values |
| 56 |
|
| 57 |
insert into test.t1 values (6,6,6), (7,7,7), (8,8,8); |
| 58 |
|
| 59 |
update test.t1 set b=10 where a < 6; |
| 60 |
|
| 61 |
select * from t1 order by a; |
| 62 |
|
| 63 |
# Example of resulting binlog content (not deterministic): |
| 64 |
# |
| 65 |
# ## Table creation |
| 66 |
# master-bin.000001 1364 Query 1 1529 use `test`; create table test.t1 ( |
| 67 |
# a int primary key, |
| 68 |
# b int, |
| 69 |
# c int, |
| 70 |
# unique(c)) engine=ndb /* xid=218 */ |
| 71 |
# |
| 72 |
# ## Epoch transaction 1, all INSERTs |
| 73 |
# master-bin.000001 1529 Anonymous_Gtid 1 1608 SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 74 |
# master-bin.000001 1608 Query 1 1679 BEGIN |
| 75 |
# master-bin.000001 1679 Table_map 1 1729 table_id: 259 (test.t1) |
| 76 |
# master-bin.000001 1729 Table_map 1 1801 table_id: 163 (mysql.ndb_apply_status) |
| 77 |
# master-bin.000001 1801 Write_rows 1 1866 table_id: 163 |
| 78 |
# master-bin.000001 1866 Write_rows 1 1966 table_id: 259 flags: STMT_END_F |
| 79 |
# master-bin.000001 1966 Query 1 2038 COMMIT |
| 80 |
# |
| 81 |
# |
| 82 |
# ## Epoch transaction 2, interleaved INSERTs and UPDATEs, with STMT_END_F on last event. |
| 83 |
# master-bin.000001 2038 Anonymous_Gtid 1 2117 SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 84 |
# master-bin.000001 2117 Query 1 2188 BEGIN |
| 85 |
# master-bin.000001 2188 Table_map 1 2238 table_id: 259 (test.t1) |
| 86 |
# master-bin.000001 2238 Table_map 1 2310 table_id: 163 (mysql.ndb_apply_status) |
| 87 |
# master-bin.000001 2310 Write_rows 1 2375 table_id: 163 |
| 88 |
# master-bin.000001 2375 Write_rows 1 2436 table_id: 259 |
| 89 |
# master-bin.000001 2436 Update_rows 1 2576 table_id: 259 |
| 90 |
# master-bin.000001 2576 Write_rows 1 2624 table_id: 259 |
| 91 |
# master-bin.000001 2624 Update_rows 1 2686 table_id: 259 flags: STMT_END_F |
| 92 |
# master-bin.000001 2686 Query 1 2758 COMMIT |
| 93 |
# |
| 94 |
# |
| 95 |
# ## |
| 96 |
# The problems can occur when processing Update_rows events after Write_rows events - the |
| 97 |
# handler settings from the Write_rows event are retained when processing the Update_rows |
| 98 |
# event and cause it to behave incorrectly. |
| 99 |
# ## |
| 100 |
# |
| 101 |
|
| 102 |
|
| 103 |
|
| 104 |
--sync_slave_with_master slave |
| 105 |
|
| 106 |
select * from t1 order by a; |
| 107 |
|
| 108 |
--connection master |
| 109 |
#show binlog events; |
| 110 |
drop table test.t1; |
| 111 |
drop table mysql.ndb_replication; |
| 112 |
--sync_slave_with_master slave |
| 113 |
|
| 114 |
|
| 115 |
--source include/rpl_end.inc |