Recovery from Operational Errors

This guide provides step-by-step instructions for recovering from common operational errors in TiDB Cloud Lake.

Introduction

TiDB Cloud Lake can help you recover from these common operational errors:

• Accidentally dropped databases
• Accidentally dropped tables
• Incorrect data modifications (UPDATE/DELETE operations)
• Accidentally truncated tables
• Data loading mistakes
• Schema evolution rollbacks (reverting table structure changes)
• Dropped columns or constraints

These recovery capabilities are powered by TiDB Cloud Lake's FUSE engine with its Git-like storage design, which maintains snapshots of your data at different points in time.

Recovery Scenarios and Solutions

Scenario: Accidentally Dropped Database

If you've accidentally dropped a database, you can restore it using the UNDROP DATABASE command:

1. Identify the dropped database:

   SHOW DROP DATABASES LIKE '%sales_data%';
   

2. Restore the dropped database:

   UNDROP DATABASE sales_data;
   

3. Verify the database has been restored:

   SHOW DATABASES;
   

4. Restore ownership (if needed):

   GRANT OWNERSHIP on sales_data.* to ROLE <role_name>;
   

Important: A dropped database can only be restored within the retention period (default is 24 hours).

For more details, see UNDROP DATABASE and SHOW DROP DATABASES.

Scenario: Accidentally Dropped Table

If you've accidentally dropped a table, you can restore it using the UNDROP TABLE command:

1. Identify the dropped table:

   SHOW DROP TABLES LIKE '%order%';
   

2. Restore the dropped table:

   UNDROP TABLE sales_data.orders;
   

3. Verify the table has been restored:

   SHOW TABLES FROM sales_data;
   

4. Restore ownership (if needed):

   GRANT OWNERSHIP on sales_data.orders to ROLE <role_name>;
   

Important: A dropped table can only be restored within the retention period (default is 24 hours).

For more details, see UNDROP TABLE and SHOW DROP TABLES.

Scenario: Incorrect Data Updates or Deletions

If you've accidentally modified or deleted data in a table, you can restore it to a previous state using the FLASHBACK TABLE command:

1. Identify the snapshot ID or timestamp before the incorrect operation:

   SELECT * FROM fuse_snapshot('sales_data', 'orders');
   

   snapshot_id: c5c538d6b8bc42f483eefbddd000af7d
   snapshot_location: 29356/44446/_ss/c5c538d6b8bc42f483eefbddd000af7d_v2.json
   format_version: 2
   previous_snapshot_id: NULL
   [... ...]
   timestamp: 2023-04-19 04:20:25.062854
   

2. Restore the table to the previous state:

   -- Using snapshot ID
   ALTER TABLE sales_data.orders FLASHBACK TO (SNAPSHOT => 'c5c538d6b8bc42f483eefbddd000af7d');
   
   -- Or using timestamp
   ALTER TABLE sales_data.orders FLASHBACK TO (TIMESTAMP => '2023-04-19 04:20:25.062854'::TIMESTAMP);
   

3. Verify the data has been restored:

   SELECT * FROM sales_data.orders LIMIT 3;
   

Important: Flashback operations are only possible for existing tables and within the retention period.

For more details, see FLASHBACK TABLE.

Scenario: Schema Evolution Rollbacks

If you've made unwanted changes to a table's structure, you can revert to the previous schema:

1. Create a table and add some data:

   CREATE OR REPLACE TABLE customers (id INT, name VARCHAR, email VARCHAR);
   INSERT INTO customers VALUES (1, 'John', 'john@example.com');
   

2. Make schema changes:

   ALTER TABLE customers ADD COLUMN phone VARCHAR;
   DESC customers;
   

   Output:

   ┌─────────┬─────────┬──────┬─────────┬─────────┐
   │ Field   │ Type    │ Null │ Default │ Extra   │
   ├─────────┼─────────┼──────┼─────────┼─────────┤
   │ id      │ INT     │ YES  │ NULL    │         │
   │ name    │ VARCHAR │ YES  │ NULL    │         │
   │ email   │ VARCHAR │ YES  │ NULL    │         │
   │ phone   │ VARCHAR │ YES  │ NULL    │         │
   └─────────┴─────────┴──────┴─────────┴─────────┘
   

3. Find the snapshot ID from before the schema change:

   SELECT * FROM fuse_snapshot('default', 'customers');
   

   Output:

   snapshot_id: 01963cefafbb785ea393501d2e84a425  timestamp: 2025-04-16 04:51:03.227000  previous_snapshot_id: 01963ce9cc29735b87886a08d3ca7e2f
   snapshot_id: 01963ce9cc29735b87886a08d3ca7e2f  timestamp: 2025-04-16 04:44:37.289000  previous_snapshot_id: NULL
   

4. Revert to the previous schema (using the earlier snapshot):

   ALTER TABLE customers FLASHBACK TO (SNAPSHOT => '01963ce9cc29735b87886a08d3ca7e2f');
   

5. Verify the schema has been restored:

   DESC customers;
   

   Output:

   ┌─────────┬─────────┬──────┬─────────┬─────────┐
   │ Field   │ Type    │ Null │ Default │ Extra   │
   ├─────────┼─────────┼──────┼─────────┼─────────┤
   │ id      │ INT     │ YES  │ NULL    │         │
   │ name    │ VARCHAR │ YES  │ NULL    │         │
   │ email   │ VARCHAR │ YES  │ NULL    │         │
   └─────────┴─────────┴──────┴─────────┴─────────┘
   

Important Considerations and Limitations

• Time Constraints: Recovery only works within the retention period (default: 24 hours).
• Name Conflicts: Cannot undrop if an object with the same name exists — rename database or rename table first.
• Ownership: Ownership isn't automatically restored—manually grant it after recovery.
• Transient Tables: Flashback doesn't work for transient tables (no snapshots stored).

For Emergency Situations: Facing critical data loss? Contact TiDB Cloud Lake Support immediately for help.