STATEMENT based replication as default in MySQL 5.1

MySQL 5.1.29, the next (and last) release candidate, will revert the default replication mode to STATEMENT based. As noted in Bug#39812, MIXED mode won't be the default anymore. But the template option files provided with the distribution will have a line that enables MIXED mode.

The rationale for this change is that MIXED mode as default may break existing applications, where statements like the following are used:


UPDATE t1 SET a=1 WHERE @@server_id = 1;
INSERT INTO t1 (origin, val) @@server_id , 1;

We know that several users are exploiting the quirks of STATEMENT based replication to their advantage. So we decided that it was safer not to force MIXED mode on users who are upgrading.

Instead, we added MIXED mode to the option files templates, to encourage new users to start with MIXED mode.

We are very eager to boost the adoption of MIXED base replication, but we are also concerned about mismatches and would like to avoid unpleasant surprises for users who switch from 5.0 to 5.1.

Please notice that this change is not aiming at making MySQL 5.1 master servers compatible with 5.0 slaves. The manual warns explicitly against using lesser versions for slave servers, and the rationale of this change has nothing to share with mixed versions usage.

Update : fixed the master/slave mixup.

Comments:

Why won't MySQL assert that 5.0 master -> 5.1 slave is safe? How can that possibly help adoption of MySQL 5.1? The first place I will deploy it is on a slave. If MySQL doesn't expect it to work there, then why should I? And if I cannot expect it to work, it won't get much use.

Posted by Mark Callaghan on October 14, 2008 at 06:43 AM CEST #

Mark,
Sorry, it was the other way around, as the second part of the statement proves.
Replication with 5.1 master and 5.0 slaves is not recommended. Not the other way around. Sorry for the mixup.

Giuseppe

Posted by Giuseppe Maxia on October 14, 2008 at 07:18 AM CEST #

Hi Giuseppe,

Odd. How can this be a "final release candidate" if fundamental defaults are still in flux?

Regards,

Jeremy

Posted by Jeremy Cole on October 14, 2008 at 11:09 AM CEST #

Hi Jeremy,

Where is the "fundamental default"?
This is a matter of users habits.

Cheers

Giuseppe

Posted by Giuseppe Maxia on October 14, 2008 at 11:57 AM CEST #

So because users aren't wrapping things appropriately in transactions, that calls for changing the default?

Something's fishy here.

"quirks" of statement-based replication? And how exactly does this break applications? Does mixed mode and row-based not support setting variables? even wrapped in a transaction?

Posted by Sheeri K. Cabral on October 14, 2008 at 11:32 PM CEST #

Sheeri,
Given the comments above, apparently, few people have tested row based replication so far.
The "quirks" I refer to were explained in a previous blog post of mine (http://tinyurl.com/6bd3bu).

You can use them at your advantage. Transactions are not the problem here. Their replication is.

The variables I mention in this article are not replication safe in STATEMENT based binlog. However, if your application uses these variables at your advantage for specific purposes, as explained in my early blog post, then, and only then, we can break compatibility.

If you are not using this kind of tricks in your application, it's perfectly safe and recommendable to use MIXED mode. Since you were not even aware of the availability of this "trick", you can adopt MIXED mode in your applications without problems.

Posted by Giuseppe Maxia on October 15, 2008 at 12:31 AM CEST #

Post a Comment:
Comments are closed for this entry.
About

Giuseppe Maxia

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today