I am trying to use pt-archive to archive my database. Below is my script for the archiving process.

pt-archiver --source h=localhost,D=***,t=** --user=*** --password=**** --where "dateTime<'2022-07-01 00:00:00'" --limit=1000 --progress=1000 --txn-size=1000 --statistics  --no-check-charset --no-delete --file="/usr/local/test1.dat" --output-format=csv

Below is an example of the output line

0, 2, 0, 0, 1, "2022-05-26 01:46:54", "2022-05-26 19:27:15", "-1.22706831", 36.70828247, 0, 0, 0, 2674.4, 0, 0.000, 1, 1, 0, 0

See this field "-1.22706831" it has double quote whereas this one does not have 36.70828247. So should I stick to the default file which generate space among the fields? In the long run which one will be more effective to maintain?

2 Answers 2


If the goal is to "archive" the data, ...

In general, a quoted (single or double quotes) number works just fine when used as any numeric datatype.

As for why? I don't know. I suspect minus (-) triggered the need for quotes. Perhaps floating numbers like 12e3 or 1.2e-3 will get quotes, too.

true, false, NULL -- these had better not be quoted.


Add a space; change




The clue was hard to see, but it was in the WARNINGS:

Incorrect datetime value: ' "2022-05-26 01:46:54"' for column 'dateTime' at row 1

Notice how, for example

0, 2, 0, 0, 1, "2022-05-26 02:06:54", "2022-05-26 19:27:17", "-1.22706831", 36.70828247, 0, 0, 0, 2674.4, 0, 0.000, 1, 1, 0, 0

always has a space after the comma.

  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Jan 23 at 13:25

If the goal is to Delete most of a table...

If you have 24 months' worth of data now but want to delete down to only 3 months' worth, then the best approach, by far, is to copy over the data you want to keep.

CREATE TABLE tmp (...,
    PRIMARY KEY(...)   -- with the `PRIMARY KEY` but no other `INDEXes`
--    include about 15 weekly partitions (as discussed in my blog)
-- because you will be deleting more data in the future,
-- Stop writing to `real`
    SELECT * FROM real;
    ADD   ((the desired secondary indexes));
             tmp TO real;
-- test things, then
-- allow writing to real

Note that the INSERT...SELECT avoids the need for pt-archiver.

My Partitioning blog .

  • but before that I would like to archive first the last 24 months data. Is it correct to do it via pt-archive first ? Also after that I do delete via pt-archive thereafter I follow you above method ? Correct me if I am wrong in my steps ? Jan 18 at 9:05
  • @user8012596 - I'm confused. Are there two things going on? 1. "Archiving" the data. and 2. "deleting" "old" data?
    – Rick James
    Jan 18 at 16:46
  • yes 1. I want to archive those say 21 Months data and just keep last 3 months data. 2. You are right I wanna delete that 21 Months data which from now on wards is to keep just latest 3 months data. Hope I am cleearer. Jan 18 at 20:51
  • Once you have created the new version of the table with just the recent 3 months, you have all the time in the world to archive or dump or whatever, the old 21 months. Deleting 3 months from the 24 will be fast, but probably unnecessary. Simply use mysqldump or pt-archiver or whatever to dump just the 21 months.
    – Rick James
    Jan 19 at 7:02
  • thank you now I am clear and confirmed with the steps first build the new table with 24 partitions there after as I pt-archive each month then I will drop the partitions right. Jan 19 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.