Flashback Query

Scenarios

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 seconds.
• 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. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
• 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.
• If flashback query is disabled and then enabled, the data cannot be flashed back to a point in time before the function is disabled.
• The minimum granularity of flashback query is 0.1 seconds.
• Flashback query only supports primary key indexes or the primary key GEN_CLUST_INDEX automatically generated by InnoDB for tables without primary keys.
• 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 following statements are not supported:
  • Prepared statements
  • Stored procedures
  • Complex statements
    • CREATE TABLE AS ...
    • INSERT SELECT ...
    • REPLACE SELECT ...
• 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.
• 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 pressure is high, adjust the flashback duration using the innodb_rds_backquery_window parameter to prevent the history list from being too long.

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 key 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 a table as a flashback table when creating it or change an existing non-flashback table to a flashback table.
  • Mark a table as a flashback table when creating it.

    When creating an InnoDB table, you can set the BACKQUERY parameter to mark the table as a flashback table. If BACKQUERY is not specified, the created table is a non-flashback table.

    The value of the BACKQUERY parameter can be:

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

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

  • Mark an existing non-flashback table as a flashback table.

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

    ALTER TABLE my_table BACKQUERY=1;

• The SELECT ... FROM table_name AS OF TIMESTAMP time syntax is supported.

  The time parameter specifies the time point for flashback query, accurate to 0.1 seconds.

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

  Example:

  Query data from the employee table at the fifth 0.1 second of 2026-03-09 17:38:18.

  INSERT INTO employee(id, name) VALUES(101, "John Doe");
  Query OK, 1 row affected (0.01 sec)

  SELECT NOW();
  +---------------------+
  | NOW()               |
  +---------------------+
  | 2026-03-09 17:38:18 |
  +---------------------+
  1 row in set (0.00 sec)

  UPDATE employee SET name = "Jane Doe" WHERE id = 101;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0

  SELECT * FROM employee AS OF TIMESTAMP '2026-03-09 17:38:18.5';
  +------+----------+
  | id   | name     |
  +------+----------+
  |  101 | John Doe |
  +------+----------+
  1 row in set (0.00 sec)

• The following complex statements are supported:
  • JOIN statements

    The following SQL example demonstrates a join between two tables using flashback data from different time points. Since the AS OF clause is table-specific, it applies only to the table it is bound to. As a result, the department table is queried in its historical state, whereas the employee table is queried normally, reflecting its current status.

    The timestamp can be passed through a SQL variable, where @a represents a predefined point in time within the time window of the flashback query.

    SELECT employee.name, department.name AS department FROM employee 
          JOIN department AS OF TIMESTAMP @a ON employee.departmentID = department.id;
    +---------------+-------------+
    | name          | department  |
    +---------------+-------------+
    | Jane Doe      | HR          |
    | Bob Jones     | Engineering |
    | Charlie Davis | HR          |
    | Diana Evans   | Engineering |
    +---------------+-------------+
    4 rows in set (0.01 sec)

  • UNION statements

    The following SQL example uses a UNION operator to combine real-time data from the employeeA table and flashback data from the employeeB table at the moment @a into a single result set.

    SELECT * FROM employeeA UNION SELECT * FROM employeeB AS OF TIMESTAMP @a;
    +------+---------------+
    | id   | name          |
    +------+---------------+
    |  101 | Jane Doe      |
    |  104 | Diana Evans   |
    |  102 | Bob Jones     |
    |  103 | Charlie Davis |
    +------+---------------+
    4 rows in set (0.01 sec)

  • Subqueries

    The following SQL example retrieves the current real-time employee list and executes a flashback subquery on each row to accurately fetch the department name as it existed at the moment @a.

    SELECT e.name AS name,
        (SELECT d.name FROM department AS OF TIMESTAMP @a d WHERE d.id = e.departmentID) AS department FROM employee e;
    +---------------+-------------+
    | name          | department  |
    +---------------+-------------+
    | Jane Doe      | HR          |
    | Bob Jones     | Engineering |
    | Charlie Davis | HR          |
    | Diana Evans   | Engineering |
    +---------------+-------------+
    4 rows in set (0.00 sec)

  • Different time expressions

    The following SQL example uses a JOIN statement to associate two tables' flashback data at different time points. In the example, @a indicates a moment before certain employee data was deleted, and @b indicates a moment after the Product department was added.

    The query joins historical employee data at the moment @a with department data at the moment @b, consolidating the data from two different time points into a single result set.

    SELECT * FROM employee;
    +------+---------------+--------------+
    | id   | name          | departmentID |
    +------+---------------+--------------+
    |  101 | Jane Doe      |            1 |
    |  102 | Bob Jones     |            2 |
    |  103 | Charlie Davis |            1 |
    |  104 | Diana Evans   |            2 |
    +------+---------------+--------------+
    4 rows in set (0.00 sec)
    SELECT * FROM department;
    
    +------+-------------+
    | id   | name        |
    +------+-------------+
    |    2 | Engineering |
    +------+-------------+
    1 row in set (0.00 sec)
    
    SET @a = NOW();
    Query OK, 0 rows affected (0.00 sec)
    
    DELETE FROM employee;
    Query OK, 4 rows affected (0.01 sec)
    
    INSERT INTO department (id, name) VALUES (1, "Product");
    Query OK, 1 row affected (0.00 sec)
    
    SET @b = NOW();
    Query OK, 0 rows affected (0.00 sec)
    
    SELECT employee.name, department.name AS department FROM employee AS OF TIMESTAMP @a 
        JOIN department AS OF TIMESTAMP @b ON employee.departmentID = department.ID;
    +---------------+-------------+
    | name          | department  |
    +---------------+-------------+
    | Diana Evans   | Engineering |
    | Bob Jones     | Engineering |
    | Charlie Davis | Product     |
    | Jane Doe      | Product     |
    +---------------+-------------+
    4 rows in set (0.00 sec)

FAQs