This message was deleted.

This behavior is likely due to how the Power BI connector (MySQL/ODBC) handles an interrupted data stream from StarRocks. When a query is killed on the server side, StarRocks is supposed to send an error packet and terminate the connection. However, if the client (Power BI) receives a "connection closed" or a truncated stream without a clear error code at the very end, it may treat the already-received data as a complete result set. Why this happens 1. Silent Stream Termination: When you run

KILL QUERY

, StarRocks stops the execution. If Power BI is already in the middle of fetching results, the server sends an error message. However, depending on the MySQL Connector version and settings, Power BI might simply stop importing and keep the data it already buffered, assuming the stream ended naturally. 2. Driver Behavior: Certain versions of the MySQL

.NET

or

ODBC

connectors are known to exhibit "silent failure" behavior where they don't propagate the server-side cancellation error to the Power BI UI, leading to "Success" with partial data. 3. Result Buffering: StarRocks has a parameter

result_buffer_cancelled_interval_time

(default 300s) that controls how long data stays in the buffer after cancellation. If the client is still reading from this buffer while the query is being torn down, it might see an EOF (End of File) rather than an error. Troubleshooting Steps To confirm and fix this, follow these steps: 1. Check the StarRocks Audit Log Examine the

fe.audit.log

on your FE nodes. Find your killed query and look at the

State

column. * If it shows

State=ERR

, StarRocks correctly signaled an error. * If it shows

State=EOF

or something else, the server may not have sent the error packet correctly. * Search command:

grep "your_query_fragment" fe.audit.log

2. Check the Kill Type Are you using

KILL QUERY <id>

or

KILL CONNECTION <id>

? *

KILL QUERY

: Stops the specific SQL but keeps the connection alive. This is more likely to cause "partial data" issues if the connector doesn't realize the specific result set was aborted. *

KILL CONNECTION

: Terminates the entire TCP session. This is generally safer if you want to ensure Power BI throws a "Connection Reset" or "Link Failure" error. 3. Update or Switch Connectors * If using the built-in MySQL connector, ensure you are using a recommended version (e.g., 8.0.28 is often cited as stable for Power BI). * Try switching to the MySQL ODBC Driver if you are currently using the .NET connector, as their error-handling logic differs. 4. Verify Server Settings Check the FE configuration for

mysql_service_kill_after_disconnect

. If this is

true

(default), StarRocks will kill queries when the client disconnects. If you are killing the query manually via SQL while Power BI is still connected, this setting isn't the cause, but it's good to keep in mind for automated timeouts. Recommendation: If you must stop a long-running import, use

KILL CONNECTION <id>

to force a hard disconnect, which is more likely to trigger a "Data Source Error" in Power BI rather than a silent partial import. References • administration/user_privs/authorization/privilege_faq.md • administration/management/FE_configuration.md • faq/Sql_faq.md • administration/management/resource_management/Query_management.md • administration/management/graceful_exit.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md

@Rocky but kill connection throws[HY000][1094] Unknown thread id:

The error

[HY000][1094] Unknown thread id

occurs because the

KILL

command in StarRocks is node-local. If your Power BI (PBI) is connected through a Load Balancer or a multi-FE setup, the connection you are trying to kill likely exists on a different Frontend (FE) node than the one you are currently logged into. 1. Why

KILL CONNECTION

fails with "Unknown thread id" In StarRocks, each FE maintains its own list of MySQL connections. When you run

SHOW PROCESSLIST

, you only see connections to your current FE. If you try to

KILL

an ID that belongs to another FE, the current FE won't recognize it. 2. How to find the correct FE node To find which FE node is handling the Power BI query: 1. Run this command (available in StarRocks 3.x+):

sql
   SHOW PROC '/global_current_queries';

2. Look for the row corresponding to your Power BI query. 3. Note the

feIp

and

ConnectionId

columns. 3. How to successfully kill the connection To ensure Power BI receives a hard connection error (instead of finishing with partial data), you must terminate the connection from the specific FE handling it: 1. Connect directly to the FE IP address identified in the

feIp

column (bypass the Load Balancer). 2. Run the kill command using the

ConnectionId

found in the previous step:

sql
   KILL CONNECTION <ConnectionId>;

4. Why Power BI reports "Success" with lost data When you use

KILL QUERY

, the server sends an error packet but keeps the connection open. Some MySQL drivers used by Power BI handle this poorly: they treat the interrupted stream as a "complete" result and silently close the import without raising a UI error. By using

