< Rocky> we have a table with many load tasks stuck in PREPA StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> we have a table with many load task...

Roman Martynov

05/10/2026, 1:59 PM

@Rocky we have a table with many load tasks stuck in PREPARED status. This command:

Copy code

SHOW PROC '/transactions/<db_name>/running';

shows that all of them have TransactionStatus=COMMITTED (CommitTime is at max 2 seconds after PrepareTime) and some of them published, having this type of error:

Copy code

Fail to publish partition 11346994 error Fail to publish version for tablets:[[11346998, 11347003, 11346997, 11346999, 11347002, 11347007]], error msg: txn log list does not contain txn log of tablet 11347007, host: <host>

This command:

Copy code

SELECT * FROM information_schema.loads WHERE state != 'FINISHED' order by CREATE_TIME desc;

shows STATE=PREPARED What is the most effective way to handle such issues? I believe there should be a settings parameter, that can clean such staled tasks quit often (in our case I see similar problem in different databases, but in all cases tasks created less than 48 hours ago). Starrocks version 4.0.10-5b36164, shared data cluster with multiple compute nodes

Rocky

05/10/2026, 2:03 PM

Based on the symptoms you've described for your shared-data (lake) cluster, here is the diagnosis and recommended actions. 1. Diagnosis of the Issue The error

Fail to publish version for tablets:..., error msg: txn log list does not contain txn log of tablet ...

indicates that the Front End (FE) has already COMMITTED the transaction (metadata recorded), but the Compute Node (CN) is failing to PUBLISH the version because it cannot find the required transaction logs in the shared object storage (S3/HDFS/etc.). * Load State = PREPARED: In StarRocks' internal state machine (especially for Stream Load/Broker Load), a load remains in

PREPARED

state if the transaction is

COMMITTED

but the version publish hasn't finished. * Why are logs missing? The most common reason in shared-data clusters is that the AutoVacuum mechanism deleted the transaction logs before the publish phase could complete. This happens if the "grace period" for keeping historical files is too short. 2. Recommended Settings & Cleanup Step A: Prevent Future Occurrences (Critical) The most effective way to prevent this is to increase the

lake_autovacuum_grace_period_minutes

. This parameter defines how long historical versions and transaction logs are retained in object storage. * Default: 30 minutes (older versions had 5 minutes). * Recommendation: Set this to at least 60 minutes (or higher if you have very long-running queries or high load latency).

Copy code

sql
ADMIN SET FRONTEND CONFIG ("lake_autovacuum_grace_period_minutes" = "60");

Step B: Handle Stale Tasks in the Load List You mentioned a desire for a parameter to clean these "stale" tasks. * label_keep_max_second
: This FE configuration determines how long StarRocks keeps the metadata for finished/failed load tasks. The default is 259200 seconds (3 days). If you see tasks from 48 hours ago, they are still within this window. You can decrease this to purge them from the

information_schema.loads

view more quickly.

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("label_keep_max_second" = "86400"); -- Keep for 24 hours

* transaction_clean_interval_second
: This is the interval for the background thread that cleans up finished/aborted transactions (default 30s). Step C: Resolving the Stuck
COMMITTED
Transactions Because these transactions are already

COMMITTED

in the FE's BDBJE (metadata store), they are persistent. The FE will continue to retry the publish phase indefinitely. 1. Check for Blocking: Since transactions must be published in version order for a partition, one stuck transaction can block all subsequent loads to that same partition. 2. Drastic Measure (If data is lost): If the transaction logs are truly missing from object storage (as the error suggests), that specific data cannot be published. If it's blocking the partition, you may need to: * Restart the Leader FE: This sometimes clears the in-memory publish queue and allows the FE to re-evaluate the state. * For Compaction: If the stuck task is a compaction (check

SHOW PROC '/compactions'

), you can use

CANCEL COMPACTION WHERE TXN_ID = <id>;

. * For Loads: If a load is stuck in

COMMITTED

