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

[13 Sep 2008 12:50] johnny slakva
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'"
[13 Sep 2008 14:08] Tomas Ulin
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
[13 Sep 2008 21:30] johnny slakva
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".
[20 May 2009 10:46] Jon Stephens
Cluster docs issue, assigning to myself and setting Stefan as lead.
[30 May 2009 7:02] Jon Stephens
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.