Use the Preemptive DDL feature and view its limits - PolarDB

Symptoms

When you run a DDL operation in a PolarDB for MySQL database, the system cannot obtain a metadata lock (MDL) and returns the following error:

ERROR HY000: Fail to get MDL on replica during DDL synchronize

ERROR HY000: Fail to get table lock on replica; you can 'set polar_support_mdl_sync_preemption = ON' and try restarting transaction

Prerequisites

PolarDB for MySQL clusters must run one of the following versions:

5.6 with revision 5.6.1.0.43 or later.
5.7 with revision 5.7.1.0.34 or later.
8.0.1 with revision 8.0.1.1.39 or later.
8.0.2 with revision 8.0.2.2.14 or later.

To view the cluster version, see Checking the engine version.

Limits

Only read-only nodes support the Preemptive DDL feature. The primary node does not support this feature.

Usage notes

Enabling the Preemptive DDL feature may interrupt connections that access the current table on a read-only node or roll back unfinished SQL statements on that table. Proceed with caution.
Only when the value of the loose_replica_lock_wait_timeout parameter is greater than the value of the loose_polar_mdl_sync_preempt_after_wait_second parameter plus 5 does the Preemptive DDL feature take effect.
MySQL 8.0.1 and MySQL 8.0.2 do not support Preemptive DDL on the rename path. Use the alter table rename statement instead.

Background information

PolarDB for MySQL uses a shared storage architecture. When you run a DDL operation, the primary node first acquires an MDL-X lock and then notifies the read-only nodes to acquire an MDL-X lock. If a transaction is accessing the table on a read-only node, the MDL lock synchronization thread is blocked. If the read-only node cannot acquire the MDL-X lock within the timeout period, the client returns the ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize error. In PolarDB for MySQL clusters with multiple read-only nodes, MDL lock synchronization often fails and causes DDL operations to fail. Preemptive DDL was added to address this issue.

Procedure

Enable the Preemptive DDL feature by using the loose_polar_support_mdl_sync_preemption parameter, and set the wait timeout used during preemption by using the loose_polar_mdl_sync_preempt_after_wait_second parameter. For instructions, see Set cluster parameters and node parameters. The following table describes the parameters.

Parameter

Level

Description

loose_polar_support_mdl_sync_preemption

Session

Controls the Preemptive DDL feature. Valid values:

ON: Enables the Preemptive DDL feature.
OFF (default): Disables the Preemptive DDL feature.

loose_polar_mdl_sync_preempt_after_wait_second

Global

Specifies the wait timeout when MDL lock synchronization is blocked. If the MDL lock is still not synchronized after this period, the system starts preempting threads.

Valid values: 1 to 31536000. Unit: seconds. Default value: 10.

Examples

Preemptive DDL disabled

First, run a query on the test.t1 table on a read-only node.

mysql> use test;
Database changed
# A large query that runs for 100s
mysql> select sleep(100) from t1;

Then, add a column to the table on the primary node.
```
mysql > alter table t1 add column c int;
ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize
```
This example shows that when the Preemptive DDL feature is disabled, the long-running transaction on the read-only node causes MDL lock synchronization to fail. The DDL operation on the primary node is blocked and then fails.
```
mysql> select sleep(100) from t1;
+------------+
| sleep(100) |
+------------+
|          0 |
+------------+
1 row in set (1 min 40.00 sec)
```

Preemptive DDL enabled

First, run a query on the test.t1 table on a read-only node.

mysql> use test;
Database changed
# A large query that runs for 100s
mysql> select sleep(100) from t1;

Then, add a column to the table on the primary node.
```
mysql> alter table t1 add column c int;
Query OK, 0 rows affected (11.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
```
This example shows that when the Preemptive DDL feature is enabled, MDL lock synchronization is blocked by the long-running transaction on the read-only node. After a wait period, the system starts preempting threads, and the DDL operation succeeds.
```
mysql> select sleep(100) from t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
```

Contact us

If you have questions about DDL operations, Contact us.