PolarDB:Flashback query

Prerequisites

• Your PolarDB cluster must meet one of the following version requirements:

  • PolarDB for MySQL 8.0.2 with revision version 8.0.2.2.21 or later

  • PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.32 or later

  • PolarDB for MySQL 5.7 with revision version 5.7.1.0.25 or later

  • PolarDB for MySQL 5.6 with revision version 5.6.1.0.36 or later

  For more information about how to view your cluster version, see Query the version number.

• The flashback query feature requires you to enable the innodb_backquery_enable parameter, which is disabled by default. Before you can use this feature, you must enable the innodb_backquery_enable parameter on the Parameters page of your PolarDB cluster.

  Note

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

Usage notes

• Use flashback queries for single-table scenarios. Avoid using them for complex queries, such as those that involve JOINs or subqueries.

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

• Undo logs store historical data. When you enable the flashback query feature, the Undo tablespace grows within the time window set by innodb_backquery_window. The tablespace might also grow in BLOB scenarios. This growth can slightly decrease write performance.

• When you run a flashback query, the number of Undo logs increases. Records marked for deletion might not be removed promptly. This can reduce query speed in some cases.

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

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

• You can use a flashback query to view a table that was deleted after the feature was enabled. You cannot use a flashback query to view a table that was deleted before the feature was enabled.

• Flashback queries are effective only for snapshot reads. Using a lock read returns an error: This query in backquery is not a consistent read, please check.. The following are common lock read statements:

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

Syntax

• Single-table flashback query

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

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

• Multi-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 in the syntax.

Parameters

PolarDB provides the following parameters to control the flashback feature:

Examples

This section provides an example of a single-table flashback query.

1. Prepare test data:

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

   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:

   At 2021-08-31 14:18, update the data in the products table.

   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. Run the flashback query:

   View the data in the products table at the point in time 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)