[MDEV-26943] mysqldump --as-of option ignores hour/minute precision Created: 2021-10-29  Updated: 2022-05-25  Resolved: 2021-10-31

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients, Versioned Tables
Affects Version/s: 10.7
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: mysqldump


 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.

 Comments   
Comment by Sergei Golubchik [ 2021-10-31 ]

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

Comment by Roel Van de Paar [ 2021-10-31 ]

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.

Generated at Thu Feb 08 09:49:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.