Tidb: support flashback table to any point in time

[leoppro]

Feature Request

Describe the feature you'd like:

TiDB already supports the tidb_snapshot variable, with the ability to read snapshots at any time. But I would like TiDB to support reverting table data back to an arbitrary time(later than GC safe time).

For example

INSERT INTO tbl (id) VALUES (1); -- commit tso = 11
INSERT INTO tbl (id) VALUES (2); -- commit tso = 12
INSERT INTO tbl (id) VALUES (3); -- commit tso = 13

SELECT * FROM tbl;
|-----|
|   id|
|    1|
|    2|
|    3|
|-----|

FLASHBACK tbl TO 12; -- Desired new syntax, with the ability to reverting table data

SELECT * FROM tbl;
|-----|
|   id|
|    1|
|    2|
|-----|

INSERT INTO tbl (id) VALUES (5); 

SELECT * FROM tbl;
|-----|
|   id|
|    1|
|    2|
|    5|
|-----|

Teachability, Documentation, Adoption, Migration Strategy:

[zz-jason]

This feature request LGTM. MariaDB also supports flashback to a point in time:

Something goes wrong on the master (like a wrong update or delete) and you would like to revert to a state of the database (or just a table) at a certain point in time.

The SQL syntax, target release version may need further discussion.

[zhangjinpeng1987]

I suggest that using the physical time to instead of 12 in 'FLASHBACK table tbl TO 12'.

[zz-jason]

it's also related to time-travel queries, maybe we can borrow a similar syntax.

[amyangfei]

Following is a requirement of the flash back feature from TiCDC:

---

Background

We have supported a tso-map feature in TiCDC, which means TiCDC can replicate data to a consistent state periodically (under the conditions that upstream TiDB is available), and persists a tso map which records a consistent state between upstream and downstream. Based on that premise, when upstream meets disaster, if downstream TiDB can recover data to the latest TSO in tso-map, we could get a latest consistent state compared to upstream.

Precondition

Currently TiDB provides a snapshot read feature, but is not able to recover data to specific TSO, we hope TiDB to provide a flash back feature, which enables:

• Purge data with commit-ts larger then specific TSO, which is equivalent to recover TiDB data to specific TSO.
  Possible solution for flash back

Supposing TiDB wants to recover data to TSO-X, we first process an incremental scan in TiKV with data larger then TSO-X (result from the incremental scan means the whole KV changes after TSO-X ), then we can use these KV changes to generate undo SQL or writes the old value back to TiKV directly.

Availability guarantee

RTO: time cost by flash back
RPO: equals to ts-map interval, minute level

Advantage

• Keep the high availability of TiCDC (no persistent storage introduced).
• Can achieve minute level RPO and RTO.
• After TiDB provides flash back feature, it can be used not only in TiCDC consistent replication, but also in many data recovery scenarios.
• This solution doesn’t change TiDB, TiKV and TiCDC too much, and it is code decoupling.

[kennytm]

Previously in #13109 the syntax was FLASHBACK tbl UNTIL TIMESTAMP '2020-01-01 12:34:56', but this syntax was removed in #14604.

Anyway FLASHBACK TABLE tbl TO tbl2 already has a meaning (equivalent to FLASHBACK TABLE tbl TO BEFORE DROP RENAME TO tbl2 in Oracle), so don't reuse bare TO as a keyword to introduce the timestamp.

I suggest that using the physical time to instead of 12 in 'FLASHBACK table tbl TO 12'.

TSO is important to communicate the exact point of flashback. I think we should support all of them just like BACKUP SNAPSHOT.

FLASHBACK TABLE tbl TIMESTAMP '2020-01-01 12:34:56';
FLASHBACK TABLE tbl TIMESTAMP 420147062101006;
FLASHBACK TABLE tbl TIMESTAMP 2 HOUR AGO;

[amyangfei]

Part1

TiDB should support the syntax as follows, where tbl must be provided

FLASHBACK TABLE tbl TIMESTAMP '2020-01-01 12:34:56';

Work procedure

• TiDB receives the FLASHBACK command, check TiKV GC safepoint, dispatches a DDL job (or create another executor and use standalone job queue).
• Job processing
  
  
  
  • Request TiKV via EventFeed API for an incremental data scan, the old-value switch is turned on in the request.
  
  
  • After the incremental scan of all regions finish, sort the received kv entries by start-ts.
  
  
  • Decode the sorted data, generate undo SQL into a list.
  
  
  • Execute the undo SQLs in the reversed order.
  
  

Task break down (approximately 30 man-day)

• Develop
  
  
  
  • incremental data scan and sorter module (similar to the logic in TiCDC). 5 man-day
  
  
  • gen undo SQL module (similar to PiTR undo SQL module, but unfortunately the module in PiTR has not been finished). 5 man-day
  
  
  • parser, command processor, the command should be processed as a DDL. 10 man-day
  
  
