DDL operations in PolarDB for MySQL can fail when a read-only node holds an active metadata lock (MDL). This topic explains why this happens and how to resolve it.
Error messages
ERROR HY000: Fail to get MDL on replica during DDL synchronizeERROR HY000: Fail to get table lock on replica; you can 'set polar_support_mdl_sync_preemption = ON' and try restarting transactionRoot cause
When the primary node runs a DDL operation, it first acquires an MDL on the affected table, then writes a redo log entry. Each read-only node parses that redo log entry and attempts to acquire an MDL on the same table. If a read-only node has ongoing queries or uncommitted transactions that already hold the MDL, it cannot acquire the lock and sends an error back to the primary node. The primary node then rolls back the DDL and returns one of the errors above.
Solutions
Choose the approach that best fits your situation:
| Situation | Recommended approach |
|---|---|
| DDL operations frequently fail due to long-running reads on read-only nodes | Enable preemptive DDL |
| Uncommitted transactions are blocking DDL and you need a persistent fix | Enable the polar_slave_work_on_nonblock_mdl_mode parameter |
| You need to unblock DDL immediately | Identify and kill the blocking session (see Diagnose and kill blocking sessions) |
| A specific transaction is blocking DDL and can safely be committed or rolled back | Commit or roll back the uncommitted transaction on the read-only node |
Enable preemptive DDL
The preemptive DDL feature lets DDL operations preempt ongoing reads on read-only nodes, preventing them from blocking DDL synchronization. For setup instructions, see Preemptive DDL.
Prevent long-running transactions from blocking DDL
Enable the polar_slave_work_on_nonblock_mdl_mode parameter on the read-only node. This prevents long-running uncommitted transactions from blocking DDL operations. For details, see Prevent long-running transactions on read-only nodes from blocking DDL operations.
Use Session Manager to find and end abnormal sessions
Log on to the PolarDB console. In the left navigation pane, choose Diagnostics and Optimization > Quick Diagnostics.
On the Session Manager tab, check for abnormal sessions.
Click View Details in the anomaly section to review the session details.
End sessions with long-running uncommitted transactions. For other abnormal sessions, optimize or end them as needed.
For more information, see Session Manager.
Contact us
If you have any questions about DDL operations, please contact technical support.
Diagnose and kill blocking sessions
Use Polar Performance Schema to query the MDL status on the target table, identify the blocking thread, and kill it.
Step 1: Check whether Polar Performance Schema is enabled
Check in one of two ways:
Option A — PolarDB console:
Log on to the PolarDB console. In the left navigation pane, choose Settings and Management > Parameter Settings. Find the loose_polar_performance_schema parameter and check its value.
All cluster parameters in the console are prefixed withloose_for MySQL configuration file compatibility. Theloose_polar_performance_schemaparameter in the console corresponds to thepolar_performance_schemavariable.
Option B — SQL:
SHOW VARIABLES LIKE 'polar_performance_schema';Expected output:
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| polar_performance_schema | ON |
+--------------------------+-------+Step 2: Query MDL status and kill the blocking thread
Follow the steps for your configuration:
Polar Performance Schema is ON
Query performance_schema.metadata_locks to identify which thread holds or is waiting for the MDL on your target table.
Use a node hint to target the read-only node and run the query. Replace
pi-bp10k7631d6k3****with your actual read-only node ID.The
test01/t1table has aSHARED_READlock that isGRANTED— held by an active query or uncommitted transaction.The
test/t1table has anEXCLUSIVElock that isPENDING— this is the DDL waiting to acquire the lock.
/*force_node='pi-bp10k7631d6k3****'*/ SELECT t.PROCESSLIST_ID, m.OBJECT_TYPE, m.OBJECT_SCHEMA, m.OBJECT_NAME, m.LOCK_TYPE, m.LOCK_DURATION, m.LOCK_STATUS FROM performance_schema.metadata_locks m LEFT JOIN performance_schema.threads t ON m.owner_thread_id=t.thread_id;Sample result: In this example: The
PROCESSLIST_IDcolumn gives you the connection ID to kill.
Kill the blocking thread using the same node hint:
/*force_node='pi-bp10k7631d6k3****'*/ kill 536976473;
If the uncommitted transaction on the read-only node is critical, do not kill it. Wait for the transaction to complete before retrying the DDL operation.
If thekillcommand fails withERROR 1094 (HY000): Unknown thread id: xxx, the thread no longer exists. If you continue to experience issues, contact technical support.
Polar Performance Schema is OFF
Query information_schema.innodb_trx to identify uncommitted transactions on the read-only node.
Use a node hint to target the read-only node and run the query. Replace
pi-bp10k7631d6k3****with your actual read-only node ID./*force_node='pi-bp10k7631d6k3****'*/ SELECT * FROM information_schema.innodb_trx\GInterpret the result based on which scenario applies: DDL blocked by a large query Sample result: A row appears for the
t1table, indicating the current connection holds the MDL. Usetrx_mysql_thread_idas the thread ID to kill. DDL blocked by a large transaction Sample result: The transaction (for example, ID 537247177) is uncommitted, buttrx_queryis empty — you cannot tell from the query alone whether it holds the MDL. Checktrx_started: if the timestamp is much earlier than the current time, this transaction is likely holding the MDL.
Kill the blocking thread:
/*force_node='pi-bp10k7631d6k3****'*/ kill 537247177