Updating asynchronous materialized views does not work

Sabotage555 · December 25, 2024, 11:59am

Updating asynchronous materialized views does not work.
The Sharrocks version is 3.3.4-56bcf6f.
I’m trying to reproduce an example from the documentation (Asynchronous materialized views | StarRocks /):

CREATE MATERIALIZED VIEW order_mv
DISTRIBUTED BY HASH(`order_id`)
REFRESH ASYNC START('2022-09-01 10:00:00') EVERY (interval 1 day)
AS SELECT
    order_list.order_id,
    sum(goods.price) as total
FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;

The order_mv view has been created, but it is not filled with data. The refresh WITH SYNC MODE command freezes for ~5 minutes at first, then just a timeout error:

mysql> refresh MATERIALIZED VIEW order_mv WITH SYNC MODE;
ERROR 1064 (HY000): java.net.SocketTimeoutException: Read timed out

The task status (information_schema.task_runs) is always “PENDING”.

Trace reason shows this:

mysql> TRACE REASON MV SELECT     `dm_analytics`.`order_list`.`order_id`,
    ->     sum(`dm_analytics`.`goods`.`price`) AS `total`
    -> FROM
    ->     `dm_analytics`.`order_list`
    -> INNER JOIN `dm_analytics`.`goods` ON
    ->     `dm_analytics`.`goods`.`item_id1` = `dm_analytics`.`order_list`.`item_id2`
    -> GROUP BY
    ->     `dm_analytics`.`order_list`.`order_id`;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     MV rewrite fail for order_mv: stale partitions MvUpdateInfo{refreshType=FULL, mvToRefreshPartitionNames=[], basePartToMvPartNames={}, mvPartToBasePartNames={}}  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,05 sec)

What could be the problem?

Sabotage555 · December 25, 2024, 12:04pm

Something from fe.warn.log:

2024-12-25 10:56:50.461Z WARN (starrocks-mysql-nio-pool-104|55070) [StmtExecutor.execute():752] execute Exception, sql refresh MATERIALIZED VIEW order_mv WITH SYNC MODE
 org.apache.thrift.transport.TTransportException: java.net.SocketTimeoutException: Read timed out
	at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:172)
	at org.apache.thrift.transport.TTransport.readAll(TTransport.java:100)
	at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:519)
	at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:387)
	at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:271)
	at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:79)
	at com.starrocks.thrift.FrontendService$Client.recv_forward(FrontendService.java:847)
	at com.starrocks.thrift.FrontendService$Client.forward(FrontendService.java:834)
	at com.starrocks.qe.LeaderOpExecutor.lambda$forward$0(LeaderOpExecutor.java:176)
	at com.starrocks.rpc.ThriftRPCRequestExecutor.call(ThriftRPCRequestExecutor.java:67)
	at com.starrocks.rpc.ThriftRPCRequestExecutor.call(ThriftRPCRequestExecutor.java:48)
	at com.starrocks.qe.LeaderOpExecutor.forward(LeaderOpExecutor.java:172)
	at com.starrocks.qe.LeaderOpExecutor.execute(LeaderOpExecutor.java:106)
	at com.starrocks.qe.StmtExecutor.forwardToLeader(StmtExecutor.java:910)
	at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:588)
	at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:353)
	at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:548)
	at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:882)
	at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.net.SocketTimeoutException: Read timed out
	at java.base/java.net.SocketInputStream.socketRead0(Native Method)
	at java.base/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
	at java.base/java.net.SocketInputStream.read(SocketInputStream.java:168)
	at java.base/java.net.SocketInputStream.read(SocketInputStream.java:140)
	at java.base/java.io.BufferedInputStream.fill(BufferedInputStream.java:252)
	at java.base/java.io.BufferedInputStream.read1(BufferedInputStream.java:292)
	at java.base/java.io.BufferedInputStream.read(BufferedInputStream.java:351)
	at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:170)
	... 21 more
2024-12-25 11:25:47.306Z WARN (replayer|91) [TaskManager.replayUpdateTaskRun():748] could not find query_id:4e26f0a0-c2a2-11ef-9100-eeeeeeeeeeee, taskId:55781, when replay update pendingTaskRun
 2024-12-25 11:26:55.098Z ERROR (nioEventLoopGroup-6-14|2978) [BaseAction$1.operationComplete():237] something wrong. 
 io.netty.channel.StacklessClosedChannelException
	at io.netty.channel.AbstractChannel.close(ChannelPromise)(Unknown Source)
2024-12-25 11:29:43.307Z WARN (replayer|91) [TaskManager.replayUpdateTaskRun():748] could not find query_id:b824b3eb-c2a5-11ef-9100-eeeeeeeeeeee, taskId:55829, when replay update pendingTaskRun
 2024-12-25 11:49:49.306Z WARN (replayer|91) [TaskManager.replayUpdateTaskRun():748] could not find query_id:38c04f2c-c2ae-11ef-9100-eeeeeeeeeeee, taskId:53590, when replay update pendingTaskRun
 2024-12-25 11:51:37.051Z WARN (Connect-Scheduler-Check-Timer-0|13) [ConnectContext.checkTimeout():931] kill query timeout, remote: 172.22.106.231:59346, query timeout: 300

yan_zhang · December 31, 2024, 9:13pm

@Sabotage555 hello

what’s kind of your base table order_list and goods ? external catalog or native table? (I assume they are native tables, right?)

And it seems it took a lot time to fill MV table because the MV SQL involves join which maybe took a lot time to execute(5 min is timeout value)

How lage are those tables? And I assume they are probably very large right?

Sabotage555 · January 9, 2025, 12:11pm

Hello, thanks for the reply.
It’s definitely not a big data set. All this is a complete reproduction of a simple example from the documentation:

CREATE TABLE goods(
    item_id1          INT,
    item_name         STRING,
    price             FLOAT
) DISTRIBUTED BY HASH(item_id1);

INSERT INTO goods
VALUES
    (1001,"apple",6.5),
    (1002,"pear",8.0),
    (1003,"potato",2.2);

CREATE TABLE order_list(
    order_id          INT,
    client_id         INT,
    item_id2          INT,
    order_date        DATE
) DISTRIBUTED BY HASH(order_id);

INSERT INTO order_list
VALUES
    (10001,101,1001,"2022-03-13"),
    (10001,101,1002,"2022-03-13"),
    (10002,103,1002,"2022-03-13"),
    (10002,103,1003,"2022-03-14"),
    (10003,102,1003,"2022-03-14"),
    (10003,102,1001,"2022-03-14");

Topic		Replies	Views
Starrocks does not support map in asynchronous mv? Data Loading Tools & Integrations	3	134	January 31, 2024
DBeaver now support StarRocks! Query and Materialized Views	0	176	March 13, 2024
StarRocks 3.2.0 Release Notes Release Notes	0	268	January 25, 2024
In shared-data mode, are materialized views created on s3 or in local storage? Working with Data	1	166	January 5, 2024
Technical Feature Advantage: Materialized View Presentations, Articles and Webinars technical-advantage	0	151	January 27, 2024

Updating asynchronous materialized views does not work

Related topics