MySQl queries in Power BI - Connection must be valid and open error

[murugesh_c]

Hello everyone,

I am trying to import data through MySQL queries. The query has Select, Inner Join, Left Join and Where clause. When I refresh, the query runs very slow and fetches less than 2000 records in a minute and after 10-12 minutes I am getting the below error.

I have connected to the server through VPN and the connection is stable only. Following by the above error message, the below load window show like this.

I tried many options and going clueless how to fix this one. Can any of you help me with the solution?

Thanks

[v-yanjiang-msft]

Hi @murugesh_c ,

Does your problem solved by @rubayatyasmin 's solution? If so, please kindly accept the helpful reply as solution. Otherwise you can try the following suggestion:

• The MySQL Connector/NET package is not installed or not compatible with Power BI. You need to install version 8.0.26 of this package on your machine and on the gateway machine if you are using one.
• The connection is not encrypted and Power BI is trying to use an encrypted connection. You need to clear the Use Encrypted Connection option in the MySQL database dialog.
• The query is too complex or contains unsupported statements such as INSERT. You need to simplify the query or use only SELECT statements in Power BI.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rubayatyasmin · Accepted solution]

Hi, @murugesh_c 

Your error typically means the connection to your MySQL server is being disrupted. This can be due to:

1. Network instability, even with a VPN
2. MySQL Server timeout (set by wait_timeout)
3. Resource limitation on your server or local machine.

Possible solutions include:

1. Optimizing your query to fetch less data or process faster
2. Increasing MySQL wait_timeout
3. Checking your VPN settings for timeouts
4. Monitoring and improving system resource usage.

if these doesn't help, try creating the connection from the scratch. 

refer: https://community.fabric.microsoft.com/t5/Desktop/Power-BI-MySQL-Unexpected-error-Connection-must-be...

[murugesh_c]

In addition to @rubys solution, I set the Command timeout to 90 minutes while importing data as shown in the screenshot below.