Details
-
Task
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
It would be great to have an option for mysqldump to read data as of specific timestamp from system-versioned tables. One should just use:
SELECT * FROM <t> FOR SYSTEM_TIME AS OF TIMESTAMP '...';
|
Instead of simple SELECT for such tables if the option is provided.
I think it makes sense to implement this separately from MDEV-16029
Attachments
Issue Links
- causes
-
MDEV-26946 Various small mysqldump manual improvements for the --as-of and --tz-utc options
-
- Open
-
-
MDEV-27749
Binary changes for --as-of and --tz-utc options
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue relates to |
| Description |
It would be great to have an option for mysqldump to read data as of specific timestamp from system-versioned tables. One should just use:
select * from <t> FOR SYSTEM_TIME AS OF TIMESTAMP '...' instead of simple SELECT for such tables if the option is provided. I think it makes sense to implement this separately from https://jira.mariadb.org/browse/MDEV-16029 |
It would be great to have an option for mysqldump to read data as of specific timestamp from system-versioned tables. One should just use:
select * from <t> FOR SYSTEM_TIME AS OF TIMESTAMP '...' instead of simple SELECT for such tables if the option is provided. I think it makes sense to implement this separately from |
| Assignee | Aleksey Midenkov [ midenok ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
Task statement
New mysqldump option: --asof-timestamp=T.
If both --dump-history and --asof-timestamp=T are specified, then history is dumped from the beginning up to T. The query is executed roughly like this:
select *, row_start, row_end from t1 for system_time between timestamp '0-0-0 0:0' and timestamp T; |
If only --asof-timestamp=T, then data is dumped without prior history. The query is executed roughly like this:
select * from t1 for system_time as of timestamp T; |
Note
This option specifies only timestamp literal.
It does not specify TRX_ID for precise-versioned tables (though specifying a timestamp is of course valid). This is the subject for further development (e.g. --asof-transaction).
It does not specify any functional expressions.
Security check
Fail on single quote (') symbol to avoid forged queries.
| Comment |
[ h3. Security precaution
Should we allow function calls as {{--asof-time}} parameter, like: {code} mysqldump '--asof-time=NOW()' test {code} ? ] |
| Assignee | Aleksey Midenok [ midenok ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Link |
This issue relates to |
MDEV-16798 suggests an option to dump "all system versioning history back to a specified date".
Although we don't need this feature, we do need MDEV-16029 which has been unchanged for quite a while.
| Link |
This issue is blocked by |
| Link |
This issue relates to |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Fix Version/s | 10.4 [ 22408 ] |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Priority | Critical [ 2 ] | Major [ 3 ] |
| Link |
This issue is blocked by |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Link |
This issue is blocked by |
| Link |
This issue is blocked by |
| Fix Version/s | 10.7 [ 24805 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Due Date | 2021-09-14 |
for the reference:
$ git fetch https://github.com/tempesta-tech/mariadb.git a19495728dea |
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
I'd expect this pushed together with MDEV-16546 and MDEV-16029.
Please review bb-10.7-midenok
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
This is a very clear, independent, and rather small feature. Let's not delay it unnecessary by combining it with two larger and way more arguable features. It can get pushed much faster it it won't wait for two other MDEVs
| Assignee | Sergei Golubchik [ serg ] | Aleksey Midenkov [ midenok ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Aleksey Midenkov [ midenok ] | Sergei Golubchik [ serg ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Component/s | Scripts & Clients [ 11002 ] | |
| Component/s | Backup [ 13902 ] | |
| Fix Version/s | 10.7.0 [ 26072 ] | |
| Fix Version/s | 10.7 [ 24805 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link | This issue relates to TODO-3118 [ TODO-3118 ] |
| Description |
It would be great to have an option for mysqldump to read data as of specific timestamp from system-versioned tables. One should just use:
select * from <t> FOR SYSTEM_TIME AS OF TIMESTAMP '...' instead of simple SELECT for such tables if the option is provided. I think it makes sense to implement this separately from |
It would be great to have an option for mysqldump to read data as of specific timestamp from system-versioned tables. One should just use:
{noformat} SELECT * FROM <t> FOR SYSTEM_TIME AS OF TIMESTAMP '...'; {noformat} Instead of simple SELECT for such tables if the option is provided. I think it makes sense to implement this separately from |
Not sure if I missing something here: TODO-3118 Lists this feature as present in 10.7 trunk, and this ticket is closed with a fix version of 10.7. However, neither 10.7 trunk, nor 10.5.11 (the github.com/tempesta-tech repo listed above) nor 10.5 trunk seems to have the feature implemented:
|
10.7.1 5cc9cf9a05582307abefd8f3b57548945da86b1a and others as described above |
$ ./bin/mysqldump --help --verbose | grep -i asof
|
$ ./bin/mysqldump --asof-timestamp
|
./bin/mysqldump: unknown option '--asof-timestamp'
|
$ ./bin/mysqldump --dump-history
|
./bin/mysqldump: unknown option '--dump-history'
|
| Resolution | Fixed [ 1 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
|
10.7 any time in Sept/Oct |
$ ./bin/mysqldump --help --verbose | grep -i as-of
|
--as-of=name Dump system versioned table as of specified timestamp.
|
as-of (No default value)
|
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
Lol. The comments above mentioned --asof... not --as-of.... Thanks!
To clarify, --dump-history was (eventually) not added here, only --as-of.
The --as-of option was documented.
| Link |
This issue causes |
Logged MDEV-26943 mysqldump --as-of option ignores hour/minute precision
For any customers/users reviewing the implementation of -as-of please note there is up-to microsecond precision:
$ ./bin/mysqldump -uroot -S./socket.sock --compact test t1 --as-of="2021-11-01 06:23:43.5" --skip-tz-utc |
...
|
INSERT INTO `t1` VALUES |
(1),
|
(2),
|
(3);
|
$ ./bin/mysqldump -uroot -S./socket.sock --compact test t1 --as-of="2021-11-01 06:23:43.7" --skip-tz-utc |
...
|
INSERT INTO `t1` VALUES |
(2),
|
(3);
|
| Link | This issue causes MDEV-26946 [ MDEV-26946 ] |
Logged MDEV-26946 Various small mysqldump manual improvements for the --as-of and --tz-utc options
| Workflow | MariaDB v3 [ 87596 ] | MariaDB v4 [ 133568 ] |
| Link |
This issue relates to |
| Link |
This issue causes |
| Link |
This issue causes |
| Link |
This issue causes |
| Link | This issue causes MENT-1457 [ MENT-1457 ] |
| Zendesk Related Tickets | 139667 111284 |
There should be options for:
1. dump with history or current snapshot only (
MDEV-16029);2. dump at specific point in time.
1. and 2. should be compatible, providing the ability to dump at specific point in time with all prior history.