Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gman
Frequent Visitor

mySQL connections staying open or failing

Hi,

 

I keep getting many connectoins error and being locked out and needing to flush hosts when accesssing my mySQL server and it looks like PowerBI is leaving connections open or accruing a bunch of failed connections. 

 

I currently have PowerBI sceduled with the enterprise gateway and dashboard are refreshed throughout the day.

 

Is this an issue I can fix on my end

 

Cheers,

 

Grant 

5 REPLIES 5
afordyce
New Member

I know this is an old thread, but I'll post just in case someone stumbles across this like I have while searching for a fix to this problem. There's not a lot of information about this issue, I've been experiencing the same problem where the Aborted_connections value increments over time as the web service refreshes reports, eventually hitting the top and blocking the host. 

 

It doesn't appear to be related to the length of time the connection is open for, because the aborted_connections value increments almost instantly when the web service starts a refresh. From what I can read around the place it appears that the Power BI web service, or the gateway makes one or more unauthenticated connection attempts to the MySQL server for every table that it queries. I have a report which queries nine tables and every time it refreshes itself the Aborted_connections value goes up by 54!

 

Microsoft is apparently aware of this problem but there is not a fix on the horizon any time soon. Some people have suggested just setting the global max_connection_errors variable to some large number but this is hardly a good fix, in fact it only delays this problem from manifesting in the hope that the hosts table will be flushed before that number is reached.

 

I found some help in this thread here:

https://community.powerbi.com/t5/Desktop/DataSource-Error-MySQL-Host-is-blocked-because-of-many/td-p...

 

To get around this issue I've slightly increased the number of connection attempts before MySQL blocks a host and an automated task that just flushes the hosts table periodically, this is less than the ideal solution, however it is effective.

Drshoe28
New Member

I'm experiencing the same exact thing. Anyone find a solution?

Hi,

 

I uninstalled the "enterprise gateway" and replaced it with the "Persona Gateway".

 

Now the refresh works fine. It seams (just my hipothesis) that the enterprise gateway closes connections incorrectly, and the personal dont.

 

It is worth to try.

 

Let me know if you have success with this option.

 

Regards,

 

GV

German Viera
http://slidemodel.com/
viera00
Helper II
Helper II

Hi @gman

 

I'm having the same exact problem.

 

Where you able to solve it ?

 

Regards,

 

GV

German Viera
http://slidemodel.com/
Eric_Zhang
Employee
Employee


@gman wrote:

Hi,

 

I keep getting many connectoins error and being locked out and needing to flush hosts when accesssing my mySQL server and it looks like PowerBI is leaving connections open or accruing a bunch of failed connections

 

I currently have PowerBI sceduled with the enterprise gateway and dashboard are refreshed throughout the day.

 



Before everything, make sure your database and driver meet the requirement, check Power BI Data Source Prerequisites.

 

May I know where did you observe that bold part? Based on my test and observation(command "show processlist"), when refreshing the dataset of MySQL from PBI Service, connections would be established from Gateway and terminated after sleeping for about 300 seconds. I don't see any connection failed or left open for very long time. So can you confirm what application are the problematic connections from?

 

If those connections are indeed from the gateway, try to restart or re-install it. If re-installation doesn't work, you may have to submit a support ticket to microsoft. For more gateway troubleshooting, click here.

 

By the way, google tells that connections can be timed out by some server options in MySQL, check whether this link would work or not.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors