Table Recycle Bin Overview for Dropped Table Recovery - PolarDB

DDL statements such as DROP TABLE and DROP DATABASE cannot be rolled back, which means accidentally dropped tables can result in permanent data loss. The table recycle bin intercepts these operations and moves the deleted tables to a safe holding area, giving you time to recover them before they are permanently purged.

Important

The table recycle bin uses cluster storage capacity, which incurs storage fees. Set the retention period based on your recovery needs to avoid unnecessary costs.

Prerequisites

Before you begin, ensure that your cluster runs one of the following engine versions. To check the version, see Engine versions.

PolarDB for MySQL 8.0.1 with revision version 8.0.1.1.2 or later
PolarDB for MySQL 8.0.2 with revision version 8.0.2.1.0 or later

How it works

When you run DROP TABLE or DROP DATABASE, PolarDB intercepts the operation and moves the table objects to the __recycle_bin__ system database instead of permanently deleting them. The following behavior applies:

Triggers and foreign keys are deleted and not recycled.
Column statistics are moved to the recycle bin together with the table.
Objects unrelated to the dropped table are not recycled. Whether they are retained depends on the specific statement executed.

To avoid name collisions when tables from different databases are moved to the same __recycle_bin__ database, PolarDB renames each recycled table using the format:

"__" + <storage engine name> + <SE private ID>

For InnoDB, the SE private ID is the table_id. For example, a recycled InnoDB table might be named __innodb_1063.

Purging happens automatically in the background. A background thread asynchronously purges tables that have been in the recycle bin longer than the period set by loose_recycle_bin_retention. For large tables, a separate background thread handles the purge to avoid blocking other operations.

Independent recycling — Primary nodes and read-only nodes maintain separate recycle bins with independent retention periods. For example, you can set a 7-day retention for the primary node and a 14-day retention for read-only nodes.

Considerations

When a PolarDB cluster starts, a database named __recycle_bin__ is initialized as the recycle bin database. The __recycle_bin__ database is a system database. You cannot modify or delete it.
You cannot run DROP TABLE on tables in the recycle bin directly. Use call dbms_recycle.purge_table('table_name') to purge a specific table manually.
To purge tables, your account must have the DROP permission on both the original tables and the tables in the recycle bin.
If the __recycle_bin__ database and the tables you want to recycle are in different file systems, DROP TABLE moves files across tablespaces, which can be time-consuming.
If the tables you want to recycle are in a general tablespace that stores multiple tables, only the individual table's data is recycled — the tablespace files are not moved.

Billing

The table recycle bin uses the cluster's storage capacity, which incurs storage fees. For pricing details, see Billing rules for storage.

Enable the table recycle bin

Configure the following parameters to enable and tune the table recycle bin.

Parameter	Scope	Default	Valid values	Description
`loose_recycle_bin`	Global and session	OFF	ON, OFF	Enables or disables the table recycle bin.
`loose_recycle_scheduler`	Global	OFF	ON, OFF	Enables or disables the background thread that automatically purges tables past their retention period. When set to OFF, `loose_recycle_bin_retention` is ignored and data is not automatically cleaned up.
`loose_recycle_bin_retention`	Global	604800 (7 days)	86400–1209600 (seconds)	Maximum retention period for data in the recycle bin. 604800 = 7 days; 1209600 = 14 days. Takes effect only when `loose_recycle_scheduler` is ON.

Enable both loose_recycle_bin and loose_recycle_scheduler to ensure tables are automatically purged after the retention period. Without auto-purge enabled, recycled data accumulates and incurs ongoing storage fees.

Manage tables in the recycle bin

PolarDB provides three stored procedures under dbms_recycle to manage recycled tables.

List tables in the recycle bin

Run the following statement to see all tables currently in the recycle bin:

call dbms_recycle.show_tables();

Example output:

+-----------------+---------------+---------------+--------------+---------------------+---------------------+
| SCHEMA          | TABLE         | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME       | PURGE_TIME          |
+-----------------+---------------+---------------+--------------+---------------------+---------------------+
| __recycle_bin__ | __innodb_1063 | product_db    | t1           | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
| __recycle_bin__ | __innodb_1064 | product_db    | t2           | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
| __recycle_bin__ | __innodb_1065 | product_db    | parent       | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
| __recycle_bin__ | __innodb_1066 | product_db    | child        | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
+-----------------+---------------+---------------+--------------+---------------------+---------------------+
4 rows in set (0.00 sec)

The output columns are:

Column	Description
`SCHEMA`	The schema of the recycle bin (`__recycle_bin__`).
`TABLE`	The renamed table as it appears in the recycle bin.
`ORIGIN_SCHEMA`	The original schema the table belonged to before it was dropped.
`ORIGIN_TABLE`	The original table name.
`RECYCLED_TIME`	The time the table was moved to the recycle bin.
`PURGE_TIME`	The estimated time when the table will be automatically purged.

Restore a table from the recycle bin

Run the following statement to restore a recycled table to a specified database and table name:

call dbms_recycle.restore_table('RECYCLE_TABLE', 'DEST_DB', 'DEST_TABLE');

Parameter	Description
`RECYCLE_TABLE`	The name of the table in the recycle bin (for example, `__innodb_1063`). If you specify only this parameter, the data of the original table is restored.
`DEST_DB`	The destination database.
`DEST_TABLE`	The new name for the restored table.

Example — restore __innodb_1063 to the testDB database as testTable:

call dbms_recycle.restore_table('__innodb_1063', 'testDB', 'testTable');

restore_table is supported only on PolarDB for MySQL 8.0 Cluster Edition with revision version 8.0.1.1.12 or later. To check your version, see Query the engine version. Your account must have ALTER_ACL and DROP_ACL permissions on the __recycle_bin__ database, and CREATE_ACL and INSERT_ACL permissions on the destination table.

Purge a table from the recycle bin

Run the following statement to permanently delete a specific table from the recycle bin before its retention period expires:

call dbms_recycle.purge_table('TABLE_NAME');

TABLE_NAME is the name of the table as it appears in the recycle bin (not the original table name).

Example — permanently delete __innodb_1063:

call dbms_recycle.purge_table('__innodb_1063');

Your account must have the DROP permission on both the original table and the table in the recycle bin.

What's next

If you have questions about DDL operations, contact us.