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

[29 Apr 2008 13:44] 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).
 - 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.
[3 May 2008 12:48] 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/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.
[3 May 2008 13:17] Sven Sandberg
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
[3 May 2008 13:27] Sven Sandberg
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 ----
[3 May 2008 13:31] Sven Sandberg
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.
[6 May 2008 8:53] 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/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.
[7 May 2008 11:00] 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/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.
[14 May 2008 15:20] Bugs System
Pushed into 5.1.25-rc
[15 May 2008 17:24] Paul DuBois
Test case change. No changelog entry needed.
[22 May 2008 9:49] Bugs System
Pushed into 6.0.6-alpha

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.071 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.