MySQL Bugs: #46572: DROP TEMPORARY table IF EXISTS does not have a consistent behavior in ROW mode

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 Tavares Correia Junior	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

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

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'.

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.

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.

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.

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.

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.

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

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)

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

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

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)

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)

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.

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.