基于JDBCCatalog创建物化视图在Refresh时报错

橡木之心 · 2023年11月17日 06:36

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】
JDBC Catalog创建

CREATE EXTERNAL CATALOG aaa
PROPERTIES
(
    "type"="jdbc",
    "user"="xxx",
    "password"="xxx",
    "jdbc_uri"="xxx",
    "driver_url"="xxx",
    "driver_class"="org.postgresql.Driver"
);

物化视图创建

CREATE MATERIALIZED VIEW somethin_detail
DISTRIBUTED BY HASH(`id`)
REFRESH ASYNC START('2022-11-17 08:00:00') EVERY (interval 1 day)
AS SELECT
    *
FROM aaa.public.bbb;

【背景】基于JDBCCatalog创建物化视图在Refresh时报错
【业务影响】暂无
【StarRocks版本】3.1.4
【集群规模】1fe（1 leader）+3be（fe与be分别部署）
【联系方式】18618264324@163.com
【附件】
FE报错：

2023-11-17 00:56:50,199 WARN (pool-23-thread-1|814946) [PartitionBasedMvRefreshProcessor.doMvRefresh():281] Refresh mv something_detail failed: java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
2023-11-17 00:56:50,199 WARN (pool-23-thread-1|814946) [TaskRunExecutor.lambda$executeTaskRun$0():54] failed to execute TaskRun.
java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
        at com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77) ~[spark-dpp-1.0.0.jar:?]
        at com.starrocks.connector.PartitionUtil.getPartitionNameWithPartitionInfo(PartitionUtil.java:387) ~[starrocks-fe.jar:?]
        at com.starrocks.catalog.MaterializedView.getUpdatedPartitionNamesOfExternalTable(MaterializedView.java:817) ~[starrocks-fe.jar:?]
        at com.starrocks.catalog.MaterializedView.getUpdatedPartitionNamesOfTable(MaterializedView.java:871) ~[starrocks-fe.jar:?]
        at com.starrocks.catalog.MaterializedView.getUpdatedPartitionNamesOfTable(MaterializedView.java:549) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.needToRefreshTable(PartitionBasedMvRefreshProcessor.java:766) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.isNonPartitionedMVNeedToRefresh(PartitionBasedMvRefreshProcessor.java:788) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.getPartitionsToRefreshForMaterializedView(PartitionBasedMvRefreshProcessor.java:860) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.getPartitionsToRefreshForMaterializedView(PartitionBasedMvRefreshProcessor.java:822) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doMvRefresh(PartitionBasedMvRefreshProcessor.java:232) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.processTaskRun(PartitionBasedMvRefreshProcessor.java:185) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:189) ~[starrocks-fe.jar:?]
        at com.starrocks.scheduler.TaskRunExecutor.lambda$executeTaskRun$0(TaskRunExecutor.java:47) ~[starrocks-fe.jar:?]
        at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
        at java.lang.Thread.run(Thread.java:829) ~[?:?]

数据小黑 · 2023年11月27日 08:11

是这样，基于jdbc的物化视图，还不支持postgresql，其次报错确实不应该。
postgresql的支持近期会有，不过回首先支持range分区的方式

不知不觉 · 2023年12月5日 08:26

目前我使用基于 jdbc的物化视图， mysql 的更新也报这个错误，请问是什么问题，也不支持么

2023-12-05 16:11:25,562 WARN (starrocks-mysql-nio-pool-387|16392) [ConnectProcessor.handleQuery():384] Process one query failed. SQL: /* ApplicationName=DBeaver 23.2.2 - SQLEditor <Script-7.sql> */ refresh table VIEW_nameid_tag_field_map, because.
com.starrocks.common.AnalysisException: Getting syntax error at line 1, column 73. Detail message: No viable statement for input ‘refresh table’.

[PartitionBasedMvRefreshProcessor.doMvRefresh():281] Refresh mv VIEW_nameid_tag_field_map failed: java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
@数据小黑

不知不觉 · 2023年12月5日 08:28

CREATE EXTERNAL CATALOG mysql_cluster
PROPERTIES (“driver_class” = “com.mysql.cj.jdbc.Driver”,
“checksum” = “95cde01c78e7b04e13305338d60e056a”,
“driver_url” = “https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar”,
“type” = “jdbc”,
“user” = “mysql”,
“jdbc_uri” = “jdbc:mysql://xxxx-xxxxx.internal.enlightent.com:3306”
)

数据小黑 · 2023年12月5日 10:57

完整的fe.log日志能给一下么，这个错误堆栈有点短，mysql的建表语句和物化视图的建表语句也麻烦给一下吧，我模拟一下

数据小黑 · 2023年12月5日 11:12

查了一下源码，结合你前面的日志，

这个pr已经修复这个问题了，方便的话，还是给一下mysql的建表语句，我核实一下。

不知不觉 · 2023年12月6日 04:03

CREATE MATERIALIZED VIEW VIEW_nameid_tag_field_map (nameid, version, age, child_tag, subtype)
COMMENT “MATERIALIZED_VIEW”
DISTRIBUTED BY HASH(nameid) BUCKETS 1
REFRESH ASYNC START(“2023-01-01 10:00:00”) EVERY(INTERVAL 1 DAY)
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “1”,
“datacache.enable” = “true”,
“enable_async_write_back” = “false”,
“storage_volume” = “builtin_storage_volume”
)
AS SELECT A.nameid, group_concat(CASE WHEN (B.type = ‘version’) THEN B.name ELSE ‘’ END SEPARATOR ‘’) AS version, group_concat(CASE WHEN (B.type = ‘age’) THEN B.name ELSE ‘’ END SEPARATOR ‘’) AS age, group_concat(CASE WHEN (B.type = ‘child_tag’) THEN B.name ELSE ‘’ END SEPARATOR ‘’) AS child_tag, group_concat(CASE WHEN (B.type = ‘subtype’) THEN B.name ELSE ‘’ END SEPARATOR ‘’) AS subtype
FROM mysql_cluster.enlightent_daily.video_nameid_tags_map_info AS A INNER JOIN mysql_cluster.enlightent_daily.video_basic_tag_info AS B ON (A.tagid = B.id) AND (B.platform = ‘yunhe’)
GROUP BY A.nameid;

不知不觉 · 2023年12月6日 04:04

已贴，建表语句，我记得在别的帖子里贴过

不知不觉 · 2023年12月6日 04:06