Why mysql INSERT ... ON DUPLICATE KEY UPDATE can upset RBR replication in master / master configuration

Question

here is the problem:

• 2 MySQL 5.5 servers
• Row-based replication + master
• Records on both servers (both active)
• autoinc trick (1 server is odd, another is even)

I have a table like

byUserDailyStatistics:

• id (PK + AUTO INC)
• date of
• idUser
• METRIC1
• METRIC2
• UNIQUE (idUser, date)

All requests

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

And sometimes replication is interrupted by a 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 master log mysql-bin.035580, end_log_pos 279798813

What could be causing this problem?

Answer 1

You are trying to simultaneously create the same pair idUser, datefor both of your replicas.

• master1
• master2 , .

; , .

Answer 2

: " ". . , INSERTing (, ) .

Galera , COMMIT .

NDB , " ".

- ; . , , .