http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> We have a materialized view that ha...
# questions-and-troubleshooting
t
@Rocky We have a materialized view that has stopped refreshing on it's own it's configured with 
REFRESH ASYNC
r
If your asynchronous materialized view (MV) has stopped refreshing, it is usually due to the MV becoming inactive or a refresh task failing due to resource constraints or schema changes. Follow these steps to diagnose and resolve the issue: 1. Check the MV Status and Activation The most common reason for a stop in refreshes is that the MV has been set to an inactive state (e.g., due to a schema change in the base table or multiple consecutive failures). Run the following command:
Copy code
sql
SHOW MATERIALIZED VIEWS LIKE 'your_mv_name'\G
Look for these fields: * 
is_active
: If this is 
false
, the MV will not refresh. * 
inactive_reason
: This will explain why the MV became inactive (e.g., "base table schema changed"). * 
last_refresh_state
: Check if it says 
FAILED
or 
PENDING
. * 
last_refresh_error_message
: This provides the specific error if the last attempt failed. To fix an inactive MV: If it is inactive, you must manually reactivate it:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
2. Inspect Refresh Task History To see exactly what happened during previous refresh attempts, query the `information_schema`:
Copy code
sql
SELECT 
    TASK_NAME, 
    CREATE_TIME, 
    FINISH_TIME, 
    STATE, 
    ERROR_MESSAGE 
FROM information_schema.task_runs 
WHERE TASK_NAME = (SELECT task_name FROM information_schema.tasks WHERE definition LIKE '%your_mv_name%')
ORDER BY CREATE_TIME DESC 
LIMIT 10;
* 
PENDING
: The task is waiting for resources. If tasks stay pending, check cluster resource usage or the 
max_maintance_task_concurrency
configuration. * 
FAILED
: Check the 
ERROR_MESSAGE
for issues like memory limits or timeouts. 3. Common Failure Causes * Timeouts: Large refreshes may exceed the default timeout (1 hour in v3.2+, 5 minutes in older versions). You can increase it:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET ('session.insert_timeout' = '3600');
* Memory Issues: If the refresh fails due to "Memory limit exceeded," consider enabling Spill to Disk:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET ('session.enable_spill' = 'true');
* Schema Mismatch: If you performed an 
ALTER TABLE
on a base table, the MV might stop refreshing until it is reactivated or recreated if the change is incompatible. 4. Manually Trigger a Refresh To test if the MV can still refresh successfully, trigger it manually:
Copy code
sql
REFRESH MATERIALIZED VIEW your_mv_name;
Then immediately check 
SHOW PROC '/current_queries';
to see if the refresh job is running and if it encounters errors. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md
t
@Rocky this mv is active manual refresh doesn't take very long
r
If your materialized view (MV) is 
ACTIVE
and manual refreshes work quickly, but it is no longer refreshing automatically, the issue is likely related to how the refresh is triggered or a task scheduling problem. 1. Identify the Trigger Mechanism In StarRocks, 
REFRESH ASYNC
behaves differently depending on whether a schedule was defined: * Triggered by Base Table Changes: If you used 
REFRESH ASYNC
without an 
EVERY
clause, StarRocks triggers a refresh only when it detects a version change in the base tables. * External Tables: If your base tables are in an external catalog (e.g., Hive, Iceberg, JDBC), StarRocks cannot automatically detect changes in the external data. For external tables, you must use a schedule (e.g., 
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
) or manual triggers. * Internal Tables: Ensure data is actually being loaded/updated in the base tables. If the base table version hasn't changed, the MV won't refresh. * Scheduled Refresh: If you used 
REFRESH ASYNC EVERY (INTERVAL ...)
, check if the schedule is still intact. Check the definition and task status:
Copy code
sql
SELECT TASK_NAME, SCHEDULE, DEFINITION 
FROM information_schema.tasks 
WHERE DEFINITION LIKE '%your_mv_name%';
* If 
SCHEDULE
is 
MANUAL
and you didn't define a schedule, it will only trigger on base table changes (for internal tables). * If 
SCHEDULE
shows a frequency, check why the tasks aren't firing. 2. Check for "Pending" or "Failing" Tasks Even if the MV is active, the background tasks might be stuck in a queue or failing before they reach the execution stage.
Copy code
sql
SELECT TASK_NAME, CREATE_TIME, STATE, ERROR_MESSAGE 
FROM information_schema.task_runs 
WHERE TASK_NAME = (SELECT TASK_NAME FROM information_schema.tasks WHERE DEFINITION LIKE '%your_mv_name%')
ORDER BY CREATE_TIME DESC 
LIMIT 5;
* PENDING: If tasks are stuck here, you may have reached the concurrency limit. Check the FE config 
max_maintance_task_concurrency
(default is 3). * FAILED: The 
ERROR_MESSAGE
will reveal why the automatic attempt failed even if a manual one works (sometimes manual sessions have different resource limits). 3. Verify FE Configuration Check if the global task scheduler or MV refresh is disabled in your FE configuration: * `enable_materialized_view_rewrite_refresh`: This should be 
true
. * Check if any 
excluded_trigger_tables
properties were added to the MV which might be preventing specific base tables from triggering the update. 4. Remediation If the MV is supposed to trigger on base table changes but isn't, try "resetting" the task by toggling the activation:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name INACTIVE;
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
If you are using external tables and want it to refresh automatically, you must switch to a scheduled refresh:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name REFRESH ASYNC EVERY (INTERVAL 1 MINUTE); -- Adjust interval as needed
Note: If the manual refresh is fast, but the automatic one never starts, look at the FE leader's 
fe.log
for any "Task" or "MaterializedView" related errors. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/information_schema/materialized_views.md
t
@Rocky base table is internal and has updated recently, mv is a month out of date, when looking at information_schema.tasks the last EVENT_TRIGGERED create_time is 4 months ago. There are no task runs, probably because this hasn't run in so long
r
The behavior you're seeing—where an 
ACTIVE
materialized view (MV) fails to trigger automatically despite base table updates, but manual refreshes work—usually points to a breakdown in the Event Trigger mechanism or a version metadata mismatch. 1. Distinguishing 
tasks
from 
task_runs
 In StarRocks: * 
