StarRocks执行挂载了同步物化视图的SQL后直接挂掉

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

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
有一个明细表ht_event，创建了一个同步物化视图mv_hit_event_1_5_count，执行如下查询SQL的时候就会报错：
select
source_organization_id key,
count(event_iid) AS value
from
watm.hit_event2
where
day between 20231026 and 20231226
and source_organization_id is not null
and source_organization_id != ‘’
group by
source_organization_id
order by
value desc
limit 300 ;
【背景】做过哪些操作？
升级StarRocks系统为3.1.4
物化视图及建表SQL如下：
create.sql (9.4 KB)
【业务影响】
挂载该物化视图的SQL都会使be挂掉。
【是否存算分离】
否
【StarRocks版本】例如：3.1.4-0c4b2a3
【集群规模】例如：单节点3fe（1 follower+2observer）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】为了在解决问题过程中能及时联系到您获取一些日志信息，请补充下您的联系方式，例如：社区群13-黄健
【附件】

#2

image
创建物化视图查询的表不是 hit_event2，物化视图创建语句写错了，还是实际就这么执行的

#3

query_id:d716b459-a3c8-11ee-89cf-cabbfdde7f98
query_id:b87de8ed-a3c8-11ee-89cf-cabbfdde7f98
query_id:648d8661-a3c7-11ee-9265-56ddc9534bce
到fe.audit.log 中找一下这几个query id，是不是都是相同的sql，对应的sql是上面发的sql么

#4

是的，相关日志如下（同时，我把StarRocks集群降级到3.1.3就没这问题了）：
2023-12-26 16:29:10,540 [query] |Client=10.100.14.61:55029|User=root|AuthorizedUser=‘root’@’%’|ResourceGroup=default_wg|Catalog=default_catalog|Db=watm|State=ERR|ErrorCode=THRIFT_RPC_ERROR|Time=1295|ScanBytes=0|ScanRows=0|ReturnRows=0|StmtId=667|QueryId=d716b459-a3c8-11ee-89cf-cabbfdde7f98|IsQuery=true|feIp=starrockscluster-fe-2.starrockscluster-fe-search.default.svc.cluster.local|Stmt=/* ApplicationName=DBeaver 23.3.0 - SQLEditor <Script-2.sql> / select source_organization_id key, count(event_iid) AS value from watm.hit_event where day between 20231219 and 20231226 and source_organization_id is not null and source_organization_id != ‘’ group by source_organization_id order by value desc limit 300 |Digest=|PlanCpuCost=4386570.600838726|PlanMemCost=80.07431250765617|CandidateMVs=mv_async_log_report,file_flow_view,file_flow_view
2023-12-26 16:28:19,325 [query] |Client=10.100.14.61:55029|User=root|AuthorizedUser=‘root’@’%’|ResourceGroup=default_wg|Catalog=default_catalog|Db=watm|State=ERR|ErrorCode=THRIFT_RPC_ERROR|Time=1413|ScanBytes=0|ScanRows=0|ReturnRows=0|StmtId=649|QueryId=b87de8ed-a3c8-11ee-89cf-cabbfdde7f98|IsQuery=true|feIp=starrockscluster-fe-2.starrockscluster-fe-search.default.svc.cluster.local|Stmt=/ ApplicationName=DBeaver 23.3.0 - SQLEditor <Script-2.sql> / select source_organization_id key, count(event_iid) AS value from watm.hit_event where day between 20231219 and 20231226 and source_organization_id is not null and source_organization_id != ‘’ group by source_organization_id order by value desc limit 300 |Digest=|PlanCpuCost=4386570.600838726|PlanMemCost=80.07431250765617|CandidateMVs=mv_async_log_report,file_flow_view,file_flow_view
2023-12-26 16:18:48,925 [query] |Client=10.100.14.61:26491|User=root|AuthorizedUser=‘root’@’%’|ResourceGroup=default_wg|Catalog=default_catalog|Db=watm|State=ERR|ErrorCode=THRIFT_RPC_ERROR|Time=1336|ScanBytes=0|ScanRows=0|ReturnRows=0|StmtId=334|QueryId=648d8661-a3c7-11ee-9265-56ddc9534bce|IsQuery=true|feIp=starrockscluster-fe-1.starrockscluster-fe-search.default.svc.cluster.local|Stmt=/ ApplicationName=DBeaver 23.3.0 - SQLEditor <Script-2.sql> */ select source_organization_id key, count(event_iid) AS value from watm.hit_event where day between 20231219 and 20231226 and source_organization_id is not null and source_organization_id != ‘’ group by source_organization_id order by value desc limit 300 |Digest=|PlanCpuCost=4386570.600838726|PlanMemCost=80.07431250765617|CandidateMVs=mv_async_log_report,file_flow_view,file_flow_view