KILL CONNECTION

on the correct FE, you force a TCP-level disconnect or a "Connection Reset," which is much more likely to trigger a "Data Source Error" in Power BI, preventing users from seeing incomplete data. Summary Checklist * Command: Use

KILL CONNECTION <id>

(not

KILL QUERY

). * Target: Must be executed on the specific FE listed in

SHOW PROC '/global_current_queries'

. * Privileges: Ensure your user has the

OPERATE

privilege (required in newer StarRocks versions to kill connections). ────────── References: • KILL Syntax • SHOW PROC Reference • Managing Big Queries References • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • administration/management/resource_management/Query_management.md • administration/user_privs/authorization/privilege_faq.md • sql-reference/sql-statements/cbo_stats/KILL_ANALYZE.md • using_starrocks/Cost_based_optimizer.md • administration/management/monitor_manage_big_queries.md

@Rocky so there is no way to kill a connection from different fe? 😕

Unfortunately, no, there is no direct command to kill a MySQL connection from a different FE node. In StarRocks, MySQL connections are managed locally by the specific Frontend (FE) node that accepted the TCP request. Because the underlying socket lives only on that node, a different FE cannot "reach over" and close it. This is why you get the

Unknown thread id

error when trying to kill it from a different node. To resolve the Power BI "lost data" issue (which happens because the connector doesn't realize the query was aborted), you must use

KILL CONNECTION

on the correct FE. How to Kill the Connection (Multi-FE/Load Balancer) 1. Find the owner FE: Run this on any FE to see which node is handling the Power BI session:

sql
    SHOW PROC '/global_current_queries';

Look for the

feIp

and

ConnectionId

columns. 2. Connect directly to that FE: Bypass your Load Balancer/VIP and connect your MySQL client (or DBeaver/Navicat) directly to the IP address found in the

feIp

column. 3. Terminate the connection: Once connected to the correct FE, run:

sql
    KILL CONNECTION <ConnectionId>;

This forces a TCP-level disconnect, which triggers a "Data Source Error" in Power BI instead of allowing it to silently finish with partial data. Why

KILL QUERY

isn't enough If you use

KILL QUERY <id>

or the global

KILL QUERY '<query_uuid>'

(supported in newer versions), StarRocks stops the execution on the BE nodes and sends an error packet to the FE. However, the connection remains open. Some Power BI drivers interpret this as "end of stream" and simply stop importing, assuming they have all the data. Alternative: Automated Safety If you frequently run into long-running Power BI queries that you want to prevent from finishing partially, consider: * Resource Groups: Set a

cpu_core_limit

or

mem_limit

on a resource group assigned to your Power BI user. When a query hits these limits, StarRocks kills it automatically with a clear error. * Query Queues: Enable

enable_query_queue_select

to prevent too many large imports from running simultaneously. ────────── References: • Nodes and processes - KILL • Monitor & Manage Big Queries • FE Configuration Reference References • administration/management/resource_management/Query_management.md • administration/management/FE_configuration.md • administration/management/graceful_exit.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md

@Rocky how to check on which fe am I

To check which specific Frontend (FE) node you are currently connected to, you can use any of the following methods: Method 1: The

feIp

from current queries (Most Reliable) The most direct way to see the IP of your current FE is to query the local process list metadata:

sql
SHOW PROC '/current_queries';

Look at the

feIp

column. Since this command only shows queries running on the FE node you are currently connected to, the IP listed there is the IP of your current FE. Method 2: Identify via Connection ID If you are on version 3.4 or later, you can find your specific session across the whole cluster: 1. Get your current session ID:

sql
   SELECT connection_id();

2. Find that ID in the global list:

sql
   SHOW PROC '/global_current_queries';

3. Match your

ConnectionId

to find the corresponding

feIp

. Method 3: Use the

host_name()

function StarRocks provides a utility function that returns the hostname of the node evaluating the expression. For a simple constant query, this is handled by the FE:

sql
SELECT host_name();

Method 4: System Variable Like MySQL, StarRocks often supports the hostname variable:

sql
SELECT @@hostname;

Note: If you are using a Load Balancer (like HAProxy or F5), the IP you used to connect is the Load Balancer's IP, but these commands will reveal the actual internal IP of the StarRocks FE node that is handling your traffic. You will need to connect directly to that internal IP if you want to perform a

KILL CONNECTION

successfully. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/sql-functions/utility-functions/host_name.md • administration/management/monitor_manage_big_queries.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_FRONTENDS.md • administration/management/FE_configuration.md • sql-reference/information_schema/fe_threads.md • deployment/deploy_manually.md • administration/Meta_recovery.md

@Rocky no even kill connection does not throw error and bi ends up with missed records

The issue you're describing—where

KILL CONNECTION

terminates the query but Power BI reports "Success" with missing rows—is a known behavior with certain MySQL drivers (especially the MySQL Connector/NET and some versions of the MySQL ODBC driver). When a connection is killed at the TCP level, these drivers sometimes interpret the abrupt end of the data stream as a "clean" end-of-file (EOF) rather than a protocol error. This leads Power BI to assume it has received the entire dataset. To ensure Power BI identifies this as a failure, try the following strategies: 1. Use

KILL QUERY

instead of

KILL CONNECTION

While I previously recommended

KILL CONNECTION

, for Power BI specifically,

KILL QUERY

might be more effective at triggering an error UI. * Why:

KILL QUERY

attempts to send an explicit "Query Interrupted" error packet (

ER_QUERY_INTERRUPTED

) over the still-open MySQL connection before stopping. * Action: 1. Connect to the specific FE node handling the query (found via

SHOW PROC '/global_current_queries'

). 2. Run:

KILL QUERY <ConnectionId>;

3. Wait a few seconds to see if Power BI catches the error packet. 2. Use Global

KILL

(StarRocks 3.1+) If you are on a recent version of StarRocks, you can kill a query by its UUID (QueryId) from any FE. This is safer because it avoids the "Unknown thread id" error and communicates the cancellation through the internal RPC. * Command:

sql
    KILL QUERY '<Query_UUID>';

(Note the single quotes around the UUID). 3. Change the Power BI Driver The MySQL Connector/NET (the default choice in the Power BI "MySQL database" connector) is notoriously fragile with StarRocks/MPP engines. * Recommendation: Switch to the MySQL ODBC Driver (Unicode). In Power BI, choose Get Data -> ODBC and use a DSN configured for StarRocks. The ODBC driver generally handles "Connection Reset" and "Query Interrupted" packets more strictly, forcing Power BI to show a "Data Source Error." 4. Use Resource Groups for Proactive Termination If you are killing queries because they are too large or taking too long, use Resource Groups to automate this. When StarRocks kills a query due to a resource limit (e.g.,

big_query_mem_limit

), it returns a hard

HY000

error to the client. * Example:

sql
    CREATE RESOURCE GROUP pbi_limit_group
    TO (user='pbi_user')
    WITH (
        "cpu_core_limit" = "1",
        "big_query_mem_limit" = "2147483648" -- 2GB
    );

When a query hits this limit, StarRocks proactively sends an error message like

exceed big query mem limit

, which Power BI is much more likely to display as a failure. Summary If

KILL CONNECTION

is silent, the driver/gateway is "masking" the failure. Try

KILL QUERY '<UUID>'

(global kill) or switch to the ODBC driver to force the error UI in Power BI. References • administration/management/resource_management/Query_management.md • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • administration/management/monitor_manage_big_queries.md References • administration/management/resource_management/Query_management.md • administration/user_privs/authorization/privilege_faq.md • administration/management/monitor_manage_big_queries.md • faq/Sql_faq.md

@Rocky we use mysql odbc driver 9.5 unicode

If

KILL CONNECTION

is resulting in a "Success" message with missing records, it is because that command terminates the session at the TCP level (closing the socket). Many versions of the MySQL ODBC driver (including 9.x) interpret an abrupt socket closure as an "End of Stream" rather than a failure, leading Power BI to believe it has finished importing all available data. To force Power BI to show a Data Source Error and prevent data loss, use one of the following methods that sends an explicit error packet before stopping: 1. Use

KILL QUERY

(Not

CONNECTION

) Instead of killing the connection, kill the specific query. This allows StarRocks to send a formal "Query Interrupted" error packet to the ODBC driver before the process stops. * Local Kill: On the FE node where the query is running:

sql
    KILL QUERY <ConnectionId>;

* Global Kill (StarRocks 3.1+): From any FE node using the Query UUID:

sql
    -- Find the QueryId (UUID)
    SHOW PROC '/global_current_queries';
    -- Kill it globally
    KILL QUERY '00000000-0000-0000-0000-000000000000';

Note: The UUID must be in single quotes. 2. Automate with Resource Groups (The "Hard Error" Method) The most reliable way to ensure Power BI fails (and doesn't just stop) is to let StarRocks' Resource Management handle the termination. When a query hits a limit, StarRocks returns a specific SQL error (e.g.,

exceed big query mem limit

), which the ODBC driver correctly identifies as a failure. * Assign the Power BI user to a Resource Group with a

mem_limit

or

cpu_core_limit

. * When the query is terminated by the system, it sends an

HY000

error packet, forcing Power BI to alert the user. 3. Driver Configuration Settings In your ODBC Data Source Administrator (64-bit), check the following settings for your DSN: * Check "Interactive Client": Sometimes enabling this ensures that the driver waits for more explicit status packets. * Switch to Driver 8.0.x: Many users in the community have reported that the 9.5 driver is less stable with Power BI's refresh mechanism than the older 8.0.28 or 8.0.33 Unicode drivers. If the 9.x driver continues to mask connection drops as successes, reverting to a stable 8.0.x version is a common fix. Summary of what to do: If you need to manually stop a query right now without silent data loss: 1. Run

SHOW PROC '/global_current_queries';

to get the QueryId (the long UUID). 2. Run

KILL QUERY '<QueryId>';

(ensure you use the UUID in quotes). This method is specifically designed to interrupt the execution while keeping the communication channel open long enough to report the failure to the driver. References • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • administration/management/resource_management/Query_management.md • administration/management/monitor_manage_big_queries.md References • administration/management/FE_configuration.md • faq/Sql_faq.md • administration/management/graceful_exit.md • faq/operation_maintenance_faq.md • data_source/datalake_faq.md • administration/management/resource_management/Query_management.md

@Rocky no even kill query does not throws error in bi

The fact that even

KILL QUERY

results in a "Success" message with missing records in Power BI (using MySQL ODBC 9.5 Unicode) confirms that the ODBC driver is swallowing the cancellation signal. In many 9.x versions of the MySQL ODBC driver, a "Query Interrupted" state is incorrectly treated as a "successful end-of-stream." To force Power BI to recognize this as an error and stop the refresh, you should use one of the following "Hard Failure" methods: 1. Force a "Hard" Error via Resource Groups Instead of manually killing the query, set a Resource Group limit that the query will hit. When StarRocks kills a query due to a resource limit (like memory or CPU), it sends a "Hard Error" (

HY000

) with a specific error message (e.g.,

exceed big query mem limit

). The ODBC driver usually cannot mask these as a "Success." 1. Create a restrictive group for the BI user:

sql
    CREATE RESOURCE GROUP pbi_kill_group
    TO (user='your_pbi_user')
    WITH (
        "big_query_mem_limit" = "1024" -- Set to 1KB to force an immediate crash
    );

2. Assign the user: Any query they run will now fail with a "Hard Error," which Power BI will display as a "Data Source Error" rather than a silent success. 2. Downgrade the ODBC Driver (Highly Recommended) The MySQL ODBC 9.x series is known to have compatibility issues with Power BI's error handling. The StarRocks community and Microsoft community generally recommend the 8.0.x series for production Power BI environments. * Target Version: Uninstall 9.5 and install MySQL ODBC 8.0.33 (Unicode) or 8.0.28. * Why: The 8.0.x drivers handle the TCP-level "Query Interrupted" packet more strictly, ensuring Power BI receives a non-zero exit code. 3. Check the

is_report_success

Variable There is a specific StarRocks session variable that can affect reporting. Ensure it is not accidentally set to

true

, which might influence how the FE handles the final status packet for specific connectors:

sql
SELECT @@is_report_success;
-- If it is 1, set it to 0
SET GLOBAL is_report_success = false;

4. Use the Global Kill with UUID If you haven't already, ensure you are killing the query using its UUID rather than its Connection ID. Killing by UUID is handled by the internal StarRocks coordinator and is more likely to propagate an error code than a local connection termination. 1. Find the `QueryId`:

SHOW PROC '/global_current_queries';

2. Kill it:

KILL QUERY 'your-uuid-here';

(include the single quotes). Summary Recommendation If manual kills are being masked as successes, the 9.5 driver is the root cause. Reverting to ODBC 8.0.33 Unicode is the most reliable long-term fix to ensure your BI reports don't silently display partial data. References • sql-reference/sql-statements/cluster-management/nodes_processes/KILL.md • administration/management/resource_management/resource_group.md • administration/management/monitor_manage_big_queries.md • sql-reference/System_variable.md References • unloading/Spark_connector.md • sql-reference/System_variable.md • administration/management/FE_configuration.md