Why isn't mixed-mode replication default on MySQL

Question

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?

Answer 1

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.

Answer 2

According to current documentation, SBR is the default for MySQL 5.5

In the question, it says It is well known that mixed-mode replication was default from MySQL 5.1.12 to MySQL 5.1.28.

However, it is worthy noting that

• MySQL 5.1.30 is the first GA release of MySQL 5.1 (Dec 2008)
• MySQL 5.1.24 was released Apr 2008
• MySQL 5.1.12 was released Nov 2006

In light of this, technically speaking, early GA releases of MySQL 5.1 are always SBR by default. In addition

• Oracle bought Sun April 20, 2009, predating Oracle involvement in MySQL 5.1 rc versions
• Sun bought MySQL Jan 2008, predating Sun's initial involvement in MySQL 5.1 rc versions

So, the proper question is Why did MySQL AB not incorporate mixed-mode replication in MySQL 5.1?

IMHO I could easily see that being the case. Oracle would naturally have a hands-off approach to describing the problem and claiming any responsibility. While Oracle has always used RBR in its flagship database, I don't see Oracle stepping in to make RBR have any sort of limelight in MySQL.

From another angle, since MySQL can readily run on commodity hardware, SBR would be more than adequate. One would also not be bludgeoned to death by rapidly growing binary logs. Larger MySQL installations with advanced replication setups would be better suited for RBR.

Getting around issues with using SBR, RBR, and Mixed should be as simple as clearing all relay logs and starting replication from scratch on any given Slave.