Lessons learned about Percona Table Sync (pt-table-sync), mySQL and trigger

At work, we have sometimes sync issues with mySQL replication, slaves are out of sync (don’t ask why..better is..).

To fix that, there is a neat tool provided by Percona called pt-table-sync

However, there is a problem related to it, if your DB actually uses trigger.

The actual problem is that pt-table-sync uses REPLACE INTO SQL statements, which will fire insert trigger in all mysql variants as “REPLACE works exactly like INSERT” (mysql docs)

This can be a huge drawback / data loss (overwrite) risk, if the application sets via insert trigger default values for certain columns / other tables.

Example:

"REPLACE INTO products ..."

will fire trigger to set default price in “products_price”.

Any changes done in “product_price” will be then finally overwritten by default value again if trigger from REPLACE INTO takes effect :(

I’ve filed a bug at Percona that the documentation should be extended to highlight this issue.

Updated: