CODEWITHSUNDEEP
mysqlreplicationon-duplicate-key
nemenems
nemenems

Reputation: 1092

Why mysql INSERT ... ON DUPLICATE KEY UPDATE can break RBR replication on a master / master configuration

here is the problem:

I have a table like

byUserDailyStatistics:

All requests are 

INSERT INTO byEmailDailyStatistics
(date, idUser, metric1, metric2)
VALUES (:date, :user:, 1, 1)
ON DUPLICATE KEY UPDATE
metric1 = metric1 + 1,
metric2 = metric2 +1

And sometimes, the replication breaks with message like

could not execute Write_rows event on table stats.byUserDailyStatistics; Duplicate entry '6447412-2016-01-06' for key 'UNIQUE', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.035580, end_log_pos 279798813

What could be the origin of this issue?

Upvotes: 4

Views: 1872

Answers (2)

Rick James
Rick James

Reputation: 142518

Let's make the statement more general: "Simultaneously acting on the same row in both Masters is unsafe." It is not only IODKU. Also, INSERTing rows with the same unique key (especially if they have different values in other columns) will cause errors.

Galera avoids the problem by checking with other nodes at COMMIT time.

NDB Cluster avoids the problem by implementing "eventual consistency".

Off-the-shelf Master-Master is full of problems; you have identified only one of them. Most of the problems can be avoided, as Mike points out, but only writing to one Master.

Upvotes: 2

Emil Vikström
Emil Vikström

Reputation: 91983

You are trying to write the same idUser, date pair to both your replicas at the same time.

  1. One client writes to master1 using an odd primary key
  2. Another client writes to master2 using an even primary key, before the first write was synced
  3. The servers try to sync up with each other

In the last step the same pair exists on both server under different primary keys; different rows but the secondary unique key is the same.

Upvotes: 3

Related Questions