MySQL Bugs: #47863: binlog_format should be writable only at transaction boundaries

Bug #47863	binlog_format should be writable only at transaction boundaries
Submitted:	6 Oct 2009 13:11	Modified:	8 Mar 2010 0:22
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any
Tags:	binlog_format

Description:
When @@session.binlog_format is modified inside a transaction, it can cause slave to go out of sync. This is true after WL#2687, and possibly before that too (it's harder to know since the behavior before WL#2687 is not completely understood).

The problem is that if @@session.binlog_format=row, we make the assumption that all previous unsafe statements were logged in row format.

Moreover, setting @@session.binlog_format in a sub-statement generally confuses the logic of binlogging. I don't have a test case that proves it doesn't work, but more importantly I don't have a proof that it works.

How to repeat:
source include/master-slave.inc;
source include/have_innodb.inc;

create table tn (a int) engine = myisam;
create table tt (a int) engine = innodb;

set @@session.binlog_format = statement;

insert into tn values (1);

begin;
  insert into tt select * from tn;
  set @@session.binlog_format = row;
  insert into tn values (1);
commit;

select * from tt;
select * from tn;

sync_slave_with_master;

select * from tt;
select * from tn;

Suggested fix:
Make @@session.binlog_format read-only if there have been any previous statements in the transaction. Disallow setting @@session.binlog_format in sub-statements.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91731

3123 Dao-Gang.Qu@sun.com	2009-11-26
      Bug #47863  	binlog_format should be writable only at transaction boundaries
      
      When @@session.binlog_format is modified inside a transaction, 
      it can cause slave to go out of sync.
      
      To fix the problem, make the SESSION variable 'binlog_format' 
      read-only in transaction.
     @ mysql-test/suite/rpl/r/rpl_binlog_format.result
        Test Result for bug#47863.
     @ mysql-test/suite/rpl/t/rpl_binlog_format.test
        Added the test file to verify if the session variable 'binlog_format' 
        is read-only in transaction and sub-statements, and is writable at 
        transaction boundaries.
     @ sql/set_var.cc
        Added code to make the SESSION variable 'binlog_format' 
        read-only in transaction.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92590

