pt-archiver misses data when only copying data

Bug #1096274 reported by Pritam Mungse on 2013-01-05
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

when using '--no-delete' option in pt-archiver (to only copy data to destination) if the where clause column can have multiple records then pt-archiver will miss records in copying.
e.g. create table with following columns:
id, timestamp
data:
| 001-25941882 | 1280490242452 |
| 001-25941883 | 1280490242452 |
| 001-25941884 | 1280490242452 |
| 001-25941885 | 1280490242452 |
| 001-25941886 | 1280495048106 |
| 001-25941887 | 1280495048108 |
| 001-25942993 | 1280495048108 |
| 001-25942994 | 1280495048108 |
| 001-25942995 | 1280490776956 |
| 001-25942996 | 1280493558772 |

use batch size of 2 (limit) and with commit-each option, the pt-archiver will miss copying few records.
pt version- 2.1.7
mysql version - 5.1.40
command:
/usr/bin/pt-archiver --source h=127.0.0.1,P=3306,D=test_db_1,u=root,p=xxx,t=test,i=idx_timestamp,A=utf8 --dest h=127.0.0.1,P=3306,D=test_db_2,u=root,p=xxx,t=test,A=utf8 --where "created < 1280493558772 " --limit 2--sentinel /tmp/sentinel --set-vars "SQL_MODE=''" --no-delete --commit-each --skip-foreign-key-checks --no-safe-auto-increment --why-quit --statistics --progress 2

It's pretty easy to reproduce and happens due to optimized SQL (which add in above case timestamp > [max value in last run]) in the next sql clause - which cause the next run to miss few records.

Daniel Nichter (daniel-nichter) wrote :

Thanks for looking into this. We'll try to reproduce it on our end too. Bug 820079 is similar, though unconfirmed yet.

tags: added: risk
Changed in percona-toolkit:
status: New → Triaged
Karoly Bujtor (bujtor-karoly) wrote :

It might be caused by the WHERE expression in file pt-archive on line 5768 which uses a less than comparision instead of less than equal.

walito (walito) wrote :

I have the same problem.
I'm trying to copy all table from serverA to serverB, this process copy all rows, but the last one, this don't copy and then I need insert this last row manually.
I tested in pt-archiver 2.2.4 and 2.2.8

Steps
- In serverA
mysql> use sample
Database changed
mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| random_data |
+------------------+
1 row in set (0.00 sec)

mysql> select count(*) from random_data;
+----------+
| count(*) |
+----------+
| 30000 |
+----------+
1 row in set (0.06 sec)

mysql> select * from random_data order by id desc limit 5;
+-------+-------+-------+-------+-------+
| id | c1 | c2 | c3 | c4 |
+-------+-------+-------+-------+-------+
| 30000 | 67938 | 80318 | 97774 | 47916 | <------------------------- this row not copy
| 29999 | 50083 | 36562 | 32562 | 53125 |
| 29998 | 74207 | 20502 | 79891 | 37951 |
| 29997 | 69171 | 88837 | 36671 | 16844 |
| 29996 | 75947 | 45070 | 97510 | 52340 |
+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

- Run pt-archiver in serverA
pt-archiver --source h=localhost,D=sample,t=random_data --where "1=1" --dest h=192.168.88.22 --user sync --ask-pass --txn-size 10000 --no-delete --progress 10000 --limit 10000
Enter password:
Enter password:
TIME ELAPSED COUNT
2014-06-05T08:18:00 0 0
2014-06-05T08:18:10 9 10000
2014-06-05T08:18:20 19 20000
2014-06-05T08:18:29 28 29999

- Test serverB
mysql> select * from random_data order by id desc limit 5;
+-------+-------+-------+-------+-------+
| id | c1 | c2 | c3 | c4 |
+-------+-------+-------+-------+-------+
| 29999 | 50083 | 36562 | 32562 | 53125 |
| 29998 | 74207 | 20502 | 79891 | 37951 |
| 29997 | 69171 | 88837 | 36671 | 16844 |
| 29996 | 75947 | 45070 | 97510 | 52340 |
| 29995 | 82478 | 74942 | 27276 | 11555 |
+-------+-------+-------+-------+-------+
5 rows in set (0.00 sec)

mysql> select count(*) from random_data;
+----------+
| count(*) |
+----------+
| 29999 |
+----------+
1 row in set (0.02 sec)

Thanks in advance

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

Other bug subscribers