PolarDB:DDL FAQ

How do I increase the database limit in PolarDB-X 2.0?

PolarDB-X 2.0 allows a maximum of 32 logical databases by default. To increase this limit, connect using a high-privilege account and run:

SET GLOBAL MAX_LOGICAL_DB_COUNT=64;

How do I identify abnormal DDL operations in PolarDB-X 2.0?

Run the following statements to check for stuck or abnormal DDL activity:

SHOW FULL DDL;
SHOW FULL physical_processlist WHERE info !='';

How many records can I insert in a single batch operation?

The safe batch size depends on your row size and the max_allowed_packet setting.

Estimate your row size

Calculate the maximum bytes per row using these InnoDB storage sizes:

For TEXT, BLOB, LONGTEXT, and other large object types, evaluate each field separately based on actual maximum usage. For example, a Group_ID VARCHAR(1024) that only stores a single digit still counts as 1,024 bytes in your maximum row estimate.

Calculate the batch size

Using an example row of 1,913 bytes (8+8+8+8+8+8+255+255+255+1,024+4+64+8):

• At 1 MB: (1,024 × 1,024) / 1,913 ≈ 548 records. Keep batches under 500 to avoid exceeding the limit based on the PolarDB-X CN computation layer's 16 MB batch insert method.

• At max_allowed_packet=16M: 500 × 16 = 8,000 records is a safe upper bound.

Parameter recommendations

• Set max_allowed_packet to its maximum value.

• Set innodb_log_buffer_size between 32M and 128M. Test different values to find the optimal response time for your workload — this prevents InnoDB from flushing logs to disk too frequently before transaction commits.

Other factors that affect insertion performance

• InnoDB buffer pool size

• Disk IOPS and network bandwidth (in distributed database deployments)

• Redo log buffer flush behavior (innodb_log_buffer_size)

DDL error: GSI table is not public

Cause:

The Global Secondary Index (GSI) is in an invalid state. Run SHOW GLOBAL INDEX FROM <table_name>; to check the index state. You can perform DDL operations on the target table only when the GSI is in the PUBLIC or ABSENT state. Any other state indicates the GSI is still being created, which blocks other DDL operations.

Solution:

1. First, run SHOW DDL; to check for any residual DDL operations that might be blocking the current one.

2. If there are residual DDL operations, wait for them to complete. If the state returned by SHOW GLOBAL INDEX is not PUBLIC or ABSENT, consider canceling the GSI creation by running CANCEL DDL <JOB_ID>;. Then, re-run the DDL operation.

   mysql> show ddl;
   +--------+---------------+-------------+--------+-------------+--------+-------------------------+---------------------------+----------+------------------------+---------------------------+
   | JOB_ID | OBJECT_SCHEMA | OBJECT_NAME | ENGINE | DDL_TYPE    | STATE  | TOTAL_BACKFILL_PROGRESS | CURRENT_PHY_DDL_PROGRESS | PROGRESS | FASTCHECKER_TASK_NUM | FASTCHECKER_TASK_FINISHED |
   | ELAPSED_TIME(MS) | PHY_PROCESS | CANCELABLE |
   +--------+---------------+-------------+--------+-------------+--------+-------------------------+---------------------------+----------+------------------------+---------------------------+
   | xxx    | xxx           | test_ck     | t_order1 | DAG       | ALTER_TABLE | PAUSED | -                     |                           | 100%     |                      | 32%                       | 0                | 0           |
   | 2.529  | 894           |             |         | true       |
   +--------+---------------+-------------+--------+-------------+--------+-------------------------+---------------------------+----------+------------------------+---------------------------+
   1 row in set (0.03 sec)
   mysql> cancel ddl xxx;
   Query OK, 1 row affected (3.23 sec)
   mysql> ALTER TABLE t_order1 PARTITION BY KEY(`ORDER_NO`) PARTITIONS 8;
   Query OK, 0 rows affected (5.54 sec)

How to recover from a partial shard failure after ALTER TABLE MODIFY COLUMN or CHANGE COLUMN

In PolarDB-X, a MODIFY COLUMN DDL can succeed on some shards and fail on others when data in the failed shards violates the new column constraint. For example:

-- Create a table partitioned across 32 shardsCREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c2` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 32;

INSERT INTO t1 VALUES (1, "1");
INSERT INTO t1 VALUES (2, "abc");

-- In strict SQL_MODE, this fails because "abc" is not a valid integerSET sql_mode="strict_trans_tables";
ALTER TABLE t1 MODIFY COLUMN c2 INT;

The error looks like:

...Not all physical DDLs have been executed successfully: 32 expected, 31 done, 1 failed...Incorrect integer value...

PolarDB-X provides two recovery paths: forward execution (fix data and continue) and reverse execution (roll back the change).