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

Syntax

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

Description

  • PolarDB for MySQL 5.6, 5.7, and 8.0 support this feature.
  • Before you use this feature, you must set innodb_backquery_enable to ON in Parameters of the requird PolarDB 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.
  • PolarDB provides the parameters to implement precise control over the flashback query feature. The following table describes the parameters.
    Parameter Data type Description
    innodb_backquery_enable BOOL Specifies whether to enable the flashback query feature. Default value: OFF. Valid values:
    • ON
    • OFF
    innodb_backquery_window ULONG Specifies the time range based on which to implement the flashback query feature.

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

Example

  • 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)