pt-archiver is not able to archive all the rows when a table has a hash partition

[Jaime Sicam]

Tested on pt-archiver 2.2.10

CREATE TABLE t1.test_table (
`a` int(10) unsigned NOT NULL DEFAULT '0',
`b` timestamp,
PRIMARY KEY (a,b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (a)
PARTITIONS 10 */;

Test data attached as test-data.sql

pt-archiver command:
PTDEBUG=1 pt-archiver --source h=127.0.0.1,P=5620,u=msandbox,p=msandbox,D=t1,t=test_table --dest h=127.0.0.1,P=5621,u=msandbox,p=msandbox,D=t1,t=test_table --where 'b < now() - interval 90 day' --commit-each --limit 500 --pid /tmp/t1.pid --nocheck-charset --why-quit --replace

Query should yield no result:
select count(*) from t1.test_table where b < now() - interval 90 day;
+----------+
| count(*) |
+----------+
| 1826 |
+----------+
1 row in set (0.01 sec)

My current workaround is to use the option --no-ascend:
PTDEBUG=1 pt-archiver --source h=127.0.0.1,P=5620,u=msandbox,p=msandbox,D=t1,t=test_table --dest h=127.0.0.1,P=5621,u=msandbox,p=msandbox,D=t1,t=test_table --where 'b < now() - interval 90 day' --commit-each --limit 500 --pid /tmp/t1.pid --nocheck-charset --why-quit --replace --no-ascend

mysql> select count(*) from t1.test_table where b < now() - interval 90 day;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)

Please note that I tested this with a smaller dataset of 1000 rows and I was not able to reproduce it. The test-data.sql I provided contains 9900 rows.

[Frank Cizmich (frank-cizmich)]

There was an problem in pt-archiver which made it depend on a consistent order of selected rows.
This is usually not a problem, but it clearly manifests itself in the case of partitioned tables.
Attached is a fix for this issue. It solves all cases where tables have primary or unique indexes.

[Shahriyar Rzayev (rzayev-sehriyar)]

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