PolarDB:Flashback query

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL cluster that meets one of the following version requirements:

  • PolarDB for MySQL 8.0.2, revision version 8.0.2.2.21 or later

  • PolarDB for MySQL 8.0.1, revision version 8.0.1.1.32 or later

  • PolarDB for MySQL 5.7, revision version 5.7.1.0.25 or later

  • PolarDB for MySQL 5.6, revision version 5.6.1.0.36 or later

• The innodb_backquery_enable parameter enabled on the Parameters page of your cluster. This parameter is disabled by default.

Running a flashback query before enabling innodb_backquery_enable returns ERROR 1815 (HY000): Internal error: the backquery_time set is out of range, too old.

For instructions on checking your cluster version, see Query the version number.

How it works

PolarDB stores historical row versions in InnoDB undo logs. When you run a flashback query, PolarDB traverses the undo log chain for each row to reconstruct the data as it existed at the specified timestamp.

The time window available for queries is bounded by innodb_backquery_window (default: 86400 seconds) and the undo log capacity set by innodb_backquery_capacity_limit. When the capacity limit is reached, the effective time window is shortened. A larger time window increases Undo tablespace usage and may slightly reduce write performance.

Syntax

All flashback queries append AS OF TIMESTAMP time_expr to the table reference.

Single-table query

SELECT column_name_list FROM table_name AS OF TIMESTAMP time_expr [alias] [WHERE ...];

Multi-table query

SELECT column_name_list
FROM table1_name AS OF TIMESTAMP time_expr [alias1],
     table2_name AS OF TIMESTAMP time_expr [alias2]
[WHERE ...];

Multi-table JOIN query

SELECT column_name_list
FROM table1_name AS OF TIMESTAMP time_expr [alias1]
JOIN table2_name AS OF TIMESTAMP time_expr [alias2] ON join_cond1
JOIN table3_name AS OF TIMESTAMP time_expr [alias3] ON join_cond2
[WHERE ...];

Parameters

Parameters

Query historical data

This example shows how to use a flashback query to view data before it was modified.

Step 1: Prepare test data

At 2021-08-31 13:51, create the products table and insert five rows.

CREATE TABLE products (
    prod_id    BIGINT(10) PRIMARY KEY NOT NULL,
    prod_name  VARCHAR(20) NOT NULL,
    cust_id    BIGINT(10) NULL,
    createtime DATETIME NOT NULL DEFAULT NOW()
);

INSERT INTO products (prod_id, prod_name, cust_id, createtime)
VALUES
    (101, 'Book',   1, NOW()),
    (102, 'Apple',  1, NOW()),
    (103, 'Beef',   2, NOW()),
    (104, 'Bread',  3, NOW()),
    (105, 'Cheese', 4, NOW());

Step 2: Verify the initial data

SELECT * FROM products;

+---------+-----------+---------+---------------------+
| prod_id | prod_name | cust_id | createtime          |
+---------+-----------+---------+---------------------+
|     101 | Book      |       1 | 2021-08-31 13:51:22 |
|     102 | Apple     |       1 | 2021-08-31 13:51:24 |
|     103 | Beef      |       2 | 2021-08-31 13:51:26 |
|     104 | Bread     |       3 | 2021-08-31 13:51:27 |
|     105 | Cheese    |       4 | 2021-08-31 13:51:29 |
+---------+-----------+---------+---------------------+
5 rows in set (0.00 sec)

Step 3: Update the data

At 2021-08-31 14:18, update two rows.

UPDATE products SET prod_id = 110, createtime = NOW() WHERE prod_name = 'Book';
UPDATE products SET prod_id = 119, createtime = NOW() WHERE prod_name = 'Apple';

The table now looks like this:

+---------+-----------+---------+---------------------+
| prod_id | prod_name | cust_id | createtime          |
+---------+-----------+---------+---------------------+
|     103 | Beef      |       2 | 2021-08-31 13:51:26 |
|     104 | Bread     |       3 | 2021-08-31 13:51:27 |
|     105 | Cheese    |       4 | 2021-08-31 13:51:29 |
|     110 | Book      |       1 | 2021-08-31 14:18:21 |
|     119 | Apple     |       1 | 2021-08-31 14:18:22 |
+---------+-----------+---------+---------------------+
5 rows in set (0.00 sec)

Step 4: Query the historical snapshot

Read the table as it existed at 2021-08-31 14:00:00—after the rows were created but before they were updated.

SELECT * FROM products AS OF TIMESTAMP '2021-08-31 14:00:00';

+---------+-----------+---------+---------------------+
| prod_id | prod_name | cust_id | createtime          |
+---------+-----------+---------+---------------------+
|     101 | Book      |       1 | 2021-08-31 13:51:22 |
|     102 | Apple     |       1 | 2021-08-31 13:51:24 |
|     103 | Beef      |       2 | 2021-08-31 13:51:26 |
|     104 | Bread     |       3 | 2021-08-31 13:51:27 |
|     105 | Cheese    |       4 | 2021-08-31 13:51:29 |
+---------+-----------+---------+---------------------+
5 rows in set (0.00 sec)

Limitations

• Single-table queries only. Avoid using flashback queries in complex queries that involve JOINs or subqueries. Although the syntax supports multi-table queries, performance degrades for complex joins.

• Primary key required. Flashback queries rely on the primary key. Querying on a secondary index falls back to a full table scan, which reduces performance.

• Snapshot reads only. Flashback queries work with snapshot reads. Locking reads return the error This query in backquery is not a consistent read, please check.. The following statement types trigger a locking read and are not supported:

  -- S LOCK (REPEATABLE-READ isolation level or higher)
  INSERT INTO t1 SELECT * FROM t2
  REPLACE INTO t1 SELECT * FROM t2
  UPDATE t SET ... FROM (SELECT ...) AS h
  CREATE TABLE t1 AS SELECT * FROM t2
  
  -- S LOCK
  UPDATE t1 JOIN (SELECT ...) t2 ON ... SET ...
  SELECT * FROM t LOCK IN SHARE MODE
  
  -- X LOCK
  SELECT * FROM t FOR UPDATE

• DDL operations break historical access. After you perform a DDL operation, you cannot run a flashback query on the data that existed before the operation. If you try to query this data, the system might return the error Backquery primary key invisible.

• 100,000-version limit per row. Each row can have at most 100,000 historical versions. Queries on rows that exceed this limit return record undo history version exceed limit.

• Deleted tables. A flashback query can read a table deleted after the feature was enabled, but not one deleted before it was enabled.

• Undo log growth. Enabling flashback query causes Undo tablespace to grow within the configured time window, and further in BLOB scenarios. This growth can slightly reduce write performance. Monitor Undo tablespace usage and tune innodb_backquery_window and innodb_backquery_capacity_limit to balance query range against storage overhead.

What's next

• Query the version number