PolarDB:Preemptive DDL

Supported versions

Your PolarDB for MySQL cluster must run one of the following versions:

• PolarDB for MySQL 5.6, revision version 5.6.1.0.43 or later

• PolarDB for MySQL 5.7, revision version 5.7.1.0.34 or later

• PolarDB for MySQL 8.0.1, revision version 8.0.1.1.39 or later

• PolarDB for MySQL 8.0.2, revision version 8.0.2.2.14 or later

To check your cluster's current version, see Query the engine version.

Limitation

Preemptive DDL applies to read-only nodes only.

How it works

PolarDB for MySQL uses a shared storage architecture. When a DDL operation runs, the system acquires an MDL-X lock on the primary node, then signals all read-only nodes to acquire the same lock.

If a read-only node is busy, the MDL-X lock synchronization thread is blocked. When the wait period expires without a successful lock acquisition, the client receives an error:

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

Common causes of MDL synchronization failures:

• Long-running queries — A query on a read-only node is scanning or processing a large amount of data and holds a lock for an extended period.

• Long-running transactions — An open transaction on a read-only node has not been committed or rolled back.

With preemptive DDL enabled, after the configured wait period expires, PolarDB launches a preemption thread that interrupts the blocking session and allows MDL synchronization to complete.

Usage notes

• Enabling preemptive DDL may interrupt active connections to the affected table on read-only nodes, or roll back SQL statements that have not yet executed on that table. Proceed with caution.

• For table renaming on PolarDB for MySQL 8.0.1 or 8.0.2, use the ALTER TABLE RENAME statement instead of the RENAME statement to ensure preemptive DDL takes effect.

• If a read-only node cannot acquire the MDL-X lock and returns ERROR HY000: Fail to get table lock on replica; you can 'set polar_support_mdl_sync_preemption = ON' and try restarting transaction, enable the preemptive DDL feature and retry the transaction.

Enable preemptive DDL

Set loose_polar_support_mdl_sync_preemption to ON and configure loose_polar_mdl_sync_preempt_after_wait_second to control how long the system waits before launching the preemption thread. For instructions, see Configure cluster and node parameters.

Examples

The following examples use the test.t1 table to show how preemptive DDL changes DDL behavior.

Preemptive DDL disabled

1. On a read-only node, run a long query against test.t1:

   mysql> use test;
   Database changed
   # Execute a large query for 100 seconds.
   mysql> select sleep(100) from t1;

2. On the primary node, add a column:

   mysql > alter table t1 add column c int;
   ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize

The long-running query on the read-only node holds the lock, blocking MDL synchronization. The DDL operation fails.

Preemptive DDL enabled

1. On a read-only node, run the same long query:

   mysql> use test;
   Database changed
   # Execute a large query for 100 seconds.
   mysql> select sleep(100) from t1;

2. On the primary node, add a column:

   mysql> alter table t1 add column c int;
   Query OK, 0 rows affected (11.13 sec)
   Records: 0  Duplicates: 0  Warnings: 0

After the configured wait period, the preemption thread interrupts the blocking session and the DDL operation completes successfully.