pt-table-sync deletes child table rows

Reported by Daniel Nichter on 2013-09-10
22
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Critical
Daniel Nichter

Bug Description

In the case of parent table <-- child table, and child table has ON DELETE CASCADE, when pt-table-sync does REPLACE on the parent table, since the REPLACE becomes DELETE + INSERT, the DELETE will cascade to the child and delete its rows.

Daniel Nichter (daniel-nichter) wrote :

This is, however, documented (in the OUTPUT section however--should be more prominent):

"""
Also be careful with tables that have foreign key constraints with C<ON DELETE> or C<ON UPDATE> definitions because these might cause unintended changes on the child tables.
"""

Ryan Huddleston (rshuddleston) wrote :

I'm thinking two changes may make sense:

1) use insert ... ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), ...
2) seems "set FOREIGN_KEY_CHECKS=0" should always be set. The goal is to not change the master just replicate what is already there

Ryan Huddleston (rshuddleston) wrote :

3) if a table does have "foreign key constraints with C<ON DELETE> or C<ON UPDATE> definitions" don't allow sync to execute without "set FOREIGN_KEY_CHECKS=0". i.e. if throw an error and say how to do safely

tags: added: rdba
Changed in percona-toolkit:
milestone: none → 2.2.6
Changed in percona-toolkit:
assignee: nobody → Daniel Nichter (daniel-nichter)
Changed in percona-toolkit:
status: Triaged → In Progress
Daniel Nichter (daniel-nichter) wrote :

1) use insert ... ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), ...

Not possible, too complex and dangerous to change this tool.

2) "set FOREIGN_KEY_CHECKS=0" should always be set.

I think that's a good idea for the next major version of this tool, but for backwards compat, I've left --[no]foreign-key-checks to the same default: yes (i.e on)

3) if a table does have "foreign key constraints with C<ON DELETE> or C<ON UPDATE> definitions" don't allow sync to execute without "set FOREIGN_KEY_CHECKS=0". i.e. if throw an error and say how to do safely

I've done this: --[no]check-child-tables, default: yes. It can be disabled with --no-check-child-tables, and then you probably also want to --no-foreign-key-checks, as noted in the docs for --[no]check-child-tables.

For the current version of this tool, I think this will go a long way: it will at least keep users from unknowingly truncating child tables, but it's still backwards compat. In the next major version of pt-table-sync we can set new/different defaults.

Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers