DDL FAQ

This topic provides answers to commonly asked questions about the execution errors of data definition language (DDL) statements in PolarDB-X 1.0.

What can I do if an execution error occurs when I create a table?

A DDL statement is processed in distributed mode. An error may cause schema inconsistency among shards. Therefore, you must manually clean up the error. You can perform the following steps:

1. PolarDB-X 1.0 provides basic error description information, such as syntax errors. If the error message is too long, the system prompts you to run the SHOW WARNINGS command to view the execution failure cause for each database shard.

2. Run the SHOW TOPOLOGY command to view the topology of physical tables.

   SHOW TOPOLOGY FROM multi_db_multi_tbl;
   +------+-----------------+-----------------------+
   | ID   | GROUP_NAME      | TABLE_NAME            |
   +------+-----------------+-----------------------+
   |    0 | corona_qatest_0 | multi_db_multi_tbl_00 |
   |    1 | corona_qatest_0 | multi_db_multi_tbl_01 |
   |    2 | corona_qatest_0 | multi_db_multi_tbl_02 |
   |    3 | corona_qatest_1 | multi_db_multi_tbl_03 |
   |    4 | corona_qatest_1 | multi_db_multi_tbl_04 |
   |    5 | corona_qatest_1 | multi_db_multi_tbl_05 |
   |    6 | corona_qatest_2 | multi_db_multi_tbl_06 |
   |    7 | corona_qatest_2 | multi_db_multi_tbl_07 |
   |    8 | corona_qatest_2 | multi_db_multi_tbl_08 |
   |    9 | corona_qatest_3 | multi_db_multi_tbl_09 |
   |   10 | corona_qatest_3 | multi_db_multi_tbl_10 |
   |   11 | corona_qatest_3 | multi_db_multi_tbl_11 |
   +------+-----------------+-----------------------+
   12 rows in set (0.21 sec)
   

1. Run the CHECK TABLE tablename command to check whether the logical table has been created.

   For example, the following example shows the scenario where a physical table shard of multi_db_multi_tbl failed to be created.

   mysql> check table multi_db_multi_tbl;
   +-------------------------------------------------+-------+----------+---------------------------------------------------------------------------+
   | TABLE                                           | OP    | MSG_TYPE | MSG_TEXT                                                                  |
   +-------------------------------------------------+-------+----------+---------------------------------------------------------------------------+
   | andor_mysql_qatest. multi_db_multi_tbl | check | Error    | Table 'corona_qatest_0. multi_db_multi_tbl_02' doesn't exist |
   +-------------------------------------------------+-------+----------+---------------------------------------------------------------------------+ 1 row in set (0.16 sec)
   

1. Create or delete the table in idempotent mode to create or delete the remaining physical tables.

   CREATE TABLE IF NOT EXISTS table1
   (id int, name varchar(30), primary key(id))
   dbpartition by hash(id);  
   DROP TABLE IF EXISTS table1;
   

What can I do if I failed to create an index or add a column?

The method for handling the failure when you create an index or add a column is similar to the preceding steps for the table creation failure. For more information, see Troubleshoot DDL exceptions.