MySQL Bugs: #46998: mysqlbinlog can't output BEGIN even if the database is included in a transaction

Bug #46998	mysqlbinlog can't output BEGIN even if the database is included in a transaction
Submitted:	30 Aug 2009 6:46	Modified:	19 Dec 2009 11:10
Reporter:	Daogang Qu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0+	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any

Description:
../client/mysqlbinlog --database=test var/log/master-bin.000001

can't output BEGIN in the binary log even if the database is included in a transaction in the binary log.

How to repeat:
#
# This test case to verify if the transaction is complete for 'test' database.
#
-- source include/have_log_bin.inc
-- source include/have_innodb.inc

set @ac = @@autocommit;
set autocommit= 0;
reset master;

use test;
create table test.t1(n int, bar int auto_increment, unique key (bar)) engine=innodb;
use mysql;
create table t2(m int, bar1 int auto_increment, unique key (bar1)) engine=innodb;
begin;
use test;
insert into t1 (n, bar) values (1, null);

use mysql;
insert into t2 (m) values (1);
commit;

drop table test.t1;
drop table mysql.t2;
set session autocommit = @ac;

../client/mysqlbinlog --database=test var/log/master-bin.000001

Produces this filtered binlog output:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090830 14:25:35 server id 1  end_log_pos 98 	Start: binlog v 4, server v 5.0.85-debug-log created 090830 14:25:35 at startup
ROLLBACK/*!*/;
# at 98
#090830 14:25:35 server id 1  end_log_pos 244 	Query	thread_id=1	exec_time=0	error_code=0
use test/*!*/;
SET TIMESTAMP=1251613535/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
create table test.t1(n int, bar int auto_increment, unique key (bar)) engine=innodb
/*!*/;
# at 244
# at 388
# at 457
# at 485
#090830 14:25:36 server id 1  end_log_pos 588 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1251613536/*!*/;
insert into t1 (n, bar) values (1, null)
/*!*/;
# at 588
#090830 14:25:36 server id 1  end_log_pos 616 	Intvar
SET INSERT_ID=1/*!*/;
# at 616
# at 709
#090830 14:25:36 server id 1  end_log_pos 736 	Xid = 12
COMMIT/*!*/;
# at 736
# at 818
# at 901
#090830 14:25:36 server id 1  end_log_pos 920 	Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

The mysqlbinlog should output BEGIN for building a complete 
transaction in this case. 

Suggested fix:
The BUG#43263 has resolved the problem in SQL thread.
Follow the way of BUG#43263 to resolve the problem in binary log.

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

2812 Dao-Gang.Qu@sun.com	2009-09-18
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN' just is output with associated database, so mysqlbinlog can't output BEGIN 
      with unassociated database even if the database is included in a transaction.
      
      To fix the problem, mysqlbinlog will output 'BEGIN' for the database included in transaction.
     @ client/mysqlbinlog.cc
        Added a judgement condition, so that mysqlbinlog will output 'BEGIN' 
        for the database included in transaction.
     @ mysql-test/r/binlog_transaction_complete.result
        Test result for bug#46998
     @ mysql-test/t/binlog_transaction_complete.test
        Added test to verify if the "BEGIN" is output for the database included 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/83880

2812 Dao-Gang.Qu@sun.com	2009-09-21
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN' just is output with associated database, so mysqlbinlog can't output 'BEGIN' 
      with unassociated database even if the database is included in transaction.
      Actually mysqlbinlog can't output 'COMMIT' and 'ROLLBACK' with unassociated database 
      included in transaction for non-transactional tables, although it can output them for 
      transactional tables.
      
      To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' for the database 
      included in transaction in regardless of the database filtering rules.
     @ client/mysqlbinlog.cc
        Added a judgement condition, so that mysqlbinlog will output 'BEGIN' 
        for the database included in transaction.
     @ mysql-test/r/binlog_transaction_complete.result
        Test result for bug#46998
     @ mysql-test/t/binlog_transaction_complete.test
        Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output 
        for the database included 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/84322

2812 Dao-Gang.Qu@sun.com	2009-09-23
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN/COMMIT/ROLLBACK' log event could be filtered out if the
      database is not selected by --database option of mysqlbinlog command.
      This can result in problem if there are some statements in the
      transaction are not filtered out.
      
      To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' 
      in regardless of the database filtering rules.
     @ client/mysqlbinlog.cc
        Skip the database check for BEGIN/COMMIT/ROLLBACK log events.
     @ mysql-test/r/mysqlbinlog.result
        Test result for bug#46998
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output
        in regardless of database filtering

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

