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
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[2 Apr 2008 5:57] Sven Sandberg
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.
[2 Apr 2008 18:45] Sveta Smirnova
Thank you for the report.

Verified as described. (SELECT * FROM  t1 should be run on slave)
[7 Apr 2008 17:07] Andrei Elkin
The matter of the bug is that
[7 Apr 2008 17:24] Bugs System
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.
[8 Apr 2008 7:40] Bugs System
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.
[8 Apr 2008 7:44] Bugs System
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.
[8 Apr 2008 9:57] Andrei Elkin
Pushed to 5.1-release.
[12 Apr 2008 13:48] Jon Stephens
Please indicate whether this made it into 5.1.24. Thanks!
[12 Apr 2008 13:48] Jon Stephens
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.
[24 Apr 2008 20:01] Jon Stephens
Now documented in the 5.1.24 changelog.
[1 May 2008 6:17] Bugs System
Pushed into 5.1.25-rc
[1 May 2008 6:19] Bugs System
Pushed into 6.0.6-alpha