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

Bug #1376561 reported by Jaime Sicam
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
High
Frank Cizmich

Bug Description

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.

Related branches

Revision history for this message
Jaime Sicam (jssicam) wrote :
Changed in percona-toolkit:
importance: Undecided → High
assignee: nobody → Frank Cizmich (frank-cizmich)
status: New → Triaged
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

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.

Changed in percona-toolkit:
status: Triaged → In Progress
milestone: none → 2.2.12
Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Bug attachments