Flashback Query

Introduction

Database flashback is a core requirement for customers in scenarios such as accidental data deletion, data errors caused by vulnerabilities, and service audit requiring historical data tracing. The traditional binlog-based flashback solution is inefficient and strongly relies on binlogs. To address these pain points, TaurusDB supports undo log-based table-level flashback query. The core advantages are as follows:

• Kernel-level support: It directly uses historical data mirrors in the undo tablespace, without external dependencies.
• Accurate restoration: Table-level data can be rolled back to a specified point in time, accurate to 0.1 second.
• Single-table flashback: There is no need to retain all undo logs in the flashback duration. Only undo logs of the flashback table need to be retained.

Supported Versions

• Flashback query is only supported when the kernel version of your TaurusDB instance is 2.0.69.250900 or later.
• If the kernel version of your TaurusDB instance is 2.0.69.250900, undo tablespace truncation and asynchronous truncation of flashback undo logs are mutually exclusive. In other words, you cannot have both undo tablespace truncation and innodb_rds_backquery_undo_async_trunc enabled at the same time.

Constraints

• Only InnoDB engine tables are supported. Other views and engine tables are not supported.
• DDL operations are not allowed within the flashback time window, especially operations that change clustered indexes, such as DROP TABLE, TRUNCATE TABLE, ADD COLUMN, DROP COLUMN, and OPTIMIZE TABLE. Only DML operations are allowed within the flashback time window.
• The minimum granularity of flashback query is 0.1 second.
• Flashback query only supports primary key indexes or the primary key GEN_CLUST_INDEX automatically generated by InnoDB for tables without primary keys.
• High service write pressure will cause a purge performance bottleneck. Limiting the length of the InnoDB history list can alleviate this bottleneck. The specific method is as follows:
  • Enable flashback for non-I/O services. A long history list slows down InnoDB purge operations significantly when pages in the InnoDB buffer pool are frequently swapped out.
  • Restrict the flashback time window length. When the write service pressure is high, adjust the flashback duration using the innodb_rds_backquery_window parameter to prevent the history list from being too long.
• Flashback query cannot be used in prepared statements or stored procedures.
• The following complex statements are not supported:
  • CREATE TABLE AS ...
  • INSERT SELECT ...
  • REPLACE SELECT ...
• If the feature is disabled and then enabled, the data cannot be flashed back to a point in time before the feature is disabled.
• If you modify the innodb_rds_backquery_enable parameter, the update will happen asynchronously. If you try to modify the parameter again while the initial update is still in process, you will receive the error message "The BACKQUERY state cannot be changed because the previous state modification is not yet complete."
• Flashback query is not supported in migration scenarios. This feature relies on undo logs. In migration scenarios, historical data generated before a new instance was created cannot be queried using the flashback feature because the source instance's undo logs are missing.

Principles

1. Purge process of undo logs:
   1. The first round of purge, triggered by native InnoDB, uses the oldest read view in the MVCC m_views to thoroughly clean primary and secondary indexes of non-flashback tables, and secondary indexes of flashback tables. Their clustered indexes are retained.

      Triggers for this process include updating data dictionary statistics, recovering from crashes or canceling atomic DDL operations, shutting down the undo tablespace, conducting regular checks in the InnoDB master thread, committing transactions, manually enabling or disabling purge by setting parameters, and stopping the MySQL process.

   2. The second round of purge, triggered by the flashback background thread, uses the oldest read view (active transaction list at a certain point in time) of the flashback system to thoroughly clean up primary key indexes of flashback tables.
2. Truncation process of undo logs:
   1. MVCC truncation: This step deletes undo logs that do not contain flashback tables.
   2. Flashback truncation: This step deletes undo logs that contain flashback tables.

   Alternatively, you can start with flashback truncation followed by MVCC truncation. They are only responsible for deleting undo logs within the read view. The following figure shows the undo log status.

   Figure 1 Undo log truncation
   
3. Flashback query

During flashback query, the read view at a specified point in time is used to construct a historical data view through the history list and return the query result.

Parameter Description

On the Parameters page of the management console, you can set the parameters listed in Table 1 to enable flashback query and set the flashback read view and flashback query time.

New System Catalog

A system catalog is created by default to store flashback read views. No manual operations are required on the system catalog. TaurusDB automatically inserts and clears data in the system catalog.

CREATE TABLE `__taurus_sys__`.`backquery` (
  `Snapshot_time` bigint unsigned NOT NULL,
  `Low_limit_id` bigint unsigned NOT NULL,
  `Up_limit_id` bigint unsigned NOT NULL,
  `Low_limit_no` bigint unsigned NOT NULL,
  `Trx_ids_count` bigint unsigned NOT NULL,
  `Trx_ids` mediumblob,
  PRIMARY KEY (`Snapshot_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Backquery system table.'

Usage

• You can mark an InnoDB table as a flashback table during creation by setting BACKQUERY, or mark an existing table as a flashback table.

  BACKQUERY can be set to 0, 1, or default. If BACKQUERY is not specified, the table is non-flashback table.

  • 0: non-flashback table
  • 1: flashback table
  • default: non-flashback table

  Example:

  CREATE TABLE my_table(
      id INT AUTO_INCREMENT PRIMARY KEY,
      data VARCHAR(255)
  ) ENGINE=InnoDB, BACKQUERY=1;

• The flashback query syntax SELECT... FROM <table name> AS OF TIMESTAMP <time> is supported. <time> indicates the flashback query time point, which is accurate to 0.1 second.

  Example:

  SELECT * FROM employee AS OF TIMESTAMP '2025-02-01 14:30:00.5'; 

  You can use the SQL statement to query the data of the employee table at the fifth millisecond of 14:30:00 on February 1, 2025.

  Notes:

  • Incomplete timestamps will be automatically filled with zeros.
  • If no read view exists at the specified time, the system automatically searches for an earlier read view within the time range specified by innodb_rds_backquery_window.
• The following complex statements are supported:
  • JOIN statements

    SELECT Employees.name, Departments.name AS department FROM Employees 
      JOIN Departments AS OF TIMESTAMP @a ON Employees.departmentID = Departments.id;

  • UNION statements

    SELECT * from EmployeesA UNION SELECT * FROM EmployeesB AS OF TIMESTAMP @a;

  • Subqueries

    SELECT e.name AS employee_name,
      (SELECT d.department_name FROM Departments AS OF TIMESTAMP @a d
       WHERE d.id = e.departmentID) AS department_name
    FROM Employees e;