MySQL Bugs: #46129: Failing mixed stm (with trans and non-trans tables) causes wrong seq in binlog

Bug #46129	Failing mixed stm (with trans and non-trans tables) causes wrong seq in binlog
Submitted:	11 Jul 2009 12:44	Modified:	5 Aug 2009 13:21
Reporter:	Alfranio Junior	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Alfranio Junior	CPU Architecture:	Any
Tags:	regression, replication

Description:
After BUG#43929, a failing non-transactional statement issued in the context of a transaction flushes the transactional cache causing a mismatch between execution and logging histories.

Execution:

CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;

CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW
BEGIN
  INSERT INTO nt_1 VALUES (NEW.a, NEW.b, NEW.c);
END

BEGIN;
INSERT INTO nt_1 VALUES ("new text 2", 2, '');
--error ER_DUP_ENTRY
INSERT INTO tt_1 VALUES ("new text 1", 1, ''), ("new text 2", 2, '');
INSERT INTO tt_1 VALUES ("new text 3", 3, '');
ROLLBACK;

Wrong binary log entries:

Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       #       Query   #       #       use `test`; INSERT INTO nt_1 VALUES ("new text 2", 2, '')
master-bin.000001       #       Query   #       #       BEGIN
master-bin.000001       #       Query   #       #       use `test`; INSERT INTO tt_1 VALUES ("new text 1", 1, ''), ("new text 2", 2, '')
master-bin.000001       #       Query   #       #       ROLLBACK
master-bin.000001       #       Query   #       #       BEGIN
master-bin.000001       #       Query   #       #       use `test`; INSERT INTO tt_1 VALUES ("new text 3", 3, '')
master-bin.000001       #       Query   #       #       ROLLBACK

Correct binary log entries:

Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       #       Query   #       #       use `test`; INSERT INTO nt_1 VALUES ("new text 2", 2, '')
master-bin.000001       #       Query   #       #       BEGIN
master-bin.000001       #       Query   #       #       use `test`; INSERT INTO tt_1 VALUES ("new text 1", 1, ''), ("new text 2", 2, '')
master-bin.000001       #       Query   #       #       use `test`; INSERT INTO tt_1 VALUES ("new text 3", 3, '')
master-bin.000001       #       Query   #       #       ROLLBACK

How to repeat:
connection master;

SET SQL_LOG_BIN=0;
CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
SET SQL_LOG_BIN=1;

connection slave;

SET SQL_LOG_BIN=0;
CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
SET SQL_LOG_BIN=1;

connection master;

DELIMITER |;

CREATE TRIGGER tr_i_tt_1_to_nt_1 BEFORE INSERT ON tt_1 FOR EACH ROW
BEGIN
  INSERT INTO nt_1 VALUES (NEW.a, NEW.b, NEW.c);
END|

DELIMITER ;|

let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1);

BEGIN;
INSERT INTO nt_1 VALUES ("new text 2", 2, '');
--error ER_DUP_ENTRY
INSERT INTO tt_1 VALUES ("new text 1", 1, ''), ("new text 2", 2, '');
INSERT INTO tt_1 VALUES ("new text 3", 3, '');
ROLLBACK;

--source include/show_binlog_events.inc

connection master;
DROP TABLE tt_1;
DROP TABLE nt_1;

sync_slave_with_master;

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

3017 Alfranio Correia	2009-07-11
      BUG#46129 Failing mixed stm (with trans and non-trans tables) causes wrong seq in binlog
      
      The fix for BUG#43929 introduced a regression issue. In a nutshell, when a
      statement that changes a non-transactional table fails, it is written to the
      binary log with the error code appended. Unfortunately, after BUG#43929, this
      failure was flushing the transactional chace causing mismatch between execution
      and logging histories. To fix this issue, we avoid flushing the transactional
      cache when a commit or rollback is not issued.

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

3017 Alfranio Correia	2009-07-14
      BUG#46129 Failing mixed stm (with trans and non-trans tables) causes wrong seq in binlog
      
      The fix for BUG#43929 introduced a regression issue. In a nutshell, when a
      statement that changes a non-transactional table fails, it is written to the
      binary log with the error code appended. Unfortunately, after BUG#43929, this
      failure was flushing the transactional chace causing mismatch between execution
      and logging histories. To fix this issue, we avoid flushing the transactional
      cache when a commit or rollback is not issued.

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

3032 Alfranio Correia	2009-07-18
      BUG#46129 Failing mixed stm (with trans and non-trans tables) causes wrong seq in binlog
      
      The fix for BUG#43929 introduced a regression issue. In a nutshell, when a
      statement that changes a non-transactional table fails, it is written to the
      binary log with the error code appended. Unfortunately, after BUG#43929, this
      failure was flushing the transactional chace causing mismatch between execution
      and logging histories. To fix this issue, we avoid flushing the transactional
      cache when a commit or rollback is not issued.

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

3028 Alfranio Correia	2009-07-18
      BUG#46129 Failing mixed stm (with trans and non-trans tables) causes wrong seq in
      binlog
            
      The fix for BUG 43929 introduced a regression issue. In a nutshell, when a
      statement that changes a non-transactional table fails, it is written to the
      binary log with the error code appended. Unfortunately, after BUG 43929, this
      failure was flushing the transactional chace causing mismatch between execution
      and logging histories. To fix this issue, we avoid flushing the transactional
      cache when a commit or rollback is not issued.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:dao-gang.qu@sun.com-20090720051125-c0ujd4mg40k82sq0) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Documented in the 5.1.38 and 5.4.4 changelogs as follows:

      When a statement that changes a non-transactional table failed, the
      transactional cache was flushed, causing a mismatch between the execution
      and logging histories. Now we avoid flushing the transactional cache
      unless a COMMIT or ROLLBACK is issued.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

The 5.4 fix has been pushed into 5.4.2.