information_schema.tasks
: This table contains the definitions of tasks. The 
CREATE_TIME
here is simply when the MV (and its underlying task) was first created. It is normal for this to be months old if you haven't recreated the MV. * 
information_schema.task_runs
: This table contains the history of executions. If there are no recent entries here, it confirms the scheduler is not even attempting to start a refresh. 2. Check the Version Metadata StarRocks triggers an 
ASYNC
refresh by comparing the data version of the base table with the version recorded in the MV's metadata. If the MV's metadata "thinks" it is already up-to-date (or ahead), it won't trigger. Run the following and check the 
base_table_refresh_info
or 
last_refresh_base_refresh_partitions
(depending on your version):
Copy code
sql
SHOW MATERIALIZED VIEWS LIKE 'your_mv_name'\G
Look for: * 
base_table_refresh_info
: This JSON should show the versions of the base tables the MV is currently tracking. * Inconsistency: If you recently performed a metadata-heavy operation (like a cluster restore, table rename, or heavy schema change), the versioning logic can occasionally get out of sync. 3. Check for Excluded Trigger Tables It is possible to configure an MV to ignore changes from specific tables. Check if the property 
excluded_trigger_tables
was accidentally set:
Copy code
sql
SHOW CREATE MATERIALIZED VIEW your_mv_name;
If you see your base table in an 
excluded_trigger_tables
list, it will never trigger a refresh automatically. 4. Restart the Task Trigger Even if the MV is 
ACTIVE
, the internal FE (Front End) listener for that specific task might be "stuck." Toggling the state forces the FE leader to reload the task definition and re-evaluate the base table versions:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name INACTIVE;
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
_Wait a few minutes after this to see if a new record appears in 
information_schema.task_runs
._ 5. Verify FE Configuration Check these global settings to ensure the background scheduler is fully operational: * `enable_materialized_view_rewrite_refresh`: Must be 
true
. * `max_maintance_task_concurrency`: If this is very low (default is 3) and other MVs are constantly running/pending, this MV might never get a slot. However, you would usually see it in 
PENDING
state in 
task_runs
if this were the case. 6. Inspect the FE Leader Logs If none of the above works, check the 
fe.log
on your FE Leader node. Search for the MV's name or its Task ID. You are looking for messages like: * 
Task scheduler skip refresh...
* 
Materialized view is already up to date.
* Any Java exceptions related to 
TaskRunManager
. Recommendation: If the MV is a month out of date and manual refreshes work, the fastest way to "clean" the metadata state is to 
DROP
and 
CREATE
the MV again. This resets the version tracking completely. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
t
@Rocky last_refresh_base_refresh_partitions is completely empty, last_refresh_start_time is NULL.
r
It sounds like the Materialized View (MV) is in a "stuck" state where the metadata indicates it has never successfully completed a refresh, which explains why 
last_refresh_start_time
is 
NULL
and 
last_refresh_base_refresh_partitions
is empty. When you run 
REFRESH MATERIALIZED VIEW
without any options, it defaults to asynchronous mode (it returns "Query OK" immediately after submitting the task). If the task then fails or stays pending in the background, you might assume it was "fast" and successful when it actually didn't do anything. 1. Force a Synchronous Refresh To see what is actually happening, run the refresh in synchronous mode. This will keep the session open until the refresh finishes or returns a specific error:
Copy code
sql
REFRESH MATERIALIZED VIEW your_mv_name WITH SYNC MODE;
* If this fails: It will give you the exact error (e.g., permission, memory, or a problem with the underlying SQL). * If this succeeds: Check 
SHOW MATERIALIZED VIEWS
again. The 
last_refresh_start_time
should no longer be 
NULL
. 2. Verify why the Task is not being Created If 
information_schema.task_runs
is truly empty, it means the scheduler isn't even attempting to start a job. This usually happens for one of two reasons: * Concurrency Limits: The FE configuration 
max_maintance_task_concurrency
(default is 3) might be fully occupied by other MVs. Even if this MV is fast, it won't start if the queue is full of other long-running tasks. * Metadata Mismatch: If the FE leader was changed or if there was a major version upgrade, the background scheduler's "job" for this MV might have failed to load. 3. Check for Pending Tasks Check if the task exists but is stuck in a 
PENDING
state:
Copy code
sql
SELECT * FROM information_schema.task_runs 
WHERE TASK_NAME = (SELECT TASK_NAME FROM information_schema.tasks WHERE DEFINITION LIKE '%your_mv_name%')
AND STATE = 'PENDING';
If you see many rows here, your refresh queue is backed up. 4. The "Reset" Strategy Since your MV is a month out of date and has 
NULL
metadata, the internal state for this MV's trigger is likely corrupted or out of sync. Option A: Re-activate (Low Impact) This forces the FE to re-register the task in the scheduler:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name INACTIVE;
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
Option B: Re-create (High Impact, but most reliable) If 
ACTIVE/INACTIVE
doesn't work, the metadata is likely stuck. Dropping and recreating the MV will generate a brand new Task ID and clear any legacy metadata:
Copy code
sql
DROP MATERIALIZED VIEW your_mv_name;
CREATE MATERIALIZED VIEW your_mv_name ...;
5. Check FE Leader Logs If you want to find the root cause without recreating, you must look at the 
fe.log
on the FE Leader node. Search for the MV name. You are looking for: * 
TaskRunManager
errors. * 
analyze task generate plan error
. * 
Materialized view refresh task ignored
. Summary: Run the refresh 
WITH SYNC MODE
. If that works but automatic refreshes still don't happen, the issue is with the background scheduler. In that case, re-creating the MV is the most common fix. References • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • data_source/catalog/iceberg/iceberg_meta_table.md • sql-reference/information_schema/partitions_meta.md
t
@Rocky There are no related task runs, however there are 671 task_run rows and most of them are in state "SKIPPED"
r
The "SKIPPED" state in 
task_runs
is a normal status for scheduled materialized views. It means the scheduler woke up to perform a refresh (based on your 
EVERY
interval), checked the base tables, and determined that no new data versions were detected, so it skipped the execution to save resources. However, if your specific MV is a month out of date and has no related task runs, it means the trigger itself is not firing. Here is how to diagnose why this specific MV is being ignored: 1. Identify the Exact Task Name Don't search 
task_runs
by the MV name; search by the internal 
TASK_NAME
associated with it. Run this to get the exact ID:
Copy code
sql
SELECT TASK_NAME, SCHEDULE, PROPERTIES 
FROM information_schema.tasks 
WHERE DEFINITION LIKE '%your_mv_name%';
* Verify the Task Name: Use this specific 
TASK_NAME
(which usually looks like 
mv-12345
) to search 
task_runs
. * Check Schedule: If the 
SCHEDULE
is 
EVENT_TRIGGERED
, it only creates a task run when the FE detects a version change in an internal base table. 2. Why "Manual" might be misleading You mentioned manual refresh "doesn't take very long." By default, 
REFRESH MATERIALIZED VIEW
is asynchronous. It simply returns 
Query OK
to tell you the task was submitted. * If the background scheduler is stuck, that manual submission might just be sitting in a queue or failing silently. * To see the real result, you must run it in sync mode:
Copy code
sql
    REFRESH MATERIALIZED VIEW your_mv_name WITH SYNC MODE;
