This may be somewhat surprising, but this error is proof that your replication filter is working. The statement that has stopped working is an UPDATE to another table, but the update is using data from the table that you have said not to replicate in the SELECT clause. Notice that the error is that the table norep_cp_banner_tvc_temp_
doesn't exist. Replication filters only are honored on the tables being affected - it's not just a straight pattern match.
Replication filters are somewhat complicated - I suggest that you read up on How Servers Evaluate Replication Filter Rules and Evaluation of Table-Level Replication Options.
The first question to ask yourself is why am I filtering replication at all? There is rarely a good reason for this and it usually causes far more problems that it solves.
You can definitely work around the problem by using Row Based Replication (RBR) instead of Statement Based Replication (SBR). RBR replicates the changes to the data instead of the statement.
I also suggest that you refactor your UPDATE as a join. IN (SELECT...)
is very optimized poorly in MySQL pre-5.6 and this will be much more performant:
UPDATE `ox_banners` A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1;
Another way that you might be able to work around this problem is by updating a dummy field in the norep table. Something like this:
UPDATE ox_banners A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1, temp.dummy = 1;
That might trick MySQL into filtering this statement, but it's a hack to say the least. RBR is likely your best bet.
UPDATE 2012-07-24
You mentioned that you are doing this to work around some of the problems with temporary tables being used with replication. Filtering them out entirely is a terrible way to handle that problem. Consider doing this instead, if you are worried about it.
Create a separate database to hold your "temporary" tables
mysql> create database temptables;
Query OK, 1 row affected (0.08 sec)
mysql> use temptables;
Database changed
Namespace the tables by using your CONNECTION_ID...
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 52 |
+-----------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE 52_my_temp ( foo int ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)
Use the table as normal, then
DROP
the table
mysql> DROP TABLE IF EXISTS 52_my_temp;
Query OK, 0 rows affected (0.05 sec)
Finally, create a process that cleans the tables up every so often, just incase the DROP TABLE didn't get executed. Something like this (Ruby Sequel syntax here):
# get a list of all the tables in the temp database
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TABLE_SCHEMA=?"
db[query, 'temptables'].each do |row|
# get the connection id
id = row[:TABLE_NAME].split('_')[0].to_i
# is the connection still active?
query = "SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = ?"
if db[query, id].first[:cnt] < 1
db["DROP TABLE #{row[:TABLE_NAME]}"]
end
end
You can set any of these three BINLOG-FORMAT :
STATEMENT causes logging to be statement based.
ROW causes logging to be row based.
MIXED causes logging to use mixed format.
The default binary logging format depends on the version of MySQL you are using:
For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.
For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.
You have to make sure that:
Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system variable is not replicated; when using MIXED or ROW logging mode, it is replicated but is ignored by the slave.) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.
When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication.
Also, this bug report is related about problem you have mentioned here :
http://bugs.mysql.com/bug.php?id=53259
Best Answer
Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.
To play it totally safe without a mysql restart, try the following:
This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.
All existing sessions prior to the first
FLUSH LOGS;
will start writing in the last binlog onceUNLOCK TABLES;
is executed.Give it a Try !!!
CAVEAT
Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.
UPDATE 2011-10-12 13:58 EDT
If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:
On the Slave, run
STOP SLAVE;
On the Master run these:
On the Slave, run
START SLAVE;
Running
STOP SLAVE;
andSTART SLAVE;
rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.