and the logs are gone, there is no official "Skip Version" command. If the partition is permanently stuck, you might need to

ALTER TABLE ... DROP PARTITION

and reload the data for that partition (ensure you have a backup). Summary Checklist 1. Increase Grace Period:

ADMIN SET FRONTEND CONFIG ("lake_autovacuum_grace_period_minutes" = "60");

2. Shorten Metadata Retention:

ADMIN SET FRONTEND CONFIG ("label_keep_max_second" = "86400");

3. Check Partition Health: Run

SHOW PARTITIONS FROM <table_name>

and compare

VisibleVersion

with

NextVersion

. If they are far apart, the stuck load is blocking the partition.

Rocky

05/10/2026, 2:03 PM

4. Version 4.0 Note: Since you are on version

4.0.10

, ensure your CN nodes are on the exact same version as the FE. Version mismatches in the transaction protocol can lead to "missing log" errors during the handshake. Rocky's Tip: If you see this across many databases simultaneously, check your object storage (S3/HDFS) performance. If the CN times out while trying to list or read the

meta/

log/

directories, it can trigger this error. References • faq/shared_data_faq.md • administration/management/compaction.md • administration/management/FE_configuration.md • sql-reference/System_variable.md • loading/SQL_transaction.md • release_notes/release-3.3.md • administration/management/BE_configuration.md

Roman Martynov

05/11/2026, 9:34 AM

@Rocky How to clean commited stuck transactions without waiting for label_keep_max_second period?

Rocky

05/11/2026, 9:35 AM

In your StarRocks shared-data (lake) cluster, the situation where transactions are stuck in

COMMITTED

status and load tasks are stuck in

PREPARED

state is a known issue typically caused by the AutoVacuum mechanism deleting transaction logs before the Publish phase completes. Because the transaction is already

COMMITTED

, the Front End (FE) considers it a finalized part of the data history and will retry the publish phase indefinitely. However, if the transaction logs (txn logs) are missing from object storage (S3/HDFS), it can never succeed. Here is the most effective way to handle this and clean up the tasks: 1. Root Cause Prevention (Critical) To prevent this from happening again, you must increase the retention period of historical files in the shared storage. * Parameter:

lake_autovacuum_grace_period_minutes

* Action: Increase this from the default (which might be as low as 5 or 30 minutes) to at least 60 minutes (or longer if your loads are frequent or have high latency).

Copy code

sql
ADMIN SET FRONTEND CONFIG ("lake_autovacuum_grace_period_minutes" = "60");

2. How to "Clean" the Stuck Tasks Currently, there is no single "Delete" or "Purge" command for transactions that are stuck in the

COMMITTED

state. Because they are not in a terminal state (

FINISHED

CANCELLED

), they are not affected by the

label_keep_max_second

parameter. To resolve these stuck tasks, use the following methods: Method A: Restart the Leader FE (Recommended First Step) In many cases, the FE's in-memory publish queue becomes congested or stuck on a specific retry logic. Restarting the Leader FE node forces the system to reload the transaction states from the EditLog. * This often clears "ghost" tasks or allows the FE to re-evaluate the status. * Note: While the transaction remains in the metadata, it may stop actively retrying and eventually reach a state where it can be cleaned. Method B: Resolve the Version Block (If the table is stuck) Since version publishing is linear per partition, one stuck

COMMITTED

transaction for

Partition 11346994

will block all subsequent loads to that same partition. * Check the partition status:

Copy code

sql
    SHOW PARTITIONS FROM <table_name> WHERE PartitionId = 11346994;

* Compare

VisibleVersion

and

NextVersion

. If

NextVersion

is much higher than

VisibleVersion

, the partition is stuck. * Resolution: If the transaction logs are truly missing (confirmed by your error msg), that specific data is lost. The only way to "unstick" the partition is to: 1. Drop the affected partition:

ALTER TABLE <table_name> DROP PARTITION <name>;

