Should you be worried about STATEMENT based replication?

Earlier this month, an announcement about STATEMENT based binary logging would be the default starting with MySQL version 5.1.29. I've always preached that backwards compatibility was key to new releases. In this case, lessons were not learned until close to final GA date.

I would like to point out that for 90% of customer cases, STATEMENT based replication will work fine as advertised. But I'd like to point out some use cases where STATEMENT based replication will be at best spotty (at least it is in 5.1.28).

If you primarily use InnoDB as your storage engine you will want to pay close attention to your transaction isolation level. There is a minimum requirement that READ COMMITED level be used, otherwise statement based replication can not be used.

Partitioning + InnoDB + STATEMENT-based binlog also has its problems. We faced constant issues, getting the error 'Binary logging not possible. Message: Statement-based format required for this
statement, but not allowed by this combination of engines'. What the heck does this mean?

It's a misleading error message. Partitioning is not a true storage engine, but is a virtual one. The first time an underlying table is opened, the partition engine caches the table flags of the real storage engine, in this case InnoDB. Lets say for example, an app performs a SELECT on a partitioned InnoDB table. Let's also assume that the transaction isolation level is READ UNCOMMITTED. The SELECT will execute without any issues. But, try to insert a record from a different session, and it will fail every time. I filed this BUG#39084 over a month and a half ago. Repeatable test cases were also given.

Since we are committed to releasing our reporting database and can not wait for MySQL to come around and realize mistakes they made, we came up with our own patch that addresses this immediate concern, and for this reporting db.

So, should you be worried? I think I would be.