MySQL Bugs: #43296: binlog format affects on binlog_ignore

Bug #43296	binlog format affects on binlog_ignore_db behavior not documented
Submitted:	2 Mar 2009 7:17	Modified:	17 Mar 2009 17:39
Reporter:	alex yurchenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	binlog_format, binlog_ignore_db, row

Description:
According to http://ftp.ku.ac.th/pub/mirror/mysql/doc/refman/5.1/en/binary-log.html:

If the server is started with binlog-ignore-db=sales, and you run USE prices; UPDATE sales.january SET amount=amount+1000;, this statement _is_ written into the binary log.

This does not seem to happen with binlog_format=ROW: update to sales won't be logged.

How to repeat:
see description

Suggested fix:
make binlog_format=ROW follow the same rules as binlog_format=STATEMENT and which are documented

Thank you for the report.

I can not repeat described behavior.

Please describe how you did check what statement was not written to the binary log file?

I checked it by comparing contents of the binlog file before and after issuing the UPDATE statement. They were identical. No matter how many times I update a table in the ignored database, binlog contents does not change, even though updates to default database (set by USE statement) are logged (binlog contents changes).

Thank you for the feedback.

In case of row-based replication changed data is written to the binary log file, not statement. Probably UPDATE statement just didn't update any row? What is the value of "Rows changed" after this problem UPDATE?

Well, I'm talking specifically about _binary_ log. Perhaps I should also mention that I'm using InnoDB engine and AUTOCOMMIT=ON. MySQL was installed from the official MySQL-server-5.1.31-0.glibc23.i386.rpm. The following sequence of commands does not get logged (binlog_ignore_db=tungsten):

mysql> use test;
Database changed
mysql> select * from tungsten.trep_commit_seqno;
+-------+
| seqno |
+-------+
|    -1 | 
+-------+
1 row in set (0.00 sec)

mysql> update tungsten.trep_commit_seqno set seqno=0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tungsten.trep_commit_seqno;
+-------+
| seqno |
+-------+
|     0 | 
+-------+
1 row in set (0.01 sec)

mysql> update tungsten.trep_commit_seqno set seqno=-1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tungsten.trep_commit_seqno;
+-------+
| seqno |
+-------+
|    -1 | 
+-------+
1 row in set (0.00 sec)

At the same time I can clearly see that

mysql> update test.auto set value=0;
Query OK, 12 rows affected (0.03 sec)
Rows matched: 12  Changed: 12  Warnings: 0

updates binlog quite nicely
Both tables use InnoDB engine.

Thank you for the feedback.

Verified as described. Really I did wrong test and problem is repeatable with any storage engine.

Test case for our test suite showing problem with replication:

$cat rpl_bug43296.test
--source include/master-slave.inc

connection slave;

create database db1;

connection master;

create database db1;

use test;

create table db1.t1(f1 int);

insert into db1.t1 values(1);

select * from db1.t1;

--sleep 1

connection slave;

show databases;

select * from db1.t1;

--vertical_results
show slave status;

$cat rpl_bug43296-master.opt
--binlog_format=row --binlog-ignore-db=db1

Unfortunately, this is a documentation bug. The behavior of binlog-do-db and binlog-ignore-db is different between row-based replication and statement-based replication. The difference in behavior is documented for replicate-do-db and replication-ignore-db under http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replic... and the same applies to binlog-do-db and binlog-ignore-db.

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 Synopsis to reflect actual nature of problem.