0

I have a mysql replication setup with one master reporting to one slave. The schema on the slave is the same than the master but for each table I have an additional synchronized column only on the slave which I use for sync with a third system. I need the following behavior:

  1. By default, the synchronized column is set to 0
  2. I have an external python script that copies each row with synchronized=0 to another system (with another schema) and then sets the synchronized field to 1
  3. If the row is updated again by the master through replication, I want the synchronized field to be set to 2 (probably through a trigger on update) so that my python script can detect updated rows and update them in the third system.

To simplify, let's say we have a master table like this :

People
id (int)
name (varchar)

and the corresponding table on slave

People
id (int)
name (varchar)
synchronized (tinyint)

If I insert I already tried to set up a trigger on update on the slave to change the synchronized field to 2. It works when I update a row manually but not when it is updated through replication.

I understood that in row-based replication triggers are not replicated from the master but I am not trying to replicate a trigger from the master but rather create a trigger on slave whenever a row is updated through replication.

Right now my replication is row-based but i could change it to statement based or mixed if needed. Hope it makes sense and that everything is clear, any suggestions are welcome,

Thanks

0

@Mati when your Master/Slave is row based replication the replication thread replicate a copy of the row into the slave instead of ruining the "insert into xx" statement into the slave.

therefore it'll never run your trigger.

however in your case i suggest you the following solution: 1- create synchronized field on the master with default value 0, and never call populate this field during an insert statement from the master 2- create a trigger on the master for each update to set synchronized to 2 when an update occur 3- remove the trigger from the slave

because the "synchronized" field already exists your replication will fail when you create the field on the master at step #1 above. you can tell your slave to skip temporary the error code related to field already exists below:

slave-skip-errors = xxx (xxx is the error code )

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.