MySQL Bugs: #34541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode

Bug #34541	mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
Submitted:	14 Feb 2008 11:09	Modified:	1 Feb 2009 12:16
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:	Sven Sandberg	CPU Architecture:	Any
Tags:	autocommit, mysqlbinlog, point in time recovery, statement mode

Description:
When using statement-based logging, and the autocommit mode is changed between two statements, the next Query_log_event will be log the autocommit mode. When the binlog is later processed by mysqlbinlog, a SET statement with no variable will be printed:

SET /*!*/;

The reason is that mysqlbinlog (in log_event.cc, Query_log_event::print_query_header(), where it calls print_set_option()) will detect that flags2 changed, so it will print a 'SET'. Then it will check the bits of flags2 that it knows about, and print a variable to set for each of them. However, it fails to test the OPTION_NOT_AUTOCOMMIT bit, so if that is the only bit that changed, no variable will be printed after 'SET'.

How to repeat:
source include/master-slave.inc
use test;
create table t1 (a int);
set autocommit = 0;
insert into t1 values (1);

Run mysqlbinlog on master-bin.000001: it produces this:
https://intranet.mysql.com/secure/paste/displaypaste.php?codeid=3453
See the line above the 'insert' statement.

Suggested fix:
If we want to replicate the autocommit flag, print it here.
If we don't want to replicate the autocommit flag, remove OPTION_NOT_AUTOCOMMIT from OPTIONS_WRITTEN_TO_BIN_LOG (and update documentation for Query_log_event, description of flags2).

Thank you for the bug report. Verified as described.

I will fix this together with BUG#29288.

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/44773

ChangeSet@1.2552, 2008-04-02 16:49:22+07:00, sven@riska.(none) +7 -0
  BUG#34541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
  Problem: a typo in the code. When autocommit, foreign_key_checks,
  sql_auto_is_null, or unique_checks changes, it prints "SET", and then a
  comma-separated list of assignments. However, it does not print the
  assignment to the @@autocommit variable.
  Fix: print the @@autocommit variable.

pushed to 5.1-new-rpl

I pushed the patch at http://lists.mysql.com/commits/44773, not the one above. It does not contain fixes for BUG#29288. BUG#29288 will be pushed separately, later.

Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32 (revid:luis.soares@sun.com-20090129165946-d6jnnfqfokuzr09y) (version source revid:sp1r-msvensson@pilot.mysql.com-20080403095043-49677) (merge vers: 5.1.25-rc) (pib:6)

Documented bugfix in the 5.1.32 and 6.0.10 changelogs as follows:

        In statement mode, mysqlbinlog failed to issue a SET @@autommit
        statement when AUTOCOMMIT mode was changed.

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090201210519-vehobc4sy3g9s38e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)