< Rocky> How can I diagnose zombie transactions for PK table StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> How can I diagnose zombie transacti...

Minn Fong

05/12/2026, 7:58 PM

@Rocky How can I diagnose zombie transactions for PK tables? My stream loads are failing because there are 100 concurrent transactions. It normally never goes above 2 or 3.

Rocky

05/12/2026, 7:59 PM

To diagnose and resolve the issue of hitting the concurrent transaction limit (100) on your Primary Key (PK) tables, follow these steps: 1. Identify the Running Transactions First, you need to see exactly what those 100 transactions are and where they are stuck. * Find your Database ID:

Copy code

sql
    SHOW PROC '/transactions';

Locate your database and note the

DbId

. * List all running transactions for that database:

Copy code

sql
    -- Replace {DbId} with the ID from the previous step
    SHOW PROC '/transactions/{DbId}/running';

2. Diagnose the "Zombie" or Stuck Transactions In the output of the command above, look at these specific columns: * TransactionStatus: * PREPARE: The transaction is still writing data. * PREPARED: In a two-phase commit (2PC) scenario (like Flink), the data is written but waiting for a

COMMIT

ROLLBACK

from the client. These are likely your "zombies." * COMMITTED: The transaction is waiting to be "Published" (merged into the tablet metadata). * ErrMsg: For PK tables, check for messages like: * `Partition's compaction score is larger than 100.0, delay commit...`: This means your ingestion frequency is too high, and BEs are intentionally slowing down commits to allow compaction to catch up. * PrepareTime: Check if there are transactions that started a long time ago. 3. Common Causes for PK Tables * Lingering 2PC Transactions: If you use the Stream Load Transaction Interface (common with Flink), and the Flink job crashes or fails a checkpoint, transactions can get stuck in the

PREPARED

state. They stay there until the client resumes or they hit the

prepared_transaction_default_timeout_second

(default is 1 day). * Slow Update Apply: PK tables use an "Apply" thread pool to handle upserts. If your BEs are overloaded, the

COMMITTED -> VISIBLE

transition slows down, keeping transaction slots occupied. Check

transaction_apply_worker_count

in your BE config. * Compaction Backlog: If compaction can't keep up with the version count, StarRocks will throttle or block new commits. 4. Immediate Resolution * Increase the limit: If your cluster has resources, you can temporarily increase the limit to allow new loads while you troubleshoot:

Copy code

sql
    -- Run on FE (Admin)
    ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "1000");

* Abort Zombies (If using 2PC): If the transactions are stuck in

PREPARED

and you have the labels, you can manually call the rollback API via `curl`:

Copy code

bash
    curl --location-trusted -u <user>:<pass> \
      -H "db:<db_name>" -H "label:<label_from_show_proc>" \
      -XPOST http://<fe_ip>:<fe_http_port>/api/transaction/rollback

* Check for FE Locks: If the

SHOW PROC

command itself hangs, there might be a metadata lock (deadlock). In this case, you would need to inspect the FE

jstack

or check

SHOW PROC '/monitor'

. 5. Long-term Prevention * Batching: Ensure your Stream Loads are large enough. Loading small batches frequently is the #1 cause of transaction exhaustion and compaction issues. * Flink Connector Version: If using Flink, ensure you are on version 1.2.8+ which includes improved logic for cleaning up lingering transactions using the

sink.label-prefix

. * Persistent Index: For PK tables, ensure

enable_persistent_index

true

if your primary keys are large, as this offloads the index from memory and prevents memory-related ingestion stalls. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/information_schema/be_txns.md

Minn Fong

05/12/2026, 8:02 PM

@Rocky wait for publishing partition 7242222 version 2623. self version: 2661. table 7211473

Rocky

05/12/2026, 8:02 PM

This error message indicates that your data loading is blocked by a version gap during the Publish Phase. In StarRocks, transactions must be "published" (made visible) in strict sequential order for each partition. Your transaction (trying to become version 2661) is stuck because version 2623 has not finished publishing yet. This backlog is why your concurrent transactions have spiked from 2–3 to 100. 1. Identify the Stuck Transaction You need to find the

TransactionId

that corresponds to the "blocking" version (2623). * Find the Database ID:

Copy code

sql
    SHOW PROC '/transactions';

* Locate the blocking transaction: Check the running and recently finished transactions for that database:

Copy code

sql
    -- Replace {DbId} with your actual Database ID
    SHOW PROC '/transactions/{DbId}/running';

Look for a transaction where the

ErrMsg

mentions version 2623 or one that has been in

COMMITTED

status for a long time. If it's not in

running

, it might be in

finished

but failed to publish on some replicas. 2. Why is Version 2623 stuck? For Primary Key tables, the most common reasons for a publish delay are: * Apply Thread Pool Saturation: PK tables perform an "Apply" operation during publish to update the primary key index. If you have many small loads, the

transaction_apply_worker_count

(on BE) might be exhausted. * High Compaction Score: If the partition has too many versions, the BE will intentionally slow down or block the "Apply" phase to prevent the system from being overwhelmed. * Replica Issues: If one BE is down or struggling, the FE may wait for the publish to succeed on a quorum of replicas. Check BE health with

SHOW BACKENDS

. 3. Troubleshooting Steps Step A: Check BE Logs Search the

<http://be.INFO|be.INFO>

logs on your backends for the specific Partition ID or Transaction ID:

Copy code

bash
grep "7242222" <http://be.INFO|be.INFO> | grep "publish"

Look for errors like

timeout

apply task failed

, or

too many versions

. Step B: Monitor the "Apply" Queue Check if the Apply thread pool is the bottleneck. You can see this in the BE metrics or by checking the thread pool status:

Copy code

bash
curl http://{BE_IP}:{BE_HTTP_PORT}/metrics | grep "update_apply"

4. Immediate Mitigations * Increase the Transaction Limit (Temporary): To stop the "100 concurrent transactions" error from failing new loads immediately, you can temporarily raise the limit:

Copy code

sql
    ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "500");

* Increase Apply Threads: If your BEs have idle CPU, you can increase the concurrency for PK updates in

be.conf

(requires BE restart):

Copy code

