MySQL ERROR 5270: Recycle Bin Object Recovery Challenges

Symptoms​
When attempting to flashback a table using SHOW RECYCLEBIN or FLASHBACK TABLE, users encounter the error:
ERROR 5270 (HY000): object not in RECYCLE BIN.

​Root Cause Analysis​
The error occurs because the FLASHBACK TABLE command defaults to the current database context. If the target table resides in a different database, it must be explicitly referenced using database.table.

​Key Findings from Testing​

1. ​Retrieving Database Names from Recycle Bin​
Use this query to identify the source database of recycled tables:

SELECT 
  rb.tenant_id, 
  rb.database_id, 
  db.database_name, 
  rb.table_id, 
  rb.original_name 
FROM 
  __all_recyclebin rb 
INNER JOIN 
  __all_virtual_database db ON rb.database_id = db.database_id;

‍

2. Handling Duplicate Table Names
‍​The recycle bin retains multiple versions of the same table name. Flashback operations prioritize the most recently deleted instance. Example:

-- Newest deletion takes precedence
FLASHBACK TABLE test.a TO BEFORE DROP;

‍

3. Recycle Bin Visibility After Disable​
‍

Even with SET SESSION RECYCLEBIN=0, recycled objects remain visible via SHOW RECYCLEBIN.

4. ​Tenant-Specific Recovery​
Recycle bin contents are tenant-isolated. Users can only access objects belonging to their own tenant.

5. ​Post-Disable Operations​
Tables and tenants can still be recovered after disabling the recycle bin using direct FLASHBACK commands.

​Conclusion​

• Enable the recycle bin during deletions to ensure recoverability.
• Use ORIGINAL_NAME for version-specific recovery and DATABASE.TABLE syntax for cross-database operations.
• Recycle bin entries persist even when disabled, but cleanup requires explicit purging.

​Optimized Workflow Tips​

• Regularly audit __all_recyclebin metadata for orphaned objects.
• Implement naming conventions to avoid collisions in multi-tenant environments.
• Test recovery procedures in non-production environments first.

‍