Bug #66391 DROP TEMPORARY TABLE command logged to binary log even when using RBR
Submitted: 14 Aug 2012 20:59 Modified: 16 Jan 2013 19:37
Reporter: Chris Calender Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.5.27, 5.5.28, 5.5.22 OS:Any (Linux, Windows)
Assigned to: CPU Architecture:Any
Tags: drop temporary table, RBR, row-based binary log format
Triage: Needs Triage: D3 (Medium)

[14 Aug 2012 20:59] Chris Calender
Description:
DROP TEMPORARY TABLE commands are logged to binary log even when the binlog_format is Row-Based Binary Log format (RBR).

Per the manual, this should not occur:

"This section does not apply when row-based replication is in use because in that case temporary tables are not replicated."

http://dev.mysql.com/doc/refman/5.5/en/replication-features-temptables.html

How to repeat:
1. Start MySQL with binary logging enabled and set binlog_format=ROW.

2. CREATE TEMPORARY TABLE t1 (id INT);

3. INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

3. DROP TEMPORARY TABLE t1;

4. Check Binary log with mysqlbinlog:

# at 530
#120814 16:51:13 server id 3  end_log_pos 654   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1344977473/*!*/;
DROP TEMPORARY TABLE IF EXISTS `t1` /* generated by server */
/*!*/;

* The above should not be there per the manual.

** Also, you will note the CREATE nor INSERT did not make it into the binary log, which is correct per the manual.

Suggested fix:
Simply do not write DROP TEMPORARY TABLE (or DROP TABLE if the table being dropped is a TEMPORARY TABLE) to the binary log when RBR is in use, per the manual.

This bug could be somewhat similar to the following:

http://bugs.mysql.com/bug.php?id=64483

Though the above bugs logs a DROP TEMPORARY TABLE command in RBR when the client disconnects and *does not* issue the DROP TEMPORARY TABLE command, whereas in this new case, the DROP TEMPORARY TABLE command is explicitly issued.
[15 Aug 2012 10:05] Richard Bensley
Having the same problem on MySQL 5.5.22 on Solaris, with ROW format enabled.

We also have replication filters enabled, to ignore some databases and tables, but the "DROP (TEMPORARY) TABLE ...." statements are still replicated.

The statements are visible in the slave replay, processlist and general logs.
[15 Aug 2012 23:18] Chris Calender
To help speed up the fix, this almost seems like a regression of the following bug:

http://bugs.mysql.com/bug.php?id=46572

Take specific note of the "changelog" comment:

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

Thus, back in versions 5.1.40, 5.5.0, and 6.0.14, the Devs stated that "even though DROP TEMPORARY TABLE statement should never be logged in row-based mode, whether the table exists or not".

I'm slightly confused if this was in fact fixed in 5.5 or not.  If it was, it is a regression.  If not, then the Devs were aware of the faulty behavior at the time, and it is clearly a bug that needs fixed.

The question in file to be updated would be sql/sql_table.cc

Check the function mysql_rm_table_no_locks(...)

In it, you will see faulty reasoning in the "comments":

3 - If the current format is row, the IF EXISTS token needs to be
appended because one does not know if CREATE TEMPORARY was previously
written to the binary log.

Obviously this comment shows the faulty logic because both the documentation and bug #46572 clearly state that when using RBR, temporary tables should not be replicated, hence they should not be written to the binlog at all.
[16 Aug 2012 8:51] Richard Bensley
My steps to recreate on MySQL 5.5.27 on Linux 64bit and MySQL 5.5.22 on Solaris:

I have a replication filter: replicate_wild_ingore_table=%.tt_% and binlog_format=ROW.
(You can also use a specific DB, same result i.e. test.tt_%).

On the Slave(s):
SET GENERAL_LOG=ON;
tail -f /path/to/general.log

On the Master:
SHOW VARIABLES LIKE 'binlog_format';
USE test;
CREATE TEMPORARY TABLE tt_mytable (a int);
INSERT INTO tt_mytable VALUES (1),(2),(3);
DROP TEMPORARY TABLE IF EXISTS tt_mytable;

