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 Correia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Alfranio Correia CPU Architecture:Any
Tags: regression, replication
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[11 Jul 2009 12:44] Alfranio Correia
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;
[11 Jul 2009 15:24] 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/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.
[14 Jul 2009 13:33] 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/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.
[18 Jul 2009 19:38] 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/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.
[18 Jul 2009 20:08] 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/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.
[4 Aug 2009 19:50] Bugs System
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)
[4 Aug 2009 20:45] Bugs System
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)
[5 Aug 2009 13:21] Jon Stephens
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.
[12 Aug 2009 21:57] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 22:50] Paul Dubois
Ignore previous comment about 5.4.2.
[1 Oct 2009 5:58] Bugs System
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)
[1 Oct 2009 7:25] Bugs System
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)
[1 Oct 2009 13:25] Bugs System
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)
[5 Oct 2009 10:50] Bugs System
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)
[7 Oct 2009 1:46] Paul Dubois
The 5.4 fix has been pushed into 5.4.2.