Use the table recycle bin to restore accidentally deleted tables - AnalyticDB

DDL statements cannot be rolled back. If you accidentally delete tables when you use DDL statements, data is lost and cannot be restored. To resolve this issue, AnalyticDB for MySQL provides the table recycle bin feature that allows you to temporarily store the deleted tables in the table recycle bin. You can configure a retention period for the table recycle bin to help you restore accidentally deleted data.

Prerequisites

An AnalyticDB for MySQL cluster of V3.2.3.0 or later is created.

Note

To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.

For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

Usage notes

The table recycle bin feature supports only AnalyticDB for MySQL internal tables whose table engine is XUANWU or XUANWU_V2. The table recycle bin feature does not support external tables.
If you execute the DROP TABLE statement when the INSERT OVERWRITE SELECT statement is being executed, the deleted table is moved to the table recycle bin, and the INSERT OVERWRITE SELECT statement fails to be executed.
If you execute the DROP TABLE statement when the BUILD statement is being executed, the deleted table is moved to the table recycle bin, and the BUILD statement is executed as expected.
Tables that are moved to the table recycle bin still use disk storage.

Introduction to the table recycle bin

The table recycle bin is used to temporarily store deleted tables. The first time you execute the DROP statement, AnalyticDB for MySQL creates a hidden database named ADB_RECYCLE_BIN and moves deleted tables to the ADB_RECYCLE_BIN database.

Recycling mechanism

Tables that are deleted by using the DROP TABLE statement are automatically moved to the table recycle bin.

Important

Tables that are forcibly deleted by using the DROP TABLE ... FORCE statement are directly deleted.

Retention period and cleanup mechanism

By default, deleted tables are retained for three days after they are moved to the table recycle bin. After three days, the retained tables are automatically deleted. You can configure the retention period and the cleanup mechanism. For more information, see the "Table recycle bin settings" section of this topic. You can also execute the PURGE statement to clean up the table recycle bin. For more information, see the "Delete tables from the table recycle bin" section of this topic.

SQL statements supported by the table recycle bin

To execute statements on tables in the table recycle bin, you must have the DROP permission on the source tables.
You can execute only the PURGE, RESTORE, and SHOW statements on tables in the table recycle bin.

Naming conventions for tables in the table recycle bin

Tables that are moved to the ADB_RECYCLE_BIN database are collected from different databases. To ensure unique table names, tables in the table recycle bin are named in the following format: SourceDatabaseName_SourceTableName_Timestamp.

For example, a user deleted a table named customer from the adb_demo database at 00:00:00 on January 1, 2024. The customer table is renamed to adb_demo_customer_1704038400000 in the table recycle bin.

Note

If the length of the new table name exceeds the limit, the names of the source table and the source database are automatically truncated in sequence. In this case, the source database name and the source table name in the new table name may be incomplete.
If a table is already moved from a database to the table recycle bin and another table that has the same name is deleted from the same database, the recently deleted table is moved to the table recycle bin and renamed by using a different timestamp.

Table recycle bin settings

You can execute the SET ADB_CONFIG statement to modify the settings of the table recycle bin. The following table describes the parameters in the statement.

Parameter	Description
DROP_FORCE	Specifies whether to permanently delete tables when you execute the `DROP` statement. If you set this parameter to FALSE, tables are moved to the table recycle bin. If you set this parameter to TRUE, tables are permanently deleted. Default value: FALSE.
RECYCLE_BIN_EXPIRED_TIME	The retention period of tables after they are moved to the table recycle bin. This parameter does not take effect on tables that already exist in the table recycle bin. Unit: milliseconds. Default value: 259200000 (3 days). Important To prevent exhausting disk storage, we recommend that you specify an appropriate retention period.
ENABLE_RECYCLE_BIN_CLEAN_EXPIRED_TABLE	Specifies whether to enable the asynchronous cleanup thread feature for the table recycle bin. Default value: TRUE. Important We recommend that you do not disable the asynchronous cleanup thread feature.
RECYCLE_BIN_CLEAN_EXPIRED_TABLE_INTERVAL	The interval at which the table recycle bin executes an asynchronous cleanup thread. Unit: milliseconds. Default value: 60000.