2812 Dao-Gang.Qu@sun.com	2009-09-24
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN/COMMIT/ROLLBACK' log event could be filtered out if the
      database is not selected by --database option of mysqlbinlog command.
      This can result in problem if there are some statements in the
      transaction are not filtered out.
      
      To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' 
      in regardless of the database filtering rules.
     @ client/mysqlbinlog.cc
        Skip the database check for BEGIN/COMMIT/ROLLBACK log events.
     @ mysql-test/r/mysqlbinlog.result
        Test result for bug#46998
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output
        in regardless of database filtering

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

2812 Dao-Gang.Qu@sun.com	2009-09-24
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN/COMMIT/ROLLBACK' log event could be filtered out if the
      database is not selected by --database option of mysqlbinlog command.
      This can result in problem if there are some statements in the
      transaction are not filtered out.
      
      To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' 
      in regardless of the database filtering rules.
     @ client/mysqlbinlog.cc
        Skip the database check for BEGIN/COMMIT/ROLLBACK log events.
     @ mysql-test/r/mysqlbinlog.result
        Test result for bug#46998
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output
        in regardless of database filtering

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

2811 Dao-Gang.Qu@sun.com	2009-09-30
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN/COMMIT/ROLLBACK' log event could be filtered out if the
      database is not selected by --database option of mysqlbinlog command.
      This can result in problem if there are some statements in the
      transaction are not filtered out.
      
      To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' 
      in regardless of the database filtering rules.
     @ client/mysqlbinlog.cc
        Skip the database check for BEGIN/COMMIT/ROLLBACK log events.
     @ mysql-test/r/mysqlbinlog.result
        Test result for bug#46998
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output
        in regardless of database filtering

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

3128 Dao-Gang.Qu@sun.com	2009-09-30 [merge]
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN/COMMIT/ROLLBACK' log event could be filtered out if the
      database is not selected by --database option of mysqlbinlog command.
      This can result in problem if there are some statements in the
      transaction are not filtered out.
      
      To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' 
      in regardless of the database filtering rules.
     @ client/mysqlbinlog.cc
        Skip the database check for BEGIN/COMMIT/ROLLBACK log events.
     @ mysql-test/r/mysqlbinlog.result
        Test result for bug#46998
     @ mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test
        The test case is updated duo to the patch of bug#46998
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output
        in regardless of database filtering

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

3613 Dao-Gang.Qu@sun.com	2009-09-30 [merge]
      Bug #46998  mysqlbinlog can't output BEGIN even if the database is included in a transaction
      
      The 'BEGIN/COMMIT/ROLLBACK' log event could be filtered out if the
      database is not selected by --database option of mysqlbinlog command.
      This can result in problem if there are some statements in the
      transaction are not filtered out.
      
      To fix the problem, mysqlbinlog will output 'BEGIN/ROLLBACK/COMMIT' 
      in regardless of the database filtering rules.
     @ client/mysqlbinlog.cc
        Skip the database check for BEGIN/COMMIT/ROLLBACK log events.
     @ mysql-test/r/mysqlbinlog.result
        Test result for bug#46998
     @ mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test
        The test case is updated due to the patch of bug#46998
     @ mysql-test/t/mysqlbinlog.test
        Added test to verify if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output
        in regardless of database filtering

The patch is pushed to mysql-5.0-bugteam, mysql-5.1-bugteam and mysql-pe.

Pushed into 5.0.87 (revid:joro@sun.com-20091006073202-rj21ggvo2gw032ks) (version source revid:dao-gang.qu@sun.com-20090930020152-h84oo3saq4fl2m15) (merge vers: 5.0.87) (pib:11)

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20091002112748-2xmjv846dk323nc3) (merge vers: 5.1.40) (pib:11)

Documented bugfix in the 5.0.87 and 5.1.40 changelogs as follows:

      BEGIN statements were not included in the output of mysqlbinlog.

Set status = NDI, waiting for 5.4 push.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)

Also documented bugfix in the 5.5.0 and 6.0.14 changelogs.

Closed.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

No additional changelog entries needed. Setting back to Closed state.