创建布隆索引报错schema change task failed after try three times: task type: ALTER, status_code: RUNTIME_ERROR

💬 StarRocks 用户问答 功能使用相关
#1

【详述】问题详细描述
创建布隆索引报错：
schema change task failed after try three times: task type: ALTER, status_code: RUNTIME_ERROR, backendId: Backend [id=348542, host=192.168.255.14, heartbeatPort=9050, alive=true], signature: 18673041
【背景】做过哪些操作？
ALTER TABLE SIEBEL_C.S_ORDER_ITEM_DETAIL_07_08_TEST SET ( “bloom_filter_columns” = “PAR_ORDER_ITEM_ID”);
【业务影响】
【StarRocks版本】例如：2.4.1
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆

image
image514×566 6.75 KB

【附件】

  • fe.log/beINFO/相应截图
    be.zip (50.0 MB)
  • 慢查询：
    • Profile信息
    • 并行度：show variables like ‘%parallel_fragment_exec_instance_num%’;
    • pipeline是否开启：show variables like ‘%pipeline%’;
      image
    • be节点cpu和内存使用率截图
  • be crash
    • be.out

建表语句：
CREATE TABLE S_ORDER_ITEM_DETAIL_07_08_TEST (
ROW_ID varchar(1048576) NULL COMMENT “”,
SALE_ORD_ID varchar(1048576) NULL COMMENT “”,
ORD_ITEM_ID varchar(1048576) NULL COMMENT “”,
OBJECT_ID varchar(1048576) NULL COMMENT “”,
ACTION_CD varchar(1048576) NULL COMMENT “”,
INTEGRATION_ID varchar(1048576) NULL COMMENT “”,
ASSET_INTEG_ID varchar(1048576) NULL COMMENT “”,
PROD_ITEM_ID varchar(1048576) NULL COMMENT “”,
CREATED datetime NULL COMMENT “”,
LAST_UPD datetime NULL COMMENT “”,
ADJ_UNIT_PRI decimal128(22, 7) NULL COMMENT “”,
UNIT_PRI decimal128(22, 7) NULL COMMENT “”,
AGREE_ID varchar(1048576) NULL COMMENT “”,
X_HQ_PRODUCT_ID varchar(1048576) NULL COMMENT “”,
X_VPN_GROUP_ID varchar(1048576) NULL COMMENT “”,
X_VPN_INNER_ID varchar(1048576) NULL COMMENT “”,
X_INTEGRATION_NUM varchar(1048576) NULL COMMENT “”,
X_USERNAME varchar(1048576) NULL COMMENT “”,
SERV_ACCNT_ID varchar(1048576) NULL COMMENT “”,
DESC_TEXT varchar(1048576) NULL COMMENT “”,
X_CIRCUIT_NUMBER varchar(1048576) NULL COMMENT “”,
X_PROM_INTEG_ID varchar(1048576) NULL COMMENT “”,
X_PROM_ID varchar(1048576) NULL COMMENT “”,
X_RELAY_MAIN_NUMBER varchar(1048576) NULL COMMENT “”,
SERVICE_END_DT datetime NULL COMMENT “”,
PAR_ORDER_ITEM_ID varchar(1048576) NULL COMMENT “”,
CREATE_BY varchar(1048576) NULL COMMENT “”,
LAST_UPD_BY varchar(1048576) NULL COMMENT “”,
PART_NUM varchar(1048576) NULL COMMENT “”,
TYPE varchar(1048576) NULL COMMENT “”,
NAME varchar(1048576) NULL COMMENT “”
) ENGINE=OLAP
DUPLICATE KEY(ROW_ID)
COMMENT “OLAP”
PARTITION BY RANGE(CREATED)
(PARTITION P1900 VALUES [(‘0000-01-01 00:00:00’), (‘1990-01-01 00:00:00’)),
PARTITION p1990 VALUES [(‘1990-01-01 00:00:00’), (‘2012-01-01 00:00:00’)),
PARTITION p201201 VALUES [(‘2012-01-01 00:00:00’), (‘2012-02-01 00:00:00’)),
PARTITION p201202 VALUES [(‘2012-02-01 00:00:00’), (‘2012-03-01 00:00:00’)),
PARTITION p201203 VALUES [(‘2012-03-01 00:00:00’), (‘2012-04-01 00:00:00’)),
PARTITION p201204 VALUES [(‘2012-04-01 00:00:00’), (‘2012-05-01 00:00:00’)),
PARTITION p201205 VALUES [(‘2012-05-01 00:00:00’), (‘2012-06-01 00:00:00’)),
PARTITION p201206 VALUES [(‘2012-06-01 00:00:00’), (‘2012-07-01 00:00:00’)),
PARTITION p201207 VALUES [(‘2012-07-01 00:00:00’), (‘2012-08-01 00:00:00’)),
PARTITION p201208 VALUES [(‘2012-08-01 00:00:00’), (‘2012-09-01 00:00:00’)),
PARTITION p201209 VALUES [(‘2012-09-01 00:00:00’), (‘2012-10-01 00:00:00’)),
PARTITION p201210 VALUES [(‘2012-10-01 00:00:00’), (‘2012-11-01 00:00:00’)),
PARTITION p201211 VALUES [(‘2012-11-01 00:00:00’), (‘2012-12-01 00:00:00’)),
PARTITION p201212 VALUES [(‘2012-12-01 00:00:00’), (‘2013-01-01 00:00:00’)),
PARTITION p201301 VALUES [(‘2013-01-01 00:00:00’), (‘2013-02-01 00:00:00’)),
PARTITION p201302 VALUES [(‘2013-02-01 00:00:00’), (‘2013-03-01 00:00:00’)),
PARTITION p201303 VALUES [(‘2013-03-01 00:00:00’), (‘2013-04-01 00:00:00’)),
PARTITION p201304 VALUES [(‘2013-04-01 00:00:00’), (‘2013-05-01 00:00:00’)),
PARTITION p201305 VALUES [(‘2013-05-01 00:00:00’), (‘2013-06-01 00:00:00’)),
PARTITION p201306 VALUES [(‘2013-06-01 00:00:00’), (‘2013-07-01 00:00:00’)),
PARTITION p201307 VALUES [(‘2013-07-01 00:00:00’), (‘2013-08-01 00:00:00’)),
PARTITION p201308 VALUES [(‘2013-08-01 00:00:00’), (‘2013-09-01 00:00:00’)),
PARTITION p201309 VALUES [(‘2013-09-01 00:00:00’), (‘2013-10-01 00:00:00’)),
PARTITION p201310 VALUES [(‘2013-10-01 00:00:00’), (‘2013-11-01 00:00:00’)),
PARTITION p201311 VALUES [(‘2013-11-01 00:00:00’), (‘2013-12-01 00:00:00’)),
PARTITION p201312 VALUES [(‘2013-12-01 00:00:00’), (‘2014-01-01 00:00:00’)),
PARTITION p201401 VALUES [(‘2014-01-01 00:00:00’), (‘2014-02-01 00:00:00’)),
PARTITION p201402 VALUES [(‘2014-02-01 00:00:00’), (‘2014-03-01 00:00:00’)),
PARTITION p201403 VALUES [(‘2014-03-01 00:00:00’), (‘2014-04-01 00:00:00’)),
PARTITION p201404 VALUES [(‘2014-04-01 00:00:00’), (‘2014-05-01 00:00:00’)),
PARTITION p201405 VALUES [(‘2014-05-01 00:00:00’), (‘2014-06-01 00:00:00’)),
PARTITION p201406 VALUES [(‘2014-06-01 00:00:00’), (‘2014-07-01 00:00:00’)),
PARTITION p201407 VALUES [(‘2014-07-01 00:00:00’), (‘2014-08-01 00:00:00’)),
PARTITION p201408 VALUES [(‘2014-08-01 00:00:00’), (‘2014-09-01 00:00:00’)),
PARTITION p201409 VALUES [(‘2014-09-01 00:00:00’), (‘2014-10-01 00:00:00’)),
PARTITION p201410 VALUES [(‘2014-10-01 00:00:00’), (‘2014-11-01 00:00:00’)),
PARTITION p201411 VALUES [(‘2014-11-01 00:00:00’), (‘2014-12-01 00:00:00’)),
PARTITION p201412 VALUES [(‘2014-12-01 00:00:00’), (‘2015-01-01 00:00:00’)),
PARTITION p201501 VALUES [(‘2015-01-01 00:00:00’), (‘2015-02-01 00:00:00’)),
PARTITION p201502 VALUES [(‘2015-02-01 00:00:00’), (‘2015-03-01 00:00:00’)),
PARTITION p201503 VALUES [(‘2015-03-01 00:00:00’), (‘2015-04-01 00:00:00’)),
PARTITION p201504 VALUES [(‘2015-04-01 00:00:00’), (‘2015-05-01 00:00:00’)),
PARTITION p201505 VALUES [(‘2015-05-01 00:00:00’), (‘2015-06-01 00:00:00’)),
PARTITION p201506 VALUES [(‘2015-06-01 00:00:00’), (‘2015-07-01 00:00:00’)),
PARTITION p201507 VALUES [(‘2015-07-01 00:00:00’), (‘2015-08-01 00:00:00’)),
PARTITION p201508 VALUES [(‘2015-08-01 00:00:00’), (‘2015-09-01 00:00:00’)),
PARTITION p201509 VALUES [(‘2015-09-01 00:00:00’), (‘2015-10-01 00:00:00’)),
PARTITION p201510 VALUES [(‘2015-10-01 00:00:00’), (‘2015-11-01 00:00:00’)),
PARTITION p201511 VALUES [(‘2015-11-01 00:00:00’), (‘2015-12-01 00:00:00’)),
PARTITION p201512 VALUES [(‘2015-12-01 00:00:00’), (‘2016-01-01 00:00:00’)),
PARTITION p201601 VALUES [(‘2016-01-01 00:00:00’), (‘2016-02-01 00:00:00’)),
PARTITION p201602 VALUES [(‘2016-02-01 00:00:00’), (‘2016-03-01 00:00:00’)),
PARTITION p201603 VALUES [(‘2016-03-01 00:00:00’), (‘2016-04-01 00:00:00’)),
PARTITION p201604 VALUES [(‘2016-04-01 00:00:00’), (‘2016-05-01 00:00:00’)),
PARTITION p201605 VALUES [(‘2016-05-01 00:00:00’), (‘2016-06-01 00:00:00’)),
PARTITION p201606 VALUES [(‘2016-06-01 00:00:00’), (‘2016-07-01 00:00:00’)),
PARTITION p201607 VALUES [(‘2016-07-01 00:00:00’), (‘2016-08-01 00:00:00’)),
PARTITION p201608 VALUES [(‘2016-08-01 00:00:00’), (‘2016-09-01 00:00:00’)),
PARTITION p201609 VALUES [(‘2016-09-01 00:00:00’), (‘2016-10-01 00:00:00’)),
PARTITION p201610 VALUES [(‘2016-10-01 00:00:00’), (‘2016-11-01 00:00:00’)),
PARTITION p201611 VALUES [(‘2016-11-01 00:00:00’), (‘2016-12-01 00:00:00’)),
PARTITION p201612 VALUES [(‘2016-12-01 00:00:00’), (‘2017-01-01 00:00:00’)),
PARTITION p201701 VALUES [(‘2017-01-01 00:00:00’), (‘2017-02-01 00:00:00’)),
PARTITION p201702 VALUES [(‘2017-02-01 00:00:00’), (‘2017-03-01 00:00:00’)),
PARTITION p201703 VALUES [(‘2017-03-01 00:00:00’), (‘2017-04-01 00:00:00’)),
PARTITION p201704 VALUES [(‘2017-04-01 00:00:00’), (‘2017-05-01 00:00:00’)),
PARTITION p201705 VALUES [(‘2017-05-01 00:00:00’), (‘2017-06-01 00:00:00’)),
PARTITION p201706 VALUES [(‘2017-06-01 00:00:00’), (‘2017-07-01 00:00:00’)),
PARTITION p201707 VALUES [(‘2017-07-01 00:00:00’), (‘2017-08-01 00:00:00’)),
PARTITION p201708 VALUES [(‘2017-08-01 00:00:00’), (‘2017-09-01 00:00:00’)),
PARTITION p201709 VALUES [(‘2017-09-01 00:00:00’), (‘2017-10-01 00:00:00’)),
PARTITION p201710 VALUES [(‘2017-10-01 00:00:00’), (‘2017-11-01 00:00:00’)),
PARTITION p201711 VALUES [(‘2017-11-01 00:00:00’), (‘2017-12-01 00:00:00’)),
PARTITION p201712 VALUES [(‘2017-12-01 00:00:00’), (‘2018-01-01 00:00:00’)),
PARTITION p201801 VALUES [(‘2018-01-01 00:00:00’), (‘2018-02-01 00:00:00’)),
PARTITION p201802 VALUES [(‘2018-02-01 00:00:00’), (‘2018-03-01 00:00:00’)),
PARTITION p201803 VALUES [(‘2018-03-01 00:00:00’), (‘2018-04-01 00:00:00’)),
PARTITION p201804 VALUES [(‘2018-04-01 00:00:00’), (‘2018-05-01 00:00:00’)),
PARTITION p201805 VALUES [(‘2018-05-01 00:00:00’), (‘2018-06-01 00:00:00’)),
PARTITION p201806 VALUES [(‘2018-06-01 00:00:00’), (‘2018-07-01 00:00:00’)),
PARTITION p201807 VALUES [(‘2018-07-01 00:00:00’), (‘2018-08-01 00:00:00’)),
PARTITION p201808 VALUES [(‘2018-08-01 00:00:00’), (‘2018-09-01 00:00:00’)),
PARTITION p201809 VALUES [(‘2018-09-01 00:00:00’), (‘2018-10-01 00:00:00’)),
PARTITION p201810 VALUES [(‘2018-10-01 00:00:00’), (‘2018-11-01 00:00:00’)),
PARTITION p201811 VALUES [(‘2018-11-01 00:00:00’), (‘2018-12-01 00:00:00’)),
PARTITION p201812 VALUES [(‘2018-12-01 00:00:00’), (‘2019-01-01 00:00:00’)),
PARTITION p201901 VALUES [(‘2019-01-01 00:00:00’), (‘2019-02-01 00:00:00’)),
PARTITION p201902 VALUES [(‘2019-02-01 00:00:00’), (‘2019-03-01 00:00:00’)),
PARTITION p201903 VALUES [(‘2019-03-01 00:00:00’), (‘2019-04-01 00:00:00’)),
PARTITION p201904 VALUES [(‘2019-04-01 00:00:00’), (‘2019-05-01 00:00:00’)),
PARTITION p201905 VALUES [(‘2019-05-01 00:00:00’), (‘2019-06-01 00:00:00’)),
PARTITION p201906 VALUES [(‘2019-06-01 00:00:00’), (‘2019-07-01 00:00:00’)),
PARTITION p201907 VALUES [(‘2019-07-01 00:00:00’), (‘2019-08-01 00:00:00’)),
PARTITION p201908 VALUES [(‘2019-08-01 00:00:00’), (‘2019-09-01 00:00:00’)),
PARTITION p201909 VALUES [(‘2019-09-01 00:00:00’), (‘2019-10-01 00:00:00’)),
PARTITION p201910 VALUES [(‘2019-10-01 00:00:00’), (‘2019-11-01 00:00:00’)),
PARTITION p201911 VALUES [(‘2019-11-01 00:00:00’), (‘2019-12-01 00:00:00’)),
PARTITION p201912 VALUES [(‘2019-12-01 00:00:00’), (‘2020-01-01 00:00:00’)),
PARTITION p202001 VALUES [(‘2020-01-01 00:00:00’), (‘2020-02-01 00:00:00’)),
PARTITION p202002 VALUES [(‘2020-02-01 00:00:00’), (‘2020-03-01 00:00:00’)),
PARTITION p202003 VALUES [(‘2020-03-01 00:00:00’), (‘2020-04-01 00:00:00’)),
PARTITION p202004 VALUES [(‘2020-04-01 00:00:00’), (‘2020-05-01 00:00:00’)),
PARTITION p202005 VALUES [(‘2020-05-01 00:00:00’), (‘2020-06-01 00:00:00’)),
PARTITION p202006 VALUES [(‘2020-06-01 00:00:00’), (‘2020-07-01 00:00:00’)),
PARTITION p202007 VALUES [(‘2020-07-01 00:00:00’), (‘2020-08-01 00:00:00’)),
PARTITION p202008 VALUES [(‘2020-08-01 00:00:00’), (‘2020-09-01 00:00:00’)),
PARTITION p202009 VALUES [(‘2020-09-01 00:00:00’), (‘2020-10-01 00:00:00’)),
PARTITION p202010 VALUES [(‘2020-10-01 00:00:00’), (‘2020-11-01 00:00:00’)),
PARTITION p202011 VALUES [(‘2020-11-01 00:00:00’), (‘2020-12-01 00:00:00’)),
PARTITION p202012 VALUES [(‘2020-12-01 00:00:00’), (‘2021-01-01 00:00:00’)),
PARTITION p202101 VALUES [(‘2021-01-01 00:00:00’), (‘2021-02-01 00:00:00’)),
PARTITION p202102 VALUES [(‘2021-02-01 00:00:00’), (‘2021-03-01 00:00:00’)),
PARTITION p202103 VALUES [(‘2021-03-01 00:00:00’), (‘2021-04-01 00:00:00’)),
PARTITION p202104 VALUES [(‘2021-04-01 00:00:00’), (‘2021-05-01 00:00:00’)),
PARTITION p202105 VALUES [(‘2021-05-01 00:00:00’), (‘2021-06-01 00:00:00’)),
PARTITION p202106 VALUES [(‘2021-06-01 00:00:00’), (‘2021-07-01 00:00:00’)),
PARTITION p202107 VALUES [(‘2021-07-01 00:00:00’), (‘2021-08-01 00:00:00’)),
PARTITION p202108 VALUES [(‘2021-08-01 00:00:00’), (‘2021-09-01 00:00:00’)),
PARTITION p202109 VALUES [(‘2021-09-01 00:00:00’), (‘2021-10-01 00:00:00’)),
PARTITION p202110 VALUES [(‘2021-10-01 00:00:00’), (‘2021-11-01 00:00:00’)),
PARTITION p202111 VALUES [(‘2021-11-01 00:00:00’), (‘2021-12-01 00:00:00’)),
PARTITION p202112 VALUES [(‘2021-12-01 00:00:00’), (‘2022-01-01 00:00:00’)),
PARTITION p202201 VALUES [(‘2022-01-01 00:00:00’), (‘2022-02-01 00:00:00’)),
PARTITION p202202 VALUES [(‘2022-02-01 00:00:00’), (‘2022-03-01 00:00:00’)),
PARTITION p202203 VALUES [(‘2022-03-01 00:00:00’), (‘2022-04-01 00:00:00’)),
PARTITION p202204 VALUES [(‘2022-04-01 00:00:00’), (‘2022-05-01 00:00:00’)),
PARTITION p202205 VALUES [(‘2022-05-01 00:00:00’), (‘2022-06-01 00:00:00’)),
PARTITION p202206 VALUES [(‘2022-06-01 00:00:00’), (‘2022-07-01 00:00:00’)),
PARTITION p202207 VALUES [(‘2022-07-01 00:00:00’), (‘2022-08-01 00:00:00’)),
PARTITION p202208 VALUES [(‘2022-08-01 00:00:00’), (‘2022-09-01 00:00:00’)),
PARTITION p202209 VALUES [(‘2022-09-01 00:00:00’), (‘2022-10-01 00:00:00’)),
PARTITION p202210 VALUES [(‘2022-10-01 00:00:00’), (‘2022-11-01 00:00:00’)),
PARTITION p202211 VALUES [(‘2022-11-01 00:00:00’), (‘2022-12-01 00:00:00’)),
PARTITION p202212 VALUES [(‘2022-12-01 00:00:00’), (‘2023-01-01 00:00:00’)),
PARTITION P2100 VALUES [(‘2023-01-01 00:00:00’), (‘2100-01-01 00:00:00’)))
DISTRIBUTED BY HASH(ROW_ID) BUCKETS 3
PROPERTIES (
“replication_num” = “3”,
“bloom_filter_columns” = “PAR_ORDER_ITEM_ID, ROW_ID”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);

