PolarDB:How to handle DDL exceptions

DDL principles

In PolarDB-X 1.0, a DDL instruction runs the corresponding operation on all sharded tables. Failures can be divided into two categories:

• The DDL operation fails on a sharded database. If a DDL operation fails on any sharded database, the table schemas of the sharded tables may become inconsistent.

  Failures on a sharded database can occur for various reasons, such as a table already existing during creation, a column already existing when a new column is added, or insufficient disk space.

• The execution becomes unresponsive. When you run a DDL operation on a large table, the operation may become unresponsive if the execution on a sharded database takes too long.

  An unresponsive operation is usually caused by a long execution time on a sharded database. For example, in MySQL, the time required for a DDL operation depends on whether the operation is In-Place (modifies the source table directly) or Copy Table (copies table data). An In-Place operation only needs to modify metadata. A Copy Table operation needs to rebuild the entire table's data and involves log and buffer operations. For more information about the relationship between different operations and these two methods, see the official MySQL document Online DDL Operations.

To determine if a DDL operation is In-Place or Copy Table, you can check the return value of rows affected after the operation completes. The following are examples:

• Changing a column's default value is very fast and does not affect the table data.

  Query OK, 0 rows affected (0.07 sec)

• Adding an index takes some time, but a return value of 0 rows affected indicates that the table data was not copied.

  Query OK, 0 rows affected (21.42 sec)

• Changing a column's data type takes a long time and requires rebuilding all rows in the table.

  Query OK, 1671168 rows affected (1 min 35.54 sec)

A PolarDB-X 1.0 DDL operation distributes all SQL statements to all sharded databases for parallel execution. A failure on one sharded database does not affect the others. In addition, PolarDB-X 1.0 provides the `CHECK TABLE` instruction to check the consistency of sharded table schemas. Therefore, you can retry a failed DDL operation. The operation fails on sharded databases where it has already succeeded, but this does not affect the other databases. You only need to ensure that all sharded table schemas are eventually consistent.

Steps to handle DDL execution failures

1. Use the `CHECK TABLE` instruction to check the table schema. If the result set contains only one row and the status is OK, the table schema is consistent. In this case, go to step 2. Otherwise, go to step 3.
2. Use the `SHOW CREATE TABLE` instruction to check the table schema. If the displayed schema matches the expected schema after the DDL operation, the DDL operation is successful. Otherwise, go to step 3.
3. Use the `SHOW PROCESSLIST` instruction to view the status of all currently running SQL statements. If a DDL operation is still running, wait for it to complete. Then, perform steps 1 and 2 to check whether the table schema is as expected. Otherwise, go to step 4.
4. Retry the DDL operation on PolarDB-X 1.0. If a Lock conflict error message is returned, go to step 5. Otherwise, go to step 3.
5. Use the `RELEASE DBLOCK` instruction to release the DDL operation lock, and then go to step 4.

The detailed steps are as follows:

1. Check table schema consistency.

   Use the `CHECK TABLE` instruction to check the table schema. The following is an example:

   check table `xxxx`;

   Note If no result is returned when you run `CHECK TABLE` in DMS, you can retry the instruction on the command line.

   If the result set contains only one row and the status is OK, the table schema is consistent. The following is an example:

   +----------------------------+-------+----------+----------+
   | TABLE                      | OP    | MSG_TYPE | MSG_TEXT |
   +----------------------------+-------+----------+----------+
   | TDDL5_APP.xxxx             | check | status   | OK       |
   +----------------------------+-------+----------+----------+
   1 row in set (0.05 sec)       

2. Check the table schema.

   Use the `SHOW CREATE TABLE` instruction to check the table schema. The following is an example:

   mysql> show create table `xxxx`;

   If the table schema is consistent and correct, the DDL operation is successful. The following is an example of the result set:

   +---------+------------------------------------------------------------------------------------------------------------------+
   | Table   | Create Table                                                                                                     |
   +---------+------------------------------------------------------------------------------------------------------------------+
   |  xxxx   | CREATE TABLE `xxxx` (
   `id` int(11) NOT NULL DEFAULT '0',
   `NAME` varchar(1024) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`id`) tbpartition by hash(`id`) tbpartitions 3                      |
   +---------+------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.05 sec)    