Low and behold, the drop table statement will be in the slave relay, and general log. And in the binlog if log_slave_updates is enabled.

On the master you can also do:
USE test;
CREATE TEMPORARY TABLE tt_mytable (a int);
INSERT INTO tt_mytable VALUES (1),(2),(3);
CREATE TABLE t SELECT * FROM tt_mytable;
DROP TEMPORARY TABLE IF EXISTS tt_mytable;

This replicates fine. But the DROP statement STILL replicates.
[19 Aug 2012 13:41] Valeriy Kravchuk
Verified just as described on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'binlog%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_cache_size                       | 32768 |
| binlog_direct_non_transactional_updates | OFF   |
| binlog_format                           | ROW   |
| binlog_stmt_cache_size                  | 32768 |
+-----------------------------------------+-------+
4 rows in set (0.00 sec)

mysql> CREATE TEMPORARY TABLE t1 (id INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> DROP TEMPORARY TABLE t1;
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| macbook-pro-bin.000008 |      407 |              |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.03 sec)

mysql> show binlog events in 'macbook-pro-bin.000008';
+------------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------+
| Log_name               | Pos | Event_type  | Server_id | End_log_pos | Info                                                                      |
+------------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------+
| macbook-pro-bin.000008 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.28-debug-log, Binlog ver: 4                               |
| macbook-pro-bin.000008 | 107 | Query       |         1 |         193 | use `test`; DELETE FROM `test`.`m1`                                       |
| macbook-pro-bin.000008 | 193 | Query       |         1 |         283 | use `test`; DELETE FROM `test`.`table1`                                   |
| macbook-pro-bin.000008 | 283 | Query       |         1 |         407 | use `test`; DROP TEMPORARY TABLE IF EXISTS `t1` /* generated by server */ |
+------------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------+
4 rows in set (0.02 sec)
[20 Aug 2012 3:14] Chris Calender
Fix:

Add the following to:

File sql/sql_table.cc, Function: int mysql_rm_table_no_locks(...)

After comments numberd 1-5, and before the main if ("if (!dont_log_query) {"):

if (thd->is_current_stmt_binlog_format_row() && drop_temporary) {
  dont_log_query = TRUE;
}

After this, the DROP TEMPORARY TABLE is not added to the binlog.

This is added to line 2025 from the latest source of 5.5.27 I pulled.

I can provide in another way if needed.
[10 Jan 2013 14:02] Erlend Dahl
Fixed as duplicate of (documentation) bug#64483
[16 Jan 2013 19:37] Chris Calender
For anyone interested, here is the new entry in the manual:

"RBL, RBR, and temporary tables. As noted in Section 16.4.1.21, “Replication and Temporary Tables”, temporary tables are not replicated when using row-based format. When mixed format is in effect, “safe” statements involving temporary tables are logged using statement-based format. For more information, see Section 16.1.2.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

Temporary tables are not replicated when using row-based format because there is no need. In addition, because temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based format.

Beginning with MySQL 5.5.5, you can switch from statement-based to row-based binary logging mode even when temporary tables have been created. However, while using the row-based format, the MySQL server cannot determine the logging mode that was in effect when a given temporary table was created. For this reason, the server in such cases logs a DROP TEMPORARY TABLE IF EXISTS statement for each temporary table that still exists for a given client session when that session ends. (Bug #11760229, Bug #11762267) While this means that it is possible that an unnecessary DROP TEMPORARY TABLE statement might be logged in some cases, the statement is harmless, and does not cause an error even if the table does not exist, due to the presence of the IF NOT EXISTS option."

http://dev.mysql.com/doc/refman/5.5/en/replication-rbr-usage.html#replication-rbr-usage-te...

And it is briefly mentioned on this page (which is referenced in the above snippet):

"16.4.1.21. Replication and Temporary Tables

This section does not apply when row-based replication is in use because in that case temporary tables are not replicated."

http://dev.mysql.com/doc/refman/5.5/en/replication-features-temptables.html