| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.5.21 | OS: | Any |
| Assigned to: | Jon Stephens | CPU Architecture: | Any |
[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.
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;