• Test 10 man-day

Part2

Should we consider to provide a syntax as follows?

FLASHBACK DATABASE test TIMESTAMP '2020-01-01 12:34:56'; 
FLASHBACK ALL TIMESTAMP '2020-01-01 12:34:56';

It is easy to extend the syntax if part-1 is finished.

Corner case

1. Supposing flashback to TSO-1 from TSO-2, what happens if DDL exists between TSO-1 and TSO-2

DDL increases the complexity, we should handle both the table schema and undo SQL should be based on two schemas.

However in the implementation of TiCDC, DDL is a strong consistent barrier, which means only after the syncpoint is recorded, more DMLs can continue to be replicated. So in the usage scenario of TiCDC, we can avoid DDL in the undo procedure.

2. GC of the TiDB server

The incremental scan should enable the old-value feature of TiKV, both the old value and new value should not be GCed.

[zz-jason]

May I suggest the following SQL syntax, which is similar to time travel queries proposed in https://github.com/pingcap/tidb/issues/18672:

FLASHBACK TABLE tbl AS OF TIMESTAMP '2020-01-01 12:34:56';
FLASHBACK TABLE tbl AS OF TIMESTAMP 420147062101006;
FLASHBACK TABLE tbl AS OF TIMESTAMP 2 HOUR AGO;

IMO, it's better to have a uniform SQL syntax to reduce the learning and remembering curve.

[bb7133]

Looks that the brief idea is straightforward, here are some details I'm considering:

• Since 'Flashback' is a DDL job, I guess that the 'Undo SQLs' are generated and executed when the job is executing(and by DDL worker), right?
• Shall we support 'rollback' for it? Considering that when the table is flash-backing, there may online updates running and conflicts may happen.
• When we consider the 'TIMESTAMP', is it timezone-aware or not?

@amyangfei Would you please file an RFC for your proposal? Thanks!

[AilinKid]

how can you distinguish the undoSQL is an update or insert from the old value in kv?

[wjhuang2016]

And， how the DDL schema changes？

[amyangfei]

Looks that the brief idea is straightforward, here are some details I'm considering:

• Since 'Flashback' is a DDL job, I guess that the 'Undo SQLs' are _generated_ and _executed_ when the job is executing(and by DDL worker), right?
• Shall we support 'rollback' for it? Considering that when the table is flash-backing, there may online updates running and conflicts may happen.
• When we consider the 'TIMESTAMP', is it timezone-aware or not?

@amyangfei Would you please file an RFC for your proposal? Thanks!

1. Yes, the undo SQLs are generated in DDL worker
2. Yep, there will be a conflict if new DMLs are executed during flashback, we should either warn users not to execute DMLs during flashback or provide some mechanism to disable new writing traffic (I have no idea about the second method)
3. the TIMESTAMP or TSO could use the same strategy in BACKUP SNAPSHOT

I will file an RFC soon. @bb7133

[amyangfei]

how can you distinguish the undoSQL is an update or insert from the old value in kv?

The kv API provides two fields, old row data and new row data

• if old row data is nil, it is insert
• if old row data is not nil, and new row data is nil, it is delete
• if both old row data and new row data are not nil, it is update

@AilinKid

[amyangfei]

And， how the DDL schema changes？

Could you please describe the scenario in greater detail? @wjhuang2016

[bb7133]

1. the TIMESTAMP or TSO could use the same strategy in BACKUP SNAPSHOT

I will file an RFC soon. @bb7133

@amyangfei So it is timezone-aware. I guess that FLASHBACK needs to be replicated by TiCDC, right? If so we should treat the timestamp carefully when the upstream/downstream clusters are in different TZ.

[wjhuang2016]

And， how the DDL schema changes？

Could you please describe the scenario in greater detail? @wjhuang2016

Is the state of the table from StatePublic to StatePublic? Is there any intermediate state?

[amyangfei]

And， how the DDL schema changes？

Could you please describe the scenario in greater detail? @wjhuang2016

Is the state of the table from StatePublic to StatePublic? Is there any intermediate state?

Emmm, in fact there is no schema change, the purpose of using DDL is to reuse the ddl job queue.

There is an alternative way, we can create another job queue just like BRIE executor does, and don't go to the ddl part, maybe it is simpler.

What do you think of it? @wjhuang2016

[wjhuang2016]

And， how the DDL schema changes？

Could you please describe the scenario in greater detail? @wjhuang2016

Is the state of the table from StatePublic to StatePublic? Is there any intermediate state?

Emmm, in fact there is no schema change, the purpose of using DDL is to reuse the ddl job queue.

There is an alternative way, we can create another job queue just like BRIE executor does, and don't go to the ddl part, maybe it is simpler.

What do you think of it? @wjhuang2016

We can refer to RecoverTable, and we need to disable GC.