MySQL Bugs: #38773: DROP DATABASE cause switch to stmt-mode when there are temporary tables open

Bug #38773	DROP DATABASE cause switch to stmt-mode when there are temporary tables open
Submitted:	13 Aug 2008 16:21	Modified:	2 Sep 2008 12:32
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1, 6.0 BZR	OS:	Any
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
When using DROP DATABASE to drop a database and there are temporary tables open, it will cause a switch to statement-based mode so that the following statements are replicated as statements.

How to repeat:
Execute the following SQL:

SET BINLOG_FORMAT=ROW;
CREATE TEMPORARY TABLE tt1 (a INT);
CREATE TABLE t2 (a INT);
INSERT INTO t2 VALUES (1);
DROP DATABASE IF EXISTS mysqltest1;
INSERT INTO t2 VALUES (1);
DROP TABLE t2;
SHOW BINLOG EVENTS;

Observe that the INSERT before the DROP DATABASE is written in row-based mode while the INSERT after the DROP DATABASE is written in statement-based mode.

Suggested fix:
Disable the binary log for the execution of the DROP DATABASE statement instead of clearing the thread variable current_stmt_binlog_row_based. The variable will not be reverted at the end of the statement since there are temporary tables open, so the field should not be cleared. However, the DROP DATABASE statement might write changes when removing objects in the database, so it is necessary to disable the binary log for the duration of the execution of the statement.

Thank you for the report.

Verified 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/51564

2658 Mats Kindahl	2008-08-13
      Bug #38773: DROP DATABASE cause switch to stmt-mode when there are temporary tables open
      
      When executing a DROP DATABASE statement in ROW mode and having temporary
      tables open at the same time, the existance of temporary tables prevent
      the server from switching back to row mode after temporarily switching to
      statement mode to handle the logging of the statement.
      
      Fixed the problem by removing the code to switch to statement mode and added
      code to temporarily disable the binary log while dropping the objects in the
      database.

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

2658 Mats Kindahl	2008-08-20
      Bug #38773: DROP DATABASE cause switch to stmt-mode when there are temporary tables open
      
      When executing a DROP DATABASE statement in ROW mode and having temporary
      tables open at the same time, the existance of temporary tables prevent
      the server from switching back to row mode after temporarily switching to
      statement mode to handle the logging of the statement.
      
      Fixed the problem by removing the code to switch to statement mode and added
      code to temporarily disable the binary log while dropping the objects in the
      database.

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

2718 Mats Kindahl	2008-08-26
      Bug #38773: DROP DATABASE cause switch to stmt-mode when there are temporary tables open
      
      When executing a DROP DATABASE statement in ROW mode and having temporary
      tables open at the same time, the existance of temporary tables prevent
      the server from switching back to row mode after temporarily switching to
      statement mode to handle the logging of the statement.
      
      Fixed the problem by removing the code to switch to statement mode and added
      code to temporarily disable the binary log while dropping the objects in the
      database.

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

2708 Mats Kindahl	2008-08-27
      Bug #38773: DROP DATABASE cause switch to stmt-mode when there are temporary
                  tables open
      
      When executing a DROP DATABASE statement in ROW mode and having temporary
      tables open at the same time, the existance of temporary tables prevent
      the server from switching back to row mode after temporarily switching to
      statement mode to handle the logging of the statement.
      
      Fixed the problem by removing the code to switch to statement mode and added
      code to temporarily disable the binary log while dropping the objects in the
      database.

Pushed into 5.1.28  (revid:mats@mysql.com-20080827084011-p6qbsfgvc2plcnih) (version source revid:kgeorge@mysql.com-20080828082606-fgzd1k01lux96jf4) (pib:3)

Documented in the 5.1.27 changelog as follows:

        Issuing a DROP DATABASE while any temporary tables
        were open caused the server to switch to statement-based mode.

Pushed into 6.0.8-alpha  (revid:mats@mysql.com-20080827084011-p6qbsfgvc2plcnih) (version source revid:konstantin@mysql.com-20080911140522-3cmh8tsfep5j6dsj) (pib:3)

Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32 (revid:luis.soares@sun.com-20090129165946-d6jnnfqfokuzr09y) (version source revid:mats@mysql.com-20080820080650-j6t5wnp9jncq5i3q) (merge vers: 5.1.28) (pib:6)

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090201210519-vehobc4sy3g9s38e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)