Pt-archiver insert fails on Duplicate Key after exiting and resuming

Polyglot Projects Percona Toolkit
1

I have a pt-archiver job that runs on a cron schedule. It is meant to copy a subset of ID columns from table A and insert into table B, then delete from table A (pretty standard, I think). However, Both table A and B have a UNIQUE Key on the primary identifier (primary key is an auto-id; not talking about that). If the job ends or fails, the next run will fail early with a Duplicate Key error as it seems to be trying to reprocess the last batch where INSERTS into table B were already done but, apparently, deletes from table A were not.

What is the best practice here?

Thanks!

2 Likes
2

I would try using --replace to convert INSERT’s to REPLACE, to help avoid the dup key issue, or use --commit-each which should make the SELECT, INSERT, DELETE procedure a single transaction.

3 Likes
3

Ah, thank you. I think --ignore might actually be what I need here, but I missed those. Thanks for pointing me in the right direction!

1 Like