#2

可以执行show alter 看下具体的报错 https://docs.starrocks.io/zh-cn/latest/sql-reference/sql-statements/data-manipulation/SHOW%20ALTER#示例

#3

通常来讲是一般是alter 内存超了默认是2G，可以增大下参数是memory_limitation_per_thread_for_schema_change

#4

好的，我尝试一下，麻烦了

#5

麻烦确认下磁盘相关是否有问题吧,看起来有io错误
1.目录权限是否和启动的用户一致
2.df -h是否有容量
3.df -ih是否有inode容量
4.磁盘是否可读写

#7

请问调大参数memory_limitation_per_thread_for_schema_change之后可以创建成功吗

#8

嗯嗯，成功了，把memory_limitation_per_thread_for_schema_change=5，感谢

#10

好的,为了更好排查问题隐患,麻烦您确认下几个事:
1、show tablet 4264781 看下这个tablet的副本在哪几台be上
2、是否有副本 在您提供的be日志对应的节点上
3、再看看其他两个副本,对应be节点的日志,这个tablet的schema change的执行情况，成功还是失败，失败报什么错

#11

目的是需要明确下 tablet=4264781分别所在be节点上,从任务失败到任务成功都经历了什么,麻烦把 对应时间段 相关be节点的日志 都发来吧

#12

您好，不好意思，可能会晚点或者明天才去找一下be日志，be日志量有点大，这两天工作有点忙。

#13

好的,查看副本所在be节点:show tablet 4264781,执行最后一列的命令
辛苦把副本所在节点be日志都发下,(把任务报错到成功这段时间的be日志找出来即可 )

京ICP备2022026421号-1