MySQL Bugs: #48426: Why mysqlbinlog cannot print DML/DDL operation with objects like 'DATABASE.TABL

Bug #48426	Why mysqlbinlog cannot print DML/DDL operation with objects like 'DATABASE.TABL
Submitted:	30 Oct 2009 6:00	Modified:	18 Jan 2010 2:51
Reporter:	He yunfei	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.0.68-enterprise, 5.1.41-bzr	OS:	Linux (5.3)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	mysqlbinlog --database =name Mysql-bin.LOG

Description:
Entironment:
----------------
OS: Red Hat Enterprise Linux Server release 5.3 (Tikanga)
MYSQL: server v 5.0.68-enterprise-gpl-log
LONG_BIT: 64

Describe:
---------------
mysqlbinlog --database=TEST mysql_bin.000001 

The command above cannot print all operations with object like 'TEST.TABLE_NAME'.
It just print operations after "use" command.

How to repeat:
mysql
flush logs;
show master status ;     ### MASTER_LOG  : mysql-bin.000023
create table test.t1 (name char(10)); 
insert into test.t1 values('aaa'); 
use test; 
insert into test.t1 values('bbb');
insert into t1 values('ccc');
exit; 

[root]#  mysqlbinlog --database=test mysql-bin.000023:
---------  ---------  ---------  ---------  ---------  ---------  ---------  
---------  ---------  ---------  ---------  ---------  ---------  ---------  
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#091030 13:37:50 server id 2  end_log_pos 98    Start: binlog v 4, server v 5.0.68-enterprise-gpl-log created 091030 13:37:50
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 98
# at 198
# at 267
# at 364
#091030 13:38:01 server id 2  end_log_pos 391   Xid = 13809368
COMMIT/*!*/;
# at 391
#091030 13:38:05 server id 2  end_log_pos 464   Query   thread_id=44    exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1256881085/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
BEGIN
/*!*/;
# at 464
#091030 13:38:05 server id 2  end_log_pos 565   Query   thread_id=44    exec_time=0     error_code=0
SET TIMESTAMP=1256881085/*!*/;
insert into test.t1 values('bbb')
/*!*/;
# at 565
#091030 13:38:05 server id 2  end_log_pos 592   Xid = 13809371
COMMIT/*!*/;
# at 592
#091030 13:38:05 server id 2  end_log_pos 665   Query   thread_id=44    exec_time=0     error_code=0
SET TIMESTAMP=1256881085/*!*/;
BEGIN
/*!*/;
# at 665
#091030 13:38:05 server id 2  end_log_pos 761   Query   thread_id=44    exec_time=0     error_code=0
SET TIMESTAMP=1256881085/*!*/;
insert into t1 values('ccc')
/*!*/;
# at 761
#091030 13:38:05 server id 2  end_log_pos 788   Xid = 13809372
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
---------  ---------  ---------  ---------  ---------  ---------  ---------  
---------  ---------  ---------  ---------  ---------  ---------  --------- 

Suggested fix:
In fact , I want to print all operations with database "test" within scene above .

Is there anyway to get all operations ?

Verified just as described with recent 5.1.41 from bzr. It can be intended behavior, but documentation (http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html) says nothing that 'use database' matters in this case.

The description for the --database option says:

"List entries for just this database (local log only). You can only specify one database with this option; if you specify multiple --database options, only the last one is used. This option causes mysqlbinlog to output entries from the binary log where the default database (that is, the one selected by USE) is db_name. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database."

The relevant part of that is "the one selected by USE". If the database has not been selected by USE, mysqlbinlog does not print statements for that database.

But this could be clearer. I'll check into that.

Also, in answer to your question: "Is there anyway to get all operations ?"

For mysqlbinlog --database=test, no, not until "USE test" selects test as the default database.

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.

Updated --database description at http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html#option_mysqlbinlog_database:

This option causes mysqlbinlog to output entries from the binary log
(local log only) that occur while db_name is been selected as the
default database by USE. The option works as follows:

* Unless db_name is selected as the default database, statements are
 not output, even if they modify tables in db_name.

* While db_name is the default database, statements are output whether
 they modify tables in db_name or a different database.

* There is an exception for CREATE DATABASE, ALTER DATABASE, and DROP
 DATABASE. The database being created, altered, or dropped is
 considered to be the default database when determining whether to
 output the statement.

Suppose that the binary log contains these statements:

INSERT INTO test.t1 (i) VALUES(100);
INSERT INTO db2.t2 (j)  VALUES(200);
USE test;
INSERT INTO test.t1 (i) VALUES(101);
INSERT INTO t1 (i)      VALUES(102);
INSERT INTO db2.t2 (j)  VALUES(201);
USE db2;
INSERT INTO test.t1 (i) VALUES(103);
INSERT INTO db2.t2 (j)  VALUES(202);
INSERT INTO t2 (j)      VALUES(203);

mysqlbinlog --database=test will not output the first two INSERT
statements because there is no default database. It will output the
three INSERT statements following USE test, but not the three INSERT
statements following USE db2.

mysqlbinlog --database=db2 will not output the first two INSERT
statements because there is no default database. It will not output
the three INSERT statements following USE test, but will output the
three INSERT statements following USE db2.

The --database option is similar to the --binlog-do-db option for
mysqld, but can be used to specify only one database. If --database
is given multiple times, only the last instance is used.

HI, all
 thanks for reply.
 But I don't think that is a good idea. 
 First, If you output the statement for other DBs . then user did follow operations: 
   1>> mysqlbinlog  mysql-binlog.0000001 --database=hh1 >> /tmp/aa.sql   
   # We supposed "aa.sql" include statement of DB:HH2 
   
   2>> scp /tmp/aa.sql otherhost
   
   # On "otherhost" , and there is no DB :HH2
   3>> mysql -uroot < /tmp/aa.sql
   
   I am sure there will be  lots of "errors" during the above operations,Since there is no DB named HH2.
   Even if there is a DB named HH2, the apply log unnecessarily is correct .
 
  In fact , My original intention is to Use "mysqlbinlog --database " to recover my database in following scene: 
    1) There is two DB in MYSQL : DB1 , DB-aa , 
    2) Both DBs backup with two mysqldump satement at "T1" like : 
       mysqldump  -uroot DB1 > bak-DB1.sql 
       mysqldump  -uroot DB-aa > bak-DB-aa.sql
    3) at moment "T2" , For some reason , I want to recover database DB1 to other "MYSQL SERVER".
       1>> I need to rerun bak-DB1.sql first : 
          mysql -uroot -h127.0.0.1 < bak-DB1.sql 
       2>> I need to apply all binlog from "T1" to "T2" . 
          Then ,just at here , the problem is I can not to print all statement for "DB1" with "mysqlbinlog --database" ,
          Since there were some sql satement may not run after "use DB1" . 
          Off course, The best secene is all satement for DB1 is run after "use DB1" . 
          But I can't control user's behavior while use database. 
          as a DBA, we can making some correct suggestion and we also need a better tools for this problem. 
   Thanks.

I was concerned to describe actual behavior.

I think we should modified mysqlbinlog's source code only.And the function is very useful for us,we hope to alter it,thinks!

The behavior for mysqlbinlog --database is similar to that for mysqld --binlog-do-db. Changing mysqlbinlog would cause it to process binary log contents in a way that is inconsistent with server behavior.