#5

还有 3.1.4的环境么，发一下 explain costs+sql 的结果，3.1.3版本的也行

#6

PLAN FRAGMENT 0(F02)
Output Exprs:19: source_organization_id | 111: count
Input Partition: UNPARTITIONED
RESULT SINK

6:MERGING-EXCHANGE
distribution type: GATHER
limit: 300
cardinality: 2
column statistics:
* source_organization_id–>[-Infinity, Infinity, 0.0, 2.0141357206300254, 2.0] ESTIMATE
* count–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN

PLAN FRAGMENT 1(F01)

Input Partition: HASH_PARTITIONED: 19: source_organization_id
OutPut Partition: UNPARTITIONED
OutPut Exchange Id: 06

5:TOP-N
| order by: [111, BIGINT, true] DESC
| offset: 0
| limit: 300
| cardinality: 2
| column statistics:
| * source_organization_id–>[-Infinity, Infinity, 0.0, 2.0141357206300254, 2.0] ESTIMATE
| * count–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
|
4:AGGREGATE (merge finalize)
| aggregate: sum[([111: count, BIGINT, true]); args: BIGINT; result: BIGINT; args nullable: true; result nullable: true]
| group by: [19: source_organization_id, VARCHAR, true]
| cardinality: 2
| column statistics:
| * source_organization_id–>[-Infinity, Infinity, 0.0, 2.0141357206300254, 2.0] ESTIMATE
| * count–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
|
3:EXCHANGE
distribution type: SHUFFLE
partition exprs: [19: source_organization_id, VARCHAR, true]
cardinality: 2

PLAN FRAGMENT 2(F00)

Input Partition: RANDOM
OutPut Partition: HASH_PARTITIONED: 19: source_organization_id
OutPut Exchange Id: 03

2:AGGREGATE (update serialize)
| STREAMING
| aggregate: sum[([110: mv_count_event_iid, BIGINT, false]); args: BIGINT; result: BIGINT; args nullable: false; result nullable: true]
| group by: [19: source_organization_id, VARCHAR, true]
| cardinality: 2
| column statistics:
| * source_organization_id–>[-Infinity, Infinity, 0.0, 2.0141357206300254, 2.0] ESTIMATE
| * count–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
|
1:Project
| output columns:
| 19 <-> [19: source_organization_id, VARCHAR, true]
| 110 <-> [110: mv_count_event_iid, BIGINT, false]
| cardinality: 156556
| column statistics:
| * source_organization_id–>[-Infinity, Infinity, 0.0, 2.0141357206300254, 2.0] ESTIMATE
| * mv_count_event_iid–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN
|
0:OlapScanNode
table: hit_event, rollup: mv_hit_event_1_5_count
preAggregation: off. Reason: Predicates include the value column
Predicates: 19: source_organization_id IS NOT NULL, [19: source_organization_id, VARCHAR, true] != ‘’
partitionsRatio=8/27, tabletsRatio=128/128
tabletList=1475907,1475911,1475915,1475919,1475923,1475927,1475931,1475935,1475939,1475943 …
actualRows=388, avgRowSize=7.014136
cardinality: 156556
column statistics:
* day–>[2.0231219E7, 2.0231227E7, 0.0, 4.0, 8.0] ESTIMATE
* source_organization_id–>[-Infinity, Infinity, 0.0, 2.0141357206300254, 2.0] ESTIMATE
* mv_count_event_iid–>[-Infinity, Infinity, 0.0, 1.0, 1.0] UNKNOWN

#7

京ICP备2022026421号-1