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 Correia
Tags: create table select
Triage: Triaged: D1 (Critical)

[29 Jul 2010 12:25] Nidhi Shrotriya
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();
[29 Jul 2010 16:39] Elena Stepanova
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
[29 Jul 2010 16:45] Elena Stepanova
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.
[30 Jul 2010 1:32] 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/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.
[30 Jul 2010 10:43] Luis Soares
See also: BUG#47899.
[30 Jul 2010 12: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/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.
[30 Jul 2010 16:18] 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/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.
[2 Aug 2010 19:49] 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/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.
[2 Aug 2010 23:07] Alfranio Correia
This bug is caused by BUG#53560.
[4 Aug 2010 9:39] Bugs System
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)
[4 Aug 2010 17:01] Jon Stephens
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.
[19 Aug 2010 15:41] Bugs System
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)
[25 Aug 2010 10:23] Bugs System
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)
[26 Aug 2010 10:14] Jon Stephens
Also documented bugfix in the 5.5.6 changelog. Closed.
[1 Sep 2010 13:13] Bugs System
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)
[1 Sep 2010 13:14] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100901130614-pgop3m80rmutewxn) (version source revid:alik@sun.com-20100901130033-8k19cjn6n2blm3py) (pib:21)
[1 Sep 2010 13:48] Jon Stephens
Also documented bugfix in the 5.6.1 changelog.

Closed.
[14 Oct 2010 8:27] Bugs System
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)
[14 Oct 2010 8:42] Bugs System
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)
[14 Oct 2010 8:57] Bugs System
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)
[14 Oct 2010 12:34] Jon Stephens
Already documented for 5.1.50 release. 

No new changelog entries required. 

Returning to Closed state.
[9 Nov 2010 19:43] Bugs System
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)
[13 Nov 2010 16:10] Bugs System
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)
[13 Nov 2010 16:30] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:jimmy.yang@oracle.com-20100804103744-vbpeghipkz6pyc9z) (pib:21)
[15 Nov 2010 13:32] Jon Stephens
No new changelog entries required.

Closed.