2. Add the partition back and reload the data. _ _Note: This will remove the metadata associated with the stuck transaction, effectively "cleaning" it from the system.* Method C: Speed Up Metadata Purging (For Finished/Cancelled Tasks) If you manage to get the tasks into a

CANCELLED

FINISHED

state (e.g., after an FE restart or partition drop), you can force them to disappear from the

information_schema.loads

view faster by temporarily reducing the retention window: 1. Reduce retention:

ADMIN SET FRONTEND CONFIG ("label_keep_max_second" = "3600");

(1 hour) 2. Wait for the background cleanup thread (runs every

label_clean_interval_second

, default 4 hours). 3. Restore retention:

ADMIN SET FRONTEND CONFIG ("label_keep_max_second" = "259200");

(3 days) Summary of Best Practices * Immediate Action: Restart the Leader FE. * Stability fix: Set

lake_autovacuum_grace_period_minutes

or higher. * Recovery: If a specific table/partition is no longer receiving new data because of these stuck tasks, you must drop and recreate that partition. * Check Compactions: If these were compaction tasks rather than loads, you can use:

Copy code

sql
    CANCEL COMPACTION WHERE TXN_ID = <txn_id>;

Rocky

05/11/2026, 9:35 AM

(Note: This only works if they haven't reached
COMMITTED
status yet. For committed compactions, the FE restart is the standard procedure.) References • administration/management/FE_configuration.md • administration/management/resource_management/be_label.md • sql-reference/sql-statements/cluster-management/nodes_processes/DELETE_BACKEND_BLACKLIST.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md

Roman Martynov

05/11/2026, 9:49 AM

@Rocky If I change label_keep_max_second, will it be applied to existing labels or only to new ones? Let's say I will set label_keep_max_second to 86400 seconds - when background cleanup thread will run, will it delete all labels with CREATE_TIME older than 1 day, or only those labels, that were created after I changed label_keep_max_second parameter?

Rocky

05/11/2026, 9:50 AM

When you change the

label_keep_max_second

parameter, it applies to both existing and new load labels. Here is how the StarRocks cleanup mechanism works and how it relates to your specific situation: 1. Application to Existing Labels The parameter is mutable, meaning you can change it dynamically without a restart. The background cleanup thread (which runs every

label_clean_interval_second

, default 4 hours) iterates through all stored load labels in the metadata. It checks the timestamp of each task against the current value of

label_keep_max_second

. * Result: If you set it to

(1 day), the next time the cleanup thread runs, it will delete all labels that are older than 1 day (based on their completion time), even if they were created before you changed the setting. 2. The Catch: State Requirements There is a critical detail for your situation:

label_keep_max_second

only deletes tasks that are in a terminal state:

FINISHED

CANCELLED

. * As long as your tasks are in the

PREPARED

state (load level) and

COMMITTED

state (transaction level), they are considered "in-progress" by the system. * Because they are not "finished," they will not be cleaned up by this parameter, no matter how old they are. 3. How to Clean Your "Stuck" Tasks Since your tasks are stuck in

COMMITTED

but failing to publish because of missing transaction logs (

txn log list does not contain txn log

), they are in a "zombie" state. StarRocks will keep retrying to publish them indefinitely because a

COMMITTED

transaction must eventually become

VISIBLE

to ensure data consistency. To remove these and "clean" your system, you should follow these steps: Step A: Restart the Leader FE This is often the most effective way to clear the in-memory publish queue. When the FE restarts, it reloads the state. While the transaction stays in the metadata, the active retry loop that causes the "PREPARED" status in the load view may be interrupted or the task may be moved to a state where it can be handled. Step B: Handle the "Missing Log" Block If the transaction logs are missing from your object storage (S3/HDFS), that transaction can never be published. In a shared-data cluster, this blocks all subsequent loads to the same partition because versions must be published in order. 1. Identify the stuck partition:

SHOW PARTITIONS FROM <table_name>;

(Look for

VisibleVersion

NextVersion

). 2. The "Hard" Fix: If the task is older than 48 hours and blocking your table, you may need to Drop and Re-add the Partition. This is the only way to completely purge the metadata of a

