Can MySQL temporary tables be made safe for statement-based replication?

A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a slave is stopped (or crashed) while a temporary table is open and is then restarted, the temporary table doesn’t exist anymore, and the slave will have problems trying to replay any further statements that refer to these tables. Thus, I claimed, there’s no alternative but to eliminate temporary tables. This problem may not exist for row-based replication in MySQL 5.1 and later, but most installations I know of are using statement-based replication, even on MySQL 5.1

This is a contentious topic. People love their temporary tables and will ask hopefully “are you sure this isn’t safe?” They’ll propose all sorts of ways to mitigate the danger, and I’ve heard many of them. But I recently heard an angle on this I had not heard before.

The argument is this: “you can create an InnoDB temporary table and use it only within one transaction, and then if the slave crashes and restarts, it’ll roll back the transaction to the beginning.” In other words, in theory if the temporary table exists only within that one transaction, and if your transaction accesses only InnoDB tables, it’s safe.

My first thought was, you can’t do that. CREATE TABLE commits the transaction, so there’s implicitly more than one transaction. However, as the person pointed out, that isn’t true with CREATE TEMPORARY TABLE. I tested this (sometimes the manual is wrong!) and found that indeed, you can open a transaction, make some changes, create a temporary table with ENGINE=InnoDB, and the InnoDB transaction ID does not change in SHOW INNODB STATUS. The statements are all within one transaction. (However, if you type ROLLBACK the temporary table doesn’t get dropped. It’s not really transactional — it just doesn’t auto-commit the transaction. The ROLLBACK will produce a warning that says “Some non-transactional changed tables couldn’t be rolled back”, which is interesting.)

But does that mean it’s safe for replication?

There is one good way to find out: test it. I fired up my master-and-two-slaves replication sandbox, flushed all the logs, and set out to get to the bottom of the matter.

First, I stopped the slave threads so I could choose which statements to replay on the slave and pick the “crash point” as I wished. (I didn’t shut down the slave, I just stopped the replication processes. This is safe to do even when temporary tables are open.) Then I created a temporary table on the master, inserted some rows into it, and dropped it:

In theory, that’s all in one transaction. Since I flushed the logs before I did this, everything in the binary log so far comes from these statements. Let’s look at the binary logs:

Very interesting! The order of statements is not the same in the binlog as I typed into the console. If you replay the binary log, you’ll get two transactions here.

This shows us something interesting that isn’t considered in the “all inside one transaction” argument: transactions aren’t the only thing that matters. How the server logs events to the binary log is equally important. It appears that we can break replication on the slave by killing the slave after event 98 executes and before event 207 executes. But let’s not draw any conclusions yet. The only way to tell for sure is to really test it.

Since I’d stopped the slave, I could easily test my theory. Let’s let the slave replay events up until position 207, kill it, and restart it:

The slave is now “vulnerable,” in theory. To test my theory, I’ll shut down and restart the slave gracefully, rather than simulating a crash with kill -9, and see what happens.