Bug #46130 Slave does not correctly handle "expected errors"
Submitted: 11 Jul 2009 13:34 Modified: 16 Sep 2009 9:59
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
Tags: replication, slave
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[11 Jul 2009 13:34] Alfranio Correia
Description:
Slave does not correctly handle "expected errors" leading to inconsistencies between the mater and slave.

Specifically, when a statement changes both transactional and non-transactional tables, the transactional changes are automatically rolled back on the master but the slave ignores the error and does not rollback them thus leading to inconsistencies.

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, '');
COMMIT;

ON MASTER:

SELECT * FROM tt_1;
a       b       c
new text 3      3

SELECT * FROM nt_1;
a       b       c
new text 2      2
new text 1      1
new text 3      3

ON SLAVE:

SELECT * FROM tt_1;
a       b       c
new text 1      1
new text 3      3

SELECT * FROM nt_1;
a       b       c
new text 2      2
new text 1      1
new text 3      3

How to repeat:
Apply the patch for BUG#33864 and use the test case reported there.
[11 Jul 2009 14:58] Alfranio Correia
Suggested fix:

Always roll back "a statement" that is written to the binary log with an "expected error code". This must be done to avoid persisting the transactional
changes and keep just the non-transactional changes.
[11 Jul 2009 15:27] Alfranio Correia
Correction: 

Apply the patch and run the test case in BUG#46129.
[4 Aug 2009 13: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/80032

3053 Alfranio Correia	2009-08-04
      BUG#46130 Slave does not correctly handle "expected errors"
      
      Slave does not correctly handle "expected errors" leading to inconsistencies
      between the mater and slave. Specifically, when a statement changes both
      transactional and non-transactional tables, the transactional changes are
      automatically rolled back on the master but the slave ignores the error and
      does not rollback them thus leading to inconsistencies.
      
      To fix the problem, we automatically rollback a statement that fails on
      the slave but note that the transaction is not rolled back unless a "rollback"
      command is in the relay log file.
[13 Aug 2009 16:21] 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/80769

3053 Alfranio Correia	2009-08-13
      BUG#46130 Slave does not correctly handle "expected errors"
      
      In STATEMENT based replication, a statement that failed on the master but that
      updated non-transactional tables is written to binary log with the error code
      appended to it. On the slave, the statement is executed and the same error is
      expected. However, when an "expected error" did not happen on the slave and was
      either ignored or was related to a concurrency issue on the master, the slave
      did not rollback the effects of the statement and as such inconsistencies might
      happen.
      
      To fix the problem, we automatically rollback a statement that should have
      failed on a slave but succeded and whose expected failure is either ignored or
      stems from a concurrency issue on the master.
[24 Aug 2009 9:32] Alfranio Correia
Pushed to mysql-5.1-bugteam.
[2 Sep 2009 16:42] Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:alfranio.correia@sun.com-20090824092452-97y32g7pmd312w90) (merge vers: 5.1.39) (pib:11)
[9 Sep 2009 9:14] Jon Stephens
Documented bugfix in the 5.1.39 changelog as follows:

        When a statement that changed both transactional and
        non-transactional tables failed, the transactional changes were
        automatically rolled back on the master but the slave ignored
        the error and did not roll them back, thus leading to
        inconsistencies between master and slave.

        This issue is fixed by automatically rolling back a statement
        that fails on the slave; however, the transaction is not rolled
        back unless a ROLLBACK statement is found in the relay log file.

Set status = NDI, waiting for push to 5.4.
[14 Sep 2009 16:03] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 9:59] Jon Stephens
Also documented bugfix in the 5.4.4 changelog.

Closed.
[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)
[2 Oct 2009 0:25] Paul Dubois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[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)