MySQL Bugs: #36398: Test case failiure: rpl

Bug #36398	Test case failiure: rpl_ddl.test
Submitted:	29 Apr 2008 13:44	Modified:	15 May 2008 17:24
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Sven Sandberg	CPU Architecture:	Any
Tags:	OPTION_KEEP_LOG, rollback, temporary table, transaction

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).
 - 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).

Problem:
A ROLLBACK transaction that contains only transactional statements, and also DDL updates to a temporary table, is written to the binlog in row-based logging mode. Since the temporary table updates are not written to the binlog, the binlog will contain a ROLLBACK transaction with only transactional statements. It would be better if this type of transaction was not logged at all.

How to repeat:
Run rpl_ddl.test, or the following minimal test case:

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;

Suggested fix:
Transactions containing only statements that affect tables that are transactional or temporary should not be written to the binlog if the transaction ends with ROLLBACK and row-based logging mode is in effect.

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/46326

ChangeSet@1.2564, 2008-05-03 14:49:50+02:00, sven@riska.(none) +2 -0
  BUG#36398: 'DROP TEMPORARY TABLE' in ROLLBACK transaction causes transaction to be logged
  BUG#26418: Slave out of sync after CREATE/DROP TEMPORARY TABLE + ROLLBACK on master
  The rpl_ddl test case was disabled by BUG#26418. Now, BUG#26418 is closed,
  but the test instead fails because of BUG#36398. Updated test result file
  and disabled.def accordingly.

the commit above does not fix the bug. it only makes rpl_ddl.test disabled by
this bug instead of by BUG#26418. it also updates rpl_ddl.result. the commit
has been pushed to 5.1-new-rpl

The same problem occurs for transactions that modify only transactional tables and non-transactional tables that are ignored by the --binlog-ignore-db option. See the following test case:

---- 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 ----

Proposed fix: 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.

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/46377

ChangeSet@1.2563, 2008-05-06 10:55:01+02:00, sven@riska.(none) +2 -0
  BUG#36398: 'DROP TEMPORARY TABLE' in ROLLBACK transaction causes transaction to be logged
  Temporarily checking in an incorrect test case. Rationale: the impact of
  this bug is negligible (it's almost a feature request). We need 5.1 to be
  stable, and making a real fix is a bit risky. So the fix is postponed
  to 6.0.

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/46448

ChangeSet@1.2563, 2008-05-07 13:02:06+02:00, sven@riska.(none) +2 -0
  BUG#36398: 'DROP TEMPORARY TABLE' in ROLLBACK transaction causes transaction to be logged
  Temporarily checking in an incorrect test case. Rationale: the impact of
  this bug is negligible (it's almost a feature request). We need 5.1 to be
  stable, and making a real fix is a bit risky. So the fix is postponed
  to 6.0.

Pushed into 5.1.25-rc

Test case change. No changelog entry needed.

Pushed into 6.0.6-alpha