Bug #40004 Replication failure with no PK + no indexes
Submitted: 13 Oct 2008 14:36 Modified: 10 Nov 2008 16:27
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1-rpl, 6.0-rpl OS:Any
Assigned to: Mats Kindahl CPU Architecture:Any
Tags: rbr HA_ERR_RECORD_DELETED

[13 Oct 2008 14:36] Philip Stoev
Description:
When no indexes are present on a table, row-based replication breaks after a few DML statements.

Note that this example contains only a single column, meaning that there should never be any ambiguity as to what row is being updated, even in the absence of a primary key.

The same bug is observed when there is more than one column, as long as there are no indexes on the table.

How to repeat:
--source include/master-slave.inc

SET binlog_format = 'row';

CREATE TABLE table1_myisam (`int` int) ENGINE=myisam;

INSERT IGNORE INTO table1_myisam VALUES (NULL);
INSERT INTO `table1_myisam` ( `int` ) VALUES ( 0 );
INSERT INTO `table1_myisam` ( `int` ) VALUES ( 9 );
INSERT INTO `table1_myisam` ( `int` ) VALUES ( 2 );
INSERT INTO `table1_myisam` ( `int` ) VALUES ( 9 );
INSERT INTO `table1_myisam` ( `int` ) VALUES ( 5 );

UPDATE `table1_myisam` SET `int` = 5 WHERE `int` = 9;
DELETE FROM `table1_myisam` WHERE `int` < 6;
UPDATE `table1_myisam` SET `int` = 9 WHERE `int` < 3;
INSERT INTO `table1_myisam` ( `int` ) VALUES ( 3 );
UPDATE `table1_myisam` SET `int` = 0 WHERE `int` < 4;
UPDATE `table1_myisam` SET `int` = 8 WHERE `int` < 5;

--save_master_pos
--connection slave
--sync_with_master
[13 Oct 2008 20:46] Sveta Smirnova
Thank you for the report.

Verified as described.
[15 Oct 2008 7:42] Giuseppe Maxia
An interesting mote. 
If you use partitions (which is a legitimate reason for not having a primary key), the bug does not kick in.

CREATE TABLE table1_myisam (i int) ENGINE=myisam 
partition by range (i) (
    partition p1 values less than (5),
    partition p2 values less than (maxvalue)
)
;

If you create the table with this statement, there is no replication error.
Even with only one partition (p2=MAXVALUE), there is no error.
[17 Oct 2008 12:42] Mats Kindahl
The failure is caused because HA_ERR_RECORD_DELETED is returned by MyISAM for the last UPDATE statement. This error code is not correctly caught in the Rows_log_event::find_row() function, so this causes an error.

On seeing a HA_ERR_RECORD_DELETED, find_row() should just proceed with fetching the next row without doing a comparison.
[20 Oct 2008 19: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/56616

2771 Mats Kindahl	2008-10-20
      Bug #40004  	Replication failure with no PK + no indexes
      
      In certain situations, a scan of the table will return the error
      code HA_ERR_RECORD_DELETED, and this error code is not 
      correctly caught in the Rows_log_event::find_row() function, which
      causes an error to be returned for this case.
      
      This patch fixes the problem by adding code to either ignore the
      record and continuing with the next one, the the event of a table
      scan, or change the error code to HA_ERR_KEY_NOT_FOUND, in the event
      that a key lookup is attempted.
[29 Oct 2008 20: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/57375

2772 Mats Kindahl	2008-10-29
      Bug #40004: Replication failure with no PK + no indexes
      
      Adding comments to test cases.
[30 Oct 2008 10: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/57426

2882 Mats Kindahl	2008-10-30 [merge]
      Merging BUG#40004 into 6.0-5.1.29-rc
[4 Nov 2008 9:20] Bugs System
Pushed into 5.1.30  (revid:mats@sun.com-20081029203751-frvnckuoufkm3dpw) (version source revid:mats@sun.com-20081103175049-ulv24ke1m2lpi632) (pib:5)
[4 Nov 2008 13:14] Jon Stephens
Documented bugfix in the 5.1.30 changelog as follows:

        Row-based replication failed with non-partitioned MyISAM tables having
        no indexes.

Set status as NDI pending push to 6.0 tree.
[10 Nov 2008 10:52] Bugs System
Pushed into 6.0.9-alpha  (revid:mats@sun.com-20081030095550-cigan7p2px2kltio) (version source revid:kgeorge@mysql.com-20081106184603-85csedg4eru93fyy) (pib:5)
[10 Nov 2008 16:27] Jon Stephens
Fix is also now documented in the 6.0.9 changelog. Closed.
[21 Nov 2008 21:02] Bugs System
Pushed into 5.1.30-ndb-6.2.17  (revid:mats@sun.com-20081029203751-frvnckuoufkm3dpw) (version source revid:tomas.ulin@sun.com-20081121161314-jhz3twf5xjt8stnh) (pib:5)
[21 Nov 2008 23:45] Bugs System
Pushed into 5.1.30-ndb-6.3.20  (revid:mats@sun.com-20081029203751-frvnckuoufkm3dpw) (version source revid:tomas.ulin@sun.com-20081121210644-zas6e9wa5kuj7d6f) (pib:5)
[27 Nov 2008 14:52] Bugs System
Pushed into 5.1.30-ndb-6.4.0  (revid:mats@sun.com-20081029203751-frvnckuoufkm3dpw) (version source revid:tomas.ulin@sun.com-20081126125835-5sohkzk2jjwpq1wp) (pib:5)