MySQL Bugs: #39752: Replication failure on RBR + MyISAM + no PK

Bug #39752	Replication failure on RBR + MyISAM + no PK
Submitted:	30 Sep 2008 9:51	Modified:	17 Oct 2008 12:50
Reporter:	Philip Stoev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	6.0.8-bzr, 5.1.30-bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When executing queries against a table containing no primary key, replication failure occurs:

Could not execute Update_rows event on table test.table1_myisam; handler error HA_ERR_RECORD_DELETED; the event's master log master-bin.000001, end_log_pos 5386

How to repeat:
Some of the queries may not be strictly necessary. This example uses "bit" however "int" is also known to be affected.

--source include/master-slave.inc
--incude include/have_innodb.inc

DROP TABLE IF EXISTS table1_myisam;
CREATE TABLE table1_myisam (`bit` bit) ENGINE=myisam;
INSERT IGNORE INTO table1_myisam VALUES (NULL);
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 0 );
UPDATE `table1_myisam` SET `bit` = 0 WHERE `bit` = 1 LIMIT 3;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 5 );
DELETE FROM `table1_myisam` WHERE `bit` < 2 LIMIT 4;
DELETE FROM `table1_myisam` WHERE `bit` < 9 LIMIT 4;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 9 );
UPDATE `table1_myisam` SET `bit` = 8 WHERE `bit` = 0 LIMIT 6;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 8 );
UPDATE `table1_myisam` SET `bit` = 0 WHERE `bit` < 6 LIMIT 0;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 4 );
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 3 );
UPDATE `table1_myisam` SET `bit` = 0 WHERE `bit` = 7 LIMIT 6;
DELETE FROM `table1_myisam` WHERE `bit` = 4 LIMIT 7;
UPDATE `table1_myisam` SET `bit` = 9 WHERE `bit` < 2 LIMIT 9;
UPDATE `table1_myisam` SET `bit` = 0 WHERE `bit` < 9 LIMIT 2;
DELETE FROM `table1_myisam` WHERE `bit` < 0 LIMIT 5;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 5 );
UPDATE `table1_myisam` SET `bit` = 4 WHERE `bit` < 6 LIMIT 4;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 5 );
UPDATE `table1_myisam` SET `bit` = 9 WHERE `bit` < 5 LIMIT 8;
DELETE FROM `table1_myisam` WHERE `bit` < 8 LIMIT 8;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 6 );
DELETE FROM `table1_myisam` WHERE `bit` < 6 LIMIT 7;
UPDATE `table1_myisam` SET `bit` = 7 WHERE `bit` = 3 LIMIT 7;
UPDATE `table1_myisam` SET `bit` = 8 WHERE `bit` = 0 LIMIT 6;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 7 );
DELETE FROM `table1_myisam` WHERE `bit` < 9 LIMIT 4;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 7 );
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 6 );
UPDATE `table1_myisam` SET `bit` = 8 WHERE `bit` = 3 LIMIT 4;
DELETE FROM `table1_myisam` WHERE `bit` = 2 LIMIT 9;
DELETE FROM `table1_myisam` WHERE `bit` = 1 LIMIT 4;
UPDATE `table1_myisam` SET `bit` = 4 WHERE `bit` = 2 LIMIT 7;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 0 );
DELETE FROM `table1_myisam` WHERE `bit` < 3 LIMIT 0;
UPDATE `table1_myisam` SET `bit` = 8 WHERE `bit` = 5 LIMIT 2;
INSERT INTO `table1_myisam` ( `bit` ) VALUES ( 1 );
UPDATE `table1_myisam` SET `bit` = 9 WHERE `bit` < 5 LIMIT 3;

--save_master_pos
--connection slave
--sync_with_master

Verified with recent 6.0.8 from bzr:

openxs@suse:/home2/openxs/dbs/6.0/mysql-test> ./mysql-test-run.pl --suite=rpl rpl_bug39752
Logging: ./mysql-test-run.pl --suite=rpl rpl_bug39752
MySQL Version 6.0.8
Using dynamic switching of binlog format
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using NDBCLUSTER_PORT_SLAVE = 9312
 - adding combinations
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
Installing Slave1 Database
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

rpl.rpl_bug39752 'row'         [ fail ]

=== SHOW MASTER STATUS ===
---- 1. ----
File    slave-bin.000001
Position        5340
Binlog_Do_DB
Binlog_Ignore_DB
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State  Waiting for master to send event
Master_Host     127.0.0.1
Master_User     root
Master_Port     9306
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     5455
Relay_Log_File  slave-relay-bin.000002
Relay_Log_Pos   5377
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      0
Last_Error      Could not execute Update_rows event on table test.table1_myisam; handler error HA_ERR_RECORD_DELETED; the event's master log master-bin.000001, end_log_pos 5386
Skip_Counter    0
Exec_Master_Log_Pos     5230
Relay_Log_Space 5758
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed      No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master
Master_SSL_Verify_Server_Cert   No
Last_IO_Errno   0
Last_IO_Error
Last_SQL_Errno  0
Last_SQL_Error  Could not execute Update_rows event on table test.table1_myisam; handler error HA_ERR_RECORD_DELETED; the event's master log master-bin.000001, end_log_pos 5386
=========================

mysqltest: At line 48: could not sync with master ('select master_pos_wait('master-bin.000001', 5455)' returned NULL)

Fails on latest 5.1.30-bzr also:

openxs@suse:/home2/openxs/dbs/5.1/mysql-test> ./mysql-test-run.pl --suite=rpl rpl_bug39752
Logging: ./mysql-test-run.pl --suite=rpl rpl_bug39752
MySQL Version 5.1.30
Using dynamic switching of binlog format
Using ndbcluster when necessary, mysqld supports it
Setting mysqld to support SSL connections
Binaries are debug compiled
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Using NDBCLUSTER_PORT       = 9311
Using NDBCLUSTER_PORT_SLAVE = 9312
Using IM_PORT               = 9313
Using IM_MYSQLD1_PORT       = 9314
Using IM_MYSQLD2_PORT       = 9315
 - adding combinations
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Installing Master Database
Installing Slave1 Database
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

rpl.rpl_bug39752 'row'         [ fail ]

=== SHOW MASTER STATUS ===
---- 1. ----
File    slave-bin.000001
Position        5339
Binlog_Do_DB
Binlog_Ignore_DB
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State  Waiting for master to send event
Master_Host     127.0.0.1
Master_User     root
Master_Port     9306
Connect_Retry   1
Master_Log_File master-bin.000001
Read_Master_Log_Pos     5454
Relay_Log_File  slave-relay-bin.000003
Relay_Log_Pos   5375
Relay_Master_Log_File   master-bin.000001
Slave_IO_Running        Yes
Slave_SQL_Running       No
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno      0
Last_Error      Could not execute Update_rows event on table test.table1_myisam; handler error HA_ERR_RECORD_DELETED; the event's master log master-bin.000001, end_log_pos 5385
Skip_Counter    0
Exec_Master_Log_Pos     5229
Relay_Log_Space 5755
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed      No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master
Master_SSL_Verify_Server_Cert   No
Last_IO_Errno   0
Last_IO_Error
Last_SQL_Errno  0
Last_SQL_Error  Could not execute Update_rows event on table test.table1_myisam; handler error HA_ERR_RECORD_DELETED; the event's master log master-bin.000001, end_log_pos 5385

Bug is a duplicate of BUG#40004.