Description:
Binlogging should work as follows:
- If a ROLLBACK transaction contains only transactional statements, then the transaction is not written to the binlog (since the transaction has no effect on the database).
- If a ROLLBACK transaction contains some non-transactional statement, it is written to the binlog (since the transaction may affect the database).
- Some non-transactional statements are not logged:
- In row-based logging mode, statements only affecting temporary tables are not written to the binlog (since temporary tables are not part of the database, and any updates of non-temporary tables are written in row mode, so such updates cannot refer to the temporary table).
- Updates to tables (transactional or non-transactional) in databases skipped with --binlog-ignore-db are never logged.
Problem:
A ROLLBACK transaction that contains only transactional statements, and also non-transactional non-logged statements, is written to the binlog. It would be better if this type of transaction was not logged at all.
This bug is like BUG#36398. However, BUG#36398 will be fixed in 5.1 by updating test cases. This bug is for the real fix, and will be fixed in 6.0.
How to repeat:
Run rpl_ddl.test, or the following test case (for temporary tables):
======== rpl_bug_1.test ========
source include/have_innodb.inc;
source include/have_binlog_format_row.inc;
source include/master-slave.inc;
--echo [master]
CREATE TABLE t1 (a INT) ENGINE=INNODB;
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=INNODB;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
--echo [slave]
sync_slave_with_master;
DROP TABLE t1;
CREATE TABLE t1 (a INT) ENGINE=MYISAM;
SHOW CREATE TABLE t1;
--echo [master]
connection master;
BEGIN;
INSERT INTO t1 VALUES (1);
DROP TEMPORARY TABLE t2;
SELECT * FROM t1;
ROLLBACK;
SELECT * FROM t1;
--echo [slave]
sync_slave_with_master;
SELECT * FROM t1;
======== end rpl_bug_1.test ========
or the following test case (for --binlog-ignore-db):
======== rpl_bug.test ========
source include/master-slave.inc;
source include/have_innodb.inc;
CREATE DATABASE ignored;
CREATE TABLE t1 (a INT) ENGINE=INNODB;
CREATE TABLE ignored.t2 (a INT) ENGINE=MYISAM;
BEGIN;
INSERT INTO t1 VALUES (1);
INSERT INTO ignored.t2 VALUES (2);
INSERT INTO t1 VALUES (3);
ROLLBACK;
SHOW BINLOG EVENTS;
======== end rpl_bug.test ========
======== rpl_bug-master.opt ========
--binlog-ignore-db=ignored
======== end rpl_bug-master.opt ========
Suggested fix:
Currently, ROLLBACK transactions that modify some non-transactional table are written to the binlog. Instead, we should write ROLLBACK transactions that *binlog* a modification to some non-transactional table to the binlog.
We could add a flag thd->transaction.all.binlogged_non_trans_table, which is like
thd->transaction.all.modified_non_trans_table, except it is set only when a
non-transactional table is binlogged, not every time a non-transactional table is
modified. Then we use our new flag to determine if a ROLLBACK transaction should be flushed from the transaction cache to the binlog, or if it should be ignored.