Native Flashback

How it works

Native Flashback is a feature of PolarDB-X Standard Edition implemented in the InnoDB storage engine. It works by retaining the undo log for a specific period, allowing you to query these historical versions of data. When an unintended data operation occurs, you can use the AS OF syntax to quickly query historical data and restore it, enabling data flashback in seconds.

Applicability

• Instance edition: PolarDB-X Standard Edition

• Engine version: MySQL 8.0

• Storage engine: InnoDB

Limitations

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

• A flashback query returns a snapshot from the point in time closest to the specified timestamp. It does not guarantee that the data is from the exact timestamp.

• Querying or restoring historical data across DDL operations is not supported. For example, you cannot use Native Flashback to query a deleted table.

Syntax

Native Flashback introduces the AS OF syntax to specify the point in time for a query. Use the following syntax:

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

The expression specifies the desired point in time and supports multiple formats. For example:

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

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

Example

1. Prepare the 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. Get 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 unintended operation.

   UPDATE flashback_test SET name = 'DELETED';
   
   -- Query the 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 a historical point in time. The query successfully returns the expected result.

   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)

   The query fails if the requested timestamp is beyond the retention period (default: 5 minutes) or if a DDL operation has been performed on the table.

   -- The requested timestamp is beyond the retention period.
   ERROR 7510 (HY000): Snapshot too old
   
   -- A DDL operation has been performed on the table.
   ERROR 7509 (HY000): The definition of the table required by the flashback query has changed

6. Restore the data.

   Note

   The following full table recovery is for demonstration purposes only. In a real-world scenario, if you identify the specific data that was accidentally modified, restore only that data in batches or row by row.

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

      CREATE TABLE flashback_test_tmp LIKE flashback_test;

   2. Insert the historical data from the original table into the temporary table.

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

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

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

   4. Rename the original table to flashback_test_bak and promote the temporary table to complete the data recovery.

      Note

      Before performing this operation, stop all read and write operations from your application to the table.

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

   5. Verify the data after recovery.

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