Bug #38230 Differences between master and slave after UPDATE or DELETE with LIMIT with pk
Submitted: 18 Jul 2008 16:25 Modified: 10 Nov 2008 16:20
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1, 5.1.28-bzr OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any

[18 Jul 2008 16:25] Philip Stoev
Description:
UPDATEs and DELETEs with LIMIT are not replicated properly with row-based replication and innodb.

Several updates/deletes are required to trigger this behavoir. More disturbingly, update/deletes on one table may influence another.

A test case will be uploaded shortly. Note that in the test case all tables have the same structure, which may facilitate erroneous updates between tables.

How to repeat:
See attached test case.
[18 Jul 2008 16:27] Philip Stoev
Test case for bug 38230

Attachment: bug38230.test (application/octet-stream, text), 4.28 KiB.

[18 Jul 2008 16:39] Philip Stoev
From what I can see, the updates/deletes must have limit and must be on primary key in order for a problem to show up.
[18 Jul 2008 16:52] Philip Stoev
5.1.23a, dated Feb 22th 2008, is also affected.
[19 Jul 2008 11:42] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.1.28-bzr on Linux. I've got 15 rows in t2 on master and 16 on slave.
[31 Oct 2008 12:19] Andrei Elkin
Could not repeat it with the latest sources in 5.1-rpl.
At the same time confirming that 5.1.28 had this issue of inconsistency.
Obviously the failure was transient.
[3 Nov 2008 10:03] 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/57672

2690 Andrei Elkin	2008-11-03
      Bug #38230 Differences between master and slave after UPDATE or DELETE with LIMIT with pk
      
      a test on the bug page does not reveal the problem with the latest trees.
      
      Adding the test to the rpl suite in order to monitor regression.
[3 Nov 2008 10:18] 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/57674

2690 Andrei Elkin	2008-11-03
      Bug #38230 Differences between master and slave after UPDATE or DELETE with LIMIT with pk
      
      a test on the bug page does not reveal the problem with the latest trees.
      
      Adding the test to the rpl suite in order to monitor regression.
[4 Nov 2008 11:03] 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/57768

2694 Andrei Elkin	2008-11-04
      bug#38230
      
      updating two test results.
[4 Nov 2008 21:58] 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/57838

2905 Andrei Elkin	2008-11-04
      manual merge for bug#38230 caused by an issue existed in the bugteam tree of 
      Warning	1286	Unknown table engine.
[6 Nov 2008 17:10] 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/58070

2700 Andrei Elkin	2008-11-06
      bug#38230 
      refining the regression test to avoid explicit innodb engine in create.
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.9-alpha  (revid:aelkin@mysql.com-20081106171009-43353edsden57o5e) (version source revid:kgeorge@mysql.com-20081106184955-dr1vxkqwou1spmkz) (pib:5)
[10 Nov 2008 11:36] Bugs System
Pushed into 5.1.30  (revid:aelkin@mysql.com-20081106171009-43353edsden57o5e) (version source revid:aelkin@mysql.com-20081106171009-43353edsden57o5e) (pib:5)
[10 Nov 2008 16:20] Jon Stephens
Documented bugfix in the 5.1.31 changelog as follows:

        With row-based replication, UPDATE and DELETE statements using LIMIT and
        a table's primary key could produce different results on the master and
        slave.

(Per IRC discussion with Philip, bug was not found in 6.0.)
[19 Jan 2009 11:31] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:08] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:14] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)