@Rocky we try to import data from starrocks to powerbi via mysql odbc driver and I see when it is on the finishline it starts the query on starrocks side all over again and powerbi just waiting for the import ://

The behavior you're describing—where StarRocks appears to restart the query just as it reaches the "finish line"—is typically caused by the MySQL ODBC driver or Power BI's internal timeout/retry logic. When the driver doesn't receive the full result set or metadata within a certain window, it may drop the connection and attempt a retry, which StarRocks sees as a new incoming query. To fix this, try the following steps: 1. Adjust MySQL ODBC Driver Settings The default "caching" behavior of the MySQL ODBC driver often causes issues with large result sets or long-running queries in Power BI. * Open the DSN Configuration for your StarRocks connection. * Navigate to the "Cursors/Results" tab (the location varies slightly by driver version). * Check "Don't Cache Results": This forces the driver to stream data directly to Power BI rather than trying to buffer the entire result set in local memory first. * Check "Limit column size to 65535": Power BI sometimes struggles with StarRocks

VARCHAR

or

STRING

columns that don't have a fixed length. * Check "Allow big results". 2. Use the Official StarRocks Power BI Connector Instead of a generic MySQL ODBC connection, use the dedicated StarRocks connector for Power BI. It is optimized for StarRocks' metadata and query execution. 1. Download the

StarRocks.mez

file from the official StarRocks website or GitHub. 2. Place it in

[Documents]\Power BI Desktop\Custom Connectors

. 3. Enable "Custom Connectors" in Power BI Desktop settings (Options and Settings > Options > Security > Data Extensions). 4. Restart Power BI and search for "StarRocks" in the "Get Data" menu. 3. StarRocks Session Variables If Power BI is failing because it expects certain metadata lengths, or the query is timing out on the StarRocks side, run these commands in StarRocks to apply them globally or to the user:

sql
-- Increase query timeout (default is 300s)
SET GLOBAL query_timeout = 3600; 

-- Help Power BI handle unknown string lengths
SET GLOBAL max_unknown_string_meta_length = 65535;

4. Check for Driver Version Mismatches There is a known stability issue with some newer versions of the MySQL ODBC driver. * Recommended Version: MySQL ODBC Connector 8.0.23 is often cited as the most stable version for StarRocks/Power BI integrations. If you are on a 9.x version or a very early 8.x version, consider downgrading or upgrading to 8.0.23. 5. Troubleshooting the "Restart" To confirm if it's a timeout, run

SHOW PROC '/current_queries';

in StarRocks while the import is running. If you see the

QueryId

change while Power BI is still "waiting," it confirms the client (Power BI) sent a new request because the previous one was interrupted. If your data volume is extremely large, consider using DirectQuery mode (if using the StarRocks Connector) rather than Import mode to avoid moving millions of rows into Power BI's memory. References • faq/Deploy_faq.md • faq/Others.md • integrations/BI_integrations/Rill.md • loading/Spark-connector-starrocks.md • integrations/BI_integrations/Tableau_Desktop.md • data_source/External_table.md • integrations/loading_tools/SMT.md • deployment/post_deployment_setup.md

@Rocky so whats the most stable version of mysql odbc driver for starrocks?

Based on documentation and common community experience, the most stable and recommended version of the MySQL ODBC driver for StarRocks (specifically when used with Power BI) is MySQL Connector/ODBC 8.0.23. Why 8.0.23? * Official Recommendation: Many StarRocks-related integration guides and partner documentations (such as CelerData/PhoenixAI) specifically point to 8.0.23 as the required version to avoid metadata and "enumeration" errors. * Regression Issues: Versions newer than 8.0.23 (such as 8.0.28+ or the 9.x series) have been reported to cause stability issues, including the "Object reference not set to an instance of an object" error during the final data load phase in Power BI. * Bitness: Always ensure the driver bitness matches your Power BI installation (usually 64-bit). Recommended Configuration Tips: If you must stay with the ODBC driver, ensure these settings are used in the DSN configuration: 1. Driver Type: Use the Unicode version of the driver, not the ANSI version. 2. Caching: Under the "Cursors/Results" tab, check "Don't Cache Results". This prevents the driver from trying to pull the entire dataset into local RAM before handing it to Power BI, which often causes the "restart" behavior you are seeing. 3. Connection String: If possible, add

tinyInt1isBit=false

to your connection parameters to avoid type mismatch errors with boolean/tinyint columns. The Better Alternative: StarRocks Custom Connector Rather than relying on the generic MySQL ODBC driver, it is highly recommended to use the official StarRocks Power BI Connector (

StarRocks.mez

). * It is specifically designed for StarRocks’ vectorized engine. * It handles StarRocks-specific metadata much better than the generic MySQL driver. * It supports DirectQuery mode, which allows you to query large datasets without importing them into Power BI memory. You can download the connector from the StarRocks Integrations page. References • faq/Deploy_faq.md • data_source/datalake_faq.md • faq/Others.md • data_source/feature-support-data-lake-analytics.md • loading/Spark-connector-starrocks.md • integrations/BI_integrations/Tableau_Desktop.md • cover_pages/bi_tools.mdx • sql-reference/sql-functions/utility-functions/version.md

@Rocky 8.0.23 has issues as well when query fails powerbi does not get that response correctly and still waiting

