Are there any good tools in TiDB for quickly rolling back the last executed erroneous SQL on a single table?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb有好的工具针对单表进行上一次执行的错误的sql,进行快速回滚操作吗

| username: 天下无贼

I found that using TiDB’s TiUP for full database backup and then restoring the corresponding table in the corresponding database results in very high load. For 10 million records, the memory usage is almost maxed out, making it impractical for online use. Additionally, the table needs to not exist, which means you need to rename the table (for 10 million records, renaming also results in memory errors and cannot complete). The only option is to delete the table and then use the full backup to restore the corresponding table, which requires stopping the business.

| username: zhanggame1 | Original post link

What do you use for backups? Dumpling?

| username: 天下无贼 | Original post link

The application database backed up by tiup br

| username: 我是咖啡哥 | Original post link

If it’s within the GC range, use Flashback.

| username: Jellybean | Original post link

TiDB’s MVCC mechanism ensures that as long as the data has not been garbage collected (GC), you can restore your data to any version at any point in time during that period.

Check out the flashback and recover features on the official website; they should be very enlightening for you.

| username: tidb菜鸟一只 | Original post link

If within the GC time:
For DML operations, directly set @@tidb_snapshot=“2016-10-08 16:45:26”; — the time point before the abnormal SQL execution, then dump the table with Dumpling, and then import it.
For DDL operations, if it’s DROP TABLE t; then FLASHBACK TABLE t; if it’s TRUNCATE TABLE t; then FLASHBACK TABLE t TO t1;.

| username: 天下无贼 | Original post link

Okay, thank you.

| username: 天下无贼 | Original post link

Official warning:
Using this feature in TiDB v7.1.0 may result in some Regions still being in the FLASHBACK process after FLASHBACK is completed. Please try to avoid using this feature in v7.1.0.
I happened to be using the latest 7.1.0, and this really happened.

| username: 天下无贼 | Original post link

The issue is that flashback cannot specify a table; all tables will be affected.

| username: Jellybean | Original post link

As long as the table deleted by DROP or TRUNCATE is after the tikv_gc_safe_point time, it can be restored using the FLASHBACK TABLE syntax.

| username: redgame | Original post link

No, flashback.

| username: 天下无贼 | Original post link

Well, but if you just delete some data from table1, FLASHBACK CLUSTER TO TIMESTAMP ‘2023-07-27 14:37:30’; will revert the entire cluster to the state at 2023-07-27 14:37:30, so other databases and tables will also be affected.

| username: 天下无贼 | Original post link

I verified it, and it is indeed the case. Because from the safe point to the time point you want to flashback, your other tables definitely have ongoing business inserts. So if you use FLASHBACK CLUSTER TO TIMESTAMP ‘2023-07-27 14:37:30’; to restore to a certain point, other tables will indeed be rolled back as well. This should be used with caution.

| username: 天下无贼 | Original post link

Help! Is there any way to do this? I just want to roll back the last change or deletion on a single table. Can TiDB’s flashback feature do this?

| username: Jellybean | Original post link

FLASHBACK supports flashing back a specific database or table. I suggest you carefully review the various documents on the official website.

You can use the FLASHBACK TABLE statement to restore tables and data that have been DROPped or TRUNCATEd.
You can use the FLASHBACK DATABASE statement to restore databases and data that have been DROPped.
You can use FLASHBACK CLUSTER TO TIMESTAMP to restore the cluster’s data to a specific point in time.

| username: 天下无贼 | Original post link

Yes, you are right, boss. I am looking for a tool or command to back table xxx to time xxx, but it seems there isn’t one.

| username: 天下无贼 | Original post link

It seems that there is no rollback or restore command or tool to revert changes to a specific table to a previous point in time.

| username: 天下无贼 | Original post link

From this perspective, tiup br is relatively easier to use because it can perform a full backup or single table backup before executing single table changes. If there are any issues, you can restore the single table through the full backup or single table backup. I have tested that a full backup snapshot to a self-built S3 takes about one minute, and restoring a single table to TiDB takes only ten seconds. However, I encountered a problem: when restoring a single table with 10 million records, the table needs to be non-existent. But if you don’t want to delete the table, you can only rename it. This renaming process takes a long time and consumes a lot of memory, even causing an OOM error.

| username: Jellybean | Original post link

TiDB can also read historical data through the system variable tidb_snapshot. You can take a look at this content first.

You can also use the Stale Read feature to read historical data. Stale Read is a mechanism for reading historical data versions, allowing you to read any historical data version stored in TiDB at any point in time (as long as it has not been garbage collected). The official recommendation is to use Stale Read for reading historical data.

| username: 天下无贼 | Original post link

Sure, sure. I just started with TiDB not long ago. Thanks for the guidance, expert.