How PolarDB‑X’s SQL Flashback Restores Accidentally Deleted Rows in Seconds

1. Origin and Motivation

In the IT community, the phrase “delete the database and run away” humorously highlights the critical value of database data. Real incidents, such as the 2020 Weimeng case where a listed company lost over 10 billion CNY in market value, demonstrate the severe impact of accidental data loss. Based on years of database experience, the author notes that while intentional “delete‑and‑run” cases are rare, careless mistakes—such as missing a condition in a DELETE statement—frequently cause data loss.

Accidental deletions are grouped into three categories:

Row‑level deletion (5★) : multiple rows removed by DELETE / UPDATE statements.

Table‑level deletion (3★) : entire tables dropped via DROP TABLE or cleared with TRUNCATE TABLE.

Database‑level deletion (1★) : whole databases removed with DROP DATABASE.

PolarDB‑X offers specialized recovery capabilities for each scenario, with this article focusing on the row‑level case.

2. Accident Scene

A concrete incident is described:

T1: DBA creates an employee table.

T2: A developer runs DELETE FROM employee WHERE name='Mary' but forgets an AND clause, unintentionally deleting Ralph’s row.

T3: Business continues; new rows are inserted while the developer discovers the loss and seeks recovery.

The article then asks how PolarDB‑X can rescue the developer.

3. Existing Recovery Solutions

3.1 Point‑in‑Time Recovery (PITR)

PITR restores a database to a specific past moment using full backups and incremental change logs. While widely supported, PITR has drawbacks:

Long recovery time : the entire database (or table) must be restored even for a few rows.

Extra storage : restored data is often placed in a new instance, requiring additional disk space.

Business data loss : restoring to a time before the accident discards legitimate changes made after that point.

Illustrations (omitted here) show that restoring to T2 recovers the deleted rows but also loses normal operations between T2 and T3.

3.2 Flashback Query (Undo‑based)

Oracle’s Flashback Query reads undo logs to reconstruct a snapshot of a table at a prior timestamp. PolarDB‑X implements a similar feature, using undo tables to fetch historical rows while ensuring consistency across shards.

Example Oracle query:

SELECT * FROM employee AS OF TIMESTAMP TO_TIMESTAMP('2021-04-06 19:23:23','YYYY-MM-DD HH24:MI:SS');

Advantages: fast recovery, no extra storage. Limitations:

Still loses business data after the chosen timestamp.

Undo data is retained only for a limited period; once cleared, flashback is impossible.

3.3 Binlog‑Based Flashback (MySQL)

MySQL’s binary log records row‑level changes when binlog_format=row. A binlog flashback tool extracts these changes, reverses them, and generates INSERT statements to restore deleted rows.

Sample reverse statements:

INSERT INTO test.employee VALUES('2','Eric Zhang');</code><code>INSERT INTO test.employee VALUES('3','Leo Li');

Pros: quicker than PITR and longer retention than undo‑based flashback. Cons:

Rollback range may be too broad, affecting legitimate operations that occurred in the same time window.

Manual filtering of unrelated rows is often required, adding effort and risk of error.

4. PolarDB‑X SQL Flashback

PolarDB‑X introduces a precise SQL‑level rollback mechanism:

Every executed SQL receives a unique TraceID, enabling exact tracing of data changes.

When an accidental DELETE is identified, the corresponding TraceID (e.g., abcm321) is used to locate the offending statement.

The system generates a rollback SQL that restores only the rows affected by that specific statement.

4.1 Quick Three‑Step Workflow

Locate the erroneous SQL’s TraceID via the SQL audit feature.

In the SQL Flashback UI, enter the approximate execution time range and the TraceID, then submit the flashback task.

After the task completes, download the recovery file and apply it to restore the data.

Images (not displayed here) illustrate each step, showing the audit view, the flashback submission form, and the download page.

5. Conclusion

The article demonstrates how PolarDB‑X’s SQL Flashback precisely rolls back row‑level accidental deletions, offering faster, more accurate recovery than traditional PITR, undo‑based flashback, or binlog tools. Future posts will detail other PolarDB‑X safety features such as Recycle Bin and PITR.