PolarDB:Flashback queries

Prerequisites

• Your PolarDB cluster must have one of the following versions:

  • PolarDB for MySQL 8.0.2 with a revision version of 8.0.2.2.21 or later.

  • PolarDB for MySQL 8.0.1 with a revision version of 8.0.1.1.32 or later.

  • PolarDB for MySQL 5.7 with a revision version of 5.7.1.0.25 or later.

  • PolarDB for MySQL 5.6 with a revision version of 5.6.1.0.36 or later.

  For more information, see Query the version number.

• The flashback query feature depends on the innodb_backquery_enable parameter. This parameter is disabled by default. To use this feature, you must enable the innodb_backquery_enable parameter in the Parameters of your PolarDB cluster.

  Note

  If you execute a flashback query before you enable the innodb_backquery_enable parameter, the system returns the following error: ERROR 1815 (HY000): Internal error: the backquery_time set is out of range, too old.

Precautions

• Use flashback queries for single-table scenarios. Do not use them for complex query scenarios such as those involving JOINs or subqueries.

• Use primary keys in flashback queries. Secondary indexes are not supported for flashback queries. If you use a secondary index in a flashback query, the query is converted to a full table scan, which degrades query performance.

• When you enable the flashback query feature, the undo tablespace increases because undo logs retain historical data within the time window specified by the innodb_backquery_window parameter. The tablespace might also increase in BLOB scenarios. Write performance might decrease slightly while the tablespace is growing.

• When the flashback query feature is enabled, the number of undo logs increases. Records marked for deletion might not be removed promptly. This can degrade query performance in some cases.

• A single record can have a maximum of 100,000 historical versions. If you perform a flashback query on a record that exceeds this limit, the system returns the following error: record undo history version exceed limit.

• After you perform a DDL operation, you cannot use a flashback query to retrieve data from a point in time before the operation. If you attempt to do so, the system might return the following error: Backquery primary key invisible.

• You can use a flashback query to view a deleted table only if the feature was enabled before the table was deleted.

• Flashback queries are effective only in Snapshot Read scenarios. If you use a locking read (Lock Read), an error is returned: This query in backquery is not a consistent read, please check. The following are common locking read statements:

  SELECT * FROM your_table WHERE condition LOCK IN SHARE MODE;
  SELECT * FROM your_table WHERE condition FOR UPDATE;
  
  /* REPEATABLE-READ and higher isolation levels use Lock Read. */
  /* The READ-COMMITTED isolation level uses Snapshot Read. */
  INSERT INTO ... SELECT ...; 

Syntax

• Syntax for a single-table flashback query

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

• Syntax for a multiple-table flashback query

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

• Syntax for a multiple-table JOIN flashback 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...;

The following table describes the parameters.

Parameters

PolarDB provides the following parameters to control the flashback feature:

Example

This example shows how to perform a flashback query on a single table.

1. Prepare the test data.

   Create the products table and insert data into it at 2021-08-31 13:51.

   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 the test data.

   Update the data in the products table at 2021-08-31 14:18.

   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 a flashback query.

   Query the data in the products table as it existed at 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)