--replicate-wild-ignore-table doesn't work?

Question

According to the document about the replication and temporary tables, I'm setting --replicate-wild-ignore-table option to the %.norep% to prevent temporary tables from being replicated:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000861
          Read_Master_Log_Pos: 332610533
               Relay_Log_File: mysqld-relay-bin.001578
                Relay_Log_Pos: 9061317
        Relay_Master_Log_File: mysql-bin.000861
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: %.norep%
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 330271548
              Relay_Log_Space: 13544924166
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 4

But as you can see from the above show slave status\G output, this statement is still replicated to the Slave and it causes the error.

Is there any problem with my query? Does it has only effect with the main tables (I mean ox_banners in this case)?

Reply to @Aaron Brown:

This may be somewhat surprising, but this error is proof that your replication filter is working.

+1.

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.

Due to the problem with temp tables: How to make MySQL replication reliable?

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 will consider switching from mixed-based to row-based.

Aaron Brown · Accepted Answer · 2012-07-24T21:05:23.217

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

score 0 · Answer 2 · answered Jul 24 '12 at 07:29

Blog for Why replication is bad for temporary tables: http://scale-out-blog.blogspot.in/2012/04/replication-is-bad-for-mysql-temp.html
Safe slave shutdown when using temporary tables:

Temporary tables are replicated except in the case where you stop the slave server (not just the slave threads) and you have replicated temporary tables that are open for use in updates that have not yet been executed on the slave. If you stop the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. For more information refer:

http://dev.mysql.com/doc/refman/5.1/en/replication-features-temptables.html

Row Based Logging, Row Based Replication, and temporary tables:

Temporary tables are not replicated when using row-based format. When mixed format is in effect, “safe” statements involving temporary tables are logged using statement-based format. For more information refer

http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html

Temporary tables are not replicated when using row-based format because there is no need. In addition, because temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based format.

--replicate-wild-ignore-table doesn't work?

2 Answers2