RDS MySQL — Can’t drop schema due to Hangfire connections (DDL stuck on metadata locks)

This is a common issue with metadata locks in RDS MySQL when applications maintain persistent connections. Here's how to address your situation:

1. RDS-specific approaches to force schema/table drop:

• Use CALL mysql.rds_kill_query() to terminate specific blocking queries rather than entire sessions
• For persistent issues, consider a brief maintenance window where you:
• Modify security groups to block all application traffic temporarily
• Kill all existing connections with mysql.rds_kill()
• Execute your DDL operations
• Restore security group settings
• Note that dropping databases created via the RDS CreateDBInstance API is not allowed; only databases created using rdsadmin.create_database can be dropped

2. Recommended timeout values:

• Set lock_wait_timeout (not metadata_lock_timeout) to a reasonable value like 60-300 seconds
• Configure wait_timeout and interactive_timeout to shorter values (e.g., 300-600 seconds) during maintenance to prevent long-lived idle connections

3. Preventing reconnects during maintenance:

• Temporarily modify security groups to block application traffic completely (not just restricting to 0.0.0.0/32 which is invalid)
• Use Network ACLs as a secondary defense if security groups aren't sufficient
• The Event Scheduler can be used to call mysql.rds_kill on RDS, but ensure it doesn't conflict with other maintenance operations

4. Identifying and killing MDL holders:

• In Aurora MySQL version 2, you can identify blocked sessions by querying performance_schema tables or sys schema views
• Example query to find blocking sessions:

SELECT blocking_session_id FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads t ON ml.owner_thread_id = t.thread_id
WHERE ml.object_schema = 'your_schema_name'

• To enable MDL instrumentation, ensure the Performance Schema is enabled and the metadata_locks consumer is turned on
• Use CALL mysql.rds_kill() with the identified session IDs

For your Hangfire issue specifically, I recommend temporarily blocking all application traffic at the security group level, killing all existing connections, performing your DDL operations, and then restoring access. This approach ensures the applications can't reconnect during the critical maintenance window.
Sources
Troubleshooting for Amazon RDS for Db2 - Amazon Relational Database Service
MySQL RDS instance connection error | AWS re:Post
synch/cond/sql/MDL_context::COND_wait_status - Amazon Aurora