Mysql – Why isn’t mixed-mode replication default on MySQL

MySQLreplication

It is well known that mixed-mode replication was default from MySQL 5.1.12 to MySQL 5.1.28. However, this was reverted, and at the current moment, statement-based replication is default.

In the announcement that statement-based replication was default, Oracle claimed that this was only because mixed mode replication constituted a behavior change which may catch some users unaware. (See also Bug 39812.) However, MySQL 5.1 has many behavior changes, and it because the benefits of mixed-base replication so clear (I'm sure every DBA has run into a case where replication failed because a user submitted a query which didn't replicate properly and caused their databases to diverge), this doesn't seem like a particularly strong reason.

There are some mutterings that mixed replication "can provide the best of both worlds, but needs testing", but we're already on MySQL 5.1.61, and one would hope all of the bugs would have been ironed out by now. So that leaves us just rather confused. Why isn't mixed mode replication default?

Best Answer

I asked myself this question recently as well. At first, I looked at why row-based replication wasn't enabled by default. Obviously, row-based replication creates the possibility of IO-strain on disks by the amount of data that potentially could be written to the binary log.

Not surprisingly, this is the number one disadvantage of row-based replication the MySQL documentation mentions. A bit further down, another disadvantage would be a potential issue with performance with MyISAM:

For tables using the MyISAM storage engine, a stronger lock is required on the slave for INSERT statements when applying them as row-based events to the binary log than when applying them as statements. This means that concurrent inserts on MyISAM tables are not supported when using row-based replication.

MyISAM is the default engine of the 5.1 series. It is not until 5.5 that InnoDB is default. This replication impact on MyISAM, to me, is a strong reason not to use RBR by default.

But your question revolves around the MIXED format. Under MIXED format, the default is to use statement-based replication until a statement is called that required row-based replication. The link above gives a nice overview of what type of logging gets performed on certain situations (safe, unsafe, row injection).

I think there are too many potential situations that would cause replication to switch to row-based replication, causing some of the performance issues noted above on non-optimal hardware. And let's face it, MySQL has a large following of users running it on non-optimal hardware.

This situation stands out to me the most:

If a statement is logged by row and the session that executed the statement has any temporary tables, logging by row is used for all subsequent statements (except for those accessing temporary tables) until all temporary tables in use by that session are dropped.

Note that all statements involving temporary table in row-based logging is considered unsafe, so this could cause issues with your replication setup.

Ultimately, I think it is a good idea to have STATEMENT be the default, having to make a conscious, educated decision to switch to MIXED.

Related Question