Bug #64483 Unnecessary "drop temporary table" in RBR binlog
Submitted: 28 Feb 2012 12:54 Modified: 10 Sep 2012 13:34
Reporter: Sergey Zhuravlev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.5.21 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[28 Feb 2012 12:54] Sergey Zhuravlev
Description:
We have server with row-based replication.
When we create temporary table, no events written to binlog, it is good and correspond to the documentation.
But when we disconnect, master generate unnecessary event "drop temporary table" and write it to binlog.

How to repeat:
1. create temporary table
2. disconnect
3. look at binlog

zhur@zhur-laptop:~/sandboxes/rsandbox_5_5_21$ ./m
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.21-log MySQL Community Server (GPL)

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.

master [localhost] {msandbox} ((none)) > flush logs;
Query OK, 0 rows affected (0.02 sec)

master [localhost] {msandbox} ((none)) > use test;
Database changed
master [localhost] {msandbox} (test) > create temporary table test (i int);
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} (test) > Bye

zhur@zhur-laptop:~/sandboxes/rsandbox_5_5_21$ ./m
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.21-log MySQL Community Server (GPL)

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.

master [localhost] {msandbox} ((none)) > show binlog events in 'mysql-bin.000003';
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                          |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.21-log, Binlog ver: 4                         |
| mysql-bin.000003 | 107 | Query       |         1 |         219 | use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test` |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

master [localhost] {msandbox} ((none)) > show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

Suggested fix:
Don't write to RBR binlog "drop temporary table" events;
[28 Feb 2012 13:42] Valeriy Kravchuk
Thank you for the problem report. Verified on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> create temporary table test (i int);
Query OK, 0 rows affected (0.14 sec)

mysql> Ctrl-C -- exit!
Bye

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| toshiba-user-bin.000004 |      219 |              |                  |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.11 sec)

mysql> show binlog events in 'toshiba-user-bin.000004';
+-------------------------+-----+-------------+-----------+-------------+-------
--------------------------------------------------------+
| Log_name                | Pos | Event_type  | Server_id | End_log_pos | Info
                                                        |
+-------------------------+-----+-------------+-----------+-------------+-------
--------------------------------------------------------+
| toshiba-user-bin.000004 |   4 | Format_desc |         1 |         107 | Server
 ver: 5.5.21-log, Binlog ver: 4                         |
| toshiba-user-bin.000004 | 107 | Query       |         1 |         219 | use `t
est`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test` |
+-------------------------+-----+-------------+-----------+-------------+-------
--------------------------------------------------------+
2 rows in set (0.00 sec)
[10 Sep 2012 11:53] Jon Stephens
This is expected behaviour; changed bug category to Docs and assigned to myself for handling.
[10 Sep 2012 13:34] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.