| Bug #46130 | Slave does not correctly handle "expected errors" | ||
|---|---|---|---|
| Submitted: | 11 Jul 2009 13:34 | Modified: | 16 Sep 2009 9:59 |
| Reporter: | Alfranio Tavares Correia Junior | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1+ | OS: | Any |
| Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
| Tags: | replication, slave | ||
[11 Jul 2009 14:58]
Alfranio Tavares Correia Junior
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 Tavares Correia Junior
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 Tavares Correia Junior
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)
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.