pt-archiver not working as expected on partitioned tables

[Fernando Ipar]

On a table partitioned by key, invoking pt-archiver --purge does not delete all the rows matching --where.

Sample test case is attached, using these tables:

create table test_normal (
    id int unsigned not null auto_increment,
    row_a int unsigned not null,
    row_b int unsigned not null,
    ts timestamp not null default current_timestamp on update current_timestamp,
    primary key (id),
    key ts (ts)
) engine = innodb;

create table test_partitioned (
    id int unsigned not null auto_increment,
    row_a int unsigned not null,
    row_b int unsigned not null,
    ts timestamp not null default current_timestamp on update current_timestamp,
    primary key (id),
    key ts (ts)
) engine = innodb
partition by key (id)
partitions 20;

The test case populates the tables with 250 rows, and running this:

pt-archiver --purge --source S=/tmp/mysql_sandbox5512.sock,u=msandbox,p=msandbox,D=test,t=test_normal --where "ts <= '2012-12-01 00:00:00'" --progress 1

successfully removes 250 rows, while this:

pt-archiver --purge --source S=/tmp/mysql_sandbox5512.sock,u=msandbox,p=msandbox,D=test,t=test_partitioned --where "ts <= '2012-12-01 00:00:00'" --progress 1

removes less rows, and I have to keep invoking it until all rows are removed.

Reproduced on Centos 5.6 and Mac OS X 10.7.4

To use the test case, just unpack at a directory containing a MySQL sandbox and run ./init_test_case.sh
The script has a couple of comment lines with the used pt-archiver invocations, you just have to adjust the socket to use whatever is set for the sandbox used.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Confirmed with latest trunk pt-archiver.

Workaround is to give a large --limit, like --limit 1000

A similar bug was encountered in https://code.launchpad.net/~percona-toolkit-dev/percona-toolkit/pt-archiver-for-percona-20933 but was fixed (tested with this too but can repeat the issue), looks like a similar bug.

[Frank Cizmich (frank-cizmich)]

Fixed along with https://bugs.launchpad.net/percona-toolkit/+bug/1376561

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1018