3. View currently running SQL statements.

   Some DDL operations run slowly. If a DDL operation is unresponsive for a long time, you can run the `SHOW PROCESSLIST` instruction to view the status of all currently running SQL statements. The following is an example:

   mysql> SHOW PROCESSLIST WHERE COMMAND != 'Sleep';

   The following is an example of the result set:

   +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
   | ID            | USER      | DB                 | COMMAND     | TIME    | STATE                                                                 | INFO                                                                                                 | ROWS_SENT | ROWS_EXAMINED | ROWS_READ |
   +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
   | 0-0-352724126 | ifisibhk0 | test_123_wvvp_0000 | Query       |      15 | Sending data                                                          | /*DRDS /42.120.74.88/ac47e5a72801000/ */select `t_item`.`detail_url`,SUM(`t_item`.`price`) from `t_i |      NULL |          NULL |      NULL |
   | 0-0-352864311 | cowxhthg0 | NULL               | Binlog Dump |      13 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                                                                 |      NULL |          NULL |      NULL |
   | 0-0-402714566 | ifisibhk0 | test_123_wvvp_0005 | Query       |      14 | Sending data                                                          | /*DRDS /42.120.74.88/ac47e5a72801000/ */select `t_item`.`detail_url`,`t_item`.`price` from `t_i      |      NULL |          NULL |      NULL |
   | 0-0-402714795 | ifisibhk0 | test_123_wvvp_0005 | Alter       |     114 | Sending data                                                          | /*DRDS /42.120.74.88/ac47e5a72801000/ */ALTER TABLE `Persons` ADD `Birthday` date                    |      NULL |          NULL |      NULL |
   ......
   +---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+
   12 rows in set (0.03 sec)       

   The TIME column indicates the number of seconds the instruction has been running. If you find a long-running instruction, such as the one with the ID 0-0-402714795 in the example, you can run the KILL '0-0-402714795' command to cancel it.

   Note PolarDB-X 1.0, one logical SQL statement corresponds to multiple instructions for the sharded databases. Therefore, stopping one logical DDL operation may require you to kill multiple instructions. You can identify the logical SQL statement to which an instruction belongs from the INFO column in the `SHOW PROCESSLIST` result set.
4. Lock conflict error.

   When PolarDB-X 1.0 runs a DDL operation, it adds a database-level lock and releases the lock after the operation is complete. If you kill a DDL operation, the lock may not be released. If you then run another DDL operation, the following error is returned:

   Lock conflict , maybe last DDL is still running

   In this case, you can run the `RELEASE DBLOCK` command to release the lock. After the instruction is canceled and the lock is released, retry the DDL operation during off-peak or maintenance hours.

FAQ

• Q: Why is the modified table schema not displayed in DMS or other clients?

  A: To be compatible with clients that retrieve table schemas from system tables, such as `COLUMNS` or `TABLES`, PolarDB-X 1.0 creates a shadow database in the shard 0 RDS instance. The shadow database has the same name as the PolarDB-X 1.0 logical database and stores all the schema information for the logical database.

  DMS retrieves the table schema of PolarDB-X 1.0 from the system tables of the shadow database. When you handle a DDL exception, the database and table schemas may be modified, but the schema in the shadow database is not updated. In this case, you can connect to the shadow database and run the DDL operation on the table again.

  Note `CHECK TABLE` does not check whether the shadow database schema is consistent with the PolarDB-X 1.0 logical database schema.
• Q: What do I do if an error code is returned when I run a DDL statement?

  A: For information about common error codes returned by PolarDB-X 1.0 and their solutions, see Error codes.