MySQL Bugs: #56366: binlog-ignore-db and ALTER TABLE with RBL

Bug #56366	binlog-ignore-db and ALTER TABLE with RBL
Submitted:	30 Aug 2010 13:24	Modified:	17 Sep 2010 17:28
Reporter:	Johannes Becker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.46sp1-enterprise-gpl-advanced-log	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	ALTER TABLE, RBL, RBR

Description:
In some cases we had Replication-Errors with different reasons like DUPLICATE KEY, Table-Structure not in sync, etc. After some investigation we pointed out that even though we use row-based logging some Queries seem to be falsely affected by "binlog-ignore-db" and therefore not written into the binlog.

How to repeat:
Master-Configuration:
---
server-id = 1001
binlog-ignore-db = mysql
binlog-ignore-db = phpmyadmin
binlog-format = ROW

Test-Table:
---
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Query:
---
Now connecting to the Master without specifying a default database and doing the following Query generates NO entry in the binlog:

ALTER TABLE test.test ADD `test` VARCHAR(50) NULL

Commenting out the binlog-ignore-db configuration from the my.cnf, restarting the mysql-server and issuing the query again generates the binlog-entry as expected.

Suggested fix:
Handle this like documented in http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b...
"The current database has no effect."

Best regards,

Thank you for the report.

Verified as described. Docs quote for reference:

Row-based format.   Tells the server not to log updates to any tables in the database db_name. The current database has no effect.

ANALYSIS
========

I think this is not a bug. ALTER TABLE is a DDL statement [1] and
as such is always logged as *Statement*. Thence, it must follow
the filtering restrictions from Statement based logging [2], in
particular:

"
--binlog-ignore-db=db_name
 
  [...]

  Statement-based logging.  

    Tells the server to not log any statement where the default
    database (that is, the one selected by USE) is db_name.
"

This happens for DDL, not for DML.

HANDS ON
========

I have tested the scenario posted in the bug report.

Environment
-----------

 -  tree: mysql-5.1-bugteam
 - revid: mattias.jonsson@oracle.com-20100913140750-7ntduuhi5ahrpr8v
 -  tool: MySQL Test Run (MTR)

Teste case (binlog_bug56366.test)
---------------------------------

-- source include/have_binlog_format_row.inc

CREATE TABLE t1 (id int(11) NOT NULL AUTO_INCREMENT,
                 value varchar(255) NOT NULL,
                 PRIMARY KEY (`id`)) ENGINE=MyISAM;

CREATE DATABASE db_bug56366;
USE db_bug56366;

# voids default database for the current sesssion
DROP DATABASE db_bug56366;

# DML: this is logged as RBR, thence there is no
#      filtering issue due to the absence of default
#      database
INSERT INTO test.t1 VALUES (1,'a');

# DDL: These are logged as SBR, thence are will
#      not be written to the binary log, because
#      their default database is void and there are
#      filtering rules set
ALTER TABLE test.t1 ADD test VARCHAR(50) NULL;
DROP TABLE test.t1;

-- source include/show_binlog_events.inc

-- exit

Configuration (binlog_bug56366-master.opt)
------------------------------------------

--binlog-ignore-db=dbx

Command line
------------

$ perl mysql-test-run.pl binlog.binlog_bug56366

The result from this test case shows:

(...)

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
binlog.binlog_x 'stmt'                   [ skipped ]  Doesn't support --binlog-format='statement'
binlog.binlog_x 'mix'                    [ skipped ]  Doesn't support --binlog-format='mixed'
CREATE TABLE t1 (id int(11) NOT NULL AUTO_INCREMENT,
value varchar(255) NOT NULL,
PRIMARY KEY (`id`)) ENGINE=MyISAM;
CREATE DATABASE db_bug56366;
USE db_bug56366;
DROP DATABASE db_bug56366;
INSERT INTO test.t1 VALUES (1,'a');
ALTER TABLE test.t1 ADD test VARCHAR(50) NULL;
DROP TABLE test.t1;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (id int(11) NOT NULL AUTO_INCREMENT,
value varchar(255) NOT NULL,
PRIMARY KEY (`id`)) ENGINE=MyISAM
master-bin.000001	#	Query	#	#	CREATE DATABASE db_bug56366
master-bin.000001	#	Query	#	#	DROP DATABASE db_bug56366
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	COMMIT
binlog.binlog_bug56366 'row'                    [ pass ]    147
(...)

Notes
-----

  N1. the DML: "INSERT INTO test.t1 VALUES (1,'a');" was
      logged (as ROW), despite the fact that there was no default
      database.

  N2. None of the ALTER or DROP TABLE was logged, because they
      are logged as statement and the server cannot tell whether
      to log these or not when there are filtering rules set and
      no default database exists.

  N3. Removing the binlog_bug56366-master.opt, ie, removing the
      filtering rules, then both the ALTER and DROP TABLE are
      written to the binary log.

CONCLUSION
==========

  C1. Despite the fact that Row Based Logging (RBL) is in use,
      the server uses Statement Based Logging (SBL) to log the
      DDL statement. This means that filtering rules for SBL
      apply to the ALTER TABLE wrt to the default database.

  C2. If the default database does not match any of the filtering
      rules stating that the statement can be logged, then it is
      skipped. NULL default database means that the statement is
      skipped, iff, some filtering rules were specified.

  C3. Given C1 and C2, this is not a bug.

REFERENCES
==========

[1] http://dev.mysql.com/doc/refman/5.1/en/alter-table.html 
[2] http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b...

<luis> jon: hi.
<jon> hi luis !
<luis> jon: can you please have a look at http://bugs.mysql.com/bug.php?id=56366 
<luis> jon: my comment
<jon> sure, looking...
<luis> jon: and see if we could add something to hte manual, in particular at:
<luis> http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#option_mysqld_b...
<jon> check
<luis> jon: thx, you're the man
<jon> luis: I'll reopen it as a Docs bug and assign to myself for possible improvements :)
<luis> jon: probably we need some sentence about void databases and filtering rules
<luis> jon: that's just great, thank you
<jon> *nod*

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.