:FAQs for DDL statements

Failed to create a table

DDL statements are executed in distributed mode. An error can cause table schema to be inconsistent among table shards. You can troubleshoot the error by performing the following steps:

1. PolarDB-X provides the basic information about errors to help you identify errors, such as syntax errors. If an error message is too long, the system displays an on-screen message to prompt you to use the SHOW WARNINGS statement to view the cause of each failure on each database shard.
2. You can use the SHOW TOPOLOGY statement to view the topology of physical table shards.

    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)

3. You can use the CHECK TABLE <Tablename> statement to check whether logical tables of the specified table are created. The following example shows that a physical table shard of the multi_db_multi_tbl table is not 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)

4. You can create or delete the table in idempotent mode to create physical tables or delete the existing 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;

Failed to create an index or a column

You can use the same methods that are described in the preceding section to troubleshoot index creation failures and column creation failures. For more information about how to handle DDL execution failures, see Handle DDL exceptions.