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
Triage: Triaged: D2 (Serious)

[27 Feb 2009 14:39] Domas Mituzas
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.
[20 May 2009 14:00] 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/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
[25 May 2009 3:54] 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/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
[30 May 2009 13:22] 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/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
[30 May 2009 13:50] 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/75308

2914 He Zhenxing	2009-05-30 [merge]
      Merge BUG#43263 from 5.0-bugteam to 5.1-bugteam
[31 May 2009 0:53] 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/75320

2916 He Zhenxing	2009-05-31
      Post fix test results for BUG#43263
[31 May 2009 0:56] 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/75322

2916 He Zhenxing	2009-05-31
      Post fix test results for BUG#43263
[31 May 2009 3:26] 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/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
[31 May 2009 5:41] 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/75325

2914 He Zhenxing	2009-05-31 [merge]
      Merge BUG#43263 from 5.0-bugteam to 5.1-bugteam
[31 May 2009 5:45] 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/75326

2914 He Zhenxing	2009-05-31 [merge]
      Merge BUG#43263 from 5.0-bugteam to 5.1-bugteam
[31 May 2009 13:17] Zhenxing He
pushed to 5.0-bugteam, 5.1-bugteam and 6.0-bugteam
[16 Jun 2009 8:32] Bugs System
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)
[16 Jun 2009 11:03] Bugs System
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)
[17 Jun 2009 19:22] Bugs System
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)
[25 Jun 2009 10:47] Jon Stephens
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.
[12 Aug 2009 21:53] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 22:47] Paul Dubois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:45] Bugs System
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)
[26 Aug 2009 13:46] Bugs System
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)
[26 Aug 2009 13:48] Bugs System
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)
[27 Aug 2009 16:32] Bugs System
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)
[7 Oct 2009 1:31] Paul Dubois
The 5.4 fix has been pushed into 5.4.2.