properties
    transaction_apply_worker_count = 16  # Default is often 8 or based on CPU
    transaction_publish_version_worker_count = 16

5. Long-term Fix: Increase Batch Size The fact that your version numbers reached 2600+ and you are hitting 100 concurrent transactions suggests you are performing high-frequency, small-batch writes. * Consolidate Loads: Instead of many small Stream Loads, group your data into larger batches (e.g., every 5–10 seconds or 100MB+). * Flink Users: If using the Flink connector, increase

sink.buffer-flush.interval-ms

to reduce the number of transactions per minute. By reducing the frequency of transactions, you give the "Apply" and "Compaction" processes more time to finish each version, preventing the version gap that causes "zombie" transactions. References • administration/management/BE_configuration.md • loading/Flink-connector-starrocks.md • administration/management/FE_configuration.md • sql-reference/System_variable.md

Minn Fong

05/12/2026, 8:05 PM

@Rocky

Copy code

Looking at this transaction log data, here's what's happening:
The core issue: a publish version lag on partition 7256112 (and others)
Almost all of these transactions are stuck waiting for partition 7256112 to publish version 154, but each transaction's "self version" keeps climbing (155, 157, 159, 161... up to 252). This means:

Partition 7256112 is frozen at version 154 and hasn't advanced
Every new transaction commits successfully but then stalls at the publish phase, waiting for that partition to catch up
The backlog started around 18:19–18:20 and all stuck transactions eventually published at 20:03:09 — roughly a ~1.5 hour delay

Secondary partitions also affected:

Partition 7242222 stuck at version 2623 (self versions climbing from ~2624 to 2661)
Partition 7253437 stuck at version 955 (self versions 959, 964, 969, 974, 979)

Transactions that completed normally (no ErrMsg, PublishTime ≈ CommitTime) are interspersed throughout — those hit different partitions that weren't lagging.
What likely happened:

A replica or BE node responsible for those partitions fell behind or became temporarily unavailable around 18:19
The FE kept accepting and committing transactions, but publish was blocked waiting for the lagging replica to catch up
At 20:03:09, all stuck transactions published simultaneously — suggesting the replica recovered or a timeout/force-publish kicked in

Things to investigate:

What happened to the BE node(s) serving partitions 7256112, 7242222, and 7253437 around 18:19?
Check BE logs for compaction storms, GC pauses, or disk I/O spikes around that time
Was there a network partition or node restart? The mass-publish at exactly 20:03:09 for all transactions suggests a single recovery event
Consider whether PreparedTimeoutMs (86400000 = 24h) is too generous — it allowed this to linger for 1.5h before resolving

Rocky

05/12/2026, 8:05 PM

Your detailed analysis is spot on. This behavior is a textbook case of Sequential Version Publishing being blocked by a "straggler" replica or a bottlenecked Apply thread on a specific Backend (BE). Here is a breakdown of why this happened and how to verify the root cause: 1. The "2-Hour" Window and
retry_apply_timeout_second
The fact that your transactions were stuck for ~1.5 to 2 hours (18:19 to 20:03) and then suddenly cleared is likely tied to the BE configuration parameter retry_apply_timeout_second
. * Default: 7200 seconds (2 hours). * Behavior: For Primary Key tables, when a transaction moves from

COMMITTED

VISIBLE

, the BE must "apply" the changes to the PK index. If an apply task fails or is delayed (due to resource exhaustion or a hung thread), the BE will retry for up to 2 hours. * The "Mass-Publish": At 200309, these retries likely hit their limit, or the underlying resource (like a stuck thread or a blocked I/O) finally cleared, allowing the BE to report success to the FE. Once the "blocking" version (v154) was reported as successful to the FE, the FE was able to instantly "batch publish" all subsequent versions (155–252) that were already queued and ready. 2. Why the "Self Version" kept climbing StarRocks handles transactions in two distinct phases: 1. Commit Phase: The FE assigns a version number (e.g., 155, 156) and logs the transaction. This is fast and succeeds as long as data is written to disk. 2. Publish Phase: This must be done strictly in order per partition. If Version 154 is stuck in "Apply," Version 155 cannot become visible, even if the data for 155 is already safely on the BEs. This is why you saw the "self version" (your current txn) climbing while the partition version (the last visible one) remained at 154. 3. What to investigate in the logs To confirm what happened at 18:19 and 20:03, run these specific searches: On the BE nodes (look for the "Apply" bottleneck):

Copy code

bash
# Look for Apply task timeouts or retries
grep -E "apply.*timeout|retry.*apply" <http://be.INFO|be.INFO> | grep "7256112"

# Check if the Apply thread pool was full
grep "pool.*update_apply" <http://be.INFO|be.INFO> | grep "full"

# Look for Long GC pauses that might have frozen the process
grep "real pause time" <http://be.INFO|be.INFO> | awk '$NF > 1000'

On the FE Leader node (look for the "Publish" manager):

Copy code

bash
# See why the FE was waiting
grep "publish version timeout" fe.log | grep "7256112"

# Check for replica health reports around 20:03:09
grep "backend.*report" fe.log | grep "20:03:09"

4. Key Recommendations * Check `transaction_apply_worker_count`: If you have many PK tables or high-frequency writes, the default apply threads (usually 8) might be too low. You can increase this in

be.conf

. * Persistent Index: Ensure

enable_persistent_index = true

is set for these tables. If the PK index is in memory and you hit a memory limit, the BE may trigger emergency GC or swap, causing the 1.5-hour "hang." * Batching: Since your versions climbed from 154 to 252 in 90 minutes, you are doing roughly 1 load per minute. If these loads are small, consider batching them more (e.g., every 5 minutes) to reduce the pressure on the version management system. * Tune `retry_apply_timeout_second`: If you want the system to fail faster rather than hanging for 2 hours, you can lower this value in

be.conf

, but be aware this may cause tablets to enter an "Error" state more quickly if the issue is transient. Summary of the "Mass-Publish" The simultaneous resolution at 200309 confirms that the FE Leader finally received a successful "Publish" acknowledgment for the oldest stuck version (v154). Because the later versions (up to 252) were already processed by the BEs and just waiting for the "all clear" on the sequence, they were all marked visible in the metadata at the metadata as

