Triggers, ETL and MySQL

One of my slaves works as a bridge between the old and the new structure. The master’s version did not allow to use triggers yet, so only the slave had its own triggers. After upgrade, the master supports triggers and has new ones (different than triggers on slave) on the same tables when comparing to the slave.
The problem seems to be very serious from my point of view. Due to the fact that you cannot set ‘multiple triggers with the same action time and event for one table’ (Error #1235 [version: 5.1.43]) I have to resolve it in other manner.
My proposition is:
1) The trigger recognizes host and runs adequate instructions;
2) Different triggers on master and slave.
I create a trigger on the slave with name e.g. slave_trigger_t1 (before insert). Next I create a trigger on the master with name e.g. master_trigger_t1 (before insert too). The replication on slave stops – due to error #1235 – i push one step on slave (SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; slave start sql_thread;). Voila;
3) I create one trigger for both master and slave (the same instructions on the master and on the slave). Those tables on the master which must not to be updated have the engine “blackhole”. The analogical situation is on the slave.

Situation

I have chose the recipe as described at point 3 – the greate adventage is that I have a common structure (of triggers) on master and slaves. I also found practical usage for the BLACKHOLE engine.

  1. No comments yet.

  1. No trackbacks yet.