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

mysqldump --as-of option ignores hour/minute precision

Details

    Description

      Whilst the --as-of option for mysqldump works correctly for dates, it does not work correctly for hour/minute precision. 

      CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
      		 
      SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
      		 
      INSERT t1 (a) VALUES (1),(2),(3);
      		 
      SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 01:00');
      		 
      DELETE FROM t1 WHERE a=1;

      When dumped with:

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

      Will produce identical results - the 1 value will be missing.

      Similarly,

      CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
      		 
      SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
      		 
      INSERT t1 (a) VALUES (1),(2),(3);
      		 
      SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-02 01:00');
      		 
      DELETE FROM t1 WHERE a=1;

      When dumped with:

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

      Will produce different results - the 1 value will be missing in the second dump.

      There is no reference to "day" only in the manual though MDEV-16355 has a comment which refers to "to a specified date".

      Proposed fix: either the manual needs to be updated to clarify the limitation, or the feature fixed to correctly process timestamp comparison.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          Roel Roel Van de Paar created issue -
          Roel Roel Van de Paar made changes -
          Field Original Value New Value
          Roel Roel Van de Paar made changes -
          Description Whilst the {{\-\-as-of}} option for {{mysqldump}} works correctly for dates, it does not work correctly for hour/minute precision.

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce identical results - the 1 value will be missing.

          Similarly,

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-02 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce different results - the 1 value will be missing in the second dump.

          There is no reference to "day" only in the manual though MDEV-16355 has a comment which refers to "to a specified date"; i.e. either the manual needs to be updated to clarify the limitation, or the feature fixed.           		Whilst the {{\-\-as-of}} option for {{mysqldump}} works correctly for dates, it does not work correctly for hour/minute precision.

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce identical results - the 1 value will be missing.

          Similarly,

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-02 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce different results - the 1 value will be missing in the second dump.

          There is no reference to "day" only in the manual though MDEV-16355 has a comment which refers to "to a specified date"; i.e. either the manual needs to be updated to clarify the limitation, or the feature fixed.
          Roel Roel Van de Paar made changes -
          Description Whilst the {{\-\-as-of}} option for {{mysqldump}} works correctly for dates, it does not work correctly for hour/minute precision.

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce identical results - the 1 value will be missing.

          Similarly,

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-02 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce different results - the 1 value will be missing in the second dump.

          There is no reference to "day" only in the manual though MDEV-16355 has a comment which refers to "to a specified date"; i.e. either the manual needs to be updated to clarify the limitation, or the feature fixed.           		Whilst the {{\-\-as-of}} option for {{mysqldump}} works correctly for dates, it does not work correctly for hour/minute precision.

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce identical results - the 1 value will be missing.

          Similarly,

          {code:sql}
          CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-01 00:00');
          INSERT t1 (a) VALUES (1),(2),(3);
          SET TIMESTAMP=UNIX_TIMESTAMP('1990-01-02 01:00');
          DELETE FROM t1 WHERE a=1;
          {code}

          When dumped with:

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

          Will produce different results - the 1 value will be missing in the second dump.

          There is no reference to "day" only in the manual though MDEV-16355 has a comment which refers to "to a specified date".

          Proposed fix: either the manual needs to be updated to clarify the limitation, or the feature fixed to correctly process timestamp comparison.
          serg Sergei Golubchik made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          serg Sergei Golubchik added a comment -

          This happens if you specify --tz-utc option to mysqldump. It'll interpret timestamps in UTC

          serg Sergei Golubchik added a comment - This happens if you specify --tz-utc option to mysqldump. It'll interpret timestamps in UTC
          serg Sergei Golubchik made changes -
          Fix Version/s N/A [ 14700 ]
          Fix Version/s 10.7 [ 24805 ]
          Resolution Not a Bug [ 6 ]
          Status Confirmed [ 10101 ] Closed [ 6 ]
          Roel Roel Van de Paar added a comment - - edited

          Confirmed/correct.

          For anyone researching this, please see MDEV-26946 Various small mysqldump manual improvements for the --as-of and --tz-utc options (or the future updated manual), which has clarifications.

          Roel Roel Van de Paar added a comment - - edited Confirmed/correct. For anyone researching this, please see MDEV-26946 Various small mysqldump manual improvements for the --as-of and --tz-utc options (or the future updated manual), which has clarifications.
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 126858 ] MariaDB v4 [ 159817 ]
          midenok Aleksey Midenkov made changes -

          People

            serg Sergei Golubchik
            Roel Roel Van de Paar
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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