Bug #47327 ROLLBACK TO SAVEPOINT binlogged if trx contains non-trx updates before savepoint
Submitted: 15 Sep 2009 13:21 Modified: 20 Jan 2010 0:16
Reporter: Sven Sandberg Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1, 6.0 OS:Any
Assigned to: Alfranio Correia CPU Architecture:Any
Tags: binlog, non-transactional table, rollback to savepoint
Triage: Triaged: D5 (Feature request)

[15 Sep 2009 13:21] Sven Sandberg
Description:
When a ROLLBACK is issued, and the transaction cache does not contain any updates to non-transactional tables, then the transaction cache is cleared and not written to the binlog. If the transaction contains updates to non-transactional tables, then the transaction cache is written to the binlog, followed by a ROLLBACK. This is all good.

When a ROLLBACK TO SAVEPOINT is issued, and the transaction cache does not contain updates to non-transactional tables, then the transaction cache is cleared from the savepoint to the end. However, if the transaction cache contains updates to non-transactional tables, then the transaction cache is not cleared, even if the non-transactional updates are outside the scope of the savepoint.

It would be better if the transaction cache was only cleared when there is a non-transactional update in the transaction cache *after* the last savepoint.

How to repeat:
--source include/have_binlog_format_statement.inc
--source include/have_innodb.inc

create table t0 (a int) engine=myisam;
create table t2 (a int) engine=innodb;

show create table t0;
show create table t2;

begin;
  insert into t2 values (1);
  insert into t0 values (1);
  savepoint s1;
    insert into t2 values (1);
  rollback to s1;
commit;
# then run mysqlbinlog on master-bin.000001

Suggested fix:
This should be simple to fix after BUG#47326 is fixed.
[4 Oct 2009 11:28] 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/85676

3519 Alfranio Correia	2009-10-04
      BUG#47327 ROLLBACK TO SAVEPOINT binlogged if trx contains non-trx updates before savepoint
      
      Draft of a patch based on the WL#2687.
[3 Nov 2009 19:20] 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/89214

3117 Alfranio Correia	2009-11-03
      BUG#47327 ROLLBACK TO SAVEPOINT binlogged if trx contains non-trx updates before savepoint
      
      When a ROLLBACK is issued, and there is no updates to non-transactional tables,
      then the transaction cache is cleared and not written to the binlog. However,
      if the transaction contains updates to non-transactional tables, then the
      transaction cache is written to the binlog, followed by a ROLLBACK.
      However, this happens even when the non-transactional changes were written to
      the binlog through the non-transaction cache and as such the transaction cache
      does not contain non-transactional.
      
      When a ROLLBACK TO SAVEPOINT is issued, and the transaction cache does not
      contain updates to non-transactional tables, then the transaction cache is
      cleared from the savepoint to the end. However, if the transaction cache
      contains updates to non-transactional tables, then the transaction cache is not
      cleared, even if the non-transactional updates are outside the scope of the
      savepoint.
      
      To fix the problem, we keep the track of the content of the transaction cache,
      in order to truncate it and avoid writing unnecessary data to the binary log
      when it does not have updates to non-transactional.
[18 Dec 2009 0:08] 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/94873

3127 Alfranio Correia	2009-12-18
      BUG#47327 ROLLBACK TO SAVEPOINT binlogged if trx contains non-trx updates before savepoint
      
      In this patch, we fix two problems described as follows.
      
      1. MIXED/RBR: T-changes are not truncated even though they could be.
      They are written to the transactional cache and binlogging is deferred
      until transaction COMMIT/ROLLBACK. On the other hand, N-changes are
      written to the non-transactional cache and binlogged upon statement
      COMMIT/ROLLBACK. Therefore, we can clear the transactional cache without
      affecting N-changes logging if a ROLLBACK is issued.
      
      2. SBR: T-changes and N-changes are written to the transactional
      cache and are both deferred until transaction COMMIT/ROLLBACK. However,
      if a SAVEPOINT is set and there are only T-changes *after* the savepoint
      and a ROLLBACK to the savepoint is issued, then the T-changes are not
      removed from cache back until the savepoint.
      
      To fix the problem, we keep the track of the content of the transactional
      cache, in order to truncate it and avoid writing unnecessary data to the
      binary log when it does not have updates to non-transactional.
