MySQL Bugs: #46655: Replication works, but no updates on slave

Bug #46655	Replication works, but no updates on slave
Submitted:	11 Aug 2009 16:05	Modified:	13 Aug 2009 21:27
Reporter:	Eugene Turkestanov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.0.84	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	replication, slave, STATUS, updates

Description:
Hi there.

We set up master-master replication. We did it before with no problems. Anyway,
Everything seems OK, because "SHOW SLAVE STATUS" statement shows that Slave_IO_Running = YES, Slave_SQL_Running = YES, relay log position number is changing and master log position is changing as well.
But despite this, databases on the slaves aren't updated.
We use InnoDB engine by default. 

The my.cnf files are:

Server 1:

# ***  Replication related settings

server-id = 1
master-host = server1
master-user = replication
master-port = 3306
master-password = **********

auto-increment-increment = 2
auto-increment-offset = 1

log-bin = master-bin
relay-log = relay-bin

# Non-Replicated Databases

# Replicated Databases and Tables
#binlog-do-db = mysql
#binlog-do-db = db0
#binlog-do-db = db1_conf
#binlog-do-db = db1_log
#binlog-do-db = db2_conf
#binlog-do-db = db2_log
#binlog-do-db = db3_conf
#binlog-do-db = db3_log
#binlog-do-db = db4_conf
#binlog-do-db = db4_log

replicate-do-db = mysql
replicate-do-db = db1
replicate-do-db = db1_conf
replicate-do-db = db2_conf
replicate-do-db = db3_conf
replicate-do-db = db4_conf

replicate-do-table = db3_log.table1
replicate-do-table = db4_log.table1
replicate-do-table = db1_log.table1
replicate-do-table = db2_log.table1

Server 2:

# ***  Replication related settings

server-id = 2
master-host = server2
master-user = replication
master-port = 3306
master-password = *********

auto-increment-increment = 2
auto-increment-offset = 2

log-bin = master-bin
relay-log = relay-bin

# Non-Replicated Databases

# Replicated Databases and Tables
#binlog-do-db = mysql
#binlog-do-db = db0
#binlog-do-db = db1_conf
#binlog-do-db = db1_log
#binlog-do-db = db2_conf
#binlog-do-db = db2_log
#binlog-do-db = db3_conf
#binlog-do-db = db3_log
#binlog-do-db = db4_conf
#binlog-do-db = db4_log

replicate-do-db = mysql
replicate-do-db = db0
replicate-do-db = db1_conf
replicate-do-db = db2_conf
replicate-do-db = db3_conf
replicate-do-db = db4_conf

replicate-do-table = db3_log.table1
replicate-do-table = db4_log.table1
replicate-do-table = db1_log.table1
replicate-do-table = db2_log.table1

Can you help us with that please?

Thank you

How to repeat:
Not sure, but we reset replication and we got the same problem.

Thank you for the report.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

You have a lot of replicate-do-db rules in the configuration file. Most likely you just [don't] hit these rules. Please read at http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replic... how replicate-do-db works and if you still think this is MySQL bug provide repeatable test case: queries which are not replicated.

Thank you for your response and the links. I've read them carefully and I recognize that our settings in my.cnf actually hit the rules. If I missed something could you please point me to there or kindly explain it? Thank you in advance. 
Anyway, the problem still exists. According to my.cnf files I've provided, all of the queries log to binary log, then we use replicate-do-db option on slave to filter the queries. And, for example, when I run the query like "update table db1_conf.table1 set column1 = 1111;" on server 1, I can see that query in binary log on server 1. Then I can see that query in relay log on slave (server 2). But the query doesn't hit db1_conf.table1 on server 2. 
It happens for all of the databases we replcate. Please feel free to ask any other information you need.

Thank you for the feedback.

Page I provided link to clearly says:

----<q>----
Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. 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. 
----</q>-----

So this is not a bug

Well, I would say it's a nice "feature". We went further in our investigation. 
If you set up master-master replication with options like:

binlog-do-db = test
binlog-do-db = test_2

replicate-do-table = test.test
replicate-do-table = test_2.table1

it works perfectly. But if you nees to replicate entire database test and one table from test_2 database, the options should be like that:

binlog-do-db = test
binlog-do-db = test_2

replicate-do-db = test.test
replicate-do-table = test_2.table1

It doesn't work.
if you set up replication like that:

binlog-do-db = test
binlog-do-db = test_2

replicate-do-db = test
replicate-do-db = test_2

it works again.
So, we you have a choice of two options: to replicate entire databases or tables that you need. And if you need to replicate, for example, 10 databases with at least 20 tables in that and just 1 table from another database you'll have to type replicate-do-table for all this stuff many times. You can't use combination of replicate-do-db and replicate-do-table. 
Well, if it works like that... I would say it's a bug.

Sorry, I've been misspelled.
The correct part is:

But if you nees to replicate entire database test and one table from
test_2 database, the options should be like that:

binlog-do-db = test
binlog-do-db = test_2

replicate-do-db = test
replicate-do-table = test_2.table1

It doesn't work.

Thank you