VISIBLE

in a single heartbeat. References

Rocky

05/12/2026, 8:05 PM

• knowledge/usage/load/shared_data_batch_ingestion.md • loading/SQL_transaction.md • loading/Stream_Load_transaction_interface.md

Minn Fong

05/12/2026, 8:06 PM

@Rocky How do I resolve this

Rocky

05/12/2026, 8:06 PM

To resolve this and prevent future "zombie" transaction spikes, you should address both the immediate backlog and the underlying "Apply" bottleneck. 1. Immediate Fix: Clear the Backlog To allow your system to process new data while you fix the bottleneck, temporarily increase the transaction limit on the FE:

Copy code

sql
-- Run this as an admin user to prevent new loads from failing
ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "1000");

Note: This doesn't fix the lag, but it prevents the "100 concurrent transactions" error from stopping your pipeline. 2. Fix the "Apply" Bottleneck (BE Configuration) The 1.5-hour delay you saw is likely caused by the update_apply
thread pool becoming saturated. For Primary Key (PK) tables, every transaction must "apply" its changes to the index. If this pool is full, transactions sit in

COMMITTED

status and wait. In your
be.conf
on all BE nodes: * Increase Apply Threads: By default,

transaction_apply_worker_count

is set to your CPU core count. If your disk I/O can handle it, increase this:

Copy code

properties
    # Example: if you have 32 cores, try 48 or 64
    transaction_apply_worker_count = 64

* Speed up Version Publishing:

Copy code

properties
    transaction_publish_version_worker_count = 64

* Reduce the "Hang" Time (Optional): The 2-hour delay you experienced is controlled by

retry_apply_timeout_second

(default 7200s). You can lower this so the system errors out faster instead of hanging, but the real fix is increasing the worker count above. You can apply these without a restart via HTTP API:

Copy code

bash
curl -XPOST http://{BE_IP}:{BE_HTTP_PORT}/api/update_config?transaction_apply_worker_count=64

3. Table-Level Optimization Ensure your PK tables are using the Persistent Index. If the index is purely in-memory and you hit memory limits, the BE will trigger heavy GC or disk swapping, leading to the exact "hang" you described. * Check Table Properties:

Copy code

sql
    SHOW CREATE TABLE your_table_name;

Ensure

"enable_persistent_index" = "true"

is set. If it is

false

, you can change it:

Copy code

sql
    ALTER TABLE your_table_name SET ("enable_persistent_index" = "true");

4. Architectural Prevention: Batching The "Self Version" climbing from 154 to 252 during the hang indicates you are committing transactions frequently (roughly once every 45-60 seconds). * Increase Batch Size: StarRocks PK tables perform much better with fewer, larger transactions than many small ones. * Target: Aim for 1 transaction every 2–5 minutes if possible, or ensure each load is at least 100MB+. This drastically reduces the number of "Apply" tasks the BE has to manage in the queue. 5. Check for "Straggler" Replicas If this happens consistently on the same partitions, one BE node might have a hardware issue (slow disk or network). 1. Identify the tablet ID for the stuck partition:

SHOW TABLET FROM your_table_name;

. 2. Check the status of replicas:

SHOW TABLETS FROM your_table_name WHERE TabletId = <ID>;

. 3. Look for a replica where

Version

is significantly lower than the others. That BE node is your "straggler." Summary Checklist 1. FE: Raise

max_running_txn_num_per_db

to 1000. 2. BE: Increase

transaction_apply_worker_count

to 1.5x or 2x CPU cores. 3. Table: Verify

enable_persistent_index

true

. 4. App: Increase the time interval between Stream Loads to reduce the total number of versions. References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md • administration/management/BE_configuration.md

Minn Fong

05/12/2026, 8:12 PM

@Rocky I just did

Copy code

ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "200");

And it quickly cleared the backlog of transactions. Now it's back down to 2-3. Why did this happen? I didnt need to do anything else to fix it

Rocky

05/12/2026, 8:13 PM

The reason the backlog cleared is a combination of how StarRocks manages transaction limits and a likely coincidence with your system's recovery. 1. Why you saw 100+ transactions In StarRocks, a transaction is considered "running" from the moment it starts until it becomes

VISIBLE

. * The Chain Reaction: Because Partition 7256112 was stuck at version 154, every subsequent load (v155, v156, etc.) successfully wrote its data and moved to

COMMITTED

status, but stayed there waiting for v154 to finish. * The Wall: If your

max_running_txn_num_per_db

was set to 100, the moment you had 100 transactions stuck in that "waiting to publish" state, the FE began rejecting all new incoming data. This is why your concurrent transaction count hit a hard ceiling. 2. Why increasing the limit "fixed" it By running

ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "200");

, you essentially doubled the "waiting room" capacity. * Immediate effect: This allowed the FE to stop rejecting new requests. If your data pipeline (like Flink or a Python script) was retrying, those loads could finally enter the system instead of failing immediately. * The "Clearing" Effect: The backlog likely dropped back to 2-3 because the blocking version (v154) finally finished. As soon as that one "Apply" task on the Backend (BE) succeeded, the FE was able to publish the entire queue (v155 through v252) in a single batch. 3. Was it just a coincidence? Most likely, yes. As you noted in your previous analysis, these transactions were stuck for about 1.5 to 2 hours. * StarRocks has a default BE setting called

retry_apply_timeout_second

which is 7200 seconds (2 hours). * When a Primary Key "Apply" task is stuck or struggling, the BE retries it for exactly 2 hours before giving up or the task finally succeeds. * Your manual config change likely happened right as that 2-hour window was closing and the BE finally finished processing that specific partition's index. Should you do anything else? While it's quiet now, the fact that you hit 100 transactions in the first place means your loading frequency is too high for the speed at which your BEs can update the index. To prevent this from happening again: 1. Make the limit permanent: The

ADMIN SET

command is volatile and resets on FE restart. Add

max_running_txn_num_per_db = 1000

to your

fe.conf

(1000 is the default in newer StarRocks versions). 2. Batch your data more: If you are doing a Stream Load every few seconds, try to group them into 1-minute or 5-minute batches. This reduces the number of "versions" the system has to manage sequentially. 3. Check BE Resources: If this happens again, check