It is a known issue that even the "stable" MySQL ODBC 8.0.23 driver can exhibit this hanging behavior. This typically happens because of a mismatch between how StarRocks reports the query status/metadata and how the driver expects it—leading Power BI to believe data is still coming when the connection has actually failed or finished. To resolve this "infinite waiting" and the query restarting, try the following solutions in order: 1. The Best Solution: Official StarRocks Power BI Connector Stop using the generic MySQL ODBC driver and use the official

StarRocks.mez

connector. It is specifically built to handle StarRocks' vectorized metadata and prevents the "enumeration" and "restart" errors common with the MySQL driver. * Download: StarRocks official integrations page. * Installation: 1. Copy

StarRocks.mez

to

Documents\Power BI Desktop\Custom Connectors

. 2. In Power BI Desktop, go to File > Options and settings > Options > Security. 3. Under Data Extensions, select (Not Recommended) Allow any extension to load without validation. 4. Restart Power BI and search for "StarRocks" in Get Data. 2. Adjust StarRocks Metadata Length If you must use the ODBC driver, Power BI often hangs because StarRocks (v3.1+) returns

STRING

or

VARCHAR

columns without a fixed max length in the metadata. The driver waits for more data because it doesn't know where the "end" is. Run this command in StarRocks to provide a fallback length:

sql
-- Apply globally to help the driver understand column boundaries
SET GLOBAL max_unknown_string_meta_length = 65535;

3. Alternative Driver: MySQL .NET Connector If ODBC continues to fail, community members have reported better success using the MySQL Connector/NET (64-bit) instead of ODBC. * In Power BI, when you select Get Data > MySQL Database, it uses the .NET connector rather than the ODBC DSN. * Note: This may require installing the MySQL Connector/NET on your machine. 4. Critical ODBC Settings (If sticking with 8.0.23) Ensure these two settings are checked in your ODBC DSN configuration (under Cursors/Results): * Don't Cache Results: If this is unchecked, the driver tries to buffer the entire result set into your local RAM before giving it to Power BI. If your dataset is large, this will crash or timeout, and Power BI will trigger a "retry," causing the query to start over on the StarRocks side. * Treat TINYINT(1) as BIT: Uncheck this to avoid type conversion errors that cause silent failures. Summary Checklist Issue | Potential Fix Query restarts on StarRocks | Check "Don't Cache Results" in ODBC. Power BI hangs at the end | Run

SET GLOBAL max_unknown_string_meta_length = 65535;

Silent failure / Hang | Switch to the StarRocks.mez custom connector. If you still see the query "restarting," check the StarRocks

fe.audit.log

. If you see two identical queries from the same user within seconds, it confirms Power BI is timing out and retrying the request because it didn't receive a clear "Success" or "Error" packet. References • sql-reference/System_variable.md • knowledge/usage/best-practice.md • faq/Others.md • cover_pages/bi_tools.mdx • faq/table_design_faq.md • administration/management/BE_configuration.md • sql-reference/data-types/string-type/STRING.md

@Rocky so you are saying that mysql connector from powerbi can be more stable than odbc?

Yes, the native MySQL Database connector in Power BI is generally more stable than the generic ODBC driver for several reasons: 1. Architecture: The native connector uses the MySQL Connector/NET, which communicates more efficiently with the MySQL protocol than the ODBC layer. It bypasses the complex "DSN" configuration and buffering issues inherent in ODBC. 2. Metadata Handling: A common cause of Power BI hanging or restarting queries is "metadata enumeration." The native connector is better at identifying column types and lengths, whereas the ODBC driver often gets stuck trying to determine the size of StarRocks' dynamic

STRING

or

VARCHAR

types. 3. Automatic Streaming: The native connector is optimized to stream data directly into Power BI's memory. Generic ODBC drivers often try to "cache" or "buffer" the entire result set locally before handing it to Power BI, leading to the timeouts and "restarts" you are seeing. ────────── Which one should you use? Ranked from most stable to least stable for StarRocks: *1. StarRocks Custom Connector (

StarRocks.mez

) — Recommended* This is the best option. It is a "tailor-made suit" for StarRocks. * Why: It is specifically built to handle StarRocks' vectorized engine and metadata quirks. It also supports DirectQuery mode much more reliably than the generic MySQL paths. * How: Download the

.mez

file from the StarRocks Integrations page and place it in your

Documents\Power BI Desktop\Custom Connectors

folder. 2. Native Power BI "MySQL Database" Connector If you cannot use custom connectors, this is the next best thing. * Requirement: You must install the MySQL Connector/NET (64-bit) on your machine for Power BI to use it. * Stability: High. It avoids the "Don't Cache Results" and "Enumeration" errors that plague the ODBC driver. 3. MySQL ODBC Driver (What you are currently using) Generally the least stable for large StarRocks imports. * Why: Requires manual tuning of "Don't Cache Results," "Unicode vs ANSI," and "TINYINT(1) as BIT" settings just to function correctly. Even then, version mismatches (like using 8.0.28+ or 9.x) frequently cause the "finish line" crashes you described. Troubleshooting the "Waiting/Restart" Issue If you switch to the Native MySQL Connector and still see issues, run this command in StarRocks to help Power BI understand the data it is receiving:

sql
-- This tells the connector a default length for columns where the length is unknown.
SET GLOBAL max_unknown_string_meta_length = 65535;

Recommendation: Try the StarRocks.mez connector first. It was created specifically to solve the exact "hanging at the end of import" and "query restart" problems found in generic MySQL drivers. References • integrations/BI_integrations/Rill.md • faq/Deploy_faq.md • unloading/arrow_flight.md • faq/Others.md • data_source/feature-support-data-lake-analytics.md