PolarDB:Flashback queries

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. Therefore, before you use this feature, you must set innodb_backquery_enable to ON on the Parameters page of the 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.

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 the innodb_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

• Flashback query syntax for a singled table

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

• Flashback query syntax for multiple tables

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

• Flashback query syntax for joining multiple tables

  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...;

The following table describes the parameters in the preceding statements.

Parameters

PolarDB provides the following parameters to implement precise control over the flashback query feature.

Examples

Flashback for a single-table is used in the examples.

1. Prepare data.

   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());
   
                           

2. 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)

3. Update data.

   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)

4. Execute the flashback query.

   Query the data in the products table at 14:00:00 on August 31, 2021.

   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)