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