Refresh Power bi service gateway ODBC mysql issue

[Simomazzi]

Hi,

I am experiencing a recurring issue with Power BI Service while refreshing a dataset connected via an on-premises data gateway using an ODBC connection to MySQL. The refresh process fails after approximately 2 hours with an "Out of Memory" error:

ERROR [HY000] [MySQL][ODBC 9.2(w) Driver][mysqld-8.0.41] MySQL client ran out of memory

Interestingly, the same dataset refreshes without any problems in Power BI Desktop, which suggests that the issue might be related to the gateway configuration or resource limitations on Power BI Service.

The RAM capacity is the same in both cases.

Could you please assist me in diagnosing and resolving this issue? 

Thanks a lot.

[v-kpoloju-msft · Accepted solution]

Hi @Simomazzi,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @nilendraFabric, and @GilbertQ, for your inputs on this issue.

After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

The MySQL ODBC driver has a default fetch buffer size and increasing it can help prevent memory-related issues. To update: Open ODBC Data Source Administrator (64-bit) on the gateway machine, select the MySQL ODBC connection, click Configure, go to the Details tab, locate “Fetch Buffer Size,” and increase it (e.g., try 256 MB or 512 MB). Click OK and restart the gateway service.

Since the query works fine in Power BI Desktop but fails in the Service, it may be retrieving too much data or using inefficient queries. Possible optimizations: Filter data at the source by modifying the query in Power Query to reduce the number of rows/columns retrieved, optimize indexing in MySQL to ensure proper indexes on tables involved in the query, and use SQL views to create optimized views in MySQL instead of pulling large raw tables into Power BI.

Power BI Service runs queries differently than Desktop, which may cause the gateway machine to run out of memory. To diagnose: Open Task Manager and check RAM & CPU usage when refresh is running. If memory usage is high, consider increasing RAM or running the gateway on a more powerful server.

Sometimes, the Power BI gateway uses limited resources for query execution. To adjust: Open Power BI Service, go to Manage Gateways, select your gateway cluster, click Settings, and under Connections, try increasing the Maximum Concurrent Queries (e.g., from 2 to 4). Restart the gateway service.

Ensure you are using the latest MySQL ODBC driver (9.2 or later) and the latest version of the Power BI on-premises gateway. If the dataset is large, using Import Mode in Power BI might cause high memory consumption. Switching to Direct Query can reduce memory usage since it queries the database in real-time rather than loading all data into memory.

Refer to the below link:
Troubleshoot refresh scenarios - Power BI | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Simomazzi,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @Simomazzi,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @Simomazzi,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[GilbertQ]

Hi @Simomazzi 

If you're using power BI pro, you have a two-hour query time limit, and that could be why you're getting the error. It might be not entirely accurate with regards to the area you're receiving, but that could be why it is not working.

A potential solution is to ensure that your query takes less than two hours to complete.

[nilendraFabric]

Hello @Simomazzi 

Power BI Desktop does not use the gateway when refreshing data. Instead, it connects directly to the data source using the credentials and connection settings you configure.

Desktop Uses local machine resources

Service Uses gateway machine + cloud service limits

Ensure that query folding is enabled in Power Query to push transformations to MySQL instead of processing them in Power BI.

Restart the gateway service

Ensure you are using a compatible 64-bit MySQL ODBC driver on the gateway machine.