How to best handle a Replication collision.
Just finished a big upgrade from 5.0.86 to 5.5.23, and now I'm having some replication issues. I've at least got figured out what's going on, but I need a little advice about the best way to handle it.
Table A has triggers on it that insert new rows into table B. So, on the Master server, table A gets a new row, trigger gets fired, new row is inserted into table B. All this information is sent to Slave server where table A gets its replicated row of data and triggers get fired to insert data into table B. The problem is that table B's primary key is auto-increment. The insert into table B gets replicated, as well. So, when the replicated insert statement comes along, the key it wants to use has already been taken by the fired trigger.
I've added a replicate-ignore-table on table B, opting to let the triggers do the word on the slave server. Does this seem like the right approach? I'm still occasionally getting duplicate key errors. So, I wonder if it's even helping or not.
Thanks,
Dave