Wednesday, April 4, 2012

MySQL error when running replication and triggers

One of my friends today has today run in to an issue where his MySQL db has run out of synch and giving an out of synch error. When exploring it and checking MySQL documentation I came across an interesting comment in the documentation.

If you want triggers to execute on both the master and the slave—perhaps because you have different triggers on the master and slave—you must use statement-based replication. However, to enable slave-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient to switch to statement-based replication only for those statements where you want this effect, and to use row-based replication the rest of the time.

So the main message is that If you are using triggers probably its not a good idea to go for row based replication, specially if you want to even complicate it more by having master master replication. I am guessing that this statement implies that replication format - mixed is a better fit. Let me give it a try. Trouble shooting on replications are like how a doctor treats a patient. You try to try different combinations and then find the needle in the haystack!

No comments:

Post a Comment