Flashback queries

seekdb supports record-level flashback queries, which allow users to retrieve data from a historical version. You can use the AS OF SNAPSHOT syntax to perform flashback queries.

seekdb supports flashback queries by setting the undo_retention parameter. After you set the undo_retention parameter, you can perform flashback queries to retrieve data from multiple versions between T - undo_retention and the current time T. The default value of the undo_retention parameter is 1800 seconds. For more information about the undo_retention parameter, see undo_retention.

Considerations

When you use the undo_retention parameter to perform a flashback query, note the following:

• The value of the undo_retention parameter specifies the time range during which data can be flashback queried. Data that is modified after the specified time cannot be flashback queried.

• If the table queried is moved to the recycle bin, you must restore the table from the recycle bin before you perform a flashback query.

  For more information about how to restore a recycle bin object, see Restore a recycle bin object.

• Assume that the current time is T. After you set the undo_retention parameter, if a DDL operation is performed within the T - undo_retention time range, the following applies:

  • For a column addition operation, the new columns have default values when you perform a flashback query before the operation.

  • For a table creation, table deletion, or column deletion operation, an error is returned when you perform a flashback query before the operation.

• Flashback queries require additional storage space to retain historical data. If you increase the value of the undo_retention parameter, the storage space required for historical data increases. We recommend that you monitor the storage space after you increase the value of the undo_retention parameter.

Prerequisites

Before you perform a flashback query by using the undo_retention parameter, you must modify the value of the undo_retention parameter. The default value of the undo_retention parameter is 1800 seconds.

1. Log in to seekdb as the root user.

   The following example shows how to connect to the database. Replace the actual environment with the parameters in the example.

   mysql -h127.0.0.1 -uroot -P2881

2. Execute the following statement to modify the value of the undo_retention parameter.

   The following example shows how to modify the value of the undo_retention parameter:

   ALTER SYSTEM SET undo_retention=900;

   For more information about the undo_retention parameter, see undo_retention.

Perform a flashback query

1. Log in to seekdb.

   The following example shows how to connect to the database. Replace the actual environment with the parameters in the example.

   mysql -h127.0.0.1 -uroot -P2881

2. Perform a flashback query on a table.

   Assume that the value of the undo_retention parameter is 900 and that you have a table named table1. The following example shows how to specify a historical time by using the AS OF SNAPSHOT clause and perform a flashback query to retrieve data from the table at the specified historical time.

   SELECT * FROM table1 AS OF SNAPSHOT 1597306800000000000;

   In the preceding example, 1597306800000000000 is a timestamp in nanoseconds. The timestamp starts at 08:00:00 on August 1970 in Beijing Time. You can convert the time to be queried to a timestamp as needed.

   For example, to flashback query data from the table at 16:20:00 on August 13, 2020, you can convert the time to a timestamp as follows:

   SELECT time_to_usec('2020-08-13 16:20:00') * 1000;
   +--------------------------------------------+
   | time_to_usec('2022-01-01 00:00:00') * 1000 |
   +--------------------------------------------+
   |                        1597306800000000000 |
   +--------------------------------------------+
   1 row in set