Bug #36574 ROLLBACK trxs with no logged non-trx updates should not be written to binlog

• Submitted: 7 May 2008 18:13
• Modified: 7 May 2008 18:20
• Reporter: Sven Sandberg
• Status: Verified
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.1, 6.0
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: --binlog-ignore-db, OPTION_KEEP_LOG, rollback, temporary table, transaction

[7 May 2008 18:13] Sven Sandberg

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.