PolarDB:Native Flashback

Feature description

Native Flashback is a feature of PolarDB-X 2.0 Standard Edition that is based on the InnoDB engine. It retains undo logs for a specific time range, which lets you query historical versions of data. If an incorrect data operation occurs, you do not need to perform complex operations. Instead, you can use the AS OF syntax to quickly query historical data and restore it as needed. This process lets you perform a data flashback in seconds.

Applicable scope

• Instance edition: PolarDB-X 2.0 Standard Edition

• Engine version: MySQL 8.0

• Storage engine: InnoDB

Usage notes

• The Native Flashback feature consumes extra undo tablespace, which you can configure using the innodb_undo_space_supremum_size parameter.

• A Native Flashback query returns a data snapshot from the point in time that is closest to the one you specify. Therefore, the returned data is not guaranteed to be an exact match for the specified time.

• Querying and restoring historical data across Data Definition Language (DDL) operations is not supported. For example, you cannot use Native Flashback to query the content of a table that has been deleted.

Syntax description

Native Flashback provides the new AS OF syntax. You can use this syntax to specify the point in time to which you want to roll back. The syntax is as follows:

 SELECT ... FROM <table_name> AS OF TIMESTAMP <expression>;

You can specify the rollback time in the expression. The expression supports multiple formats. Examples:

SELECT ... FROM tablename AS OF TIMESTAMP '2020-11-11 00:00:00';

SELECT ... FROM tablename AS OF TIMESTAMP now();

SELECT ... FROM tablename AS OF TIMESTAMP (SELECT now());

SELECT ... FROM tablename AS OF TIMESTAMP DATE_SUB(now(), INTERVAL 1 minute);

Parameters

The Native Flashback feature provides the following configurable parameters. You can modify these parameters on the Configuration And Management > Parameter Settings page in the PolarDB-X console.

Usage example

1. Prepare test data.

   CREATE TABLE flashback_test (
       id INT PRIMARY KEY,
       name VARCHAR(50)
   );
   
   INSERT INTO flashback_test VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

2. View the original data.

   SELECT * FROM flashback_test;
   
   -- Query result
   +----+---------+
   | id | name    |
   +----+---------+
   |  1 | Alice   |
   |  2 | Bob     |
   |  3 | Charlie |
   +----+---------+
   3 rows in set (0.00 sec)

3. Obtain the current timestamp.

   SELECT now();
   
   -- Query result
   +---------------------+
   | now()               |
   +---------------------+
   | 2025-09-25 10:54:48 |
   +---------------------+
   1 row in set (0.00 sec)

4. Update the data to simulate an incorrect operation.

   UPDATE flashback_test SET name = 'DELETED';
   
   -- Query data
   SELECT * FROM flashback_test;
   
   -- Query result
   +----+---------+
   | id | name    |
   +----+---------+
   |  1 | DELETED |
   |  2 | DELETED |
   |  3 | DELETED |
   +----+---------+
   3 rows in set (0.00 sec)

5. Query the data at the historical point in time.

   SELECT * FROM flashback_test AS OF timestamp '2025-09-25 10:54:48';
   
   -- Query result
   +----+---------+
   | id | name    |
   +----+---------+
   |  1 | Alice   |
   |  2 | Bob     |
   |  3 | Charlie |
   +----+---------+
   3 rows in set (0.00 sec)

   If the query is for a time outside the historical data retention period (5 minutes by default) or if a DDL operation was performed on the table, the query fails.

   -- The historical data is outside the retention period
   ERROR 7510 (HY000): Snapshot too old
   
   -- A DDL operation was performed
   ERROR 7509 (HY000): The definition of the table required by the flashback query has changed

6. Restore the data.

   Note

   The following full table restoration is an example. In a real data restoration scenario, if you can identify the specific data affected by the incorrect operation, you can restore the data in batches or row by row as needed.

   1. Create a temporary table with the same schema as the original table.

      CREATE TABLE flashback_test_tmp LIKE flashback_test;

   2. Write the historical data from the original table to the temporary table.

      INSERT INTO flashback_test_tmp SELECT * FROM flashback_test AS OF timestamp '2025-09-25 10:54:48';

   3. Confirm that the data in the temporary table is correct.

      -- Query data
      SELECT * FROM flashback_test_tmp;
      
      -- Query result
      +----+---------+
      | id | name    |
      +----+---------+
      |  1 | Alice   |
      |  2 | Bob     |
      |  3 | Charlie |
      +----+---------+
      3 rows in set (0.00 sec)

   4. You can change the original table name to flashback_test_bak and rename the temporary table to the original table name to complete the data restoration.

      Note

      Before you perform this operation, stop business read and write operations on the table.

      -- Rename the original table and change the name of the temporary table to the original table name.
      RENAME TABLE flashback_test TO flashback_test_bak, flashback_test_tmp to flashback_test;

   5. Confirm the data after the restoration is complete.

      -- Query data
      SELECT * FROM flashback_test;
      
      -- Query result
      +----+---------+
      | id | name    |
      +----+---------+
      |  1 | Alice   |
      |  2 | Bob     |
      |  3 | Charlie |
      +----+---------+
      3 rows in set (0.00 sec)