Bug #44129 MySQL don't write query in binlog
Submitted: 7 Apr 2009 9:31 Modified: 7 Apr 2009 10:19
Reporter: Alex Sheshenya Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.33 OS:Linux (Red Hat Enterprise Linux Server release 5.2 (Tikanga))
Assigned to: CPU Architecture:Any
Tags: binlog, replication

[7 Apr 2009 9:31] Alex Sheshenya
Description:
MySQL master don't write query (create, insert, alter, update) in binlog when don't use select database and use query like database_name.table_name

my.cnf
binlog_cache_size = 1M
sync_binlog = 1
transaction_isolation = REPEATABLE-READ
log-bin=mysql-bin
binlog_ignore_db = mysql,information_schema
binlog_format=mixed
general_log
innodb_flush_log_at_trx_commit = 1

How to repeat:
database "test" empty.

mysqlbinlog -d test mysql-bin.000027
#090407 14:20:07 server id 1  end_log_pos 8144  Xid = 9551
COMMIT/*!*/;
# at 8144
# at 8213
# at 8469
# at 8640
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

[root@localhost:mysql] mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 5.1.33-community-log MySQL Community Server (GPL)

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

mysql> create table test.test (f1 INT) engine=InnoDB;
query OK, 0 rows affected (0.01 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysqlbinlog -d test mysql-bin.000027
#090407 14:20:07 server id 1  end_log_pos 8144  Xid = 9551
COMMIT/*!*/;
# at 8144
# at 8213
# at 8469
# at 8640
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

in binlog same data.
ok, now use database.
mysql> use test;
Database changed
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)
mysql> create table test.test (f1 INT) engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
and see binlog:
mysqlbinlog -d test mysql-bin.000027
#090407 14:20:07 server id 1  end_log_pos 8144  Xid = 9551
COMMIT/*!*/;
# at 8144
# at 8213
# at 8469
# at 8640
# at 8710
# at 8787
# at 9067
#090407 14:25:06 server id 1  end_log_pos 9094  Xid = 10762
COMMIT/*!*/;
# at 9094
# at 9163
# at 9419
# at 9590
# at 9660
#090407 14:25:29 server id 1  end_log_pos 9738  Query   thread_id=71    exec_time=0     error_code=0
SET TIMESTAMP=1239103529/*!*/;
drop table test
/*!*/;
# at 9738
#090407 14:25:48 server id 1  end_log_pos 9846  Query   thread_id=71    exec_time=0     error_code=0
SET TIMESTAMP=1239103548/*!*/;
create table test.test (f1 INT) engine=InnoDB
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

drop table and create table write in binlog.

Suggested fix:
Always use select database before any query to MySQL.
[7 Apr 2009 9:40] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/binary-log.html:

Evaluation of update selection options.   The server evaluates the options for logging or ignoring updates to the binary log according to the following rules: 

...

# There are some rules (--binlog-do-db, --binlog-ignore-db, or both). Is there a default database (has any database been selected by USE?)?

    * No: Do not write the statement, and exit.
    * Yes: Go to the next step.
[7 Apr 2009 10:19] Alex Sheshenya
Thanks.
But I think if mysql allow query like database.table more rigt check query. If we use database.table query then use Yes: Go to the next step.