Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26946

Various small mysqldump manual improvements for the --as-of and --tz-utc options

    XMLWordPrintable

Details

    Description

      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.
      

      Attachments

        Issue Links

          There are no Sub-Tasks for this issue.

          Activity

            People

              dbart Daniel Bartholomew
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.