| Bug #34600 | Rolled-back punch transactions not replicated correctly | ||
|---|---|---|---|
| Submitted: | 15 Feb 2008 16:27 | Modified: | 11 Nov 2009 19:10 |
| Reporter: | Sven Sandberg | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
| Tags: | mixed engines, punch transaction, replication | ||
[15 Feb 2008 17:24]
Susanne Ebrecht
Verified as described.
[11 Nov 2009 19:09]
Alfranio Tavares Correia Junior
The problem reported in BUG#34600 does not happen in 5.1.41 after BUG#47287. Let's consider the same example reported by [15 Feb 2008 17:27] Sven Sandberg: BEGIN; modify table_1 modify table_2 ROLLBACK; The binary log would have what follows for both ROW and STATEMENT: modify table_1 BEGIN; modify table_2 ROLLBACK; However, the following example is still not handled correctly in 5.1.41: BEGIN; modify table_2 modify table_1 ROLLBACK; The binary log would have what follows for both ROW and STATEMENT: BEGIN; modify table_2 modify table_1 ROLLBACK; The sequence of events in the binary log is wrong as the locks on the table_1 (my_isam) are released as soon as the statement is executed. This last case is only fixed in rep+3 after WL#2687.
[11 Nov 2009 19:10]
Alfranio Tavares Correia Junior
Closing as can't repeat due to the explanation above.
Description: Assume: - table_1 is non-transactional on master and transactional on slave. - table_2 is transactional on both master and slave. The execute: BEGIN; modify table_1 modify table_2 ROLLBACK; Result: table_1 will be modified on master but not on slave. Explanation: non-transactional modifications take place even if there is a rollback later (such modifications are called punch transactions). Hence, table_1 will be modified on master. However, since table_1 is transactional on slave, it will rollback gracefully, so table_1 will not be modified on slave. (To reproduce this, it is necessary to have the transactional table_2 in the transaction, since transactions that involve only non-transactional tables are handled specially by the logging code.) How to repeat: $ cat t/rpl_bug.test --source include/master-slave.inc --source include/have_innodb.inc connection master; create table tinnodb (a int) engine = innodb; create table tmyisam_innodb (a int) engine = myisam; sync_slave_with_master; drop table tmyisam_innodb; create table tmyisam_innodb (a int) engine = innodb; connection master; begin; insert into tinnodb values (1); insert into tmyisam_innodb values (1); rollback; select * from tmyisam_innodb; sync_slave_with_master; select * from tmyisam_innodb; $ cat t/rpl_bug-master.opt --innodb $ cat t/rpl_bug-slave.opt --innodb Suggested fix: 1. Introduce a way mark a modification as being non-transactional in the binlog (a bit in the flag field of row events?). 2. Make slave take that bit into account on rollback. Note: even if this bug may have little impact (the setup is strange), I think it is good from a design point of view to at least implement (1) in row-based mode, because all modifications should be explicit in the binlog. See also BUG#29288.