Forum Discussion
DataSource.Error: MySQL: Host is blocked because of many connection errors
I just encountered this issue as well. It started yesterday even though there were no changes to the datamodel done in a while...
Does anyone know of a better solution than just raising the param? One of our datasets causes the 'sum_connection_error' value to rise by +-30 every time.... Our limit is set to 100 so you can see the problem... (this can be raised but still...) (***part incorrect, see edit***)
I just installed the latest gateway to see if this would solve the issue but no luck there.
Reading the other responses here it does not seem to be something wrong with the config or dataset?
---------------------------------------------------------------------------------------
EDIT: I did a few refreshes (both manual and scheduled) and noticed a few things.
The sum_connect_errors value (which is the one giving problems when exeding the standard 100 limit) often resets. I think when a 'good connection' gets made by the 'host' => staggering the refreshes in time seems to help avoid crossing this limit since there are not 100's of requests being sent at once.
Not every refresh causes the same number of 'error connections'. One refresh cased 52, another 24, and so on. This can also explain why I never got this issue before.
I now increased the max connection error limit to 300 to see if this avoids any issue even when refreshing everything together.
You can monitor these values in the 'host_cache' table in the 'performance_schema' of mySQL
Changing the max_connect_errors is done by: 'SET GLOBAL max_connect_errors=999999' and choosing your desired value.
setting it to 10000 is an easy fix, but might not be needed I sugest experimenting with it.
Advocate IIHi,
Has anyone found decent solution for this problem? From a security perspective it isnt recommended to have too many max_conect_errors. The way around is to set up a scheduled task to flush, but again seems to be a patch then a fix. Any recommendations? Possibly a different more resilient gateway?
Hi Everyone,
I am about 3 weeks into setting up an Executive deployment of Power BI, and the Data Gateway bug to MySQL is absolutely going to kill this project. I was amazed to see how long this issue has been reported, yet not addressed. It seems the data gateway is in a perpetual state of Beta testing.
It has been 2 years now Microsoft - what is the deal with getting this fixed? It should be relatively easy. Please debug the unauthenticated user issue asap or you will continue losing the business of everyone who keeps data warehouses in MySQL.
We're hitting the same issue with dataset refreshing from app.powerbi.com, through an on-premises gateway and into our MySQL database. We've increased the max_connection_errors and it doesn't help. It seems that Power BI sometimes insists on trying to connect without credentials and we get the "Unauthenticated User" connections on the MySQL side. We usually end up with 6 or more and then the dataset refresh fails. We'll try the on-demand refresh repeatedly and continue to see 1 or 2 authenticated connections with the correct credentials, and a smattering of "Unauthenticated User" connections as well. Again, the refresh fails.
Eventually, after attemping the on-demand refresh multiple times, we'll finally see ONLY authenticated connections on the MySQL side and no "Unauthenticated User" connections. Only then does the refresh completely successfully. And, just to clarify, this is with zero changes to our Power BI settings, Gateway, credentials, flushing of MySQL hosts, etc. It's stricly just retrying over and over until Power BI finally stops sending unauthenticated requests.
I definitely feel like this is an issue within PowerBI itself and it's causing us daily frustartion. Would appreciate any additional ideas. Someone mention turning on tracing. Is that done in Power BI desktop or via the cloud app?
