更新时间:2022-09-03 18:29:38
DDL的执行是一个分布式处理过程,出错可能导致各个分片表结构不一致,所以需要进行手动清理,详细操作步骤如下:
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)
CHECK TABLE tablename
指令来查看逻辑表是否创建成功。比如下面的例子展示了multi_db_multi_tbl
的某个物理分表没有创建成功时的情况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)
CREATE TABLE IF NOT EXISTS table1 (id int, name varchar(30), primary key(id)) dbpartition by hash(id); DROP TABLE IF EXISTS table1;
建索引失败或加列失败的处理方法跟上面建表失败的处理类似,详情请参见如何处理DDL异常 。