Bug #40186 Replication failure on RBR + Innodb + SET AUTOCOMMIT=OFF + DROP TABLE
Submitted: 20 Oct 2008 18:41 Modified: 26 Feb 2009 21:54
Reporter: Philip Stoev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1, 6.0-bzr OS:Any
Assigned to: Mats Kindahl CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[20 Oct 2008 18:41] Philip Stoev
Description:
A non-concurrent workload containing INSERT/UPDATE/DELETE CREATE/DROP TABLE and SET AUTOCOMMIT = OFF; causes replication failure:

081020 21:39:15 [ERROR] Slave SQL: Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 2747, Error_code: 1032
081020 21:39:15 [Warning] Slave: Can't find record in 't1' Error_code: 1032
081020 21:39:15 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 2062

How to repeat:
This is an unsimplifed test case. Some queries are likely redundant however without an automatic test case simplification tool, it is extremely time-consuming to reduce those test cases further.

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

SET binlog_format = 'row';

CREATE TABLE `t1` (
        `int` int,
        `int_key` int,
        pk integer auto_increment,
        `char_key` char (1),
        `char` char (1),
        key (`int_key` ),
        primary key (pk),
        key (`char_key` )
) ENGINE=innodb;
INSERT IGNORE INTO t1 VALUES ('1', '9', NULL, 'c', 'k');
DELETE FROM `t1` WHERE `char` < 0 LIMIT 1;
INSERT INTO `t1` ( `int` ) VALUES ( 2 );
INSERT INTO `t1` ( `int_key` ) VALUES ( 3 );
CREATE TABLE IF NOT EXISTS o ( F1 INTEGER );
CREATE TABLE IF NOT EXISTS r ( F1 INTEGER );
SET AUTOCOMMIT = OFF;
DELETE FROM `t1` WHERE `int` < 5 LIMIT 1;
DROP TABLE IF EXISTS l;
CREATE TABLE IF NOT EXISTS y ( F1 INTEGER );
UPDATE `t1` SET `char_key` = 9 ORDER BY `char` LIMIT 3;
INSERT INTO `t1` ( `char` ) VALUES ( 8 );
DROP TABLE IF EXISTS v;
INSERT INTO `t1` ( `int` ) VALUES ( 8 );
SET AUTOCOMMIT = OFF;
CREATE TABLE IF NOT EXISTS y ( F1 INTEGER );
DELETE FROM `t1` WHERE `char_key` < 2 LIMIT 1;
UPDATE `t1` SET `char_key` = 8 ORDER BY `pk` LIMIT 0;
INSERT INTO `t1` ( `char` ) VALUES ( 6 );
SET AUTOCOMMIT = OFF;
UPDATE `t1` SET `int_key` = 1 ORDER BY `int` LIMIT 4;
DELETE FROM `t1` WHERE `int_key` < 9 LIMIT 1;
UPDATE `t1` SET `pk` = 0 ORDER BY `int` LIMIT 6;
SET AUTOCOMMIT = OFF;
SET AUTOCOMMIT = OFF;
UPDATE `t1` SET `int` = 5 ORDER BY `pk` LIMIT 6;
INSERT INTO `t1` ( `int_key` ) VALUES ( 6 );
UPDATE `t1` SET `pk` = 9 ORDER BY `char_key` LIMIT 4;
DELETE FROM `t1` WHERE `char_key` < 5 LIMIT 1;
INSERT INTO `t1` ( `pk` ) VALUES ( 7 );
SET AUTOCOMMIT = OFF;
DROP TABLE IF EXISTS o;

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

Verified as described.