MySQL Bugs: #40004: Replication failure with no PK + no indexes

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

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

Thank you for the report.

Verified as described.

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.

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.

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.

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.

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

Pushed into 5.1.30  (revid:mats@sun.com-20081029203751-frvnckuoufkm3dpw) (version source revid:mats@sun.com-20081103175049-ulv24ke1m2lpi632) (pib:5)

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.

Pushed into 6.0.9-alpha  (revid:mats@sun.com-20081030095550-cigan7p2px2kltio) (version source revid:kgeorge@mysql.com-20081106184603-85csedg4eru93fyy) (pib:5)

Fix is also now documented in the 6.0.9 changelog. Closed.

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)

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)

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)