MySQL Bugs: #47312: RBR: Disabling key on slave breaks replication: HA_ERR_WRONG

Bug #47312	RBR: Disabling key on slave breaks replication: HA_ERR_WRONG_INDEX
Submitted:	14 Sep 2009 15:23	Modified:	19 Dec 2009 11:34
Reporter:	Luis Soares	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1, next	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	INDEX, RBR

Description:
In row-based replication, if disabling a key on slave table, then
replication may break unexpectedly.

For example, consider replicating the following table:

   CREATE TABLE t (a int, b int, c int, key(b));

Then, after some load, the user decides, that the slave should
have no keys, so he issues:

   ALTER TABLE t DISABLE KEYS;

After disabling the key, if doing an update or delete, then
replication will break and slave will show:

Last_Error	Could not execute Delete_rows event on table test.t; Got error 124 from storage engine, Error_code: 1030; handler error HA_ERR_WRONG_INDEX; the event's master log master-bin.000001, end_log_pos 820

It breaks unexpectedly, because if defining declaring master
table with key and slave without key independently and not
using "ALTER TABLE ... DISABLE KEYS", then replication does not
break.

For details check below the "How to repeat" section.

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

#
# Case #1: master has key, but slave has not. 
#          Replication does not break.
# 

SET SQL_LOG_BIN=0;
CREATE TABLE t (a int, b int, c int, key(b));
SET SQL_LOG_BIN=1;

-- connection slave

CREATE TABLE t (a int, b int, c int);

-- connection master

INSERT INTO t VALUES (1,2,4);
INSERT INTO t VALUES (4,3,4);
DELETE FROM t;
#UPDATE t SET a=3;

-- sync_slave_with_master

-- connection master
DROP TABLE t;

-- sync_slave_with_master

#
# Case #2: master has key, slave also has one, 
#          but it gets disabled sometime.
#          Replication does break.
# 
-- source include/master-slave-reset.inc
-- connection master

CREATE TABLE t (a int, b int, c int, key(b));

-- sync_slave_with_master

ALTER TABLE t DISABLE KEYS;

-- connection master

INSERT INTO t VALUES (1,2,4);
INSERT INTO t VALUES (4,3,4);
DELETE FROM t;
#UPDATE t SET a=3;

-- sync_slave_with_master

-- connection master
DROP TABLE t;

-- sync_slave_with_master

-- exit

Suggested fix:
Make slave thread to check if the key on the table is active before using it.

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/83245

3124 Luis Soares	2009-09-15
      BUG#47312: RBR: Disabling key on slave breaks replication:
      HA_ERR_WRONG_INDEX
      
      In RBR, if disabling keys on slave table it breaks replication if
      updating or deleting a record. When the slave thread tries to
      finding the row, by searching the storage engine, it checkes
      whether the table has a key or not. If it has one, then the slave
      thread uses it to search the record.
      
      Unfortunately, the slave only checks whether the key exists or
      not, it does not verify if it is active. Should the key be
      disabled (eg, DBA has issued an ALTER TABLE ... DISABLE KEYS)
      then it will result in error: HA_ERR_WRONG_INDEX.
      
      This patch addresses this issue by making the slave thread also
      verify whether the key is active or not.

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/84763

3136 Luis Soares	2009-09-27
      BUG#47312: RBR: Disabling key on slave breaks replication:
      HA_ERR_WRONG_INDEX
            
      In RBR, disabling keys on slave table will break replication when
      updating or deleting a record. When the slave thread tries to
      find the row, by searching in the storage engine, it checks
      whether the table has a key or not. If it has one, then the slave
      thread uses it to search the record.
            
      Nonetheless, the slave only checks whether the key exists or not,
      it does not verify if it is active. Should the key be
      disabled (eg, DBA has issued an ALTER TABLE ... DISABLE KEYS)
      then it will result in error: HA_ERR_WRONG_INDEX.
            
      This patch addresses this issue by making the slave thread also
      check whether the key is active or not before actually using it.

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/84765

3620 Luis Soares	2009-09-27 [merge]
      BUG#47312: RBR: Disabling key on slave breaks replication: 
      HA_ERR_WRONG_INDEX
      
      Automerge: mysql-5.1-bugteam --> mysql-pe

Pushed to mysql-5.1-bugteam and mysql-pe.

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:luis.soares@sun.com-20090927210247-mc0tbx8uxsuojkch) (merge vers: 5.1.40) (pib:11)

Documented bugfix in the 5.1.40 changelog as follows:

        Performing ALTER TABLE ... DISABLE KEYS on a slave table caused
        row-based replication to fail.

Set status = NDI, waiting for push to 5.4.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)

Bugfix also documented in the 5.5.0 and 6.0.14 changelogs.

Closed.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

No additional changelog entries needed. Setting back to Closed state.

See also bug #47312