MySQL Bugs: #33917: log contains traffic for ignored tables

Bug #33917	log contains traffic for ignored tables
Submitted:	18 Jan 2008 12:03	Modified:	7 Mar 2008 15:43
Reporter:	Nikola Strahija	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.51	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	binlog, IGNORE, network, replication, table

Description:
Let's say you have 5 replication slaves and 1 master.
Only one database is being replicated and not all tables from that database need replication.

The tables which don't need replication are stored in the binary log file and sent to the replication slaves.

On both master and slaves you have:
replicate-wild-ignore-table=%table1%
replicate-wild-ignore-table=%table2%
replicate-wild-ignore-table=%table3%

Master writes sql statements for these tables in the binary log even though they won't get replicated. In case these tables have a lot of data being written you'll end up with huge network traffic, high hard drive usage and big replication logs which shouldn't be in the first place.

How to repeat:
binlog-do-db=BIGdatabase
binlog-ignore-db=mysql
replicate-wild-ignore-table=%table1%
replicate-wild-ignore-table=%table2%
replicate-wild-ignore-table=%table3%

check the logs on slaves, ignored tables will be in the replication logs.

Suggested fix:
Add binlog-ignore-table (with % as the wildcard, the same as in replication-wild-ignore-table). I should be as simple as:

if(binlog-ignore-table[0]!=current_table_for_writing
{
 ... write binary log ...
} 
else {
... else statements not needed ...
}

As easy as that. (?)

Many thanks for writing a bug report.

Do you use statement based replication or row based replication?

We also need the exact version, that you use for master and slaves.
You can figure it out with:

mysql>select version();

This is the latest version available for debian etch (4).
5.0.32-Debian_7etch4-log

Therefore, it's statement based replication. Row based was introduced in mysql 5.1 if I remember correctly.

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

Our newest version at the moment is: MySQL 5.0.51

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

Yup, same on 5.0.51

Changed unsupported to open

Please, re-read http://dev.mysql.com/doc/refman/5.0/en/replication-options.html about correct format of replicate_wild_ignore_table. Then, please, provide a complete test case to run on master, with CREATE TABLE statements and exact updates you perform.

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".