Pt-archiver csv double quote missing for some fields

Question

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?

Answer 1

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.

Solution

Add a space; change

FIELDS TERMINATED BY ','

to

FIELDS TERMINATED BY ', '

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.

Answer 2

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`
    )
    PARTITION BY RANGE (TO_DAYS(...)), ...
--    include about 15 weekly partitions (as discussed in my blog)
-- because you will be deleting more data in the future,
-- Stop writing to `real`
INSERT INTO tmp
    SELECT * FROM real;
ALTER TABLE tmp
    ADD   ((the desired secondary indexes));
RENAME TABLE real TO old,
             tmp TO real;
-- test things, then
-- allow writing to real
DROP TABLE old;

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

My Partitioning blog .