Bug #43263 BEGIN skipped in some replicate-do-db cases
Submitted: 27 Feb 15:39 Modified: 25 Jun 12:47
Reporter: Domas Mituzas
Status: Closed
Category:Server: Replication Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any
Assigned to: Zhenxing He Target Version:5.0+
Triage: Triaged: D2 (Serious)

[27 Feb 15: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 16: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 5: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 15: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 15: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 2: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 2: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 5: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 7: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 7: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 15:17] Zhenxing He 
pushed to 5.0-bugteam, 5.1-bugteam and 6.0-bugteam
[16 Jun 10: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 13: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 21: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 12: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 23:53] Paul DuBois 
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 0:47] Paul DuBois 
Ignore previous comment about 5.4.2.
[26 Aug 15: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 15: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 15: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 18: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 3:31] Paul DuBois 
The 5.4 fix has been pushed into 5.4.2.