更新时间:2022-09-02 09:27:05
PolarDB-X 1.0的DDL指令会在所有分表上执行对应的DDL操作。失败的情况可以分为两类:
判断DDL操作是In-place还是Copy Table操作,可以查看操作结束后rows affected
这一项的返回值。示例如下:
Query OK, 0 rows affected (0.07 sec)
0 rows affected
说明表数据没有被复制)。Query OK, 0 rows affected (21.42 sec)
Query OK, 1671168 rows affected (1 min 35.54 sec)
因此,执行一个大表DDL操作前,可以先通过以下步骤判定这是一个快速还是慢速的操作:
rows affected
值是否为0。非0的值意味着该操作需要重建整张表,这时可能需要考虑在业务低峰期执行该操作。PolarDB-X 1.0 DDL操作会将所有SQL分发到所有分库上并行执行。任一分库上执行失败不会影响其他分库。另外,PolarDB-X 1.0还提供了CHECK TABLE指令来检测分表结构的一致性。因此,失败的DDL操作可以重新执行,已经执行成功的分库上失败报错并不会影响其他分库。只需保证最终所有分表结构一致即可。
Lock conflict
的报错请进行步骤5,否则进行步骤3。详细操作如下:
mysql> check table `xxxx`;
+----------------------------+-------+----------+----------+ | TABLE | OP | MSG_TYPE | MSG_TEXT | +----------------------------+-------+----------+----------+ | TDDL5_APP.xxxx | check | status | OK | +----------------------------+-------+----------+----------+ 1 row in set (0.05 sec)
mysql> show create table `xxxx`;
+---------+------------------------------------------------------------------------------------------------------------------+ | 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)
mysql> SHOW PROCESSLIST WHERE COMMAND != 'Sleep';
+---------------+-----------+--------------------+-------------+---------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+-----------+---------------+-----------+ | 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)
0-0-402714795
的指令)后,您可使用KILL '0-0-402714795'命令来取消该慢指令。Lock conflict
报错处理。PolarDB-X 1.0执行DDL操作先会加库级锁,操作完后再释放掉。KILL DDL操作很可能会导致该锁没有释放,此时再执行DDL会出现以下报错:Lock conflict , maybe last DDL is still running