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:

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.

4 Replies to “Replication with InnoDB and MyISAM Transactions”

  1. Hi

    I’ve been trying to find out what exactly is meant by “transactional” and “non-transactional” – what is a transaction?
    Is a normal insert/update/delete a transaction, or is it only when you do something like line 5 in your example, where you’re doing a select query within a update query?
    Should all other types of tables be MyIsam on not innoDB?
    Thanks

    1. Put simply, a transaction is when you run multiple queries on a table, and they’re all written to disk at the same time. To simplify the above to one transaction, assuming that ‘foo’ and ‘bar’ are both InnoDB tables, and foo.col1 is a unique column:

      Now, if you were to run these queries on a MyISAM table, there’d be no guarantee ‘somecol’ would be set to ‘asdf’. Another query could be run between the INSERT and the UPDATE to change the value. With a transaction, on the other hand (signified by the START TRANSACTION/COMMIT), there can be no changes to the rows that all of these queries use until the transaction is complete.

      As a bonus, say you have many queries in this transaction, you get to the last one, and it fails. You can undo all of your changes by issuing the ‘ROLLBACK’ command, which will undo everything back to ‘START TRANSACTION’.

      Here’s a good introduction to transactions in MySQL:
      http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/

      For most normal usage, I would recommend using InnoDB for your tables. MyISAM does have a few advantages (in particular, can be faster under some circumstances), but a properly tuned InnoDB server is usually just as fast, and will recover your data properly after a crash. MyISAM is not crash-proof.

Comments are closed.