« The Last Soda | Main | Configuration Options for JavaScript Lint »

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?

...
Replicate_Wild_Ignore_Table: db_%.projection_temp\_%
Last_Errno: 1146
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
FROM db_12345.projection_temp_gdoivxtt

We're now replicating those previously ignored tables.

Posted by mike at March 25, 2009 2:21 PM