MySQL Bugs: #55598: RBR: CREATE TABLE IF NOT EXISTS and INSERT written to binary log twice

Bug #55598	RBR: CREATE TABLE IF NOT EXISTS and INSERT written to binary log twice
Submitted:	28 Jul 2010 10:11	Modified:	15 Oct 2010 14:04
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1-wl5370	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
If a table t already exists and current binlog_format is ROW (or MIXED, and the existing conditions make it switch to ROW), the block CREATE TABLE IF NOT EXISTS t AS SELECT .. and INSERT ... is written into the binary log twice -- once in ROW format (for INSERT) and then in pure STATEMENT. It is executed on slave twice, too, thus causing data inconsistency between master and slave.

For the test case below:
 
# Table contents on master:
SELECT * FROM t;
f
1
1
# Table contents on slave:
SELECT * FROM t;
f
1
1
1

How to repeat:
# The test is for 5.1, but if you run it on 5.5, do it with
# --mysqld=--binlog-format=ROW, it will save some trouble
# with *slave* not being able to execute an event
# (statement is in row format and BINLOG_FORMAT = STATEMENT on slave)

--source include/master-slave.inc

--disable_warnings
DROP TABLE IF EXISTS t;
--enable_warnings

SET binlog_format = ROW;

CREATE TABLE IF NOT EXISTS t
  SELECT 1 as f;

--disable_warnings
CREATE TABLE IF NOT EXISTS t
  SELECT 1 as f;
--enable_warnings

--echo # Table contents on master:
SELECT * FROM t;

--sync_slave_with_master
--echo # Table contents on slave:
SELECT * FROM t;

--connection master

SHOW BINLOG EVENTS;
DROP TABLE t;

--exit

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

3476 Li-Bing.Song@sun.com	2010-07-30
      Postfix for WL#5370
      It fixs Bug#55598 and Bug#55616
      
      Add test for Bug#44498.
      Add parameter errcode for binlog_show_create_table function, and set right
      error code to it.
      Confine select_create::write_to_binlog on SBR.

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

3479 Li-Bing.Song@sun.com	2010-08-17
      WL#5370 Keep forward-compatibility when changing
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#55474, BUG#55499, BUG#55598, BUG#55616 and BUG#55777 are fixed
      in this patch too.
      
      This is the 5.1 part.
      It implements:
      - if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
      
      - Insert nothing and binlog nothing on master if the existing object
        is a view. It only generates a warning that table already exists.
     @ sql/sql_class.h
        Declare virtual function write_to_binlog() for select_insert.
        It's used to binlog 'create select'
     @ sql/sql_insert.cc
        Implement write_to_binlog();
        Use write_to_binlog() instead of binlog_query() to binlog the statement.
        if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
     @ sql/sql_lex.h
        Declare create_select_start_with_brace and create_select_pos.
        They are helpful for binlogging 'create select'
     @ sql/sql_parse.cc
        Do nothing on master if the existing object is a view.
     @ sql/sql_yacc.yy
        Record the relative postion of 'SELECT' in the 'CREATE ...SELECT' statement.
        Record whether there is a '(' before the 'SELECT' clause.

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

3484 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#55474, BUG#55499, BUG#55598, BUG#55616 and BUG#55777 are fixed
      in this patch too.
      
      This is the 5.1 part.
      It implements:
      - if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
      
      - Insert nothing and binlog nothing on master if the existing object
        is a view. It only generates a warning that table already exists.
     @ mysql-test/r/trigger.result
        Ather this patch, 'CREATE TABLE IF NOT EXISTS ... SELECT' will not
        insert anything if the creating table already exists and is a view.
     @ sql/sql_class.h
        Declare virtual function write_to_binlog() for select_insert.
        It's used to binlog 'create select'
     @ sql/sql_insert.cc
        Implement write_to_binlog();
        Use write_to_binlog() instead of binlog_query() to binlog the statement.
        if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS
        and INSERT ... SELECT
     @ sql/sql_lex.h
        Declare create_select_start_with_brace and create_select_pos.
        They are helpful for binlogging 'create select'
     @ sql/sql_parse.cc
        Do nothing on master if the existing object is a view.
     @ sql/sql_yacc.yy
        Record the relative postion of 'SELECT' in the 'CREATE ...SELECT' statement.
        Record whether there is a '(' before the 'SELECT' clause.

It was fixed in the final patch for WL#5370.

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)

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)

Bug does not appear in any released version. No changelog entry needed.

Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)

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)

Reverted to Closed; see above.