SBR vs RBR when using On Duplicate Key Update for High Availability

The other day, I was discussing high availability (HA) with other people. The discussion was going in the direction of, as the application was only using INSERT ... ON DUPLICATE KEY UPDATE (IODKU) and traffic could be replayed, promoting a slave in case of the failure of the master was easy: replaying traffic would make& all the slaves converge back to a common state. It looks as this could work but it might not be as simple. Before going into the details, let's take a step back and look into HA in general.

tl;dr: be careful when using row-based replication when replicating IODKU as slaves will stop replicating if a row is present on the master and missing on slave (or vice-versa).

High Availability by Promoting a Slave as the new Master

Let's suppose we are working with this replication topology composed of 1 master and 3 slaves:

  +---+
  | A |
  +---+
    |
    +------+------+
    |      |      |
  +---+  +---+  +---+
  | B |  | C |  | D |
  +---+  +---+  +---+

In the general case, when we want to promote one of B, C or D as the new master after the failure of A, the main problem is that those 3 slaves might be inconsistent:

B might be 1 transaction late from A,
C might be 2 transactions late from A,
and D might be 3 transactions late from A.

The solution to this problem is to level all slaves before promoting one of them as the new master. Once all slaves are levelled, promoting a new master is easy:

choose any slave as the new master,
note the binary log position of the chosen slave (SHOW MASTER STATUS),
place the other nodes as slaves of the new master at the position noted in the previous step.

Many solutions exist to level all the slaves, some of them are:

MHA: using the relay logs of the most up-to-date slave (B in our example) to bring the other nodes at its level,
GTIDs: using the binary logs of the most up-to-date slave to bring the other nodes at its level,
Binlog Tailer with Semi-Sync: using mysqlbinlog to keep a backup of the master's binary logs and using those to bring the slaves at the level of the failed master (copying the missing binlogs to the slaves and running them locally),
Binlog Server: keeping a copy of the master's binary logs on a proxy and replicating through this proxy (the slaves converge automatically in case of a failure of the master).

IODKUs (or REPLACEs) for High Availability

The other situation I am presenting in the introduction, and that is very application-dependent, is:

skip the leveling of the slaves,
and let the application make sure that all slaves converge back to a consistent state.

Using IODKUs (or REPLACEs) and replaying the last transactions (if this can be done by our application), all the slaves would become consistent:

the slaves missing the row would INSERT them,
and the slaves already having the row would UPDATE them.

This works using statement-based replication (SBR), but does not work using row-based replication (RBR). The reason why it breaks with RBR is that IODKUs (or REPLACEs) are lost when the transactions are logged in the binary logs:

if the master ends up doing an INSERT, a Write_rows event is logged in the binary logs,
if the master ends up doing an UPDATE, an Update_rows event is logged in the binary logs,
when a Write_rows event is executed on a slave where the row is already present, a duplicate key error is generated,
and when an Update_rows event is executed on a slave where the row is missing, a can't find record error is generated.

You can see test scripts in annex to this post.

I think you can still use IODKUs (or REPLACEs) as an HA strategy. If you use SBR, everything looks fine. But be careful not to later switch to RBR without using one of these solutions in case of the failure of the master:

Clean all slaves of transactions that will be replayed (level down) before promoting a master.
Replay the transactions on all slaves (level up) before promoting the a new master.
After promoting a slave as the new master, replay transactions in SBR before switching back to RBR.

Is any of you using IODKUs (or REPLACEs) as an HA strategy ? If yes, I would be interested to ear your use-case and learn from your experience. Feel free to leave a comment below.

Wishlist

We saw above that IODKUs (and REPLACEs) statements are lost in RBR, but should this be the case ? Maybe keeping the information that a Write_rows or an Update_rows event was generated by a IODKU (or a REPLACE) would be useful. With that, the slaves could execute the events as a true IODKUs (or REPLACEs). It is probably suitable that, by default, an IODKU (or a REPLACE) event breaks replication so a dba can investigate the source of the problem. Then, he could decide to skip or execute the event if suited (a new GLOBAL variable, similar to sql_slave_skip_counter, would be needed: sql_slave_row_event_force).

