MySQL Bugs: #39432: slave stops if master does insert ... on duplicate key update

Bug #39432	slave stops if master does insert ... on duplicate key update
Submitted:	13 Sep 2008 12:50	Modified:	30 May 2009 7:02
Reporter:	johnny slakva	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	ndb-6.2+	OS:	Any (debian)
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	--ndb-log-update-as-write, cluster, insert on duplicate key update, replication

Description:
i have a setup with ndb database as replication master, and myisam being replication slave.

Everything works fine until master executes statement "insert ... on duplicate key update", which encounters duplicate key.

At that point, slave sql thread fails with error like "Duplicate entry '1' for key 'PRIMARY'".

This doesnt seem to be repeat neither with stmt-based replication, nor with row-based (i tried to repeat with myisam replicated to myisam with binlog-format = ROW - bug doesnt happen.) Bug only happens if master update was on ndbcluster table.

I didnt try to repeat with cluster-to-cluster.

Setting slave_exec_mode='idempotent' did solve this issue for me; though, im afraid keeping it bc of other possible errors that may be missed in this execution mode.

How to repeat:
schema on master:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL,
  `data` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `k` (`k`)
) ENGINE=ndbcluster;

schema on slave:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL,
  `data` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `k` (`k`)
) ENGINE=myisam;

with these schemas, start replication.

now, on master, do:

INSERT INTO test (k, data) values (5, 'aaa');
INSERT INTO test (k, data) values (5, 'bbb') ON DUPLICATE KEY UPDATE data='bbb';

after this second query, slave stops with error "Duplicate entry '1' for key 'PRIMARY'"

This is the current behavior in 6.2 because of the way logging is done.  In 6.3 there are options to control logging in ndb such that this is can be avoided.

--ndb-log-update-as-write=0

I tried --ndb-log-update-as-write=0 with 6.2, and it worked fine, thank you very much for this hint.

Though, im not sure if this is a problem with settings, bc regular inserts and regular updates are replicated fine; so i dont see why "insert .. on duplicate key update" should break the replication under certain logging mode...

I wish this were either fixed somehow in code, or mentioned in docs (that one may need to switch logging mode if there is intention to use "insert .. on duplicate key update".

Cluster docs issue, assigning to myself and setting Stefan as lead.

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.