MySQL Bugs: #55625: RBR breaks on failing 'CREATE TABLE'

Bug #55625	RBR breaks on failing 'CREATE TABLE'
Submitted:	29 Jul 2010 12:25	Modified:	15 Nov 2010 13:32
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.49	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	create table select

Description:
While testing wl5370
with mysql-5.1-wl5370
RBR breaks on failing 'CREATE TABLE IF NOT EXISTS' stmt. 

delimiter |
CREATE FUNCTION f1() RETURNS INT
BEGIN
  insert into t2 values(1);
  insert into t3 values(1);
  return 1;
END|
delimiter ;

CREATE TABLE t2(a INT) ENGINE=MYISAM;
CREATE TABLE t3(a INT) ENGINE=INNODB;
CREATE TABLE t1(UNIQUE(a)) ENGINE=MYISAM SELECT 1 AS a UNION ALL SELECT f1();
throws duplicate key error.

RBR breaks on executing below stmt saying Table t1 already exists.
CREATE TABLE t1(UNIQUE(a)) ENGINE=INNODB SELECT 1 AS a UNION ALL SELECT f1();

How to repeat:
delimiter |
CREATE FUNCTION f1() RETURNS INT
BEGIN
  insert into t2 values(1);
  insert into t3 values(1);
  return 1;
END|
delimiter ;

CREATE TABLE t2(a INT) ENGINE=MYISAM;
CREATE TABLE t3(a INT) ENGINE=INNODB;
CREATE TABLE t1(UNIQUE(a)) ENGINE=MYISAM SELECT 1 AS a UNION ALL SELECT f1();
CREATE TABLE t1(UNIQUE(a)) ENGINE=INNODB SELECT 1 AS a UNION ALL SELECT f1();

The problem is reproducible on 5.1.49 and is not reproducible on 5.1.48. 
When the test is run on 5.1.49, both failed CREATE TABLE statements are written into the binary log (with error code 0), so the 2nd one causes replication abort. In 5.1.48 they are not binlogged.

# MTR test case:

--source include/master-slave.inc

--connection slave
STOP SLAVE;
SET GLOBAL binlog_format = ROW;
START SLAVE;

--connection master
SET binlog_format = ROW;

delimiter |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
  insert into t2 values(1);
  return 1;
END|
delimiter ;|

CREATE TABLE t2(a INT);

--error 1062
CREATE TABLE t1(UNIQUE(a)) SELECT 1 AS a UNION ALL SELECT f1();
--error 1062
CREATE TABLE t1(UNIQUE(a)) SELECT 1 AS a UNION ALL SELECT f1();

SHOW BINLOG EVENTS;

--sync_slave_with_master
SET GLOBAL binlog_format = @@binlog_format;

--connection master

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP FUNCTION f1;

--exit

Additional note:
The behavior on 5.1.48 is also incorrect, as t2 is not updated on slave while it is on master; it's just that it's incorrect in a different way, so this particular test case is a regression in 5.1.49.

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

3462 Luis Soares	2010-07-30
      BUG#55625
      
      Sketch of patch.
      
      If in RBR, on a CREATE SELECT and not creating a TEMPORARY
      table, always write a DROP TABLE IF EXISTS after writing the
      ROLLBACK EVENT.
     @ mysql-test/suite/binlog/r/binlog_row_insert_select.result
        Extra DROP TABLE IF EXISTS for CREATE ... SELECT ... statements 
        that fail.
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Extra DROP TABLE IF EXISTS for CREATE ... SELECT ... statements 
        that fail.

See also: BUG#47899.

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

3462 Alfranio Correia	2010-07-30
      BUG#55625 RBR breaks on failing 'CREATE TABLE'
      
      A CREATE...SELECT that fails is written to the binary log if a non-transactional
      statement is updated. If the logging format is ROW, the CREATE statement and the
      changes are written to the binary log as distinct events and by consequence the
      created table is not rolled back in the slave.
      To fix the problem, we add a DROP TABLE IF EXIST to the binary log if the CREATE
      ...SELECT fails.
     @ mysql-test/suite/rpl/r/rpl_drop.result
        Added a test case.
     @ mysql-test/suite/rpl/t/rpl_drop.test
        Added a test case.
     @ sql/sql_insert.cc
        Added a drop to clean up the created table in the slave.

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

3462 Alfranio Correia	2010-07-30
      BUG#55625 RBR breaks on failing 'CREATE TABLE'
      
      A CREATE...SELECT that fails is written to the binary log if a non-transactional
      statement is updated. If the logging format is ROW, the CREATE statement and the
      changes are written to the binary log as distinct events and by consequence the
      created table is not rolled back in the slave.
      
      In this patch, we opted to let the slave goes out of sync by not writting to the
      binary log the CREATE statement. We do this by simply reseting the binary log's
      cache.
     @ mysql-test/suite/rpl/r/rpl_drop.result
        Added a test case.
     @ mysql-test/suite/rpl/t/rpl_drop.test
        Added a test case.
     @ sql/log.cc
        Introduced a function to clean up the cache.
     @ sql/log.h
        Introduced a function to clean up the cache.
     @ sql/sql_insert.cc
        Cleaned up the binary log cache if a CREATE...SELECT fails.

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

3462 Alfranio Correia	2010-08-02
      BUG#55625 RBR breaks on failing 'CREATE TABLE'
      
      A CREATE...SELECT that fails is written to the binary log if a non-transactional
      statement is updated. If the logging format is ROW, the CREATE statement and the
      changes are written to the binary log as distinct events and by consequence the
      created table is not rolled back in the slave.
      
      In this patch, we opted to let the slave goes out of sync by not writting to the
      binary log the CREATE statement. We do this by simply reseting the binary log's
      cache.
     @ mysql-test/suite/rpl/r/rpl_drop.result
        Added a test case.
     @ mysql-test/suite/rpl/t/rpl_drop.test
        Added a test case.
     @ sql/log.cc
        Introduced a function to clean up the cache.
     @ sql/log.h
        Introduced a function to clean up the cache.
     @ sql/sql_insert.cc
        Cleaned up the binary log cache if a CREATE...SELECT fails.

This bug is caused by BUG#53560.

Pushed into 5.1.50 (revid:alfranio.correia@sun.com-20100803115202-djw53teru56ls7nd) (version source revid:alfranio.correia@sun.com-20100803115202-djw53teru56ls7nd) (merge vers: 5.1.50) (pib:18)

Documented bugfix in the 5.1.50 changelog as follows:

        When using the row-based logging format, a failed CREATE TABLE
        statement was written to the binary log, causing to replication
        to fail if the failed statement was later re-run on the master.

Set NM status, waiting for merges to 5.5 and later trees.

Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825102234-a3q8x0l7voa13ts3) (version source revid:alik@ibmvm-20100825102234-a3q8x0l7voa13ts3) (merge vers: 5.5.6-m3) (pib:20)

Also documented bugfix in the 5.5.6 changelog. Closed.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100901130501-4g2k86dub29auj8y) (version source revid:alik@sun.com-20100901130012-9bmmvzcnnw6n5rw6) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100901130614-pgop3m80rmutewxn) (version source revid:alik@sun.com-20100901130033-8k19cjn6n2blm3py) (pib:21)

Also documented bugfix in the 5.6.1 changelog.

Closed.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Already documented for 5.1.50 release. 

No new changelog entries required. 

Returning to Closed state.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (merge vers: 5.1.51) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (merge vers: 5.1.51) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)

No new changelog entries required.

Closed.