0

Is mariaDB INSERT … ON DUPLICATE KEY UPDATE safe to use with Galera replication?

I have found that it is not safe with Row based replications + master master Why mysql INSERT ... ON DUPLICATE KEY UPDATE can break RBR replication on a master / master configuration

But I cannot find how this relates to Galera replication. Can Galera cluster be configured, so this operation will always work, or can I use somethig else than INSERT … ON DUPLICATE KEY UPDATE?

My use case is as given below:

INSERT into logData (logKey, month_of_year) 
values(:logKey,:month_of_year) ON DUPLICATE KEY UPDATE
counter=counter+1

my logData table is created as follows:

CREATE TABLE `logData ` (
    `logKey` VARCHAR(20) NOT NULL,
    `month_of_year` DATE NOT NULL,
    `counter` INT(11) NOT NULL DEFAULT '1',
    `latest_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`logKey`, `month_of_year`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
Bartosz Bilicki
  • 12,599
  • 13
  • 71
  • 113

1 Answers1

1

That reference is bogus. It had a problem because of having two UNIQUE keys (the PK and the UNIQUE key) and both Masters were receiving the same values for the unique key.

You don't seem to have a UNIQUE key other than the PK, so you should not have any problem. And, furthermore, Galera has code to resolve the problem, whereas ordinary Master-Master does not.

Or at least you won't get the same problem.

Before COMMITting the IODKU, Galera will check with all the other nodes. If one of them is simultaneously receiving the same query, it will complain. Then the original node will cause an error on the COMMIT.

For this reason, you must check for errors even after COMMIT when using Galera.

What to do with the error? Re-execute the query. By the time you execute the IODKU the second time, the other node's query will probably be finished, and this node will do the 'update'. And the counter will be '2', as it should be.

If you need to discuss this further, please provide SHOW CREATE TABLE.

Rick James
  • 135,179
  • 13
  • 127
  • 222