[18 Dec 2009 1: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/94875

3127 Alfranio Correia	2009-12-18
      BUG#47327 ROLLBACK TO SAVEPOINT binlogged if trx contains non-trx updates before savepoint
      
      In this patch, we fix two problems described as follows.
      
      1. MIXED/RBR: T-changes are not truncated even though they could be.
      They are written to the transactional cache and binlogging is deferred
      until transaction COMMIT/ROLLBACK. On the other hand, N-changes are
      written to the non-transactional cache and binlogged upon statement
      COMMIT/ROLLBACK. Therefore, we can clear the transactional cache without
      affecting N-changes logging if a ROLLBACK is issued.
      
      2. SBR: T-changes and N-changes are written to the transactional
      cache and are both deferred until transaction COMMIT/ROLLBACK. However,
      if a SAVEPOINT is set and there are only T-changes *after* the savepoint
      and a ROLLBACK to the savepoint is issued, then the T-changes are not
      removed from cache back until the savepoint.
      
      To fix the problem, we keep the track of the content of the transactional
      cache, in order to truncate it and avoid writing unnecessary data to the
      binary log when it does not have updates to non-transactional.
[18 Dec 2009 2:09] 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/94876

3127 Alfranio Correia	2009-12-18
      BUG#47327 ROLLBACK TO SAVEPOINT binlogged if trx contains non-trx updates before savepoint
      
      In this patch, we fix two problems described as follows.
      
      1. MIXED/RBR: T-changes are not truncated even though they could be.
      They are written to the transactional cache and binlogging is deferred
      until transaction COMMIT/ROLLBACK. On the other hand, N-changes are
      written to the non-transactional cache and binlogged upon statement
      COMMIT/ROLLBACK. Therefore, we can clear the transactional cache without
      affecting N-changes logging if a ROLLBACK is issued.
      
      2. SBR: T-changes and N-changes are written to the transactional
      cache and are both deferred until transaction COMMIT/ROLLBACK. However,
      if a SAVEPOINT is set and there are only T-changes *after* the savepoint
      and a ROLLBACK to the savepoint is issued, then the T-changes are not
      removed from cache back until the savepoint.
      
      To fix the problem, we keep the track of the content of the transactional
      cache, in order to truncate it and avoid writing unnecessary data to the
      binary log when it does not have updates to non-transactional.
[20 Jan 2010 0:04] Alfranio Correia
Summary:
========

Note that this bug is about optimization and there is no problem with correctness.
In particular, there are two optimizations that could be done:

1 - Avoid writing to the binary log rolled back savepoints that does not have
nt-changes:

BEGIN;
   CHANGE nt-table;
   SAVE POINT s1;
   CHANGE tt-table;
   ROLLBACK TO s1;
COMMIT;

Currently, in all modes, a warning is printed out and the t-changes are written to
the binary log although that is not really necessary, because we do not track exactly
where the nt-changes where stored in the cache.

2 - Avoid writing to the binary log rolled back transactions in mixed and row modes.

BEGIN;
   CHANGE tt-table;
   CHANGE nt-table;
ROLLBACK;

Currently, in both mixed and row modes, the t-changes are written to the binary
log although that is not really necessary because the nt-changes are written to
the binary log through the nt-cache and the t-cache could just be truncated.

Sketch of the binary log for both cases.

1 -

nt-cache      --->

nt-cache:     --->      begin;
   nt-rows                nt-rows;
                        commit;
t-cache:      --->      begin;
   t-rows                 save point s1;
                          t-rows;
                          rollback to s1;
                        rollback;

2 -

nt-cache:      --->      begin;
   nt-rows                 nt-rows;
                         commit;
t-cache:       --->      begin;
   t-rows                  t-rows;
                         rollback;

Solutions
=========

1 - The fix for the second case is straightforward and could be easily
implemented. However, to fix the first case, we need to maintain
a list of savepoints and merge information from inner-points to
outer-points when they commit/rollback.

2 - To avoid any fancy solution, we could provide a simple and sub-optimal
solution that would fix the second case and partially the first case.
[20 Jan 2010 0:16] Alfranio Correia
After discussing this bug with Andrei and Luis, we have reached an agreement
that as correctness is preserved and the bug request two optimizations,
fixing it would introduce unnecessary complexity in the code, and for that
reason, it should be closed as "will not fix". However, if in the future any
client requested such optimizations, we would provide the first solution.