MySQL Bugs: #47952: Ndb replication : Unique index modifications give slave duplicate key errors

Bug #47952	Ndb replication : Unique index modifications give slave duplicate key errors
Submitted:	9 Oct 2009 17:09	Modified:	29 Apr 2011 8:25
Reporter:	Frazer Clement	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Replication	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.2	OS:	Any
Assigned to:	Jonas Oreland	CPU Architecture:	Any
Tags:	mysql-5.1-telco-6.2+

Description:
Row events in the Ndb Binlog are ordered according to the partitioning of the 'base' table, and may appear in a different order within an epoch than they were executed in.

Where the base table has unique index(es), this re-ordering of events can result in duplicate key errors when the binlog is executed.

How to repeat:
--source include/have_ndb.inc
--source include/have_binlog_format_mixed_or_row.inc
--source include/ndb_master-slave.inc

#
# Slave behaviour when replicating unique index operations
#
#

--connection master

CREATE TABLE t1 (pk int primary key, uk int, unique(uk)) engine=ndb;

# Now perform some operations which do not result in uniqueness
# violations when applied in-order, but which can result in them
# when applied out-of-order
# 5 distinct values chosen to increase the chance of different
# fragments (and therefore potentially different SUMA->Binlog
# orders) being seen.
#
insert into t1 values (1,1);
insert into t1 values (2,2);
insert into t1 values (3,3);
insert into t1 values (4,4);
insert into t1 values (5,5);

# Do a 'right shift' on the unique key values
update t1 set uk=uk-1 order by pk;

--echo Master table contents
select * from t1 order by pk;

--sleep 1

--connection slave

--echo Slave table contents
select * from t1 order by pk;

--source include/show_slave_status.inc

--connection master
drop table t1;

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

http://lists.mysql.com/commits/114649

Fix pushed to 7.0.25 and 7.1.14

Documented as follows in the NDB-7.0.25 and 7.1.14 changelogs:

        Operations that updated unique keys of NDB tables could cause
        duplicate key errors when trying to execute the binary log.
        (Previously, row events in the binary log were ordered according
        to the partitioning of the base table, which could differ in
        order within the epoch for that in which they were executed.

Also updated dev history, cluster replication Known Issues.

Closed.