<http://be.INFO|be.INFO>

logs for

update_apply

thread pool saturation. You may need to increase

transaction_apply_worker_count

be.conf

. Summary: You cleared the "symptom" (the limit error) by increasing the config, and the "disease" (the stuck version) resolved itself naturally due to timing. If you don't increase your batch sizes, you will likely hit the 200 limit next time.

Minn Fong

05/12/2026, 8:17 PM

@Rocky Isnt it dangerous to have 2 hour timeout? It will block the other successfull commits from being applied. I want to decrease it to maybe 5 minutes?

Rocky

05/12/2026, 8:18 PM

It is not necessarily "dangerous" in the sense of data loss, but it is a trade-off between availability and stability. If you decrease

retry_apply_timeout_second

from 2 hours to 5 minutes, here is what will happen: 1. The Good: Faster "Self-Healing" for the Queue If a single replica on one BE node becomes extremely slow (a "straggler"), setting the timeout to 5 minutes will force that specific replica to fail quickly. * Result: The FE will stop waiting for that slow replica and will instead publish the version using the other healthy replicas (provided you have a majority/quorum). * Outcome: The "blocking" effect you saw on the partition version would resolve in 5 minutes instead of 2 hours. 2. The Bad: Increased System Stress (The "Danger") If the problem isn't a broken node, but just a transient spike (e.g., a 10-minute disk I/O stall or a heavy compaction task), a 5-minute timeout might be too aggressive. * Replica Failure: Once the 5-minute timer hits, that tablet replica is marked as ERROR/DEAD. * Automatic Repair: StarRocks will immediately try to "repair" that tablet by cloning data from a healthy replica to a new BE node or re-syncing the old one. * Network/Disk Storm: If thousands of tablets hit this 5-minute timeout simultaneously, your cluster will start a massive "Clone" operation. This consumes significant network bandwidth and disk I/O, which could potentially slow down the healthy nodes and trigger even more timeouts—creating a cascading failure. 3. Why 2 Hours is the Default The 2-hour default is designed to be extremely conservative. It assumes that a "slow" Apply is usually a temporary resource bottleneck (like high CPU or memory pressure). The system would rather wait for the node to catch up than trigger a high-overhead data migration (Clone) across the network. Recommendations Instead of dropping straight to 5 minutes, consider a tiered approach: * Middle Ground (15-30 minutes): If your business cannot tolerate a 2-hour hang, try

(30 mins) or

(15 mins). This is usually enough time for transient OS-level issues (like JVM pauses or disk flushing) to resolve without triggering a cluster-wide repair storm.

Copy code

bash
    # Update on BE
    curl -XPOST http://{BE_IP}:{BE_HTTP_PORT}/api/update_config?retry_apply_timeout_second=900

* Lower the retry interval: You can also adjust

retry_apply_interval_second

(default 30s). If you set this lower, it will check more frequently if the bottleneck has cleared. * The Real Fix: The "blocking" happened because you hit a bottleneck. To actually solve the root cause: 1. Increase
transaction_apply_worker_count
to allow more parallel index updates. 2. Enable
enable_persistent_index
to offload the PK index from memory to disk (crucial for stability). 3. Check for Disk Latency: Use

iostat

on the BE nodes during the hang. If one disk is consistently at 100% util while others are at 20%, that node is a hardware straggler and should be replaced. Summary: 5 minutes is safe if your network/disks can handle frequent background "Repair/Clone" tasks. However, 15–20 minutes is a safer "fail-fast" starting point for production environments. References • administration/management/resource_management/Replica.md • faq/operation_maintenance_faq.md

Minn Fong

05/12/2026, 8:24 PM

@Rocky But ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "200") alone solved the issue. The 2 hour limit wasn't up yet when I ran it. It just cleared the backlog of transactions. For context, my primary key table has no replication enabled. My transactions normally stay about 3 concurrent. WHat could have caused this cascading effect of blocking transactions

Rocky

05/12/2026, 8:24 PM

In a Primary Key (PK) table, especially with no replication (
replication_num = 1
), you encountered a classic Sequential Version Bottleneck. Here is exactly why increasing that FE limit cleared the logjam and what caused the cascade. 1. The Root Cause: Sequential "Apply" In StarRocks, versions for a specific partition must be published in strict numerical order. * If Version 154 is currently in the "Apply" phase (updating the PK index on the BE), Version 155 cannot become

VISIBLE

, even if it has finished writing all its data. * Versions 155 through 252 were all sitting in the BE memory/disk, fully written and in

COMMITTED

status, just waiting for Version 154 to finish its work. 2. The Cascading Effect The "cascade" happened because of how the FE counts "running" transactions: 1. The Hang: Version 154 hit a bottleneck (likely a large update or a momentarily slow disk/CPU on that single BE). 2. The Queue: Because you load frequently, versions 155, 156, ..., 253 were created rapidly. Each one finished writing and moved to

COMMITTED

status. 3. The Ceiling: Even though they were "finished" writing, they were still considered "Running" by the FE because they weren't

VISIBLE

yet. 4. The Blockage: Once the count of these "waiting-to-be-visible" transactions hit exactly 100, the FE reached its

max_running_txn_num_per_db

limit. At this point, the FE began rejecting every new load attempt with the error you saw. 3. Why
ADMIN SET
appeared to "solve" it By increasing the limit to 200, you did two things: * Unblocked the Intake: You allowed the FE to stop rejecting new loads. This let the "waiting room" grow from 100 to 200. * The Timing Coincidence: While you were changing the config, the "bottleneck" (Version 154) finally completed its Apply task. * The Rapid Clear: Once the "blocking" version (154) finished, the BE and FE were able to batch-publish all the subsequent committed versions (155 through 252) almost instantly. This is why you saw the count drop from ~150 down to 2-3 in a matter of seconds. 4. Why it’s riskier with
replication_num = 1
When you have 3 replicas, StarRocks only needs a quorum (2 out of 3) to publish a version. If one BE is slow, the other two can "carry" the load, and the partition won't hang. With 1 replica, you have no redundancy. If that single BE node experiences any minor "hiccup" (like a long OS disk flush or CPU spike), the entire partition's version chain stops until that one node recovers. Summary The config change didn't "fix" the BE bottleneck; it just expanded the queue so the system didn't error out while waiting for the BE to finish. The "clearing" was the result of Version 154 finally finishing its task, allowing the sequential "Apply" pipeline to flush all pending versions at once. Recommendation: Since you are on a single replica, keep

