pt-table-sync is executing DELETE CASCADE on MASTER as well as SLAVE
General
Escalation
General
Escalation
Description
Sorry, if this is not a bug, I started using this wonderful toolkit this week.
This is a simple MASTER->SLAVE setup, where I have 2 tables:
frontend_users and saved_searches
saved_searches has a foreign key with ON DELETE CASCADE to frontend_users
When I modify a "frontend_user" in the SLAVE to test the checksum/sync process (for example I add one letter to the username of the frontend_user with id=1. I run the "pt-table-checksum" in MASTER, it successfully detects a difference.
When I run this same command with --execute instead of --print, I would expect the username to be changed in SLAVE and also the deletion of the 'saved_searches' associated with it in SLAVE only, I'd then run another checksum/sync to fix those. But what is actually happening is that this command is causing the deletion of the saved_searches for this user in both MASTER and SLAVE
Thanks and let me know if I can provide any more info
Environment
None
Smart Checklist
Activity
Show:
Jira Bot June 6, 2021 at 9:56 AM
Hello , It's been 52 days since this issue went into Incomplete and we haven't heard from you on this.
At this point, our policy is to Close this issue, to keep things from getting too cluttered. If you have more information about this issue and wish to reopen it, please reply with a comment containing "jira-bot=reopen".
Jira Bot May 29, 2021 at 8:56 AM
Hello , It's jira-bot again. Your bug report is important to us, but we haven't heard from you since the previous notification. If we don't hear from you on this in 7 days, the issue will be automatically closed.
Jira Bot May 14, 2021 at 7:56 AM
Hello , I'm jira-bot, Percona's automated helper script. Your bug report is important to us but we've been unable to reproduce it, and asked you for more information. If we haven't heard from you on this in 3 more weeks, the issue will be automatically closed.
Jericho Rivera April 15, 2021 at 7:12 AM
This is not a bug. pt-archiver document states:
You can couple --no-check-child-tables with --no-foreign-key-checks (WITH CAUTION!) to avoid cascading deletes to the child tables. However, please test prior to doing this in production!
Sorry, if this is not a bug, I started using this wonderful toolkit this week.
This is a simple MASTER->SLAVE setup, where I have 2 tables:
frontend_users and saved_searches
saved_searches has a foreign key with ON DELETE CASCADE to frontend_users
When I modify a "frontend_user" in the SLAVE to test the checksum/sync process (for example I add one letter to the username of the frontend_user with id=1. I run the "pt-table-checksum" in MASTER, it successfully detects a difference.
Then I run the following command in SLAVE:
pt-table-sync --print --sync-to-master --replicate=percona.checksums --databases aspu h=localhost,u=checksum_user,p='XXXXXXXXXXX' --nocheck-child-tables
Which gives the following output:
REPLACE INTO `frontend_users`(`id`, `g_uid`, `firstname`, `lastname`, `email`, `pronoun`, `sharing_emails`, `admin`, `verification_hash`, `created_at`, `last_login`) VALUES ('1', 'xxxx', 'nic', 'f', 'xxxx@gmail.com', '(f)ae', '[{"email":"xxxxx@gmail.com","name":"Nico"}]', '1', NULL, '2021-03-28 19:07:30', '2021-03-31 19:12:21') /percona-toolkit src_db:xxxxxxxxxxxxxx/;
When I run this same command with --execute instead of --print, I would expect the username to be changed in SLAVE and also the deletion of the 'saved_searches' associated with it in SLAVE only, I'd then run another checksum/sync to fix those. But what is actually happening is that this command is causing the deletion of the saved_searches for this user in both MASTER and SLAVE
Thanks and let me know if I can provide any more info