PolarDB for MySQL allows you to use the flashback query feature to retrieve data from clusters, databases, and tables as the data was at a past point in time in an efficient manner.

Prerequisites

  • Your PolarDB cluster meets one of the following requirements:
    • A PolarDB for MySQL 8.0.2 cluster whose revision version is 8.0.2.2.2 or later.
    • A PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.24 or later.
    • A PolarDB for MySQL 5.7 cluster whose revision version is 5.7.1.0.21 or later.
    • A PolarDB for MySQL 5.6 cluster whose revision version is 5.6.1.0.32 or later.

    For more information about how to check the cluster version, see Query the engine version.

  • To use the flashback query feature, you must set the innodb_backquery_enable parameter to ON. By default, this parameter is set to OFF. Before you use this feature, you must set Parameters to ON in PolarDB of the cluster.
    Note If you execute a flashback query before you set innodb_backquery_enable to ON, the "ERROR 1815 (HY000): Internal error: the backquery_time set is out of range, too old" message is returned.

Precautions

  • We recommend that you use flashback queries in single-table scenarios, instead of in complex query scenarios such as JOIN and subqueries.
  • We recommend that you use primary keys in flashback queries. Secondary indexes are not supported for flashback queries. If you use a secondary index in a flashback query, a full table scan is performed and the query performance is low.
  • If you enable the flashback query feature within the time window specified by theinnodb_backquery_window parameter, the undo tablespace increases because some historical difference data is retained in undo logs. The tablespace also increases in BLOB scenarios. The write performance may decrease slightly during this period.
  • Up to 100,000 historical versions can be provided for a single record. If you perform flashback queries for a record after this limit is reached, the "record undo history version exceed limit" error message is returned.
  • After a DDL statement is executed, you cannot perform flashback queries to retrieve the previous data. If you do so, the "Backquery primary key invisible" error message may be returned.
  • If you delete a table before the flashback query feature is enabled, you cannot view the table by using the flashback query feature. If you delete a table after the flashback query feature is enabled, you can view the table by using the flashback query feature.

Syntax

SELECT <column_name_list> FROM <table_name> AS OF <TIMESTAMP>;

Parameters

PolarDB provides the parameters to implement precise control over the flashback query feature. The following table describes the parameters.
ParameterTypeDescription
loose_innodb_backquery_enableBOOLSpecifies whether to enable the flashback query feature. Default value: OFF. Default value: true. Valid values:
  • ON
  • OFF
loose_innodb_backquery_windowULONGSpecifies the time range based on which to implement the flashback query feature.

Valid values: 1 to 604800. Unit: seconds. Default value: 86400.

Examples

  • Prepare data for the test:
    Create a table named products at 13:51 on August 31, 2021. Insert data into the table.
    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());
    
                            
  • Query the data in the products table.
    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)
  • Update the data for the test:
    Update the products table at 14:18 on August 31, 2021.
    UPDATE products SET prod_id = 110, createtime = NOW() WHERE prod_name = "Book";
    UPDATE products SET prod_id = 119, createtime = NOW() WHERE prod_name = "Apple";
    
    SELECT * FROM products;
    +---------+-----------+---------+---------------------+
    | 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)
  • Execute the flashback query:
    View the data in the products table based on 2021-08-31 14:00:00.
    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)