If this finishes quickly and 
SHOW MATERIALIZED VIEWS
still shows 
NULL
for the last refresh time, then the MV definition itself might be logically returning zero rows or "skipping" due to a partition mismatch. 3. The "NULL" Metadata Problem The fact that 
last_refresh_start_time
is 
NULL
and 
last_refresh_base_refresh_partitions
is empty is a major red flag. It indicates the MV has never successfully completed a refresh. * Trigger Failure: If the first-ever refresh fails, the MV often gets stuck in a state where the event trigger won't fire again because it doesn't have a "base version" to compare against. * Partition Mismatch: If the MV is partitioned but the base table is not (or vice versa), or if the partition TTL/range doesn't overlap with any data, the refresh might "succeed" while doing nothing. 4. Recommendation to Fix Since you have 671 other task runs (mostly 
SKIPPED
), your scheduler is clearly running, but it's ignoring this specific MV. To fix the metadata "stall": 1. Force a Sync Refresh: Run 
REFRESH MATERIALIZED VIEW your_mv_name WITH SYNC MODE;
. 2. Check for Error Messages: If it fails, the error will appear immediately in your SQL client. 3. Re-activate: If sync refresh works but automatic ones still don't start, toggle the state to force the FE to reload the event listener:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name INACTIVE;
    ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
