Skip to main content

Native flashback

Native Flashback lets a query read retained historical InnoDB data with AS OF TIMESTAMP. AliSQL periodically records snapshot history and uses retained undo to reconstruct a consistent historical read view.

It is a targeted investigation and recovery mechanism. It is not a replacement for backups, binlogs, restore drills, or disaster-recovery procedures.

The three controls that matter​

Control8.0.44-2 defaultPurpose
innodb_rds_flashback_enabledONAllows AS OF TIMESTAMP queries
innodb_rds_flashback_task_enabledOFFParticipates in snapshot generation
innodb_undo_retention0 secondsSets the undo-retention target

The query gate being on does not create historical coverage. Snapshot generation requires both the task and nonzero undo retention.

Configure before startup​

The background thread is created at server startup when the task or retention is enabled. To avoid enabling variables later without a running background thread, configure both values before starting the server:

[mysqld]
innodb_rds_flashback_task_enabled=ON
innodb_undo_retention=3600
innodb_rds_flashback_interval=1

Confirm the effective state after startup:

SHOW GLOBAL VARIABLES LIKE 'innodb_rds_flashback%';
SHOW GLOBAL VARIABLES LIKE 'innodb_undo_retention';

Query a historical view​

SELECT id, status, updated_at
FROM orders AS OF TIMESTAMP DATE_SUB(NOW(), INTERVAL 5 MINUTE)
WHERE customer_id = 1001;

The requested wall-clock time and selected snapshot do not have to be identical. innodb_rds_flashback_allow_gap limits the accepted difference; its open-source default is 30 minutes. A timestamp outside the retained window returns ER_SNAPSHOT_OUT_OF_RANGE.

Inspect the available window before planning recovery:

CALL dbms_admin.analyze_flashback_snapshots();
CALL dbms_admin.show_flashback_snapshots(NOW(), 'DESC', 20);

The analysis procedure reports the minimum and maximum snapshot timestamps and the undo-space usage reported by InnoDB. The show and delete procedures require SUPER.

Recover into a separate table​

Keep the historical read separate from the destructive cutover:

CREATE TABLE recovered_orders LIKE orders;

INSERT INTO recovered_orders
SELECT *
FROM orders AS OF TIMESTAMP '2026-07-16 10:00:00';

Validate row counts and business invariants first. Quiesce application traffic before any rename or replacement, and retain the original table under a backup name until the recovered result has been verified.

Operational boundaries​

  • Historical table references support SELECT on InnoDB base tables.
  • Temporary tables, views, FOR UPDATE, and LOCK IN SHARE MODE are not supported.
  • DDL that changes the visible primary-key record can make an older snapshot incompatible; historical access across arbitrary DDL is not guaranteed.
  • Undo retention is a target, not an unconditional guarantee. Space limits and workload pressure can shorten the effective window.
  • Retaining more undo consumes storage and can increase purge work.
  • innodb_rds_flashback_task_stop_all=ON pauses snapshot creation and history cleanup.

Variables to review​

VariableDefaultRole
innodb_rds_flashback_interval1 secondSnapshot generation interval
innodb_rds_flashback_allow_gap30 minutesMaximum requested-time / selected-snapshot gap
innodb_rds_flashback_print_warningONWarn when the selected snapshot is not exact
innodb_undo_space_supremum_size10240 MiBUndo-space ceiling used by retention cleanup
innodb_undo_space_reserved_size0 MiBReserved undo-space threshold

Authoritative references​