Thursday, October 4, 2007

How safe is setting read_only?

We use a lot of dual master setups for redundancy and recoverability. Typically, most apps can't write to both masters at the same time, so for consistency we keep only one of the masters read-write at any given instant. To switch over gracefully, turn the primary read-only, wait for replication to catch up, turn the secondary read-write. Simple, eh?

Well, I've just had a report that setting the primary read-only caused a transaction to be partially replicated to a slave. Wait, what? If I'm reading the email correctly, they switched masters in the middle of a transaction. The transaction commit correctly failed on the master with a read-only error, but somehow the other master (or a slave) got part of the transaction that failed and had to be skipped.

This was an innodb table in MySQL 5.0. My first thought was that XA was disabled (though we typically use 4.1 here, so I thought of innodb_safe_binlog). I verified that the XA support appeared to be enabled correctly. My other thoughts are maybe the binlog didn't get synced properly or a thousand other possibilities... I'll update if find out more.

Now, I'm not panicking too much, this could be a non-issue, bad report, what-have-you. However, it got me thinking. How safe *is* it to simply set read-only? Transactionally speaking, is this doing what I want, which is an atomic "deny writes from now on" change? Is this safe with binlogging?

My user claims that doing a flush tables with read lock before setting read-only is safer, but is it? Does setting read-only somehow muck with the binlog to prevent it from being resynced with the inno transactional state somehow?

I figured I'd post it out here and see what people say...

UPDATE: I found out the user isn't using normal transactions, but rather AUTOCOMMIT. I'm following up to try to figure out what the exact nature of the slave error was.

However, another related question is: Is there a race condition between applying a write statement and writing the binary log? Is it possible to do one without the other if there's a sudden server crash? With Innodb, I'd expect the XA transactions to handle this event, but what about plain old MyISAM?

1 comment:

Baron said...

Did the transaction involve non-transactional tables in any way? If so, it gets binlogged regardless of what happens on the master. Braindead I know, but...