MySQL Bugs: #45574: CREATE IF NOT EXISTS is not binlogged if the object exists

Bug #45574	CREATE IF NOT EXISTS is not binlogged if the object exists
Submitted:	18 Jun 2009 3:49	Modified:	19 Dec 2009 10:13
Reporter:	Zhenxing He	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.36-bzr, 6.0	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any

Description:
CREATE IF NOT EXISTS will not be binlogged if the object already exists, so after execution of such a statement on master, the object may not be created on slave if the object already exist on master. And since DROP IF EXISTS statements are always binlogged whether the object exists or not, I think CREATE IF NOT EXISTS should also always be binlogged.

This problem caused some of the tests fails randomly on PB or PB2.

How to repeat:
Run the following statements with binlog on:

DROP DATABASE IF EXISTS db1;
CREATE DATABASE IF NOT EXISTS db1;
CREATE DATABASE IF NOT EXISTS db1;
SHOW BINLOG EVENTS;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	4	Format_desc	1	107	Server ver: 5.4.4-alpha-debug-log, Binlog ver: 4
master-bin.000001	107	Query	1	196	DROP DATABASE IF EXISTS db1
master-bin.000001	196	Query	1	291	CREATE DATABASE IF NOT EXISTS db1

As we can see, the second CREATE DATABASE statement is not be binlogged.

Suggested fix:
Binlog CREATE IF NOT EXISTS always.

Verified just as described.

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

2941 Li-Bing.Song@sun.com	2009-07-28
      BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
      
      There is an inconsistency with DROP DATABASE|TABLE IF EXISTS and CREATE
      EVENT IF NOT EXISTS:DROP IF EXISTS statements are binlogged even if the DB or TABLE does not
      exist, CREATE EVENT IF NOT EXISTS are binlogged even if the event exists. whereas CREATE
      DATEBASE|TABLE IF NOT EXIST dose not binlog when the DB or TABLE exists. 
      
      This patch includes 2 tests to test all CREATE IF NOT EXISTS statement.
      CREATE DATABASE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS ... LIKE have this bug.
      CREAET TABLE IF NOT EXISTS ... SELECT has this bug in row mode.
      This patch adds code to fix all above.

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

2941 Li-Bing.Song@sun.com	2009-07-31
      BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
      
      There is an inconsistency with DROP DATABASE|TABLE IF EXISTS and CREATE
      EVENT IF NOT EXISTS:DROP IF EXISTS statements are binlogged even if the DB or TABLE does not
      exist, CREATE EVENT IF NOT EXISTS are binlogged even if the event exists. whereas CREATE
      DATEBASE|TABLE IF NOT EXIST dose not binlog when the DB or TABLE exists. 
      
      This patch includes 2 tests to test all CREATE IF NOT EXISTS statement.
      CREATE DATABASE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS ... LIKE have this bug.
      CREAET TABLE IF NOT EXISTS ... SELECT has this bug in row mode.
      This patch adds code to fix all above.
     @ sql/sql_insert.cc
        This paragraph codes was moved from create_table_from_items to select_create::prepare.
        This paragraph codes check if the table which will be created exists.
        When table existing and row mode, the CREATE TABLE IF NOT EXIST statement is binlogged.

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

2941 Li-Bing.Song@sun.com	2009-08-01
      BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
      
      There is an inconsistency with DROP DATABASE|TABLE|EVENT IF EXISTS and
      CREATE EVENT IF NOT EXISTS :DROP IF EXISTS statements are binlogged even
      if either the DB, TABLE or EVENT does not exist, CREATE EVENT IF NOT
      EXISTS is binlogged even if the EVENT exists. In contrast, CREATE
      DATEBASE|TABLE IF NOT EXISTS are not binlogged when either the DB or TABLE
      exists.  
      
      This patch includes 2 tests to test all CREATE IF NOT EXISTS statement.
      CREATE DATABASE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS ... LIKE have this bug.
      CREAET TABLE IF NOT EXISTS ... SELECT has this bug in row mode.
      This patch adds code to fix all above.
     @ sql/sql_insert.cc
        This paragraph codes was moved from create_table_from_items to select_create::prepare.
        This paragraph codes check if the table which will be created exists.
        When table existing and row mode, the CREATE TABLE IF NOT EXIST statement is binlogged.

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

