MySQL Bugs: #51501: RBR, slave stops with HA_ERR_KEY_NOT

Bug #51501	RBR, slave stops with HA_ERR_KEY_NOT_FOUND
Submitted:	25 Feb 2010 12:44	Modified:	16 Aug 2010 3:43
Reporter:	Matthias Leich	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1.37, 5.1.44, 5.1.45,5.5.99-m3	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
My script:
----------
--source include/master-slave.inc
--disable_abort_on_error

CREATE TABLE t3 ( pk INT, col2 BIGINT, PRIMARY KEY (pk));
CREATE TABLE IF NOT EXISTS t3 ENGINE = MyISAM AS
SELECT NULL,3
UNION ALL
SELECT NULL,NULL;

SET SESSION BINLOG_FORMAT = ROW;
REPLACE INTO t3 ( col2 ) VALUES (  13 ) ;

--connection master
--sync_slave_with_master

Result on 5.1.45
mysql-5.1 revno: 3360 2010-02-17
--------------------------------
./mysql-test-run  --mem --mysqld=--binlog-format=row Alfranio1
...
main.Alfranio1                           [ fail ]
...
=== SHOW SLAVE STATUS ===
...
Last_Errno  1032
Last_Error  Could not execute Update_rows event on table test.t3; Can't find record in 't3', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 391
...

Observations:
1. We have the same problem in
   5.5.99-m3
   mysql-next-mr revno: 3097 2010-02-22
2. The problem disappears if I
   a) replace
      SET SESSION BINLOG_FORMAT = ROW;
      by
      SET SESSION BINLOG_FORMAT = MIXED or STATEMENT
   b) replace
      CREATE TABLE IF NOT EXISTS t3 ...
      by
      CREATE TABLE t3 ...
      or
      INSERT INTO t3 ...

How to repeat:
See above

Workaround:
   Use SET SESSION SQL_MODE='STRICT_ALL_TABLES';

I assume the following observation is to be expected:
In case we replace the 
   REPLACE INTO t3 ( col2 ) VALUES (  13 ) ;
by
   DELETE FROM t3;
we get
Could not execute Delete_rows event on table test.t3; Can't find record in 't3', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log master-bin.000001, end_log_pos 378

Older versions (5.1.37 and 5.1.44) are also affected, so this is NOT a recent regression.

The root reason is that 'CREATE TABLE IF NOT EXISTS t1... SELECT' statement is not binlogged when t1 exists before the statement and at least one row has been inserted into the existing table.

But we need not fix it now, for the behavior is changed after bug#47132.
After the patch for bug#47132, The result of 'SELECT ..' will not be insert into the existing table t1 which has existed before existing the statement.

So this bug will disappear if the patch for bug#47132 is pushed.

Typo of the last comment:
   which has existed before executing the statement.

I think the cause of this problem is that when there are any errors (duplicate key) in the inserting part of the CREATE...SELECT statement, then the whole statement will not be binlogged in STATEMENT format. If the table already exist and is a MyISAM table, then there might already been data inserted into the table before the error, and cause difference between the master and slave.

Agree with libing on that this bug can be fixed by the proposed solution for bug#47132, that is, not executing the insert part of the CREATE...SELECT statement if the table already exists.

Bug#55605 marked as a duplicate of this one.

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

3479 Li-Bing.Song@sun.com	2010-08-13
      Bug #39804 Failing CREATE ... SELECT doesn't appear in binary log
      Bug #51501  	RBR, slave stops with HA_ERR_KEY_NOT_FOUND
      
      Failing CREATE ... SELECT that updates non-transactional tables
      via stored function(bug#39804) or the creating non-transactional
      table already exists(Rows are inserted into it, but it can not be
      dropt) is not binlogged on SBR. The cause is that the statement
      will never be binlogged on SBR if it fails. As the table will be 
      dropt automatically after an error happens.
      
      In this patch, the statement will be binlogged on SBR if a other
      non-trasactional table is modified or the creating non-transactional
      table already exists(It means it can not be dropt).
      
      It also fix Bug#55625  RBR breaks on failing 'CREATE TABLE'
      
      It will be binlogged if a nontrasactional table is modified.
      But the 'CREATE TABLE' statement will always be binlogged even
      when the creating table is dropt automatically on master. But
      it can not be dropt automatically on slave. This will cause the 
      second 'CREATE TABLE' on the same name fails on slave(It can be
      executed on master well). 
      
      Because rows for different tables are in different events. So
      after this patch, only the row events on other tables are binlogged
      with 'ROLLBACK'. The 'CREATE TABLE' event and all row events on the
      creating table are truncated. They will never be binlogged.
     @ sql/log.cc
        add binlog_truncate_stmt() to truncate a statement.
     @ sql/sql_class.h
        add variable modified_other_non_trans_table in select_create class.
        It is set on the rare occasion that SELECT clause calls a function
        in which a non-transaction table is modified.

Dup of bug#39804