3201 Dao-Gang.Qu@sun.com	2009-12-03
      Bug #47863  	binlog_format should be writable only at transaction boundaries
            
      When @@session.binlog_format is modified inside a transaction, 
      it can cause slave to go out of sync. This is true after WL#2687, 
      and possibly before that too (it's harder to know since the behavior 
      before WL#2687 is not completely understood).
            
      To fix the problem, In 5.1, a warning is logged when the @@session.binlog_format 
      is set inside a transaction. In 5.1-rep+3, an error is given when the 
      @@session.binlog_format is set inside a transaction.
     @ mysql-test/suite/rpl/r/rpl_binlog_format.result
        Test Result for bug#47863.
     @ mysql-test/suite/rpl/t/rpl_binlog_format.test
        Added test to verify if a warning will be logged, when the session variable 
        'binlog_format' is set inside a transaction, and the session variable 
        'binlog_format' is read-only inside a sub-statements.
     @ sql/set_var.cc
        Added code to log a warning when the @@session.binlog_format 
        is set inside a transaction.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/93465

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/93842

3125 Dao-Gang.Qu@sun.com	2009-12-14
      Bug #47863  	binlog_format should be writable only at transaction boundaries
      
      When @@session.binlog_format is modified inside a transaction, 
      it can cause slave to go out of sync.
      
      To fix the problem, make the session variable 'binlog_format' 
      read-only inside a transaction.
     @ mysql-test/suite/rpl/r/rpl_binlog_format.result
        Test result for bug#47863.
     @ mysql-test/suite/rpl/t/rpl_binlog_format.test
        Added the test file to verify if the session variable 'binlog_format' 
        is read-only inside a transaction and in sub-statements.
     @ sql/set_var.cc
        Added code to make the session variable 'binlog_format' 
        read-only inside a transaction.

Hi Omer,
So far we can not find a test case that cause slave 
to go out of sync, when binlog_format is modified 
inside a transaction on 5.1. And the WL#2687 is not  
pushed into 5.1. 
So we don't want to push the patch to 5.1.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94704

3125 Dao-Gang.Qu@sun.com	2009-12-17
      Bug #47863  	binlog_format should be writable only at transaction boundaries
      
      When @@session.binlog_format is modified inside a transaction, 
      it can cause slave to go out of sync.
      
      To fix the problem, make the session variable 'binlog_format' 
      read-only inside a transaction.
     @ mysql-test/suite/rpl/r/rpl_binlog_format.result
        Test result for bug#47863.
     @ mysql-test/suite/rpl/t/rpl_binlog_format.test
        Added the test file to verify if the session variable 'binlog_format' 
        is read-only inside a transaction and in sub-statements.
     @ sql/set_var.cc
        Added code to make the session variable 'binlog_format' 
        read-only inside a transaction.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/94879

3125 Dao-Gang.Qu@sun.com	2009-12-18
      Bug #47863  	binlog_format should be writable only at transaction boundaries
      
      When @@session.binlog_format is modified inside a transaction, 
      it can cause slave to go out of sync.
      
      To fix the problem, make the session variable 'binlog_format' 
      read-only inside a transaction.
     @ mysql-test/suite/binlog/r/binlog_format_switch_inside_trans.result
        Test result for bug#47863.
     @ mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test
        Added test file to verify if the session variable 'binlog_format' 
        is read-only inside a transaction and in sub-statements.
     @ sql/set_var.cc
        Added code to make the session variable 'binlog_format' 
        read-only inside a transaction.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95324

3125 Dao-Gang.Qu@sun.com	2009-12-22
      Bug #47863  	binlog_format should be writable only at transaction boundaries
      
      When @@session.binlog_format is modified inside a transaction, 
      it can cause slave to go out of sync.
      
      To fix the problem, make the session variable 'binlog_format' 
      read-only inside a transaction.
     @ mysql-test/suite/binlog/r/binlog_format_switch_inside_trans.result
        Test result for bug#47863.
     @ mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test
        Added test file to verify if the session variable 'binlog_format' 
        is read-only inside a transaction and in sub-statements.
     @ sql/set_var.cc
        Added code to make the session variable 'binlog_format' 
        read-only inside a transaction.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/95478

3129 Dao-Gang.Qu@sun.com	2009-12-23
      Bug #47863  	binlog_format should be writable only at transaction boundaries
            
      When @@session.binlog_format is modified inside a transaction,
      it can cause slave to go out of sync.
            
      To fix the problem, make the session variable 'binlog_format' 
      read-only inside a transaction.
     @ mysql-test/suite/binlog/r/binlog_format_switch_inside_trans.result
        Test result for bug#47863.
     @ mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test
        Added test file to verify if the session variable 'binlog_format' 
        is read-only inside a transaction and in sub-statements.
     @ sql/set_var.cc
        Added code to make the session variable 'binlog_format'
        read-only inside a transaction.

The patch is pushed to mysql-5.1-rep+3.

Pushed into 6.0.14-alpha (revid:alik@ibmvm-20100118120357-hnzhgadkpzqfnvsc) (version source revid:alik@ibmvm-20100118115413-kd3klpine09yyktw) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100118120111-73dulkgc893it4r9) (version source revid:alik@ibmvm-20100118115335-0stecyzftqm7bqx6) (pib:16)

Documented bugfix in the 5.6.0 and 6.0.14 changelogs as follows:

        The binlog_format system variable can no longer be set inside a 
        transaction. In other words, the binary logging format can no longer
        be changed while a transaction is in progress.

Also noted behaviour change in binlog_format option/variable descriptions in 6.0 Manual.

Set NDI status, waiting for additional merges.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@linux-rbsx-20100118220048-5vnyqi5ghsbgmdsd) (merge vers: 5.5.99-m3) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.

Updated 5.5 binlog_format description.

If investigating issues with this see bug #39016 that introduced in 5.1.29, 6.0.8 and later the restriction that the session setting can only be changed with SUPER privilege, as for the global one.