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

[18 Jun 2009 3:49] Zhenxing He
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.
[18 Jun 2009 4:18] Valeriy Kravchuk
Verified just as described.
[28 Jul 2009 8: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/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.
[31 Jul 2009 12:31] 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/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.
[1 Aug 2009 2:15] 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/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.
[10 Aug 2009 6:55] 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/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.
[19 Aug 2009 5:54] Libing Song
The patch was pushed to mysql-5.1-bugteam and mysql-pe
[20 Aug 2009 10:35] 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/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.
[20 Aug 2009 10:36] 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/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.
[31 Aug 2009 17:12] 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/82033

2864 Davi Arnaut	2009-08-31 [merge]
      Manual merge of Bug#45574.
[2 Sep 2009 16:42] Bugs System
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)
[10 Sep 2009 12:50] Jon Stephens
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.
[14 Sep 2009 16:05] Bugs System
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)
[16 Sep 2009 10:05] Jon Stephens
Also documented in the 5.4.4 changelog and the 5.4 Manual.

Closed.
[1 Oct 2009 5:59] Bugs System
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)
[1 Oct 2009 7:25] Bugs System
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)
[1 Oct 2009 13:25] Bugs System
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)
[2 Oct 2009 0:18] Paul DuBois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50] Bugs System
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)
[6 Oct 2009 8:59] Bugs System
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)
[18 Dec 2009 10:34] Bugs System
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)
[18 Dec 2009 10:50] Bugs System
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)
[18 Dec 2009 11:05] Bugs System
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)
[18 Dec 2009 11:19] Bugs System
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)
[19 Dec 2009 10:13] Jon Stephens
No additional changelog entries needed. Closed.