Does any one use mixed?
I bet many people have used Mixed-mode replication, since this was the default for a long time according to the MySQL Reference Manual:
From MySQL 5.1.12 to MySQL 5.1.28, mixed format is the default. Beginning with MySQL 5.1.29, statement-based format is the default.
The MySQL Performance Blog talks about different problems with the different replication methods.
So, it seems that MySQL has been trying to switch between mixed-mode, statement-based and row-based replication within the 5.1.x series, and that is unnerving.
I'm currently reading High Performance MySQL, Second Edition from O'Reilly. It has a great chapter on replication, and is well worth the read. Many of my questions have been answered with an hour of reading. You can read some of the chapter in Chapter 8. Replication > Replication Under the Hood - Pg. 357:
Because neither format is perfect for every situation, MySQL 5.1 switches between
statement-based and row-based replication dynamically. By default, it uses
statement-based replication, but when it detects an event that cannot be replicated
correctly with a statement, it switches to row-based replication. You can also control
the format as needed by setting the binlog_format session variable.
I assume you did this on one setup without cleaning up the slave and setting it up from scratch.
As the error message for the statement based replication tells you your application used some commands which can't be replicated using statement based replication. An example is a query like
INSERT INTO t (t) VALUES(NOW())
where NOW() will return different values when executed on the master and on the slave.
By doing this you have different data on the master and slave. Which is bad as depending on your slave your clients will read different data and subsequent writes will change other data, so you end up with data which is even more different.
Now you switch over to mixed replication, which might use row-based replication for some statements. With RBR you really need the exact same data as it has a hard time to identify the rows which were changed and update them.
So what should you do? - Configure your master to use mixed logging and then setup the slave using a consistent snapshot.
Best Answer
There's no discrepancy. RDS, being a managed service, does not enable every possible configurable value for every configuration parameter.
The architects of RDS chose not to make
STATEMENTavailable as an option forBINLOG_FORMAT, presumably, because they recognize the fact that it has many limitations in a replication environment.Even if you don't have RDS replicas, the binary log is still used for RDS point-in-time recovery. Without requiring at least
MIXEDlogging, there would be no way for RDS to restore an instance to a point-in-time and have it guaranteed to actually be identical to the original instance at the same point in time. UsingMIXEDorROWmakes that possible.Indeed, the more confusing question is why the default value in the official distributions wasn't changed to
MIXEDback in MySQL 5.1 or 5.5. UsingSTATEMENTmode is almost always the wrong choice.