@Rocky I am running into a strange issue where for complex SQL views the query is running for 30 minutes and reports as completed on backend, but the mysql CLI does not actually get any results back. What could be the causes of this and how to better debug?

This issue where a query is marked as Finished in StarRocks but the client (MySQL CLI) receives no results after a long duration (30 minutes) usually points to a network disconnection or a timeout occurring at an intermediate layer (like a Load Balancer, Proxy, or Firewall) rather than the StarRocks engine itself. Here are the likely causes and the steps to debug this: 1. Common Causes * Intermediate Proxy/LB Timeout: If you are connecting through a Load Balancer (e.g., AWS ELB, HAProxy, Nginx), these often have an "Idle Timeout." If the query takes 30 minutes to produce its first row of data, the proxy may close the TCP connection because no data was transmitted during that window. * TCP Keep-Alive: If the connection is idle (from a network packet perspective) while StarRocks is processing the complex view, network equipment might drop the "silent" connection. * FE

mysql_service_kill_after_disconnect

Setting: If this FE configuration is set to

false

, StarRocks will continue executing a query to completion even if the client has already disconnected. This explains why the backend shows "Finished" while the client is long gone. * Client-Side Timeout: While the standard MySQL CLI usually waits indefinitely, certain environments or wrappers have their own read timeouts. ────────── 2. How to Debug Step A: Verify the Backend Status Check the Audit Log on the FE (

fe/log/fe.audit.log

). Find the entry for the specific Query ID and check: * State: Does it actually say

EOF

,

Error

, or

Finished

? * Return Rows: Check the

ReturnRows

column. If it says

0

but you expected data, the view logic might be returning nothing. If it shows a large number, StarRocks successfully "sent" them, but they didn't reach the CLI. * ErrorCode: Check if there is a "Broken pipe" or "Connection reset by peer" error in the

fe.log

at the exact time the query was supposed to finish. Step B: Use

SHOW PROFILELIST

and

ANALYZE PROFILE

This will tell you exactly what the query was doing during those 30 minutes. 1. Run

SHOW PROFILELIST;

to get the

QueryId

. 2. Run

ANALYZE PROFILE FROM '<QueryId>';

3. Look at the Summary section. If the query spent 29 minutes in "Execution" and only seconds in "Result Deliver," then the bottleneck is the view computation. If it spent a long time in "Result Deliver," the FE was struggling to push data to the client. Step C: Bypass the Proxy If you are using a Load Balancer or Proxy (like ProxySQL), try connecting directly to the FE's IP and port 9030. If the query returns correctly when connected directly, the issue is your proxy's idle timeout. ────────── 3. Potential Solutions * Increase Proxy Idle Timeout: If using an ELB/ALB, increase the idle timeout to be greater than your

query_timeout

(e.g., 3600 seconds). * Check `mysql_service_kill_after_disconnect`: Ensure this is set to

true

(default) in

fe.conf

. This ensures that if the client drops, the query is killed immediately, preventing "ghost" completions on the backend.

properties
    mysql_service_kill_after_disconnect = true

* Optimize the View: A 30-minute query for a view is extremely long for StarRocks. Use

EXPLAIN ANALYZE <query>

to see if the view is causing: * Massive cross-joins. * Lack of partition pruning. * Data being pulled from a slow external catalog (Hive/Iceberg) without a cache. * Session Keep-Alive: You can try to set the

net_write_timeout

and

net_read_timeout

session variables higher, though these are more relevant to the StarRocks-to-Client communication once data starts flowing.

sql
    SET net_read_timeout = 3600;
    SET net_write_timeout = 3600;

Recommendation: Check your

fe.audit.log

first. If the

ReturnRows

is high but the

Time

is 1800s+, and the client got nothing, it is almost certainly a TCP/Proxy timeout. timeout at the Load Balancer/Network layer. References

@Rocky What about for a tableau extract? Does net_read_timeout and net_write_timeout actually do anything for StarRocks?

In StarRocks, the system variables

net_read_timeout

and

net_write_timeout

are only included for compatibility with the MySQL protocol; they have no practical effect on how StarRocks handles data transmission or timeouts. For a Tableau extract taking 30 minutes, the fact that the backend reports "Completed" but the client gets no results strongly suggests a socket disconnection at the network or driver level during the long period of "silence" while StarRocks was processing the complex view. Here is how to properly address this for Tableau: 1. Configure the JDBC/Driver Timeout If you are using the StarRocks Tableau JDBC Connector (recommended), the timeout is controlled by the underlying MySQL JDBC driver properties. You can add these to your connection string or a

.properties

file: *

socketTimeout

: This is the most critical. It defines how long the driver will wait for data before closing the socket. Set this to a value larger than your longest query (in milliseconds). _ _Example:*

socketTimeout=3600000

(1 hour). *

connectTimeout

: Time to wait for the initial connection. 2. Check the StarRocks

wait_timeout

While

query_timeout

controls how long a query can run,

wait_timeout

controls how long StarRocks keeps an idle connection open. * If Tableau sends the query and then "waits" for 30 minutes without any packet exchange, an intermediate proxy or the StarRocks

wait_timeout

might close the session. * The default

wait_timeout

is 8 hours (28800s), so this is rarely the culprit unless it has been manually lowered. 3. Intermediate Infrastructure (Load Balancers/Proxies) If your Tableau Server connects to StarRocks via an F5, Nginx, or AWS ELB/NLB, these devices almost always have an Idle Timeout (often defaulting to 60 seconds or 5 minutes). * If the query takes 30 minutes to produce the first row, the Load Balancer will see 30 minutes of "TCP silence" and terminate the connection. * Solution: Increase the Idle Timeout on your Load Balancer to exceed your longest expected extract time. 4. How to Debug the "Completed" Status The backend showing "Completed" means the FE (Frontend) finished the work and tried to hand it off, but the "pipe" to the client was already broken. 1. Check FE Audit Log: Look for the query in

fe.audit.log

. Check the

ReturnRows

column. If it shows 1,000,000 rows but the

Time

is 1800s, StarRocks did its job, but the network failed to deliver. 2. Use a Direct Connection: To rule out network issues, try running the same view from a machine that can connect directly to the FE IP (bypassing any Load Balancers) using the same JDBC driver. 3. Check for "Broken Pipe" in FE Logs: Search

fe.log

for the

QueryId

. You will likely see a

java.io.IOException: Broken pipe

or

Connection reset by peer

at the moment the query finished. Recommended Settings for Tableau Extracts If you are running very long extracts, set these at the session level before the extract starts (if your connector allows initial SQL):

sql
-- Increase query timeout to 1 hour
SET query_timeout = 3600;

-- Ensure the session doesn't die during long processing
SET wait_timeout = 3600;

Note: If the view is "complex," also check

new_planner_optimize_timeout

. If the view has dozens of joins, the StarRocks planner might be timing out during the planning phase, which defaults to 3000ms. If the view is complex, also check

new_planner_optimize_timeout

. If the view has dozens of joins, the StarRocks planner might be timing out during the planning phase, which defaults to 3000ms. However, this would usually return an error to the client immediately.ms. However, this would usually return an error to the client immediately. References • faq/operation_maintenance_faq.md