max_running_txn_num_per_db

at 1000 (the current StarRocks default) to provide a larger buffer for these transient BE slowness. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md

Minn Fong

05/12/2026, 8:49 PM

@Rocky

Copy code

[42000][1064] Invalid plan:
PhysicalOlapScanOperator {table=7211473, selectedPartitionId=[7245825, 7247284, 7249026, 7253436, 7254919], selectedIndexId=7211474, outputColumns=[521: campaign_id, 522: has_won], projection=[521: campaign_id, 522: has_won], predicate=520: received <= 2026-05-12 17:00:00, prunedPartitionPredicates=[1: received >= 2026-05-12 13:00:00], limit=-1}
Input dependency cols check failed.
The required cols {1} cannot obtain from input cols {520,521,522}.

Caused by sql query

Copy code

EXPLAIN ANALYZE select campaign_id, sum(has_won) as wins, sum(has_click) as clicks from requests where received >= '2026-05-12 13:00:00' and received <= '2026-05-12 17:00:00' group by campaign_id order by wins desc limit 100;

Rocky

05/12/2026, 8:49 PM

This error is a known optimizer bug in certain versions of StarRocks (often seen when the Query Optimizer/CBO gets confused between identical columns used in different contexts). In your case, the

received

column is assigned two different internal IDs: ID 1 (used for partition pruning) and ID 520 (used for the general filter). The plan fails because a higher-level operator is asking for ID 1, but the Scan operator is only providing ID 520. Immediate Workarounds You can usually bypass this by disabling specific optimizations for the current session. Try running these before your query: 1. Disable Global Dictionary Optimization: This is the most common cause of "Input dependency" mismatches.

Copy code

sql
    SET enable_low_card_global_dict = false;

2. Disable Predicate Reordering: If the first one doesn't work, try:

Copy code

sql
    SET enable_predicate_reorder = false;

3. Bypass the CBO (Last Resort): This forces the query to run using the old logic, which is slower but avoids the optimizer bug.

Copy code

sql
    SET enable_cbo = false;

Why is this happening? * Column ID Mismatch: StarRocks' optimizer sometimes incorrectly maps a column during "Partition Pruning." It sees

received

in your

WHERE

clause and successfully uses it to select partitions (PrunedPartitionPredicates). However, it then "forgets" that this column is needed for the rest of the query or thinks it’s a different column altogether. * Missing Column: I also noticed that your

EXPLAIN

output shows

outputColumns=[521: campaign_id, 522: has_won]

, but your SQL asks for

sum(has_click)

. The column

has_click

is completely missing from the Scan's output, which would also cause this dependency failure. Permanent Fix 1. Check StarRocks Version: This specific bug (related to

prunedPartitionPredicates

and Column ID mapping) has been addressed in several patches (e.g., fixes in the 3.2.x and 3.3.x branches). If you are on an older version, consider upgrading to the latest maintenance release of your current branch. 2. Check for Materialized Views: If you have an Async Materialized View on this table, the optimizer might be trying to rewrite the query to use the MV and failing the column mapping. You can check if this is the case by setting:

Copy code

sql
    SET enable_materialized_view_rewrite = false;

Recommendation: Try

SET enable_low_card_global_dict = false;

first. If that allows the

EXPLAIN ANALYZE

to run, you have identified the culprit. References • administration/management/resource_management/Replica.md • faq/Sql_faq.md • sql-reference/sql-statements/administration/SET_CONFIG.md References • faq/loading/Broker_load_faq.md • faq/Sql_faq.md • loading/Spark-connector-starrocks.md • faq/loading/Loading_faq.md • faq/Deploy_faq.md

Minn Fong

05/12/2026, 8:54 PM

@Rocky I used expression based partitioning

Copy code

PRIMARY KEY(`received`, `auction_id`, `imp_key`)
PARTITION BY date_trunc('hour', received)
DISTRIBUTED BY HASH(`auction_id`) BUCKETS 128
ORDER BY(`received`, `auction_id`)

Why is it causing this bug

Rocky

05/12/2026, 8:55 PM

The error you're seeing is a known optimizer bug (specifically related to Issue #67665 and similar cases in StarRocks 3.x/4.0). It occurs because of a conflict between how the Cost-Based Optimizer (CBO) handles Expression-based Partitioning and Primary Key table internal column IDs. Why this is happening 1. Dual Column IDs: When you use

PARTITION BY date_trunc('hour', received)

, the optimizer internally tracks

received

as two different entities: * ID 1: The source column for partition pruning logic (seen in your

prunedPartitionPredicates

). * ID 520: The actual physical column stored in the Primary Key index. 2. The Mismatch: During query planning, one part of the optimizer (the Partition Pruning rule) correctly identifies the partitions using Column ID 1. However, when it passes the plan to the next step, it "forgets" to map ID 1 to the actual physical ID 520. 3. The Failure: The Scan operator provides ID 520, but the upper-level operator (like the filter or join) is still looking for ID 1. Because it can't find ID 1 in the input, it crashes with

Input dependency cols check failed

. Why it triggers on this query Your query is particularly susceptible because you are filtering directly on the partition column (

received >= ...

). This forces the optimizer to use

received

for both partition pruning (mapping to ID 1) and as a standard predicate (mapping to ID 520). The CBO gets confused trying to optimize both at once. How to Fix It 1. Immediate Workaround (Session Level) The most common culprit is the Global Dictionary (Low Cardinality) optimization. Disabling it usually fixes the column mapping:

Copy code

sql
SET enable_low_card_global_dict = false;

If that doesn't work, disable Predicate Reordering:

Copy code

sql
SET enable_predicate_reorder = false;

2. Change the Query Slightly Sometimes, wrapping the column in a simple function can "break" the incorrect optimization logic and force the CBO to re-evaluate the IDs:

Copy code

