Use the Preemptive DDL feature and view its limitations - PolarDB

Symptoms

When you run a DDL operation in a PolarDB for MySQL database, an error occurs indicating that a metadata lock (MDL) cannot be obtained. The following error messages are displayed:

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

Your PolarDB for MySQL cluster must use one of the following revisions:

PolarDB for MySQL 5.6: revision 5.6.1.0.43 or later.
PolarDB for MySQL 5.7: revision 5.7.1.0.34 or later.
PolarDB for MySQL 8.0.1: revision 8.0.1.1.39 or later.
PolarDB for MySQL 8.0.2: revision 8.0.2.2.14 or later.

To check the revision of your cluster, see View the version of a cluster.

Limitations

The Preemptive DDL feature is supported only on read-only nodes, not on the primary node.

Usage notes

Enabling Preemptive DDL can terminate connections that are accessing the target table on a read-only node or roll back in-progress SQL queries on that table. Use this feature with caution.
The Preemptive DDL feature is effective only if 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.
For PolarDB for MySQL 8.0.1 and PolarDB for MySQL 8.0.2, this feature does not support rename operations. We recommend that you 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 exclusive metadata (MDL-X) lock and then directs all read-only nodes to acquire the same lock. If a transaction is accessing the target table on a read-only node, the MDL lock synchronization process is blocked. If a read-only node fails to acquire the MDL-X lock within the specified timeout period, the client returns the ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize error. This issue is common in PolarDB for MySQL clusters with multiple read-only nodes. The Preemptive DDL feature is designed to address this problem.

Procedure

You can enable the Preemptive DDL feature with the loose_polar_support_mdl_sync_preemption parameter and set the preemption wait timeout with the loose_polar_mdl_sync_preempt_after_wait_second parameter. For more information, see Specify cluster 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 in seconds when an MDL lock synchronization is blocked. If the lock is not acquired after this period, the system initiates the preemption of blocking threads.

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

Examples

Preemptive DDL disabled

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

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

On the primary node, add a column to the table.
```
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 DDL operation is blocked and eventually fails. This is because the long-running transaction on the read-only node prevents MDL lock synchronization.
```
mysql> select sleep(100) from t1;
+------------+
| sleep(100) |
+------------+
|          0 |
+------------+
1 row in set (1 min 40.00 sec)
```

Preemptive DDL enabled

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

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

On the primary node, add a column to the table.
```
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, the MDL lock synchronization is initially blocked by the long-running transaction. After a wait period, the feature preempts the blocking thread, 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 any questions about DDL operations, please contact technical support.