COMMITTED

transaction that has lost its data logs.

Copy code

sql
    ALTER TABLE <table_name> DROP PARTITION <p_name>;
    -- Then re-add and reload the data

Step C: Prevent Future "Stuck" Tasks The error occurs because the AutoVacuum cleaned up the logs too early. You should increase the grace period so the logs stay available long enough for the FE to finish the publish phase:

Copy code

sql
-- Set this to at least 60 or 120 minutes
ADMIN SET FRONTEND CONFIG ("lake_autovacuum_grace_period_minutes" = "60");

Summary for your Settings: * To clean finished metadata faster: Set

label_keep_max_second = 86400

. It will affect existing finished tasks. * To address the current stuck tasks:

label_keep_max_second

will not help; you must either restart the Leader FE or drop the affected partitions if they are permanently blocked by the partition. References • faq/shared_data_faq.md • knowledge/admin_and_operation/shared_data_garbage_cleanup.md • knowledge/admin_and_operation/shared_data_compaction.md

Roman Martynov

05/11/2026, 9:54 AM

@Rocky how to find our when background cleanup thread ran the last time (or when it will run next time)?

Rocky

05/11/2026, 9:55 AM

In StarRocks, there is no direct SQL command to see the exact timestamp of the last or next scheduled run for the label cleanup thread. This activity is managed by an internal background thread called the

LabelCleaner

. To find this information, you must check the logs or calculate it based on the configuration: 1. Checking the logs (Most Accurate) The

LabelCleaner

thread runs on the Leader FE. You can find the last time it executed by searching the

fe.log

for specific keywords. * Keyword to search:

finished to clean label

LabelCleaner

* Command example (from the Leader FE terminal):

Copy code

bash
    grep "finished to clean label" fe.log | tail -n 5

This will show you the exact timestamp when the last cleanup cycle completed and how many labels were removed. 2. Calculating the "Next Run" StarRocks uses a fixed interval for this thread. You can find the interval by running:

Copy code

sql
ADMIN SHOW FRONTEND CONFIG LIKE 'label_clean_interval_second';

* Default:

seconds (4 hours). * Next Run: Take the timestamp of the last "finished" message from your logs and add the

label_clean_interval_second

value to it. 3. Understanding why your tasks aren't cleaning Even if the thread runs, it will not clean the tasks you described in your previous message for two reasons: 1. State Requirement: The cleaner only removes tasks in

FINISHED

CANCELLED

states. Your tasks are in

PREPARED

, so the cleaner ignores them. 2. Retention Period: Even if they were finished, they must be older than

label_keep_max_second

(default 3 days). How to "Force" a Cleanup Cycle If you have thousands of finished labels and want to trigger a cleanup immediately without waiting 4 hours: 1. Shorten the interval temporarily:

Copy code

sql
    -- Note: In some versions this is not mutable and requires a restart.
    -- Check 'IsMutable' in ADMIN SHOW FRONTEND CONFIG.
    ADMIN SET FRONTEND CONFIG ("label_clean_interval_second" = "60");

2. Wait a few minutes for the thread to wake up and perform the work. 3. Check the logs to confirm it ran. 4. Restore the original value (e.g.,

). Summary for your "Stuck" Tasks Since your tasks are stuck in

PREPARED

because of the "missing txn log" error in your shared-data cluster: * The background cleaner will never remove them automatically because they are not "finished." * You must restart the Leader FE or Drop/Re-add the affected partitions to clear these specific "zombie" entries from the system views. References • administration/management/FE_configuration.md • administration/management/logs.md

Roman Martynov

05/13/2026, 5:40 AM

@Rocky I disabled port 9060 on my local Starrocks Backend node:

docker exec starrocks_be iptables -I INPUT -p tcp --dport 9060 -j DROP

Then initiated Broker load task. The task stuck on PREPARED status. Then I deleted meta data:

find /opt/starrocks/be/storage/data -type f -newermt "2026-05-13 04:25:00" -delete