4. Check FE Leader Logs: If it still won't trigger, search the 
fe.log
on your leader node for the string 
mv-XXXXX
(the Task Name you found in step 1). This will tell you if the FE is encountering an exception when trying to calculate the refresh plan. Summary: Your MV likely has a "broken" first-refresh state. Use 
WITH SYNC MODE
to force it through once; this usually populates the metadata and allows the automatic 
EVENT_TRIGGERED
logic to take over. References • using_starrocks/async_mv/materialized_view_task_run_details.md • sql-reference/information_schema/task_runs.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References
t
@Rocky flipping from inactive to active got it to update again
r
The reason flipping the state from 
INACTIVE
to 
ACTIVE
worked is that it forces the StarRocks Front End (FE) to re-register the task and rebuild its metadata mapping. Here is what likely happened: 1. Stale Metadata/Trigger: When an MV is 
EVENT_TRIGGERED
, the FE monitors the "version" of the base tables. If a base table undergoes a schema change (even a subtle one), a rename, or if the metadata becomes inconsistent after an upgrade or leader change, the internal "listener" for that MV can stall. 2. State Reset: Running 
ALTER MATERIALIZED VIEW ... ACTIVE
(even if it already appeared active) tells StarRocks to: * Re-validate the SQL definition against the current base tables. * Re-calculate the data versions of the base tables. * Re-submit the task definition to the internal background scheduler. 3. Clearing the "NULL" State: Since your 
last_refresh_start_time
was 
NULL
, the MV was essentially in an uninitialized state. Toggling it active forced the first-ever valid task run to be generated. How to prevent this in the future: * Monitor for Schema Changes: If you perform 
ALTER TABLE
or 
REPLACE
on base tables, the dependent MVs are often automatically set to 
INACTIVE
. * Automatic Re-activation: If you are on StarRocks v3.1.6 or higher, ensure the FE config 
enable_mv_automatic_active_check
is set to 
true
. This background thread periodically checks for inactive MVs and tries to repair them. * Health Check Query: You can run this query periodically to find MVs that might be stuck:
Copy code
sql
    SELECT database_name, mv_name, is_active, last_refresh_error_message
    FROM information_schema.materialized_views
    WHERE is_active = 'false';
