Replication with InnoDB and MyISAM Transactions
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:
| item | val |
|---|---|
| 1 | 1 |
| 2 | 2 |
And the Master myisam_tbl will look like this:
| item | val |
|---|---|
| 1 | 2 |
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:
| item | val |
|---|---|
| 1 | 0 |
| 2 | 1 |
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:
| item | val |
|---|---|
| 1 | 2 |
| 2 | 2 |
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.
-
Gary Pendergast, noun: Gamer, developer, MySQL, WordPress, web and music geek. Currently taking a leave of absence from life as a MySQL Support Engineer for Oracle to tour around Europe. Can be observed in his natural habit at pento.net, or usually in Bologna, Italy.
