March 25, 2009
Tripping up MySQL Replication's wild-ignore-table
Ran into something I hadn't seen before with MySQL replication this morning (details obscured).
We use the MySQL replication slave option
replicate-wild-ignore-table to prevent tables generated for short-term use like calculated data, projections, and reports from being duplicated on our hot spare machines.
So if you've told MySQL that no tables containing the phrase "projection_temp" should be replicated, why would you see the following in
show slave status on the slave?
Last_Error: Error 'Table 'db_12345.projection_temp_gdoivxtt' doesn't exist'
I looked at the message for a long time, trying to figure out how that database or table name violates the ignore table rule. The statement to create the table wasn't applied to the slave, as evidenced by the 'table doesn't exist', but why would MySQL be trying to apply this binary log statement that's destined for this table?
The answer, an INSERT statement that SELECTs data from the ignored table.
As part of a recent enhancement we added, one of these short-term tables may be referenced to populate data in another table:
INSERT INTO projection_history (field1,field2)
SELECT field1, field2
We're now replicating those previously ignored tables.
Posted by mike at March 25, 2009 2:21 PM