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
I addressed something like back on June 14, 2012 : Configured MySQL replication but its not working
The basic problem may be the queries you are using
If you have a query that looks like this
INSERT INTO db1.tb SELECT * FROM db2.tb;
You could be potentially blocking queries of this nature
Queries like this:
USE db1
INSERT INTO tb SELECT * FROM db2.tb;
should work if you have replicate-do-db=db1
and Replicate-wild-do-table=db.%
To be sure, check out the replication filtering rules to make sure your queries are allowing your current settings to work on the Slave.
According to the replication filtering rules
To make it easier to determine what effect an option set will have, it
is recommended that you avoid mixing “do” and “ignore” options, or
wildcard and nonwildcard options. An example of the latter that may
have unintended effects is the use of --replicate-do-db and
--replicate-wild-do-table together, where --replicate-wild-do-table uses a pattern for the database name that matches the name given for
--replicate-do-db. Suppose a replication slave is started with --replicate-do-db=dbx --replicate-wild-do-table=db%.t1. Then, suppose that on the master, you issue the statement CREATE DATABASE dbx.
Although you might expect it, this statement is not replicated because
it does not reference a table named t1.
Perhaps you need to get rid of one of those rules on the Slave and restart mysql on the Slave.
Best Answer
Without seeing the exact my.cnf entries on the slave, or knowing where the update came from (normal
UPDATE
command, or a stored procedure for example), or knowing what type of binary log format (row vs statement) you are using, all I can do is give you a link to how MySQL handles the replicate table options here.A couple of things to note on this to explain why the above points are needed:
If you are using statement based logging and the statements are executed from a stored procedure, the slave will run it because --replicate-table rules do not apply to stored objects.
If the table name you are trying to ignore contains an underscore, you might need to escape it like "foo_bar". The same applies to the database name.