If the MV falls out of sync again without a schema change, it may indicate a bug in the version tracking for your specific table type (e.g., Iceberg, Hudi, or certain internal table configurations). In that case, checking the FE leader logs for "TaskRunManager" errors would be the next step. References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
t
@Rocky I have a routine load that is taking a while to pull new data from kafka
r
When a Routine Load is slow or falling behind (lagging), it is usually because the rate at which data is being consumed is lower than the rate at which it is being produced in Kafka. Here is how to diagnose and fix the bottleneck. 1. Check for Consumption Lag Run the following command to see if the offsets are falling behind:
Copy code
sql
SHOW ROUTINE LOAD FOR your_job_name\G
Look at these specific fields in the output: * Progress: The last offset StarRocks has committed. * LatestSourcePosition: The current maximum offset available in Kafka. * Statistic: Check 
loadRowsRate
and 
receivedBytesRate
. If these are significantly lower than your Kafka production rate, you have a bottleneck. 2. Increase Task Concurrency A single Routine Load job is split into multiple parallel tasks. Each task can consume from one or more Kafka partitions. The number of parallel tasks is limited by: 
min(alive_be_number, kafka_partition_number, desired_concurrent_number, max_routine_load_task_concurrent_num)
* 
desired_concurrent_number
: (Job property) Default is 
3
. If your Kafka topic has 10 partitions and you have 5 BE nodes, increase this to 
5
or 
10
. * 
max_routine_load_task_concurrent_num
: (FE config) Default is 
5
. If you need more than 5 concurrent tasks, you must change this global FE configuration. How to update concurrency for an existing job:
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
PROPERTIES ("desired_concurrent_number" = "10");
3. Tune Batch Size and Consumption Time Each task runs for a short period, consumes a batch of data, and then commits. If the batch is too small, the overhead of frequent commits slows you down. * 
max_batch_interval
: (Job property) Default is 
10
seconds. Increasing this (e.g., to 
20
or 
30
) can improve throughput by allowing larger batches per transaction, but it increases the data latency. * 
max_routine_load_batch_size
: (FE dynamic config) This limits the size of a single batch. * 
routine_load_task_consume_second
: (FE dynamic config) Default is 
15s
. This is how long a task stays open to pull data. Tip: Check your BE logs (
<http://be.INFO|be.INFO>
). Look for 
consumer group done
. * If 
left_bytes
is negative, it means the task hit the 
max_batch_size
before the time limit. You should increase 
max_routine_load_batch_size
. * If 
left_bytes
is positive, the task timed out before filling the batch. You should increase 
routine_load_task_consume_second
. 4. Check for Data Errors If the 
Statistic
field in 
SHOW ROUTINE LOAD
shows a high 
errorRows
count, the job might be spending most of its time retrying or logging errors. * Find the error URL in 
ErrorLogUrls
. * Check if 
max_filter_ratio
is causing the job to pause frequently. 5. Infrastructure Bottlenecks * BE CPU/IO: Routine Load (especially JSON parsing) is CPU intensive. Check if BE nodes are at high CPU. * Kafka Network: Ensure there is sufficient bandwidth between the StarRocks BE nodes and the Kafka brokers. Summary Checklist 1. Run 
SHOW ROUTINE LOAD
to identify the lag. 2. Increase 
desired_concurrent_number
to match the number of Kafka partitions (up to the number of BEs). 3. If still slow, increase 
max_batch_interval
to 
20
and check BE logs to see if you need to increase 
max_routine_load_batch_size
. References • faq/loading/Routine_load_faq.md • loading/RoutineLoad.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md References
t
@Rocky ReasonOfStateChanged: ErrorReason{errCode = 105, msg='The lag [7986] of partition [7] exceeds Config.routine_load_unstable_threshold_second [3600]'}
r
This error indicates that your Routine Load has fallen behind Kafka by more than one hour (3600 seconds), causing StarRocks to flag the job as 
UNSTABLE
. Specifically, StarRocks looks at the timestamp of the latest message consumed. If the current time minus that message's timestamp is greater than 
routine_load_unstable_threshold_second
, it triggers this warning. In your case, partition 7 is lagging by 7,986 seconds (~2.2 hours). How to Fix the Lag 1. Increase Concurrency If your Kafka topic has multiple partitions, ensure StarRocks is using enough parallel tasks to keep up. * Check your topic's partition count. * Increase 
desired_concurrent_number
to match the partition count (up to the number of BE nodes).
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
PROPERTIES ("desired_concurrent_number" = "10"); -- Set this to your partition count
2. Tune Batch Size and Intervals If you have a high volume of small messages, the overhead of committing every 10 seconds might be the bottleneck. * Increase 
max_batch_interval
: This allows the task to accumulate more data before committing. * Increase 
max_routine_load_batch_size
: Ensure the batch size (default ~100MB) isn't causing the task to commit too early.
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
PROPERTIES (
    "max_batch_interval" = "20",
    "max_routine_load_batch_size" = "524288000" -- 500MB
);
3. Adjust the "Unstable" Threshold If your data naturally arrives with a delay or you are intentionally processing historical data and don't want the 
UNSTABLE
warning, you can increase the global threshold in the FE:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("routine_load_unstable_threshold_second" = "86400"); -- Set to 24 hours
Why is it "UNSTABLE" and not "PAUSED"? The 
UNSTABLE
state is a warning. The job will continue to run and try to catch up. However, if the lag continues to grow or if other errors occur, it may eventually transition to 
PAUSED
. To check the current throughput and verify if you are catching up: Run 
SHOW ROUTINE LOAD FOR your_job_name\G
and compare: * `loadRowsRate`: Are you processing more rows per second than Kafka is producing? * 
LatestSourcePosition
vs `Progress`: Is the gap between the offsets narrowing? References • administration/management/FE_configuration.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/ALTER_ROUTINE_LOAD.md • loading/RoutineLoad.md References • administration/management/FE_configuration.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • administration/management/BE_configuration.md
t
@Rocky still not updating, is this the lag per partition? OffsetLag: {"0":"1","1":"1","2":"1","3":"1","4":"1","5":"1","6":"1","7":"1"}
r
An 
OffsetLag
of 1 across all partitions means that StarRocks is fully caught up with Kafka. In Kafka, the "Latest Offset" often refers to the offset of the next message to be written. If StarRocks has consumed everything up to the current end of the log, the lag will show as 1 (or 0). What this tells us: 1. The Bottleneck is not StarRocks: Since the lag is 1, StarRocks has already processed every message available in your Kafka topic. It is currently sitting idle, waiting for Kafka to provide new data. 2. The "Unstable" Error is Time-Based: The error message you saw earlier (
lag [7986] ... exceeds ... 3600
) is about Time Lag, not Offset Lag. It means the last message StarRocks received from Kafka has a timestamp from over 2 hours ago. Why you might feel it's "not updating": Scenario A: Kafka is empty If no new data has been produced to your Kafka topic in the last 2 hours, StarRocks will have nothing to load. * Action: Check your Kafka producer or run a console consumer (
kafka-console-consumer
) to verify if new messages are actually arriving in the topic partitions. Scenario B: Data is being filtered/errors StarRocks might be consuming the messages but discarding them because they don't match your column mapping or because of parsing errors. * Action: Run 
SHOW ROUTINE LOAD FOR your_job_name\G
and look at 
ErrorRows
. If this number is increasing, click the 
ErrorLogUrls
link in the output to see why the rows are being rejected. Scenario C: Downstream Materialized View (MV) If you are loading into a base table and then using an Asynchronous Materialized View to view the data: * Action: Check the base table directly: 
SELECT count(*) FROM base_table
. * If the base table is growing but your MV isn't changing, the issue is with the MV refresh trigger (likely because the "timestamp lag" makes the system think the source is too unstable to trigger a refresh). Recommendation 1. Verify Kafka: Confirm that your producer is actually sending new data to partitions 0-7. 2. Check Base Table: Check the row count of the target table directly to see if it's increasing. 3. Ignore the "Unstable" Warning: If your data naturally arrives in "bursts" with long gaps in between, you can silence that warning by increasing the threshold:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("routine_load_unstable_threshold_second" = "86400"); -- 24 hours
References • faq/loading/Routine_load_faq.md • loading/RoutineLoad.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md References • administration/management/FE_configuration.md • loading/loading_introduction/feature-support-loading-and-unloading.md • administration/management/monitoring/metrics.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • faq/loading/Routine_load_faq.md
t
@Rocky I see the latest message in kafka for a given key, but that is not reflective of the latest value in starrocks
r
If StarRocks shows an 
OffsetLag
of 1 (or 0) for all partitions, it means StarRocks has successfully consumed every message available in the Kafka topic up to the current head. If you still don't see the "latest" value for a specific key in StarRocks, it is usually due to one of the following three reasons: 1. Primary Key Versioning (Sequence Column) If your table is a Primary Key table and you specified a 
SEQUENCE COLUMN
or 
merge_condition
in your Routine Load: * The Problem: StarRocks only updates a row if the incoming message has a higher (or equal) sequence value than the one currently in the table. * The Check: Check the timestamp or version number of the "latest" message in Kafka. If it is lower than the value already stored in StarRocks for that key, StarRocks will consume the message but silently ignore the update to prevent out-of-order data from overwriting newer information. 2. Data Errors or Filtering The message might have been consumed but failed to load. * The Check: Run 
SHOW ROUTINE LOAD FOR your_job_name\G
and look at the 
ErrorRows
count. * The Action: If 
ErrorRows
is greater than 0, copy the URL from 
ErrorLogUrls
into your browser. This will show you exactly which rows failed and why (e.g., "column count mismatch", "invalid data format", or "value out of range"). * Filtering: Check if your 
CREATE ROUTINE LOAD
statement included a 
WHERE
clause that might be excluding this specific message. 3. Querying a Materialized View (MV) Are you querying the base table directly, or an Asynchronous Materialized View? * The Problem: Routine Load only updates the base table. If you are querying an Asynchronous MV, it will not show the new data until the next refresh cycle completes. * The Action: Query the base table directly: 
SELECT * FROM base_table WHERE primary_key_col = 'your_key'
. 4. Hidden Kafka Partition * The Check: Run 
SHOW ROUTINE LOAD FOR your_job_name\G
and verify that the 
KafkaPartitions
list in the output actually includes the partition where your "latest message" resides. * The Action: If you recently added partitions to Kafka, you may need to restart or recreate the Routine Load job for it to discover the new partitions (depending on your StarRocks version). Quick Debugging Command: Run this and look for errors:
Copy code
sql
SHOW ROUTINE LOAD FOR your_job_name\G
If 
ErrorRows
is increasing, the data is being rejected. If 
ErrorRows
is 0 and lag is 1, the data was likely ignored by the Primary Key upsert logic or you are looking at an MV. References • loading/Load_to_Primary_Key_tables.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md References • loading/Load_to_Primary_Key_tables.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md References • loading/RoutineLoad.md
t
@Rocky there are no error rows, the base table is not reflective of the changes
r
If your OffsetLag is 1 (meaning it's caught up) and ErrorRows is 0, StarRocks has definitely processed the messages. If the table isn't reflecting the changes, the data is being "silently" ignored or filtered. Here are the three most likely reasons and how to verify each: 1. Sequence Column Conflict (Primary Key Table) This is the most common cause for "missing" updates. If your table is a Primary Key table and uses a 
SEQUENCE COLUMN
(often a timestamp or version ID): * How it works: StarRocks will only update a row if the incoming message has a strictly higher sequence value than what is already stored. If the Kafka message has an older or identical timestamp/version, StarRocks consumes it but discards the update. * How to check: 1. Find the current value in StarRocks: 
SELECT your_sequence_col FROM table WHERE pk = 'your_key';
2. Check the value in the "latest" Kafka message. 3. If the Kafka value is $\leq$ the StarRocks value, the update is ignored by design to prevent out-of-order data corruption. 2. The 
WHERE
Clause Filter If your 
CREATE ROUTINE LOAD
statement includes a 
WHERE
clause, messages that don't meet the criteria are discarded. * How to check: Run 
SHOW ROUTINE LOAD FOR your_job_name\G
. * Look at the 
Statistic
field for 
unselectedRows
. * If 
unselectedRows
> 0, it means your 
WHERE
clause is filtering out those messages. 3. Upsert vs. Delete (
__op
column) If you are loading into a Primary Key table, StarRocks can interpret an optional 
__op
column to determine if a message is an 
upsert
(0) or a 
delete
(1). * How it works: If your Routine Load mapping accidentally maps a field to 
__op
and that field contains a 
1
, StarRocks will delete the row for that key instead of updating it. * How to check: Review your 
COLUMNS
mapping in the Routine Load job: 
SHOW CREATE ROUTINE LOAD your_job_name\G
. Ensure you aren't accidentally mapping a data column to 
__op
. 4. Partial Updates Are you trying to update only some columns? * The Problem: If you didn't enable 
partial_update
and didn't include the specific column in your 
COLUMNS (...)
list, StarRocks won't update that specific field. * How to check: Check if 
"partial_update" = "true"
is in your job properties. If it is not, StarRocks expects a full row; any missing columns in the mapping will be filled with their default values (often 
NULL
), which might make it look like the "latest" value isn't there. Recommended Debugging Step: To see exactly what StarRocks is doing with the data, try to manually load one of the "latest" messages via a 
curl
Stream Load using the same column mapping. Stream Load provides more immediate feedback in the HTTP response:
Copy code
bash
curl --location-trusted -u user:pass \
    -H "columns: col1, col2, col3..." \
    -T data_sample.json \
    http://<fe_ip>:8030/api/db/table/_stream_load
If this manual load also fails to update the row, the response will often tell you if the row was "filtered" or "pre-empted" by a newer version. References • loading/Load_to_Primary_Key_tables.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/RoutineLoad.md References • loading/Load_to_Primary_Key_tables.md • faq/loading/Routine_load_faq.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md
t
@Rocky The last 2 messages in kafka for this key
Copy code
{"topic":"<persistent://public/client/767a20a8>","topicParams":{"client_id":"767a20a8"},"timestampMillis":1793293012732,"processedTimestampMillis":1793293009519,"date":"2026-10-29","clientId":"767a20a8","connected":true,"sinceSeconds":1793293012}
{"topic":"<persistent://public/client/767a20a8>","topicParams":{"client_id":"767a20a8"},"timestampMillis":1793292996989,"processedTimestampMillis":1793293691505,"date":"2026-10-29","clientId":"767a20a8","connected":false,"sinceSeconds":1793293691}
This query 
SELECT * FROM connected_client WHERE clientId = '767a20a8' ORDER BY timestampMillis DESC LIMIT 1;
shows this result
Copy code
timestampMillis: 1793293012732
                clientId: 767a20a8
               connected: 1
            sinceSeconds: 1793293012
                    date: 2026-10-29
processedTimestampMillis: 1793293009519
                   topic: <persistent://public/client/767a20a8>
             topicParams: {"client_id": "767a20a8"}
r
The data you provided shows exactly why the update is being ignored. You are likely using a Primary Key table with a sequence column (or 
merge_condition
) set to 
timestampMillis
. The Problem: Out-of-Order Timestamps Look closely at the 
timestampMillis
values in your two Kafka messages: 1. Connect Message: 
"timestampMillis": 1793293012732
(Higher value) 2. Disconnect Message: 
"timestampMillis": 1793292996989
(Lower value) Even though the "Disconnect" message appears later in your Kafka log, its internal 
timestampMillis
is smaller than the "Connect" message (it is about 15 seconds older). In StarRocks Primary Key tables, if you use a column for ordering (either via 
merge_condition
in your Routine Load or as a sequence column in the table properties), StarRocks will ignore any incoming message that has a lower value than the one already in the table. Because 
1793292996989
(Disconnect) < 
1793293012732
(Connect), StarRocks assumes the Disconnect message is "old data" arriving late and discards the update to prevent overwriting newer information. How to Fix This If you want the table to reflect the order in which messages arrive in Kafka (or the order they were processed), you should use a column that is guaranteed to increase. Option 1: Use 
processedTimestampMillis
 Your 
processedTimestampMillis
is correctly ordered: * Connect: 
1793293009519
* Disconnect: 
1793293691505
(Higher, so it would win) Modify your Routine Load to use this column for the merge condition:
Copy code
sql
ALTER ROUTINE LOAD FOR your_job_name
PROPERTIES (
    "merge_condition" = "processedTimestampMillis"
);
Option 2: Remove the merge condition If you remove the 
merge_condition
entirely, StarRocks will simply apply updates in the order they are consumed from Kafka (by offset), regardless of the timestamps inside the JSON. To Confirm: Run this command to see if you have a 
merge_condition
or 
SEQUENCE COLUMN
defined: 1. 
SHOW CREATE ROUTINE LOAD your_job_name\G
(Look for 
merge_condition
) 2. 
SHOW CREATE TABLE connected_client\G
(Look for 
PROPERTIES ("sequence_column" = "...")
) References • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md References • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md
t
@Rocky back to the MV issue, the base table updated and the MV has never updated
r
If your base table is updating (e.g., via Routine Load) but the Materialized View (MV) is not reflecting those changes, it is usually because the refresh task is either not being triggered or is failing. Follow these steps to diagnose the issue: 1. Check if the MV is "Active" If the schema of the base table changed or if a previous refresh failed catastrophically, the MV might become 
inactive
. An inactive MV will neither refresh nor be used for query rewrite. * Check: Run 
SHOW MATERIALIZED VIEWS LIKE 'your_mv_name'\G
and look for the 
is_active
column. * Fix: If 
is_active
is 
false
, try to reactivate it:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
2. Verify the Refresh Strategy Check if the MV is actually set to refresh automatically when data changes. * Check: In the same 
SHOW MATERIALIZED VIEWS
output, look at 
refresh_type
. * `MANUAL`: It will never update unless you manually run a refresh command. * `ASYNC`: It should update automatically when base table data changes. * Fix: If it is 
MANUAL
and you want it to be automatic, alter it:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name REFRESH ASYNC;
3. Inspect the Refresh History (Error Logs) This is the most important step. StarRocks records every attempt to refresh an MV in a system task table. * Query the history:
Copy code
sql
    SELECT
        TASK_NAME,
        CREATE_TIME,
        FINISH_TIME,
        STATE,
        ERROR_MESSAGE
    FROM information_schema.task_runs
    WHERE TASK_NAME LIKE '%your_mv_name%'
    ORDER BY CREATE_TIME DESC
    LIMIT 5;
* Analysis: * If no rows appear: The refresh was never even triggered. Check if you have properties like 
excluded_trigger_tables
set. * If STATE is `FAILED`: The 
ERROR_MESSAGE
will tell you exactly why (e.g., "Memory limit exceeded", "Transaction conflict", or "Timeout"). 4. Manually Trigger a "Sync" Refresh To see the error in real-time without digging through logs, run a manual refresh in sync mode. This forces the command to wait until completion and returns the error directly to your console:
Copy code
sql
REFRESH MATERIALIZED VIEW your_mv_name WITH SYNC MODE;
5. Common "Silent" Failure Reasons * 
batch_size
/ `partition_refresh_number`: If your MV is partitioned and has many partitions to catch up on, it may only refresh a few at a time (controlled by 
partition_refresh_number
, default is 1 in older versions). It might look like it's not updating if it's slowly working through a backlog. * Primary Key Table "Final" Updates: If you are using a Primary Key table and the updates are "deletes" or "ignored updates" (due to the 
sequence_column
logic discussed earlier), the base table version may not actually increment in a way that triggers the MV. Recommendation: Start with the 
information_schema.task_runs
query. If you see a specific error message there, please share it! References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md
t
@Rocky there are no errors, it claims to be active, it just never schedules. Toggling active off and back on gets it to refresh once, but it doesn't start refreshing on it's own after that
r
This behavior—where a Materialized View (MV) is "Active" but never schedules itself unless you toggle the state—usually points to a configuration or metadata issue where the Task Manager doesn't believe a new run is necessary or isn't being triggered correctly. To find the root cause, please check the following three areas: 1. Check the Task State Every 
ASYNC
Materialized View has an underlying system task. Even if the MV is active, the task might be in a state that prevents scheduling. Run this query:
Copy code
sql
SELECT * FROM information_schema.tasks WHERE TASK_NAME LIKE 'mv-%';
* Look at 
STATE
: If it is not 
ACTIVE
, the scheduler will ignore it. * Look at 
SCHEDULE
: * If you see 
PERIODIC: ...
, it's a scheduled refresh. * If you see 
MANUAL
, it will only trigger when the base table changes. 2. Check for "Skipped" Runs If the scheduler is running but deciding not to do any work, it will show up as 
SKIPPED
in the history.
Copy code
sql
SELECT TASK_NAME, CREATE_TIME, STATE, EXTRA_MESSAGE 
FROM information_schema.task_runs 
WHERE TASK_NAME LIKE 'mv-%' 
ORDER BY CREATE_TIME DESC LIMIT 10;
* If you see 
SKIPPED
: This means StarRocks checked the base tables and concluded nothing has changed since the last refresh. * The Culprit: If you are using a Primary Key table as a base, StarRocks compares the "Version" of the table. If your data loads (like the Routine Load we discussed earlier) are being "filtered" or discarded due to sequence column logic, the table version might not advance, or the MV might not perceive the update as a "change." 3. Verify the Refresh Properties Run 
SHOW CREATE MATERIALIZED VIEW your_mv_name\G
and look for these specific properties in the 
PROPERTIES
block: * 
excluded_trigger_tables
: If your base table is listed here, the MV will never auto-refresh when that table changes. * 
auto_refresh_partitions_limit
: If this is set to a very low number (or 0), it can limit the scheduler's ability to pick up new data. * 
partition_refresh_number
: If this is set to a small number and you have a large backlog of partitions, it might look like nothing is happening because it's slowly processing one old partition at a time. 4. Check FE Configuration If none of your MVs are auto-refreshing, it might be a cluster-wide setting. Check these FE configurations:
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%mv%';
* Check `mv_refresh_scheduler_interval_seconds`: This is how often the FE looks for MVs to refresh (default is usually 30-60s). * Check `materialized_view_min_refresh_interval`: If your 
EVERY
interval is shorter than this, the scheduler might ignore it. Recommended Fix to Test: If you want to bypass the "base table change detection" (which might be failing) and force a schedule, try altering the MV to a fixed interval instead of a pure trigger:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name 
REFRESH ASYNC START('2024-01-01 00:00:00') EVERY(INTERVAL 1 MINUTE);
Wait 2-3 minutes. If it starts refreshing every minute, the issue was with the trigger detection on the base table. If it still doesn't refresh, the issue is with the Task Manager/Scheduler itself. Could you share the 
SHOW CREATE MATERIALIZED VIEW
output? I can then tell you exactly which property is likely blocking the automation. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/information_schema/tasks.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • administration/management/FE_configuration.md References
Open in Slack
PreviousNext
Powered by