MySQL Bugs: #35762: Failing CREATE-SELECT steels Table map of the following query

Bug #35762	Failing CREATE-SELECT steels Table map of the following query
Submitted:	2 Apr 2008 5:57	Modified:	24 Apr 2008 20:01
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1 BK	OS:	Any
Assigned to:	Andrei Elkin	CPU Architecture:	Any
Tags:	create select, logging, RBR, replication, row-based, transaction

Description:
If a CREATE ... ENGINE=INNODB ... SELECT statement fails (e.g., due to a duplicate key), then the next statement can contain a Write_rows_log_event without a preceding Table_map_log_event. This causes the slave to ignore the Write_rows_log_event, so the slave will lose data.

There are two problems:
 (1) The Table_map_log_event should be written.
 (2) The Write_rows_log_event should not be silently ignored. An error should be produced, causing the slave to stop.

The test case below works correctly if it is modified so that one or more of the following holds:
 - t2 is myisam, or
 - CREATE...SELECT does not fail.

However, the error remains if:
 - t1 is innodb instead of myisam, or
 - all the INSERT statements and the CREATE...SELECT are in a transaction instead of autocommitted.

Related bugs: BUG#22864.

How to repeat:
==== rpl_bug-master.opt ====
--innodb
==== rpl_bug-slave.opt ====
--innodb
==== rpl_bug.test ====
source include/master-slave.inc;
source include/have_binlog_format_row.inc;

--echo [on master]

CREATE TABLE t1 (a INT);
SET AUTOCOMMIT=1;

INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (1);
--error 1062
CREATE TABLE t2 (a INT UNIQUE) ENGINE=INNODB SELECT * FROM t1;
INSERT INTO t1 VALUES (2);

SHOW TABLES;
SELECT * FROM t1;

Suggested fix:
(1) Make sure that the Rows_log_event is always written, even if the transaction is interrupted by a failing CREATE-SELECT.
(2) Generate an error whenever an attempt is made to execute a [Write|Update|Delete]_rows_log_event[_old] without a preceding Table_map_log_event.

Workaround:
If CREATE...SELECT fails, do some harmless modification to some table, to reset the logging code to a clean state.

Thank you for the report.

Verified as described. (SELECT * FROM  t1 should be run on slave)

The matter of the bug is that

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

ChangeSet@1.2567, 2008-04-07 20:21:05+03:00, aelkin@mysql1000.(none) +3 -0
  Bug #35762 Failing CREATE-SELECT steels Table map of the following query
  
  Amoung two artifacts described the former is the critical one.
  It's in that the Table map of a query following failing CREATE..SELECT is skipped from
  instantionating and binlogging both. That ends up with sending a "chopped" (ie without
  the table map head) row-events. The slave can not apply the only data row events.
  It's not easy to force the slave to react with an error in such a case (the second complaint on the bug
  report) because the lack of a table
  in the row handling (Rows_log_event::do_apply_event) is a common situation to mean the event has
  been filtered out basing on the repliation do/ingore rules.
  
  Fixed: table map creating and binlogging is restored via deploying the standard cleanup call in
  select_create::abort().
  No error is reported if by chance the table map was not been binlogged.
  Leaving this out to resolve with considering how to combine the do/ingore rules with the situation
  when erronoulsy the Table_map is not written to binlog.

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

ChangeSet@1.2567, 2008-04-08 10:38:32+03:00, aelkin@mysql1000.(none) +3 -0
  Bug #35762 Failing CREATE-SELECT steels Table map of the following query
  
  Among two claimed artifacts the critical one is in that the Table map of 
  a query following the failing with a duplicate key error CREATE-SELECT is skipped from
  instantionating and binlogging both. That leads to sending a "chopped" group of the data
  row-events without the table map head to the slave. 
  The slave can not apply the only data row events.
  It's not easy to force the slave to react with an error in such a case (the second complaint
  on the bug report), because the lack of a table Rows_log_event::do_apply_event the data row event
  handler is a common situation which  normally designates the event has to be filtered out
  basing on the repliation do/ingore rules decision.
  
  Fixed: table map creating and binlogging is restored via deploying the standard cleanup call in
  select_create::abort().
  No error is reported if by chance the table map was not been binlogged.
  Leaving this out to resolve with considering how to combine the do/ingore rules with the situation
  when erronoulsy the Table_map is not written to binlog.

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

ChangeSet@1.2567, 2008-04-08 10:43:00+03:00, aelkin@mysql1000.(none) +3 -0
  Bug #35762 Failing CREATE-SELECT steels Table map of the following query
  
  Among two claimed artifacts the critical one is in that the Table map of 
  a query following the failing with a duplicate key error CREATE-SELECT is skipped from
  instantionating (and thus binlogging). That leads to sending a "chopped" group of the data
  row-events without the table map head to the slave. 
  The slave can not apply the only data row events.
  It's not easy to force the slave to react with an error in such a case (the second complaint
  on the bug report), because the lack of a table Rows_log_event::do_apply_event the data row event
  handler is a common situation which  normally designates the event has to be filtered out
  basing on the repliation do/ingore rules decision.
  
  Fixed: table map creating and binlogging is restored via deploying the standard cleanup call in
  select_create::abort().
  No error is reported if by chance the table map was not been binlogged.
  Leaving this out to resolve with considering how to combine the do/ingore rules with the situation
  when erronoulsy the Table_map is not written to binlog.

Pushed to 5.1-release.

Please indicate whether this made it into 5.1.24. Thanks!

Documented in the 5.1.24-ndb-6.3.13 changelog as follows:

        The failure of a CREATE TABLE ... ENGINE=InnoDB ... SELECT statement
        caused the slave to lose data.

Now documented in the 5.1.24 changelog.

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha