MySQL Bugs: #49635: rename table db1.tbl to db2.tbl does not always get logged in the binary logs

Bug #49635	rename table db1.tbl to db2.tbl does not always get logged in the binary logs
Submitted:	11 Dec 2009 22:58	Modified:	14 Feb 2010 13:15
Reporter:	Siddharth Taneja	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.77	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	binary logs, rename table

Description:
I see that an operation like 'rename table db1.tbl to db2.tbl' does not always get logged into the binary logs. 

In particular, a new client connection with no database selected fails to log this command; a new client connection with any database selected except 'db1' or 'db2' also fails to log this command.

How to repeat:
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> use test1;
Database changed

mysql> create table testing (ch varchar(255));
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

myhost:~#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 978923
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> rename table test1.testing to test2.testing;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000010 | 151078428 | 
| mysql-bin.000011 | 170657737 | 
| mysql-bin.000012 |    590249 | 
| mysql-bin.000013 |     46808 | 
| mysql-bin.000014 |     14848 | 
| mysql-bin.000015 |    265272 | 
+------------------+-----------+

myhost:~#echo "show binlog events in 'mysql-bin.000015'"  | mysql | grep "test"
mysql-bin.000015	2309	Query	1	2394	create database test1
mysql-bin.000015	9282	Query	1	9384	use `test1`; create table testing (ch varchar(255))
mysql-bin.000015	13952	Query	1	14037	create database test2

Thank you for the problem report. Please, send your my.cnf file content.

my.cnf file - only a few database ignored and test* is not one of them

Attachment: my.cnf (application/octet-stream, text), 1.25 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I did provide the my.cnf file. See comment and attachment on 12 Dec 2009 17:46.

Please, check with a newer version, 5.0.89, and inform about the results. Look:

77-52-1-11:5.0 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.0.90-debug-log Source distribution

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

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> use test1;
Database changed
mysql> create table testing(c1 char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> rename table test1.testing to test2.testing;
Query OK, 0 rows affected (0.00 sec)

mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| testing         | 
+-----------------+
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| openxs.000002 |      472 |              |                  | 
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'openxs.000002';
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                                     |
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
| openxs.000002 |   4 | Format_desc |         1 |          98 | Server ver: 5.0.90-debug-log, Binlog ver: 4              | 
| openxs.000002 |  98 | Query       |         1 |         183 | create database test1                                    | 
| openxs.000002 | 183 | Query       |         1 |         280 | use `test1`; create table testing(c1 char(10))           | 
| openxs.000002 | 280 | Query       |         1 |         365 | create database test2                                    | 
| openxs.000002 | 365 | Query       |         1 |         472 | use `test1`; rename table test1.testing to test2.testing | 
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
5 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".