| 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: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1, 6.0 | OS: | Any |
| Assigned to: | Alfranio Junior | CPU Architecture: | Any |
| Tags: | binlog, non-transactional table, rollback to savepoint | ||
[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 Junior
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 Junior
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.
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.