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

• Submitted: 13 Aug 2008 18:21
• Modified: 2 Sep 2008 14:32
• Reporter: Mats Kindahl
• Status: Closed
• Category: Server: RBR
• Severity: S3 (Non-critical)
• Version: 5.1, 6.0 BZR
• OS: Any
• Assigned to: Mats Kindahl
• Target Version: 5.1.28
• Triage: D2 (Serious) / R2 (Low) / E2 (Low)

[13 Aug 2008 18:21] Mats Kindahl

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.

[13 Aug 2008 21:00] Sveta Smirnova

Thank you for the report.

Verified as described.

[13 Aug 2008 21:46] 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/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.

[20 Aug 2008 10:07] 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/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.

[26 Aug 2008 16:04] 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/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.

[27 Aug 2008 10: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/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.

[28 Aug 2008 11:07] Bugs System

Pushed into 5.1.28  (revid:[e-mail]-20080827084011-p6qbsfgvc2plcnih) (version source
revid:[e-mail]-20080828082606-fgzd1k01lux96jf4) (pib:3)

[2 Sep 2008 14:32] Jon Stephens

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.

[14 Sep 2008 5:30] Bugs System

Pushed into 6.0.8-alpha  (revid:[e-mail]-20080827084011-p6qbsfgvc2plcnih) (version
source revid:[e-mail]-20080911140522-3cmh8tsfep5j6dsj) (pib:3)

[30 Jan 14:28] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090129165607-wiskabxm948yx463)
(version source revid:[e-mail]-20090129163120-e2ntks4wgpqde6zt) (merge vers:
6.0.10-alpha) (pib:6)

[30 Jan 16:08] Bugs System

Pushed into 5.1.32 (revid:[e-mail]-20090129165946-d6jnnfqfokuzr09y) (version
source revid:[e-mail]-20080820080650-j6t5wnp9jncq5i3q) (merge vers: 5.1.28) (pib:6)

[17 Feb 15:55] Bugs System

Pushed into 5.1.32-ndb-6.3.23 (revid:[e-mail]-20090217131017-6u8qz1edkjfiobef)
(version source revid:[e-mail]-20090203133556-9rclp06ol19bmzs4) (merge vers:
5.1.32-ndb-6.3.22) (pib:6)

[17 Feb 17:43] Bugs System

Pushed into 5.1.32-ndb-6.4.3 (revid:[e-mail]-20090217134419-5ha6xg4dpedrbmau)
(version source revid:[e-mail]-20090203133556-9rclp06ol19bmzs4) (merge vers:
5.1.32-ndb-6.3.22) (pib:6)

[17 Feb 19:19] Bugs System

Pushed into 5.1.32-ndb-6.2.17 (revid:[e-mail]-20090217134216-5699eq74ws4oxa0j)
(version source revid:[e-mail]-20090201210519-vehobc4sy3g9s38e) (merge vers:
5.1.32-ndb-6.2.17) (pib:6)