@Rocky Are there known errors related to setting session variables (

SET my_var = my_value

) and the use of proxysql to front starrocks? We are seeing an issue where setting variables and calling a select hangs indefinitely, and it looks like a peculiar interaction between proxysql and multi node starrocks, doesn't happen in single node

The issue you're experiencing—where

SET

session variables followed by a

SELECT

hang indefinitely in a multi-node StarRocks setup with ProxySQL—is likely caused by ProxySQL's connection multiplexing and its interaction with StarRocks' FE session management. 1. Connection Multiplexing Mismatch ProxySQL's "Multiplexing" feature allows multiple frontend client connections to share a smaller pool of backend connections to the StarRocks FEs. • The Issue: When you execute a

SET

command, ProxySQL attempts to track the session state to "replay" it if the session is moved to a different backend FE node. In a multi-node setup, if ProxySQL routes the subsequent

SELECT

to a different FE or fails to reconcile the state (e.g., due to parsing errors or unsupported MySQL capability bits), the connection can hang while ProxySQL waits for a protocol response that StarRocks doesn't send in the expected format. • Single Node vs. Multi-Node: In a single-node setup, ProxySQL typically stays on the same backend connection, masking the state synchronization issue. 2. Recommended Solutions A. Disable Multiplexing The most reliable fix when using session variables with ProxySQL is to disable multiplexing for those connections. You can do this globally or for specific users/queries: • Globally: Set

mysql-multiplexing=false

in the ProxySQL global variables. • Via Query Rules: Create a rule to disable multiplexing for connections that use

SET

or for all StarRocks traffic:

sql
  INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex) VALUES (1, 1, '.', 0);
  LOAD MYSQL QUERY RULES TO RUNTIME;

B. Use

SET_VAR

Hints (Best Practice) Instead of executing a separate

SET

statement, use StarRocks' statement-level hints. This makes the variable scoped only to that specific query, which is stateless and proxy-friendly:

sql
SELECT /*+ SET_VAR(query_timeout = 60, exec_mem_limit = 8589934592) */ * FROM my_table;

This avoids triggering ProxySQL's session-state tracking logic entirely. C. FE Configuration Ensure your StarRocks FEs are configured to handle disconnected proxy clients properly. In your

fe.conf

, check: • `mysql_service_kill_after_disconnect = true`: This ensures that if ProxySQL drops or resets a connection, StarRocks kills the associated query immediately rather than leaving it in a "hanging" state. D. ProxySQL Version If you are using an older version of ProxySQL (e.g., 2.x), consider upgrading to 3.0.8 or later. These versions introduced improved MySQL session-variable tracking (

mysql-session_track_variables

) which is more robust, though StarRocks compatibility with this specific protocol may vary depending on the StarRocks version. 3. Summary of Causes * Session State: StarRocks session variables are local to the FE node that received the

SET

command. They are not synchronized across other FEs. * Protocol Hang: ProxySQL may hang if it enters a state where it's waiting for a "Success" or "OK" packet with session-state flags that StarRocks (which is MySQL-compatible but not a 1:1 MySQL clone) does not provide in the specific sequence ProxySQL expects during state requires. sequence. * Forwarding Logic: In multi-node StarRocks, some queries are internally forwarded to the Leader FE; this internal hop can sometimes clash with ProxySQL's own routing and timeout logic if multiplexing is active. multiplexing is active References • administration/management/FE_configuration.md • sql-reference/System_variable.md • deployment/deploy_manually.md

@Rocky does this multiplexing connection behaviour have any implication for transactions opened via proxysql that could lead to future issues?

Yes, ProxySQL multiplexing has significant implications for transactions in a multi-node StarRocks environment. Because StarRocks sessions and transactions are node-local (bound to a specific FE), any behavior that switches backend connections mid-session will break the transaction. 1. Transaction Integrity and Session Locality In StarRocks, a transaction is associated with a single session on a specific FE. • The Risk: If ProxySQL multiplexing is active and fails to detect a transaction state, it might route the next statement in your transaction to a different FE. Since the new FE has no record of that transaction ID or session state, the query will fail with an error like

Transaction not found

or, in some protocol edge cases, hang as the proxy and backend fail to sync. • ProxySQL's Logic: ProxySQL is designed to automatically disable multiplexing when it detects

BEGIN

,

START TRANSACTION

, or an active transaction flag in the MySQL protocol. However, if your application uses

SET autocommit=0

to start transactions, some older versions of ProxySQL or specific configurations might not catch this state immediately, leading to a connection switch. 2. Potential Issues with Multiplexing & Transactions * Visibility Issues: If an

INSERT

happens on FE-A but the

COMMIT

is accidentally routed to FE-B (due to a multiplexing glitch), the

COMMIT

will fail, and your data will never be persisted. * Hanging on Commit/Rollback: As you've seen with session variables, if the protocol state becomes "dirty" (where the Proxy thinks the connection is multiplex-safe but the Backend is in a transaction), the handshake for the next query can hang or time out. * Internal Forwarding Conflicts: StarRocks FEs often forward DML (Write) requests to the Leader FE. If ProxySQL is also trying to manage the connection via multiplexing, you end up with two layers of routing (Proxy -> FE -> Leader FE). This double-hop increases the chance of a timeout or a "broken pipe" if any layer decides to prune the connection. 3. Recommended Safeguards for Transactions To avoid future issues and "mysterious" hangs during transactions: * Explicitly Disable Multiplexing for Transactional Users: If you have a specific service user that performs writes/transactions, disable multiplexing for that user entirely in ProxySQL:

sql
    UPDATE mysql_users SET transaction_persistent=1 WHERE username='your_app_user';
    LOAD MYSQL USERS TO RUNTIME;

This ensures that once a session starts, it stays pinned to the same backend FE for its duration. * Use

BEGIN ... COMMIT

Blocks: Avoid relying solely on

autocommit=0

. Explicit

BEGIN

and

COMMIT

statements are more reliably intercepted by ProxySQL's state machine to trigger "multiplexing disabled" mode. * StarRocks v3.5+ SQL Transactions: If you are using the newer SQL Transaction feature (available from v3.5.0), ensure your ProxySQL is updated to at least v2.5.5+ or v2.6.x to ensure better compatibility with modern MySQL protocol flags that StarRocks uses to signal transaction status. * Set `mysql-connection_delay_multiplex_ms`: In ProxySQL, you can set a small delay (e.g.,

SET mysql-connection_delay_multiplex_ms=100

) which prevents the proxy from immediately returning a connection to the pool after a query, giving the app time to send the next statement in a sequence without a connection switching. Summary While ProxySQL is excellent for load balancing StarRocks

SELECT

traffic, multiplexing is generally dangerous for session-heavy or transactional workloads in StarRocks. Because StarRocks FEs do not share session state (like variables or open transactions), you should pin these connections to a single FE using

transaction_persistent=1

or by disabling multiplexing for those specific query rules. References

@Rocky does this approach B. Use

SET_VAR

Hints (Best Practice) Instead of executing a separate

SET

statement, use StarRocks' statement-level hints. This makes the variable scoped only to that specific query, which is stateless and proxy-friendly:

sql
SELECT /*+ SET_VAR(query_timeout = 60, exec_mem_limit = 8589934592) */ * FROM my_table;

still allow us to get the profile of the query with SELECT get_query_profile(last_query_id()) through the same connection? Assuming we are changing these variables SET SESSION enable_profile = true; SET SESSION big_query_profile_threshold = 0; SET SESSION pipeline_profile_level = 1; SET SESSION runtime_profile_report_interval = 0; SET SESSION enable_query_cache = false;

While the

SET_VAR

approach allows you to set profiling variables for a single query without the "hanging" issues caused by ProxySQL multiplexing, it creates a new challenge for retrieving the profile. 1. Does

SET_VAR

work for profiling variables? Yes. You can successfully include profiling variables in the hint. This will trigger the StarRocks BEs to generate a profile for that specific statement:

sql
SELECT /*+ SET_VAR(enable_profile = true, pipeline_profile_level = 1, enable_query_cache = false) */ * FROM my_table;

This avoids modifying the session state, which is what typically causes ProxySQL to hang or get "out of sync" when tracking variables. 2. Can you still retrieve the profile? This is where the ProxySQL interaction becomes problematic. Even if the query generates a profile, the standard retrieval workflow (

last_query_id()

->

get_query_profile()

) will likely fail if multiplexing is active: *

last_query_id()

is Session-Local: ProxySQL multiplexing allows different queries from the same application client to be sent to different StarRocks sessions (and different FE nodes). If the

SELECT

query runs on FE-A but the

SELECT last_query_id()

is routed to FE-B (or even a different session on FE-A), it will return the wrong ID or

NULL

. * Profile Storage is Node-Local: In multi-node StarRocks, query profiles are typically stored in the memory of the specific FE node that coordinated the query. If ProxySQL routes your

get_query_profile()

call to a different FE, that FE won't have the profile in its local cache, and the function will return an empty result. *

get_query_profile

Requirement: The

get_query_profile()

function itself checks the

enable_profile

variable. If you use

SET_VAR

for the main query, the variable is only true for that query. You would need to use

SET_VAR

again on the retrieval call itself:

sql
    -- This might still return empty if ProxySQL switched your backend connection
    SELECT /_+ SET_VAR(enable_profile=true) _/ get_query_profile('your-query-id');

3. Recommended Solution for Profiling with ProxySQL If you need to retrieve profiles while using ProxySQL, the

SET_VAR

hint alone is not enough because of the "state" requirement of the profile retrieval steps. Use one of these two paths: Option A: Pin the Connection (Best for DBAs) Create a ProxySQL rule or use a specific user that has multiplexing disabled. This ensures all three steps (

SET

,

SELECT

,

get_query_profile

) happen on the exact same physical session and FE:

sql
-- In ProxySQL, pin this user to avoid connection switching
UPDATE mysql_users SET transaction_persistent=1 WHERE username='dba_user';
LOAD MYSQL USERS TO RUNTIME;

Option B: Use the StarRocks Web UI Instead of using SQL functions through the proxy, log directly into the Web UI of any FE node (

http://<fe_ip>:8030

). * In a multi-node FE setup, you may need to check the "Queries" tab on each FE to find your query, as each node lists the queries it coordinated. * This bypasses ProxySQL entirely and is the most reliable way to view profiles in a multi-node environment. Summary of variables compatibility Variable |

SET_VAR

Compatible? | Notes

enable_profile

| Yes | Triggers profile generation for the specific query.

big_query_profile_threshold

| Yes | Note: Value must be a string (e.g.,

'0s'

).

pipeline_profile_level

| Yes | Controls the depth of the generated profile.

enable_query_cache

| Yes | Disables cache for that specific execution. Conclusion: Use

SET_VAR

to avoid the hang during query execution, but be aware that you will likely need to disable ProxySQL multiplexing or bypass the proxy to successfully retrieve that profile using

last_query_id()

. References • sql-reference/System_variable.md