sql
-- Instead of: received >= '2026-05-12 13:00:00'
-- Try adding a redundant cast or function:
WHERE CAST(received AS DATETIME) >= '2026-05-12 13:00:00'

3. Permanent Fix This bug has been fixed in recent maintenance releases of StarRocks. * If you are on 3.2.x, upgrade to 3.2.13+. * If you are on 3.3.x, upgrade to 3.3.8+. * The fix is fully integrated into the v3.4 and v4.0 branches. Recommendation: Use

SET enable_low_card_global_dict = false;

as a quick fix for now, but plan an upgrade to a recent maintenance patch to avoid having to set this globally. References • table_design/data_distribution/expression_partitioning.md • sql-reference/sql-functions/date-time-functions/date_trunc.md

Minn Fong

05/12/2026, 8:56 PM

@Rocky I am on version 4.0+. THis bug shouldnt happen. Will SET enable_low_card_global_dict = false; affect any performance

Rocky

05/12/2026, 8:56 PM

Even on StarRocks version 4.0+, this bug can still occur. While many "Input dependency" bugs were fixed in late 2025, new edge cases (like Issue #67665) have been identified in early 2026 involving the combination of Expression-based Partitioning and Primary Key tables. Will
SET enable_low_card_global_dict = false;
affect performance? Yes, it will. Here is the specific impact: * String Processing Slowdown: This optimization (also known as

cbo_enable_low_cardinality_optimize

) typically provides a 2x to 3x performance boost for queries involving

STRING

columns. * The "Why": When enabled, StarRocks encodes low-cardinality strings (like your

campaign_id

) as integers. It then performs the

GROUP BY

and

DISTINCT

operations on those integers rather than the raw strings. * Your Query: Since you are grouping by

campaign_id

, disabling this will force the engine to hash and compare the actual string values for every row. If you have a large dataset, this will increase CPU usage and query latency. A Better Workaround for 4.0+ Before disabling the global dictionary entirely, try these more surgical workarounds which often resolve the "Input dependency" error in 4.0 without a major performance hit: 1. Switch the Optimization Strategy (v3.3+): StarRocks 4.0 uses a new "V2" rewrite for dictionaries. Reverting to the legacy "V1" logic often clears the column ID mismatch:

Copy code

sql
    SET low_cardinality_optimize_v2 = false;

2. Redundant Column Hint: Sometimes the optimizer loses track of a column ID because it thinks it only needs it for partition pruning. Adding the column to your

SELECT

list (even if you don't use it in your app) can force the optimizer to keep the ID "alive":

Copy code

sql
    -- Add 'received' to the select to see if it clears the error
    SELECT received, campaign_id, sum(has_won) ...

3. Predicate Casting: Force the optimizer to treat the

received

column as a standard column rather than a special "partition column" by wrapping it in a function:

Copy code

sql
    WHERE CAST(received AS DATETIME) >= '2026-05-12 13:00:00'

Recommendation If

SET low_cardinality_optimize_v2 = false;

works, use that. It keeps the performance benefits of dictionary encoding but uses the more stable (older) rewrite logic. If you must use

enable_low_card_global_dict = false

, monitor your query latency; if it stays within your SLA, it is a safe temporary fix until the next 4.0.x maintenance release. References • sql-reference/System_variable.md • using_starrocks/query_acceleration_with_auto_increment.md • administration/management/BE_configuration.md • table_design/data_distribution/expression_partitioning.md References • sql-reference/System_variable.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • using_starrocks/query_acceleration_with_auto_increment.md • loading/SparkLoad.md

Minn Fong

05/12/2026, 8:59 PM

@Rocky SET low_cardinality_optimize_v2 = false; DOesnt solve my iussue

Rocky

05/12/2026, 8:59 PM

Since you are on StarRocks 4.0+, you are likely hitting a recent regression or a known edge case where the optimizer incorrectly handles Expression-based Partitioning in combination with Primary Key tables. This error happens because the Cost-Based Optimizer (CBO) creates a "virtual" column (ID 1) for your

date_trunc

partition logic, but then fails to map it to the physical column (ID 520) when building the final execution plan. Since

low_cardinality_optimize_v2 = false

did not work, here are the most effective workarounds for this specific "Input dependency" bug: 1. Disable Predicate Reordering The optimizer often loses the column mapping when it tries to reorder predicates to improve performance. This is the most likely culprit for this specific mismatch in 4.0.

Copy code

sql
SET enable_predicate_reorder = false;

2. Disable Global Dictionary Optimization Even if you are on 4.0, the global dictionary can cause column ID confusion during the "Decode" phase.

Copy code

sql
SET enable_low_card_global_dict = false;

Note: This will impact performance for string-heavy GROUP BYs, but it is a reliable way to bypass plan validation errors. 3. Syntax Workaround (The "Brent-Statsig" Fix) There is a documented edge case in recent versions where the optimizer fails on

column >= X AND column <= Y

but succeeds if you change the syntax slightly to break the "Range" optimization pattern. Try:

Copy code

sql
-- Use BETWEEN instead of >= and <=
WHERE received BETWEEN '2026-05-12 13:00:00' AND '2026-05-12 17:00:00'

OR wrap the column in a function to bypass the direct partition pruning rule:

Copy code

sql
WHERE CAST(received AS DATETIME) >= '2026-05-12 13:00:00' 
  AND CAST(received AS DATETIME) <= '2026-05-12 17:00:00'

4. Disable Partition Pruning (Diagnostic/Last Resort) To confirm this is 100% a partition-pruning mapping bug, try:

Copy code

sql
SET enable_partition_pruning = false;

If the query works (though slower), the issue is confirmed to be in how StarRocks translates your

date_trunc

expression into partition IDs. Recommendation If

SET enable_predicate_reorder = false;

solves it, use that for now. It has a much lower performance impact than disabling the Global Dictionary. This issue has been identified in very recent builds (Jan/Feb 2026) for v4.0.x and usually requires a specific patch to the

PartitionPruneRule

. If you can share your exact sub-version (e.g.,

4.0.6

), I can check if a newer maintenance release has the fix. References • sql-reference/System_variable.md • best_practices/primarykey_table.md • using_starrocks/accelerate-cardinality-preserving-joins.md

Minn Fong

05/12/2026, 9:01 PM

@Rocky SET enable_predicate_reorder = false; False is the default. It is not solving the issue

Rocky

05/12/2026, 9:02 PM

If you are on StarRocks 4.0+, you are likely hitting a recent regression (similar to Issue #67665) where the optimizer incorrectly handles the relationship between Primary Key internal column IDs and Expression-based Partitioning logic. The error "Input dependency cols check failed" happens because the optimizer creates a virtual ID for the result of

date_trunc('hour', received)

, but then fails to ensure that the physical column

received

(which is part of your Primary Key index) is passed up through the execution plan to satisfy the query filters. Since

enable_predicate_reorder = false

did not help, try these workarounds specifically for version 4.0+: 1. The Most Likely Fix: Disable "Short Circuit" In StarRocks 4.0, Primary Key tables often use a "Short Circuit" execution path to bypass the standard CBO for simple lookups. This path sometimes misses the mapping for expression-based partition columns.

Copy code

sql
SET enable_short_circuit = false;

2. Disable Global Dictionary (Performance Impact) You asked about the performance impact. Yes, there is one.

Copy code

sql
SET enable_low_card_global_dict = false;

* The Impact: This optimization allows StarRocks to process strings (like your

campaign_id

) as integers. Disabling it will make string-based

GROUP BY

DISTINCT

, and

JOIN

operations 2x to 4x slower because the engine has to hash and compare the actual strings. * The Recommendation: Use this only as a diagnostic. If it fixes the bug, you know the issue is in the "Global Dictionary Rewrite" phase. 3. Bypass the Partition Pruning Rule (Syntax Trick) Force the optimizer to treat the

received

column as a standard data column instead of a "partitioning expression" by wrapping it in a no-op function. This usually clears the "Required cols cannot be obtained" error:

Copy code

sql
-- Instead of: WHERE received >= '2026-05-12 13:00:00'
-- Try this:
WHERE IFNULL(received, '1970-01-01') >= '2026-05-12 13:00:00'
-- Or:
WHERE CAST(received AS DATETIME) >= '2026-05-12 13:00:00'

4. Check for Column Type Mismatch In StarRocks,

date_trunc('hour', ...)

requires the input column to be a

DATETIME

. * If your

received

column is defined as a

DATE

(which only stores YYYY-MM-DD),

date_trunc('hour', ...)

will return an error or produce an invalid plan because "hour" doesn't exist in a

DATE

type. * Fix: Ensure

received

DATETIME

in your DDL. 5. Disable Materialized View Rewrite Even if you aren't using MVs, the 4.0 optimizer attempts to check for MV matches by default.

Copy code

sql
SET enable_materialized_view_rewrite = false;

Why this happens specifically with your DDL: Your

received

column is in the Primary Key, the Partition Expression, and the Order By (Sort Key). This "triple-duty" creates three different internal references to the same data in the optimizer's memory. When the CBO tries to prune partitions using the expression, it sometimes "loses" the reference needed for the Primary Key index lookup. Recommendation: Try

SET enable_short_circuit = false;

first. If that fails, the

IFNULL

CAST

trick is the most reliable way to fix it without sacrificing the plan without sacrificing the without hurting performance. References • table_design/data_distribution/expression_partitioning.md • sql-reference/System_variable.md • best_practices/primarykey_table.md • sql-reference/sql-functions/date-time-functions/date_trunc.md References • table_design/data_distribution/expression_partitioning.md

Minn Fong

05/12/2026, 9:03 PM

@Rocky

Copy code

select count(*) from requests where received between '2026-05-12 09:00:00' and '2026-05-12 13:00:00';

This works

Rocky

05/12/2026, 9:04 PM

The fact that

SELECT count(*) ...

works confirms that the basic filtering and partition pruning on the

received

column are functioning correctly. The "Input dependency" error only triggers when your query tries to load and process additional columns (like

campaign_id

) through the Global Dictionary or specific aggregation paths. Will
SET enable_low_card_global_dict = false;
affect performance? Yes, significantly for specific queries. * The Impact: This optimization allows StarRocks to process string columns (like

campaign_id

) as integers. When disabled, StarRocks must perform

GROUP BY

DISTINCT

, and

JOIN

operations on the raw strings. * Performance Penalty: For queries grouping by low-cardinality strings, you can expect a 2x to 5x increase in query latency and higher CPU consumption. * No Impact on `count()`:* Since

count(*)

doesn't process string columns, disabling this setting will have zero impact on the performance of the query that currently "works" for you. Why `count()` works but your full query fails* In StarRocks 4.0+, the optimizer uses a "Global Dictionary Rewrite" to speed up string processing. The "Input dependency" error happens when the optimizer correctly identifies that it needs a column (e.g., your ID 1) but fails to "see" that column in the output of the data-reading layer (the OlapScan).

count(*)

works because it doesn't need to load the

campaign_id

column or map it to a dictionary; it only reads the metadata or the

received

column for filtering. Better workarounds for 4.0+ Since

low_cardinality_optimize_v2 = false

and

enable_predicate_reorder = false

did not work, try these two settings which are specific to the 4.0 architecture: 1. Disable Short-Circuit Execution: StarRocks 4.0 uses a "fast path" for simple queries that sometimes misaligns column IDs in the plan.

Copy code

sql
    SET enable_short_circuit = false;

2. Disable Materialized View Rewrite: Even if you don't have MVs, the 4.0 optimizer attempts a "transparent rewrite" check that can occasionally trigger this dependency error during the pruning phase.

Copy code

sql
    SET enable_materialized_view_rewrite = false;

3. Force Column Loading (The "Dummy" Filter): If the optimizer thinks it can skip a column needed for the dictionary, adding a dummy filter can force the "input" to include the column:

Copy code

sql
    -- Add this to your query:
    AND campaign_id IS NOT NULL

Recommendation: If

SET enable_short_circuit = false;

resolves the issue, use that. It has a much smaller performance impact than disabling the Global Dictionary entirely. If you must use

enable_low_card_global_dict = false

, treat it as a temporary fix and check for a patch in the next 4.0.x maintenance release (e.g., 4.0.7+) maintenance release. References • release_notes/release-4.0.md

Minn Fong

05/12/2026, 9:06 PM

@Rocky

Copy code

select campaign_id, sum(has_won) as wins, sum(has_click) as clicks from requests where received BETWEEN '2026-05-12 09:00:00' and '2026-05-12 13:00:00' group by campaign_id order by wins desc limit 100;

The above query works

Copy code

select campaign_id, sum(has_won) as wins, sum(has_click) as clicks from requests where received BETWEEN '2026-05-12 09:00:00' and '2026-05-12 13:00:00' group by campaign_id order by wins desc limit 100;

This one doesnt work. Simply adding the "order by wins desc" causes the issue

Rocky

05/12/2026, 9:06 PM

Since adding the

ORDER BY

clause on the alias (

wins

) is the specific trigger for the "Input dependency cols check failed" error, this confirms a bug in the StarRocks 4.0 optimizer where the Sort node fails to correctly map the result of the aggregation. This usually happens because the planner incorrectly tries to "decode" or "prune" the column IDs before the sorting happens. Here are the most effective ways to bypass this without disabling the Global Dictionary (which would hurt performance). 1. Use the full expression in ORDER BY Sometimes the alias resolution is what breaks the dependency chain. Try replacing the alias with the actual aggregate function:

Copy code

sql
SELECT campaign_id, sum(has_won) AS wins, sum(has_click) AS clicks 
FROM requests 
WHERE received BETWEEN '2026-05-12 09:00:00' AND '2026-05-12 13:00:00' 
GROUP BY campaign_id 
ORDER BY sum(has_won) DESC -- Use the expression here
LIMIT 100;

2. Use Positional ORDER BY This forces the optimizer to look at the output index of the aggregation rather than the name/alias mapping:

Copy code

sql
SELECT campaign_id, sum(has_won) AS wins, sum(has_click) AS clicks 
FROM requests 
WHERE received BETWEEN '2026-05-12 09:00:00' AND '2026-05-12 13:00:00' 
GROUP BY campaign_id 
ORDER BY 2 DESC -- 2 refers to 'wins' (the 2nd column)
LIMIT 100;

3. Wrap in a Subquery (The "Force Materialize" Trick) By wrapping the aggregation in a subquery, you force the optimizer to complete the aggregation and "output" the columns clearly before the outer layer tries to sort them.

Copy code

sql
SELECT * FROM (
    SELECT campaign_id, sum(has_won) AS wins, sum(has_click) AS clicks 
    FROM requests 
    WHERE received BETWEEN '2026-05-12 09:00:00' AND '2026-05-12 13:00:00' 
    GROUP BY campaign_id
) t
ORDER BY wins DESC 
LIMIT 100;

4. Disable "Short-Circuit" Path In StarRocks 4.0, simple queries often take a "short-circuit" execution path to reduce latency. This path has a known issue where it skips certain column mapping checks required for Sort nodes.

Copy code

sql
SET enable_short_circuit = false;
-- Then run your original query

5. Performance impact of
enable_low_card_global_dict
You asked earlier if this setting hurts performance. * Yes: It can make queries that group by strings (like

campaign_id

) 2x to 5x slower. * Why: Without it, StarRocks has to process the actual string values (hashing and comparing bytes) instead of treating them as small integers. * Recommendation: Try the workarounds above (1, 2, or 3) first. They will keep your query fast while bypassing the planner bug. Summary of what is happening The error

Input dependency cols check failed

in version 4.0 is a known regression where the optimizer's Partition Pruning and Aggregation logic disagree on which column IDs should exist. When you add

ORDER BY wins

, the planner adds a new "Sort" node that asks for "Column X" (the sum result). Because of the partition logic on

received

, the planner accidentally prunes the dependency that "Column X" needs, leading to the crash. Is there a permanent fix? This issue is being actively patched in maintenance releases. If you are on an early 4.0.x build, check if 4.0.7 or later is available, as many "Input dependency" fixes were merged in late Jan/Feb 2026. References • sql-reference/System_variable.md • faq/Sql_faq.md • sql-reference/sql-functions/Window_function.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md

Minn Fong

05/12/2026, 9:14 PM

@Rocky https://github.com/StarRocks/starrocks/pull/67986 Which version was this released in? My current starrocks version is 4.0.7-b75f536

Rocky

05/12/2026, 9:15 PM

GitHub PR #67986 ("Fix JSON path rewrite pruning partition predicate columns") was merged on January 19, 2026. It was officially released in StarRocks 4.0.3. Since your current version is 4.0.7-b75f536, you already have the fix from that specific PR. Why are you still seeing the error? The "Input dependency cols check failed" is a generic error that occurs when the optimizer's pruning logic and execution plan logic become desynchronized. While PR #67986 fixed one specific cause (related to JSON paths), other community reports (such as Issue #68184) indicate that similar crashes can still occur even after that fix, especially when certain dictionary optimizations are active. In your specific case, the

ORDER BY wins

(an alias for an aggregate) is triggering a separate but related dependency mapping bug. Immediate Action for 4.0.7 Because you already have the PR #67986 fix, you are likely hitting a secondary regression. To solve the issue on your current version without waiting for a patch, use one of these workarounds: 1. Disable the Dictionary Optimization (Specific to 4.0+):

Copy code

sql
    SET enable_low_card_global_dict = false;

Note: This will likely resolve the crash, but will make string groupings about 2-3x slower. 2. Use Positional Sorting (Bypasses alias mapping):

Copy code

sql
    SELECT campaign_id, sum(has_won) as wins, sum(has_click) as clicks
    FROM requests
    WHERE received BETWEEN '...' AND '...'
    GROUP BY campaign_id
    ORDER BY 2 DESC -- Use the column index '2' instead of the name 'wins'
    LIMIT 100;

3. Disable Short-Circuit Path:

Copy code

sql
    SET enable_short_circuit = false;

Recommendation: Since you are on 4.0.7, try Workaround #2 first. It is the least intrusive and often bypasses the specific "Input dependency" check that fails during the alias-to-column mapping phase in the 4.0.x optimizer.

Open in Slack

Previous Next