MySQL Bugs: #43263: BEGIN skipped in some replicate-do-db cases

Bug #43263	BEGIN skipped in some replicate-do-db cases
Submitted:	27 Feb 2009 14:39	Modified:	25 Jun 2009 10:47
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:	Zhenxing He	CPU Architecture:	Any

Description:
MySQL sometimes logs "BEGIN" before "USE" for some stored procedure executions, so replicate-do-db ignores BEGIN, and executes transaction with each statement in auto-commit mode.

example binlog:

#090227 16:27:36 server id 555  end_log_pos 928 	Query	thread_id=2	exec_time=0	error_code=0
use test/*!*/;
SET TIMESTAMP=1235744856/*!*/;
BEGIN
/*!*/;
# at 928
#090227 16:27:35 server id 555  end_log_pos 1019 	Query	thread_id=2	exec_time=0	error_code=0
use testdb2/*!*/;
SET TIMESTAMP=1235744855/*!*/;
insert into t1 values (1)
/*!*/;
# at 1019
#090227 16:27:35 server id 555  end_log_pos 1110 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1235744855/*!*/;
insert into t1 values (2)
/*!*/;
# at 1110
#090227 16:27:36 server id 555  end_log_pos 1137 	Xid = 32
COMMIT/*!*/;
DELIMITER ;
# End of log file

How to repeat:
Verified with both 5.0 and 5.1

create database testdb2;
create table testdb2.t1 (a int) ENGINE=InnoDB;
delimiter ;;
create procedure testdb2.p1 () begin insert into t1 values (1); insert into t1 values (2); end;;
delimiter ;

call testdb2.t1();

(and replicate with --replicate-do-db=testdb2)

Suggested fix:
1. write BEGIN and COMMIT for all events where transaction has been started, doesn't matter in which database. 
2. execute BEGINs and COMMITs for all events in binlog, doesn't matter if any filtering is on or off.

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

2741 He Zhenxing	2009-05-20
      BUG#43263 BEGIN skipped in some replicate-do-db cases
      
      BEGIN/COMMIT/ROLLBACK was subject to replication db rules, and
      caused the boundary of a transaction not recognized correctly 
      when these queries were ignored by the rules.
      
      Fixed the problem by skipping replication db rules for these
      statements.
     @ sql/log_event.cc
        Skip checking replication db rules for BEGIN/COMMIT/ROLLBACK 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/74863

2741 He Zhenxing	2009-05-25
      BUG#43263 BEGIN skipped in some replicate-do-db cases
      
      BEGIN/COMMIT/ROLLBACK was subject to replication db rules, and
      caused the boundary of a transaction not recognized correctly 
      when these queries were ignored by the rules.
      
      Fixed the problem by skipping replication db rules for these
      statements.
     @ sql/log_event.cc
        Skip checking replication db rules for BEGIN/COMMIT/ROLLBACK 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/75305

2759 He Zhenxing	2009-05-30
      BUG#43263 BEGIN skipped in some replicate-do-db cases
      
      BEGIN/COMMIT/ROLLBACK was subject to replication db rules, and
      caused the boundary of a transaction not recognized correctly 
      when these queries were ignored by the rules.
      
      Fixed the problem by skipping replication db rules for these
      statements.
     @ sql/log_event.cc
        Skip checking replication db rules for BEGIN/COMMIT/ROLLBACK 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/75308

2914 He Zhenxing	2009-05-30 [merge]
      Merge BUG#43263 from 5.0-bugteam to 5.1-bugteam

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

2916 He Zhenxing	2009-05-31
      Post fix test results for BUG#43263

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

2916 He Zhenxing	2009-05-31
      Post fix test results for BUG#43263

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

2759 He Zhenxing	2009-05-31
      BUG#43263 BEGIN skipped in some replicate-do-db cases
      
      BEGIN/COMMIT/ROLLBACK was subject to replication db rules, and
      caused the boundary of a transaction not recognized correctly 
      when these queries were ignored by the rules.
      
      Fixed the problem by skipping replication db rules for these
      statements.
     @ sql/log_event.cc
        Skip checking replication db rules for BEGIN/COMMIT/ROLLBACK 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/75325

2914 He Zhenxing	2009-05-31 [merge]
      Merge BUG#43263 from 5.0-bugteam to 5.1-bugteam

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

2914 He Zhenxing	2009-05-31 [merge]
      Merge BUG#43263 from 5.0-bugteam to 5.1-bugteam

pushed to 5.0-bugteam, 5.1-bugteam and 6.0-bugteam

Pushed into 5.0.84 (revid:gkodinov@mysql.com-20090616082753-kwe0l8uoictxhojf) (version source revid:zhenxing.he@sun.com-20090531032658-tixdqryqps8w0vfi) (merge vers: 5.0.83) (pib:6)

Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:zhenxing.he@sun.com-20090531054441-xmb9dzqjicq96tsq) (merge vers: 5.1.36) (pib:6)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:zhenxing.he@sun.com-20090531091844-5i8f683ee7r4k1mm) (merge vers: 6.0.12-alpha) (pib:11)

Documented bugfix in the 5.0.84, 5.1.36, and 5.4.4 changelogs as follows:

        BEGIN, COMMIT, and ROLLBACK statements are no longer affected 
        by --replicate-do-db or --replicate-ignore-db rules.

Updated descriptions of affected statements/options in Manual.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

The 5.4 fix has been pushed into 5.4.2.