[#MDEV-26946] Various small mysqldump manual improvements for the --as-of and --tz-utc options

The documentation lists the --as-of option to mysqldump as:

Dump system versioned table as of specified timestamp. From MariaDB 10.7.0.

1. It needs to be clarified that this option is only applicable to the data, not the table structure. The table structure is always dumped, irrespective of the date/time specified. @serg fyi, in case this is considered a bug.

CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;

INSERT t1 (a) VALUES (1),(2),(3);

DELETE FROM t1 WHERE a=1;

Followed by:

./bin/mysqldump -uroot -S./socket.sock --compact --as-of="1990-01-01 00:01" test t1

Will dump the table structure but not the data.

2. In regards timestamps, the connection with -tz-utc / -skip-tz-utc would best be clarified to make it easier for users to understand why their timestamps are not working when dumping data. Perhaps something like:

Please note the interaction of this option with <a href="https://jira.mariadb.org/browse/MDEV-26943">--tz-utc</a>.

3. Please change "table" to "table(s)".

4. The mysqldump binary's help for -as-of should be changed in a similar matter as items 1 to 3 above.

$ ./bin/mysqldump --help --verbose | grep 'as-of'

  --as-of=name        Dump system versioned table as of specified timestamp.

4. The mysqldump binary's help for -skip-tz-utc should be changed as currently it only tells part of the story, and forgets to mention that the TZ for mysqldump is changed, which is significant (i.e. it does not just add something on the top of the dump, it actually changes the dump as the timezone values will be different. See below for proposed text.).

$ ./bin/mysqldump --help --verbose | grep -A3 -m1 'tz-utc'

  --tz-utc            SET TIME_ZONE='+00:00' at top of dump to allow dumping of

                      TIMESTAMP data when a server has data in different time

                      zones or data is being moved between servers with

                      different time zones.

The documentation explains it more clearly. The first two lines from the documentation could be used as the new mysqldump help:

This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE=´+00:00´ to the dump file.

5. (Cosmetic) Re: 4 add "top of the" in the manual and in the binary help, and add "before commencing the dump" to clarify further i.e. manual and binary help text could become:

This option enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC before commencing the dump, and adds SET TIME_ZONE=´+00:00´ to the top of the dump file.

Inline this would look something like this:

$ ./bin/mysqldump --help --verbose | grep -A5 -m1 'tz-utc'

  --tz-utc            This option enables TIMESTAMP columns to be dumped and

                      reloaded between servers in different time zones.

                      mysqldump sets its connection time zone to UTC before

                      commencing the dump, and adds SET TIME_ZONE=´+00:00´

                      to the top of the dump file.

[MDEV-26946] Various small mysqldump manual improvements for the --as-of and --tz-utc options Created: 2021-10-31 Updated: 2023-12-15
Status:	Open
Project:	MariaDB Server
Component/s:	Scripts & Clients, Versioned Tables
Affects Version/s:	10.7
Fix Version/s:	N/A

[MDEV-26946] Various small mysqldump manual improvements for the --as-of and --tz-utc options Created: 2021-10-31 Updated: 2023-12-15