3059 Li-Bing.Song@sun.com	2009-08-10
      BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
      
      There is an inconsistency with DROP DATABASE|TABLE|EVENT IF EXISTS and
      CREATE DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
      binlogged even if either the DB, TABLE or EVENT does not exist. In
      contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
      exists.  
      
      This patch fixes the following cases for all the replication formats:
      CREATE DATABASE IF NOT EXISTS.
      CREATE TABLE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS ... LIKE,
      CREAET TABLE IF NOT EXISTS ... SELECT.
     @ sql/sql_insert.cc
        Part of the code was moved from the create_table_from_items to select_create::prepare.
        When replication is row based, CREATE TABLE IF NOT EXISTS.. SELECT is binlogged if the table exists.

The patch was pushed to mysql-5.1-bugteam and mysql-pe

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

3064 Li-Bing.Song@sun.com	2009-08-12
      BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
      
      There is an inconsistency with DROP DATABASE|TABLE|EVENT IF EXISTS and
      CREATE DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
      binlogged even if either the DB, TABLE or EVENT does not exist. In
      contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
      exists.  
      
      This patch fixes the following cases for all the replication formats:
      CREATE DATABASE IF NOT EXISTS.
      CREATE TABLE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS ... LIKE,
      CREAET TABLE IF NOT EXISTS ... SELECT.
     @ sql/sql_insert.cc
        Part of the code was moved from the create_table_from_items to select_create::prepare.
        When replication is row based, CREATE TABLE IF NOT EXISTS.. SELECT is binlogged if the table exists.

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

3067 Li-Bing.Song@sun.com	2009-08-13
      BUG#45574 CREATE IF NOT EXISTS is not binlogged if the object exists
      
      There is an inconsistency with DROP DATABASE|TABLE|EVENT IF EXISTS and
      CREATE DATABASE|TABLE|EVENT IF NOT EXISTS. DROP IF EXISTS statements are
      binlogged even if either the DB, TABLE or EVENT does not exist. In
      contrast, Only the CREATE EVENT IF NOT EXISTS is binlogged when the EVENT
      exists.  
      
      This patch fixes the following cases for all the replication formats:
      CREATE DATABASE IF NOT EXISTS.
      CREATE TABLE IF NOT EXISTS,
      CREATE TABLE IF NOT EXISTS ... LIKE,
      CREAET TABLE IF NOT EXISTS ... SELECT.
     @ sql/sql_insert.cc
        Part of the code was moved from the create_table_from_items to select_create::prepare.
        When replication is row based, CREATE TABLE IF NOT EXISTS.. SELECT is binlogged if the table exists.

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

2864 Davi Arnaut	2009-08-31 [merge]
      Manual merge of Bug#45574.

Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:li-bing.song@sun.com-20090813024857-1emgf5lhj0zikoj8) (merge vers: 5.1.38) (pib:11)

Documented bugfix in the 5.1.39 changelog as follows:

        The binary logging behavior of DROP DATABASE IF EXISTS, DROP
        TABLE IF EXISTS, and DROP EVENT IF EXISTS was not consistent
        with that of CREATE DATABASE IF NOT EXISTS, DROP TABLE IF NOT
        EXISTS, and DROP EVENT IF NOT EXISTS. (The DROP ... IF EXISTS
        statements are logged even if the database object to be dropped
        does not exist. However, of the CREATE ... IF NOT EXISTS
        statements, only the CREATE EVENT IF NOT EXISTS statement was
        logged when the database object to be created already existed.

        Now, every CREATE ... IF NOT EXISTS statement is written to the
        binary log, whether the database object named in the statement
        exists or not. For more information, see "Replication of CREATE 
        ... IF NOT EXISTS Statements".

Added new section "Replication of CREATE ... IF NOT EXISTS Statements", which will appear online soon as http://dev.mysql.com/doc/refman/5.1/en/replication-features-create-if-not-exists.html.

Set status = NDI, waiting for push to 5.4 tree.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

Also documented in the 5.4.4 changelog and the 5.4 Manual.

Closed.

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:mikael@mysql.com-20090907102257-fflwgm6rp5m5kmfw) (merge vers: 5.1.39) (pib:11)

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

No additional changelog entries needed. Closed.