cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gman Frequent Visitor
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
Moderator Eric_Zhang
Moderator

Re: mySQL connections staying open or failing


@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.

viera00 Regular Visitor
Regular Visitor

Re: mySQL connections staying open or failing

Hi @gman

 

I'm having the same exact problem.

 

Where you able to solve it ?

 

Regards,

 

GV

German Viera
http://slidemodel.com/
Drshoe28 New Member
New Member

Re: mySQL connections staying open or failing

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

viera00 Regular Visitor
Regular Visitor

Re: mySQL connections staying open or failing

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/
afordyce Frequent Visitor
Frequent Visitor

Re: mySQL connections staying open or failing

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.