And in the more general case, maybe a RBR recovery mode would be useful. In this mode:

Write_rows would be run as UPDATE when the row is present,
Update_rows would be run as INSERT when the row is missing,
Delete_rows would not fail if the row is absent.

What do you think about those 2 proposed features (IODKU / REPLACE in RBR events and RBR recovery mode), would you like them to be integrated in MySQL ? Feel free to leave comments about those below.

Annex: Test it Yourself

Note: using REPLACEs instead of IODKU will lead to similar results.

Note2: those tests were run with MySQL 5.6.20.

After setting a master-slave replication (a single slave is enough), you can test that it works using SBR:

# On the master, initialize the test:
SET binlog_format = 'STATEMENT';
SHOW VARIABLES like 'binlog_format';

DROP DATABASE if exists test_iodku;
CREATE DATABASE test_iodku;
CREATE TABLE test_iodku.t1 (
  id BIGINT PRIMARY KEY,
  nb BIGINT DEFAULT NULL);
INSERT into test_iodku.t1 VALUES (1, NULL), (2, NULL);
SELECT * FROM test_iodku.t1;

# On the slave slave, DELETE a row present on the master:
DELETE FROM test_iodku.t1 WHERE id = 1;
SELECT * FROM test_iodku.t1;

# On the master, UPDATE a row absent on the slave:
INSERT into test_iodku.t1 VALUES (1, 1)
  ON DUPLICATE KEY UPDATE nb = 1;
SELECT * FROM test_iodku.t1;

# On the slave (everything is there as on the master):
SELECT * FROM test_iodku.t1;

# On the master, DELETE locally and INSERT a row present on the slave:
SET sql_log_bin = 0;
DELETE FROM test_iodku.t1 WHERE id = 2;
SET sql_log_bin = 1;
SELECT * FROM test_iodku.t1;

INSERT into test_iodku.t1 VALUES (2, 2)
  ON DUPLICATE KEY UPDATE nb = 2;
SELECT * FROM test_iodku.t1;

# On the slave (everything is there as on the master):
SELECT * FROM test_iodku.t1;

And you can test that it is breaking with RBR:

# On the master, initialise the test:
SET binlog_format = 'ROW';
SHOW VARIABLES like 'binlog_format';

DROP DATABASE if exists test_iodku;
CREATE DATABASE test_iodku;
CREATE TABLE test_iodku.t1 (
  id BIGINT PRIMARY KEY,
  nb BIGINT DEFAULT NULL);
INSERT into test_iodku.t1 VALUES (1, NULL), (2, NULL);
SELECT * FROM test_iodku.t1;

# On the slave slave, DELETE a row present on the master:
DELETE FROM test_iodku.t1 WHERE id = 1;
SELECT * FROM test_iodku.t1;

# On the master (UPDATE a row absent on the slave):
INSERT into test_iodku.t1 VALUES (1, 1)
  ON DUPLICATE KEY UPDATE nb = 1;
SELECT * FROM test_iodku.t1;

# On the slave (the row is not there and replication is broken):
SELECT * FROM test_iodku.t1;
SHOW SLAVE STATUS\G

# On the slave, let’s skip the error to resume replication:
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE; SHOW SLAVE STATUS\G

# On the master (DELETE locally and INSERT a row present on the slave):
SET sql_log_bin = 0;
DELETE FROM test_iodku.t1 WHERE id = 2;
SET sql_log_bin = 1;
SELECT * FROM test_iodku.t1;

INSERT into test_iodku.t1 VALUES (2, 2)
  ON DUPLICATE KEY UPDATE nb = 2;
SELECT * FROM test_iodku.t1;

# On the slave (the row is not updated and replication is broken):
SELECT * FROM test_iodku.t1;
SHOW SLAVE STATUS\G

# On the slave, let’s skip the error to resume replication:
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE; SHOW SLAVE STATUS\G

J-F Gagné's MySQL Blog

Wednesday, August 13, 2014