PolarDB provides the polar_replica_work_on_nonblock_mdl_mode parameter. You can set this parameter to ON to prevent long-running transactions on PolarDB read-only nodes from blocking DDL operations on the primary node. This topic describes how to configure this parameter for PolarDB.

Limits

Your PolarDB cluster must meet one of the following requirements:
  • A cluster of PolarDB for MySQL 8.0 whose revision version is 8.0.1.1.17 or later.
  • A cluster of PolarDB for MySQL 5.7 whose revision version is 5.7.1.0.11 or later.
  • A cluster of PolarDB for MySQL 5.6 whose revision version is 5.6.1.0.28 or later.
Note For information about how to view the kernel version of your cluster, see Query the kernel version number.

The polar_replica_work_on_nonblock_mdl_mode parameter is effective only if the transaction isolation level on PolarDB read-only nodes is Read Committed or Read Uncommitted.

The polar_replica_work_on_nonblock_mdl_mode parameter applies only to PolarDB read-only nodes.

Background information

Long-running transactions on PolarDB read-only nodes hold the metadata locks (MDLs) on the accessed data tables. In this case, DDL operations on the PolarDB primary node cannot synchronize the MDLs. As a result, timeout errors occur and the ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize error message is returned.
Note The replica_lock_wait_timeout parameter specifies the timeout period for MDL synchronization. The default timeout period is 50 seconds.
If MDL synchronization times out, execute the show processlist statement on the PolarDB primary node. If the value returned for the State parameter is Wait for syncing with replicas, you can execute the select * from information_schema.innodb_log_mdl_slot where slot_state = "SLOT_ACQUIRING" statement on PolarDB read-only nodes to query MDLs that are in the waiting state. For example, the following returned information indicates that long-running transactions on PolarDB read-only nodes hold MDLs.
+---------+----------------+-----------+----------+-----------+
| slot_id | slot_state     | slot_name | slot_lsn | thread_id |
+---------+----------------+-----------+----------+-----------+
|       0 | SLOT_ACQUIRING | test/t    | 35025648 | thread-0  |
+---------+----------------+-----------+----------+-----------+

Usage notes

After you set the polar_replica_work_on_nonblock_mdl_mode parameter to ON, each transaction whose isolation level is Read Committed or Read Uncommitted on a PolarDB read-only node is automatically committed after a single SQL statement is executed.

The polar_replica_work_on_nonblock_mdl_mode parameter can be used to solve only the DDL blocking issues that are caused by long-running transactions. After you set the polar_replica_work_on_nonblock_mdl_mode parameter to ON, MDL synchronization failures caused by SQL queries that consume more time than the timeout period of MDL synchronization can still occur.

The MDLs used to lock or flush tables must be obtained by explicitly unlocking tables. Therefore, the MDL blocking issues caused by locking or flushing tables cannot be solved by setting the polar_replica_work_on_nonblock_mdl_mode parameter to ON.

Procedure

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region where the cluster that you want to manage is deployed.
  3. Find the cluster that you want to manage and click the cluster ID.
  4. In the left-side navigation pane, choose Settings and Management > Parameters.
  5. Find the loose_polar_replica_work_on_nonblock_mdl_mode parameter and click the Icon icon in the Current Value column. In the dialog box that appears, enter ON and click OK.
    Configure parameters
  6. In the upper-left corner of the page, click Apply Changes. In the Save Changes panel, click OK.
    Save changes

Examples

After you set the polar_replica_work_on_nonblock_mdl_mode parameter to ON, a transaction on a read-only node may return different table structures.
  1. Query the test.t table on a read-only node.
    mysql> begin;
    mysql> select * from test.t;
    +------+
    | a    |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
  2. Perform DDL operations on test.t on the primary node.
    mysql> alter table test.t add column b int;
    Query OK, 0 rows affected (0.32 sec)
    Records: 0  Duplicates: 0  Warnings: 0
  3. Query the test.t table again on the read-only node.
    mysql> select * from test.t;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 | NULL |
    +------+------+
    1 row in set (0.00 sec)

The preceding example indicates that after the polar_replica_work_on_nonblock_mdl_mode parameter is set to ON, a transaction on a read-only node returns different number of columns when the transaction is committed before and after DDL operations are performed on the primary node. After you set the polar_replica_work_on_nonblock_mdl_mode parameter to OFF, the DDL operations on the primary node are not allowed until the transactions on read-only nodes are committed or timeout occurs. In this case, the ERROR 8007 (HY000): Fail to get MDL on replica during DDL synchronize error message is returned.