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.
You had me at “Don’t use non-transactional tables” 🙂
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
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:
START TRANSACTION; INSERT INTO foo (col1, col2) VALUES (5, 'asdf'); UPDATE bar SET somecol=(SELECT col2 FROM foo WHERE col1=5) WHERE othercol=20; COMMIT;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.