Then enabled port 9060:

docker exec starrocks_be iptables -D INPUT -p tcp --dport 9060 -j DROP

Now the table is blocked for new Broker load tasks and stuck in PREPARED status tasks persist for ever and never cleaned. How can I resolve this case except dropping of truncating the table?

Rocky

05/13/2026, 5:41 AM

This is a classic "metadata-data mismatch" caused by manual intervention. Because you deleted the physical data files while the transaction was in the

PREPARED

state, the BE cannot complete the "Publish" phase (it can't find the rowsets it was supposed to commit), and the FE won't let go because it's waiting for a response from the BE. Since you want to avoid dropping or truncating the entire table, here is the sequence of steps to "unstick" the system: 1. Identify the Transaction ID The load is stuck because of an underlying transaction. You need the

TxnId

. 1. Run

SHOW LOAD FROM <db_name> WHERE Label = '<your_label>';

to confirm the status and get the

JobId

. 2. Get your Database ID:

SELECT id FROM information_schema.tables WHERE table_schema = '<db_name>' LIMIT 1;

(or use

SHOW PROC '/dbs';

). 3. Check the stuck transactions:

Copy code

sql
    -- Replace <db_id> with your actual database ID
    SHOW PROC '/transactions/<db_id>/running';

Look for a transaction that has been running since you started your test. Note the

TransactionId

. 2. Force Cancel the Load Try to cancel the load job using its label. Even if it is in

PREPARED

, this may signal the FE to move the transaction to an

ABORTED

state.

Copy code

sql
CANCEL LOAD FROM <db_name> WHERE LABEL = "<your_label>";

3. Clear the Publish Queue (Restart Leader FE) In StarRocks, once a transaction reaches

PREPARED

COMMITTED

, the Leader FE places it in a Publish Queue. If the BE cannot complete the publish (because you deleted the files), the FE will retry this task indefinitely in memory. * Action: Restart the Leader FE node. * Why: This clears the in-memory publish queue and forces the FE to re-evaluate the state of all transactions from the metadata (EditLog). Since the BE will likely report that the rowsets are missing, the FE can finally transition the task to a failed/cancelled state. 4. Drop the Affected Partition (Targeted Fix) If the table is still blocked after an FE restart, it means the version chain for a specific partition is broken. A

COMMITTED

transaction that cannot be published blocks all subsequent loads to that partition. * Action: Instead of dropping the whole table, drop only the partition that was being loaded:

Copy code

sql
    ALTER TABLE <table_name> DROP PARTITION <partition_name>;

* Effect: This removes the specific metadata associated with the stuck version. You can then add the partition back. This is much faster and less destructive than a full table drop/truncate. 5. Repair the Tablet (If Partitioning is not used) If your table does not have partitions (it's a single-partition table) and the above fails: 1. Check the health of the tablets:

SHOW TABLET FROM <table_name>;

2. If the tablets are reported as "unhealthy" or stuck in a version behind the "NextVersion", the FE may need to be told the BE state is inconsistent. 3. Since you deleted the files manually, the BE's

TabletMeta

and the FE's

Catalog

are out of sync. If Method 3 (FE restart) didn't work, dropping the partition (Method 4) is the only surgical way to clear the metadata. Summary Recommendation 1. Try

CANCEL LOAD

first. 2. Restart the Leader FE. This is the most common fix for "stuck PREPARED" tasks where the underlying data has been tampered with. 3. If the table is still blocked, find the specific partition and DROP PARTITION. Warning: Never manually delete files inside

/storage/data

. If you need to clear space or reset, use

TRUNCATE

DROP

. Manual deletion leaves the StarRocks "EditLog" (metadata) pointing to non-existent files, which is what caused this deadlock's the version publishing process. References • administration/management/FE_configuration.md • faq/shared_data_faq.md

Rocky

05/13/2026, 5:41 AM

• administration/management/compaction.md

2 Views

Open in Slack

Previous Next