Replication with InnoDB and MyISAM Transactions

February 13, 2009 · Posted in MySQL · 4 Comments 

There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:

TRANSACTION 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0);
TRANSACTION 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1);
TRANSACTION 1: START TRANSACTION;
TRANSACTION 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
TRANSACTION 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;
TRANSACTION 2: START TRANSACTION;
TRANSACTION 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
TRANSACTION 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
TRANSACTION 2: COMMIT;
TRANSACTION 1: COMMIT;

After this, the Master innodb_tbl would look like this:

itemval
11
22

And the Master myisam_tbl will look like this:

itemval
12

In 5.1.30 and earlier, the Slave tables will be correct. However, in 5.1.31, the Slave myisam_tbl will be correct, but the innodb_tbl will look like this:

itemval
10
21

As a bonus, there’s no workaround. Statement Based Replication has never worked for this case. For an SBR Slave (In MySQL 5.0.x and 5.1.x), the Slave myisam_tbl will be correct, but the Slave innodb_tbl will look like this:

itemval
12
22

And so, we come to the moral of the story. Don’t use non-transactional tables in the middle of a transaction. Ever. You will only cause yourself more pain than you can possibly imagine. Instead, move the writes to the non-transactional tables outside of the transaction.

Tags: , , , ,

« Previous Page