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
Tags: autocommit, mysqlbinlog, point in time recovery, statement mode
Triage: Triaged: D3 (Medium)

[14 Feb 2008 11:09] Sven Sandberg
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).
[14 Feb 2008 11:29] Miguel Solorzano
Thank you for the bug report. Verified as described.
[27 Feb 2008 17:59] Sven Sandberg
I will fix this together with BUG#29288.
[2 Apr 2008 9:49] Bugs System
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.
[2 Apr 2008 10:21] Sven Sandberg
pushed to 5.1-new-rpl
[2 Apr 2008 10:24] Sven Sandberg
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.
[30 Jan 2009 13:29] Bugs System
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)
[30 Jan 2009 15:09] Bugs System
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)
[1 Feb 2009 12:16] Jon Stephens
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.
[17 Feb 2009 14:56] Bugs System
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)
[17 Feb 2009 16:43] Bugs System
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)
[17 Feb 2009 18:19] Bugs System
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)