Search code examples
mysqlsqlpercona

Turn MySQL query into Percona pt-archiver string

How to express this in pt-archiver?

DELETE * FROM Table1 JOIN ON Table1.id=Table2.id WHERE Table2.id>=1;

I cannot find any possibility to join Table1 and Table2 within a string of pt-archiver. I tried this but the join string to Table2 is missing:

pt-archiver --source h=127.0.0.1,D=db,t=Table1 --purge --where 'Table2.id >= 1'

Please provide me some of your ideas.

Solution

  • I've done tasks like what you describe, where I used pt-archiver to archive rows that must also be joined to another table.

    pt-archiver --source h=host,D=db,t=child --purge \ 
--where 'EXISTS(SELECT * FROM parent WHERE col=child.col AND child.col>=1)'.

    This could work, but I am not confident how and where the parent table is to be declared. Some ideas?

    You declare exactly one table in the source DSN for pt-archiver. That's the child table in this example.

    You don't declare the parent table in the pt-archiver options. It's referenced only in the subquery you spell out in your --where option, exactly as in your example above.