We currently are trying to use pt-archiver to archive and trim old data out of our MySQL 5.7 database on AWS RDS. The archiver works as expected in our tasks except for one problem - it seems to mangle the binary data in one column. A normal mysqldump of the same row is able to be restored correctly. The normal method for handing binary data is to use --hex-blob (with mysqldump) or hex() and unhex() on the field, but I see no way to do that with pt-archiver. To get around this limitation we're currently performing a dump with mysqldump then using pt-archiver to trim the rows afterwards. This method is adequate but much slower and more cumbersome. Is there a way to preserve binary data archived from pt-archiver? Or a way to hex() that field during the archive process? Here is the command we are using:
the resulting archive is able to be restored without complaint, but the binary data field is mojibake garbage.
Environment
Ubuntu 16.04
pt-archiver 3.0.13
MySQL 5.7.21
Smart Checklist
Activity
Show:
William Valadez August 6, 2019 at 1:48 PM
This is exactly my experience as well. I'm glad you're able to reproduce it.
Lalit Choudhary August 6, 2019 at 10:20 AM
Hello
Thank you for the details.
In my test, I see an issue for blob data restore extracted using pt-archive. Where as same data restore works fine if we use mysqldump with – hex-blob option.
Test:
Preparing BLOB data
mysqldump and restore works fine for the same data.
William Valadez August 5, 2019 at 2:50 PM
In addition, to view the encrypted field we use the following:
Medium
We currently are trying to use pt-archiver to archive and trim old data out of our MySQL 5.7 database on AWS RDS. The archiver works as expected in our tasks except for one problem - it seems to mangle the binary data in one column. A normal mysqldump of the same row is able to be restored correctly. The normal method for handing binary data is to use --hex-blob (with mysqldump) or hex() and unhex() on the field, but I see no way to do that with pt-archiver. To get around this limitation we're currently performing a dump with mysqldump then using pt-archiver to trim the rows afterwards. This method is adequate but much slower and more cumbersome. Is there a way to preserve binary data archived from pt-archiver? Or a way to hex() that field during the archive process? Here is the command we are using:
pt-archiver --file /data/test.dump.$(date +%s).sql --where "_created < '2014-12-31 23:59:59' AND _created > '2014-01-01 00:00:00'" --source D=client_db,t=messages --charset 'utf8' --statistics --progress 10000
the resulting archive is able to be restored without complaint, but the binary data field is mojibake garbage.