1

We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that this INSERT is coming through using Row-based replication and the trigger (which is on table_a on slave) is not firing. We need to have the trigger on the slave table as that is where our DW is.

Looking around online it looks like this should work if statement-based replication is used. Is it possible to force the INSERT to table A to be processed as statement-based replication? Or is there any other way we can achieve this?

The INSERT itself is deterministic as is the trigger. We are using MySQL 5.6.

If you need any other information please let me know.

  • Could you provide the insert statement? – Derek Downey Nov 24 '15 at 15:09
  • The trigger actually isn't deterministic - its basically: INSERT INTO db_wh.wh_table (Cols) SELECT Cols FROM db_main.table_a JOIN db_main.table_b ON condition WHERE db_main.table_a.id = NEW.id; – Tinoater Nov 25 '15 at 9:44
0

As documentation say

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.

This behavior is by design.

So in anycase statement/row, you don't need to be worrying about the data on slave and it should match the master.

but still if you want and can fit-the-logic on an event, this might be a possibility on slave at the risk of inconsistency!! As you said this is dw slave, would you consider a separate process to do the task you're looking to do, say a procedure?

  • The issue is that the DW doesn't exist on the master, so we need the trigger to only fire on the slave. So in the sense that there is no DW on master, but is one on slave then we want the slave to not match the master. The only other thing I can think of would be procedure that would poll the table for changes, but this would be resource expensive. – Tinoater Nov 24 '15 at 15:18
  • Yes, this is main reason for triggers on slave, but this is unsafe (and on 5.7 still marked as unsafe). So depending from business logic - may be possible achieve same goals but by other methods - like stored procedure which check some parameters and make same changes as triggers – a_vlad Nov 24 '15 at 15:41
  • Thanks for the replies - what kind of stored procedures could work? As we need these to execute exactly once when a row is inserted, I can't see how we could achieve this without just constantly polling the table. – Tinoater Nov 24 '15 at 16:19
  • Your slave has DW? --> Yes... Then as your question suggests, you have to change the replication to STATEMENT BASED and then triggers on slave will fireup. Alternatively you can also try changing binlog_format to statement on per-session basis whenever you need... (I'd not like to do "polling" on db for sure) – mysql_user Nov 25 '15 at 5:10
  • I don't want to change the replication type on the whole DB, as this could have repercussions elsewhere and we can't easily QA the change. These rows are inserted into the master by a web app so I won't be able to do it per-session either... do you know if it's possible to do it per-table? – Tinoater Nov 25 '15 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.