Use the Preemptive DDL feature and view its limitations - PolarDB - Centro de documentación de Alibaba Cloud

PolarDB for MySQL provides the Preemptive DDL feature to prevent Data Definition Language (DDL) operations from failing due to large queries or long-running transactions on tables in read-only nodes.

Síntomas

Cuando se ejecuta una operación DDL en una base de datos de PolarDB for MySQL, se produce un error que indica que no se puede obtener un bloqueo de metadatos (MDL). A continuación se muestran los mensajes de 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

Requisitos previos

El clúster de PolarDB for MySQL debe usar una de las siguientes revisiones:

PolarDB for MySQL 5.6: revisión 5.6.1.0.43 o posterior.
PolarDB for MySQL 5.7: revisión 5.7.1.0.34 o posterior.
PolarDB for MySQL 8.0.1: revisión 8.0.1.1.39 o posterior.
PolarDB for MySQL 8.0.2: revisión 8.0.2.2.14 o posterior.

Para comprobar la revisión del clúster, consulte View the version of a cluster.

Limitations

La función Preemptive DDL feature solo es compatible con nodos de solo lectura, no con el nodo principal.

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.
En PolarDB for MySQL 8.0.1 y PolarDB for MySQL 8.0.2, esta función no admite operaciones de cambio de nombre. Se recomienda usar la instrucción ALTER TABLE ... RENAME en su lugar.

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.

Parámetro

Nivel

Descripción

loose_polar_support_mdl_sync_preemption

Session

Controla la función Preemptive DDL. Valores válidos:

ON: habilita la función Preemptive DDL.
OFF (predeterminado): deshabilita la función Preemptive DDL.

loose_polar_mdl_sync_preempt_after_wait_second

Global

Especifica el tiempo de espera en segundos cuando se bloquea la sincronización de un bloqueo MDL. Si no se adquiere el bloqueo después de este periodo, el sistema inicia la apropiación de los hilos que lo bloquean.

Valores válidos: de 1 a 31536000. Unidad: segundos. Valor predeterminado: 10.

Ejemplos

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
```
Este ejemplo muestra que cuando 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
```
Este ejemplo muestra que cuando 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
```

Contacto

Si tiene dudas sobre las operaciones DDL, Contact us.