Bug #46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior in ROW mode
Submitted: 5 Aug 2009 16:34 Modified: 19 Dec 2009 11:06
Reporter: Alfranio Correia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Libing Song CPU Architecture:Any
Tags: Drop, row-based replication, temporary table
Triage: Triaged: D4 (Minor)

[5 Aug 2009 16:34] Alfranio Correia
Description:
In ROW mode, the command DROP temp-table IF EXISTS is only written to the binary log when the temporary table does not exist.

How to repeat:
source include/master-slave.inc;
source include/have_binlog_format_row.inc;

CREATE TEMPORARY TABLE IF NOT EXISTS tmp(c1 int);
CREATE TABLE IF NOT EXISTS tt(c1 int);

INSERT INTO tmp VALUES (1), (2), (3);
INSERT INTO tt VALUES (1), (2), (3);

DROP TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS tmp;
DROP TABLE IF EXISTS tmp;

DROP TABLE IF EXISTS tt;
DROP TABLE IF EXISTS tt;
DROP TABLE IF EXISTS tt;

SHOW BINLOG EVENTS;

...
master-bin.000001       428     Query   1       515     use `test`; DROP TABLE IF EXISTS tmp
master-bin.000001       515     Query   1       602     use `test`; DROP TABLE IF EXISTS tmp
master-bin.000001       602     Query   1       688     use `test`; DROP TABLE IF EXISTS tt
master-bin.000001       688     Query   1       774     use `test`; DROP TABLE IF EXISTS tt
master-bin.000001       774     Query   1       860     use `test`; DROP TABLE IF EXISTS tt
[21 Sep 2009 7:19] Libing Song
The log events are reasonable, If using 'DROP TABLE IF EXISTS' statement to drop a temporary table which is not exists. Server cannot know whether the table
is temporary or not.

This bug still exists with "DROP TEMPORARY TABLE IF EXISTS'.
[21 Sep 2009 7:30] 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/83837

3117 Li-Bing.Song@sun.com	2009-09-21
      BUG #46572 DROP temp-table IF EXISTS does not have a consistent behavior in ROW mode
      
      In RBR, 'DROP TEMPORARY TABLE IF EXISTS...' statement is binlogged when the table
      is not exists.
      
      In RBR, 'DROP TEMPORARY TABLE ...' statement is never binlogged.
      This patch add an extra condition to fix the logic.
[22 Sep 2009 4:40] 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/84023

3117 Li-Bing.Song@sun.com	2009-09-22
      BUG #46572 DROP temp-table IF EXISTS does not have a consistent behavior in ROW mode
      
      In RBR, 'DROP TEMPORARY TABLE IF EXISTS...' statement is binlogged when the table is not exists.
      
      In fact, 'DROP TEMPORARY TABLE ...' statement should be never binlogged in RBR
      whether the table exists or not. 
      This patch wrote code to fix a logical error, which results in this bug.
[23 Sep 2009 6:45] 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/84244

3117 Li-Bing.Song@sun.com	2009-09-23
      BUG #46572 DROP temp-table IF EXISTS does not have a consistent behavior in ROW mode
            
      In RBR, 'DROP TEMPORARY TABLE IF EXISTS...' statement is binlogged when the table
      does not exist.
            
      In fact, 'DROP TEMPORARY TABLE ...' statement should never be binlogged in RBR
      no matter if the table exists or not. 
      This patch addresses this by checking whether we are dropping a
      temporary table or not, when building the custom drop statement.
[25 Sep 2009 5:39] 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/84582

3130 Li-Bing.Song@sun.com	2009-09-25
      BUG #46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior in ROW mode
            
      In RBR, 'DROP TEMPORARY TABLE IF EXISTS...' statement is binlogged when the table
      does not exist.
            
      In fact, 'DROP TEMPORARY TABLE ...' statement should never be binlogged in RBR
      no matter if the table exists or not. 
      This patch addresses this by checking whether we are dropping a
      temporary table or not, when building the custom drop statement.
[28 Sep 2009 2:23] 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/84770

3137 Li-Bing.Song@sun.com	2009-09-28
      BUG #46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior in ROW mode
            
      In RBR, 'DROP TEMPORARY TABLE IF EXISTS...' statement is binlogged when the table
      does not exist.
            
      In fact, 'DROP TEMPORARY TABLE ...' statement should never be binlogged in RBR
      no matter if the table exists or not. 
      This patch addresses this by checking whether we are dropping a
      temporary table or not, when building the custom drop statement.
[28 Sep 2009 2:41] Libing Song
Pushed to mysql-5.1-bugteam and mysql-pe
[4 Oct 2009 11:20] 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/85672

3620 Georgi Kodinov	2009-10-04
      Disabled the test until the fix for bug #46572 is updated not to cause
      mysql-pe to fail the regression tests run.
[6 Oct 2009 9:01] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:li-bing.song@sun.com-20090928022306-m88jkwh9flnbvwl7) (merge vers: 5.1.40) (pib:11)
[9 Oct 2009 8:01] 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/86275

3622 Li-Bing.Song@sun.com	2009-10-09
      BUG #46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior in ROW mode
      
      Postfix
[12 Oct 2009 2:20] 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/86504

3645 Li-Bing.Song@sun.com	2009-10-12
      Bug #46572 DROP TEMPORARY table IF EXISTS does not have a consistent behavior in ROW mode
      
      Postfix
[22 Oct 2009 6:36] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:08] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 17:23] Jon Stephens
Bugfix documented in the 5.1.40, 5.5.0, and 6.0.14 changelogs, as follows:

        When using row-based replication, DROP TEMPORARY TABLE IF EXISTS
        was written to the binary log if the table named in the
        statement did not exist, even though DROP TEMPORARY TABLE
        statement should never be logged in row-based mode, whether the
        table exists or not.

Closed.
[18 Dec 2009 10:37] 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:52] 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:07] 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:22] 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 11:06] Jon Stephens
No additional changelog entries needed. Closed.