Manage the table recycle bin

Query tables in the table recycle bin

Syntax

Query all tables in the table recycle bin.
```
SHOW RECYCLE_BIN ALL;
```
Query information about a specific table in the table recycle bin. Multiple tables that have the same database name and table name may be returned.
```
SHOW RECYCLE_BIN TABLE <SourceDatabaseName>.<SourceTableName>;
```
Query information about a specific table in the table recycle bin. Multiple tables that have the same table name but different database names may be returned.
```
SHOW RECYCLE_BIN TABLE <SourceTableName>;
```
Query all tables that belong to a specific database in the table recycle bin.
```
SHOW RECYCLE_BIN DATABASE <SourceDatabaseName>;
```

Examples

Query all tables in the table recycle bin.

SHOW RECYCLE_BIN ALL;

Sample result:

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | adb_demo_orders_1720086094102   | adb_demo         | orders          | 2024-07-04 17:41:37 | 2024-07-07 17:41:37 |
| testUser | ADB_RECYCLE_BIN | testdb_customer_1720085752664   | testdb           | customer        | 2024-07-04 17:35:56 | 2024-07-07 17:35:56 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

Query information about the adb_demo.customer table in the table recycle bin.

SHOW RECYCLE_BIN TABLE adb_demo.customer;

Sample result:

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

Query all tables whose source table name is customer in the table recycle bin.

SHOW RECYCLE_BIN TABLE customer;

Sample result:

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | testdb_customer_1720085752664   | testdb           | customer        | 2024-07-04 17:35:56 | 2024-07-07 17:35:56 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

Query all tables that belong to the adb_demo database in the table recycle bin.

SHOW RECYCLE_BIN DATABASE adb_demo;

Sample result:

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | adb_demo_orders_1720086094102   | adb_demo         | orders          | 2024-07-04 17:41:37 | 2024-07-07 17:41:37 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

Restore tables from the table recycle bin

Rules

Restored tables use the names before they were moved to the table recycle bin.
If you want to restore a table to the source database but the database contains another table that has the same name, you can rename, delete, or move the existing table in the database to another database.
If you restore multiple tables that have the same database name and table name but different timestamps, AnalyticDB for MySQL restores the table with the latest timestamp and then reports an error for the other tables. In this case, you can rename, delete, or move the restored table to another database.

Syntax

Restore all tables from the table recycle bin to the source databases.
```
RESTORE RECYCLE_BIN ALL;
```

Restore a specific table from the table recycle bin to the source database.

RESTORE RECYCLE_BIN TABLE <Name of the table in the ADB_RECYCLE_BIN database>;

Example

Restore the adb_demo_customer_1720086117277 table from the table recycle bin.

RESTORE RECYCLE_BIN TABLE adb_demo_customer_1720086117277;

Delete tables from the table recycle bin

Syntax

Delete all tables from the table recycle bin.
```
PURGE RECYCLE_BIN ALL;
```

Delete a specific table from the table recycle bin.

PURGE RECYCLE_BIN TABLE <Name of the table in the ADB_RECYCLE_BIN database>;

Example

Delete the adb_demo_customer_1720086117277 table from the table recycle bin.

PURGE RECYCLE_BIN TABLE adb_demo_customer_1720086117277;

Change the retention period of tables in the table recycle bin

Syntax

Change the retention period of tables in the table recycle bin.

SET adb_config RECYCLE_BIN_EXPIRED_TIME=<Retention period of tables>;

Important

After you change the retention period of tables, the new retention period takes effect only on tables that are deleted after the change. The previous retention period takes effect on existing tables in the table recycle bin.

Example

Change the retention period of tables in the table recycle bin to two days.

# The retention period is calculated by using the following formula: 2 × 24 × 3600 × 1000 = 172800000. Unit: milliseconds.
SET adb_config RECYCLE_BIN_EXPIRED_TIME=172800000;