DataSource.Error: MySQL: Host is blocked because of many connection errors

[Hike_N]

I'm trying to connect to Power BI and receiving the following error.

DataSource.Error: MySQL: Host 'xxx.xx.xxx.xx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Details:
    DataSourceKind=MySql
    DataSourcePath=prod-replication.************.eu-west-1.rds.amazonaws.com;***********
    Message=Host 'xxx.xx.xxx.xx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
    ErrorCode=-2147467259

I'm using amazon rds. I did flush my hosts and even rebooted the rds instance.

What else can I do to fix the problem?

[andrew_adcock]

Hi All, 

Has anyone found a solution to this...

I can refresh once, but the second refresh always fails... until i flush-hosts, then i get one more refresh.. 

I really dont want to increase the Maximum connection errors variable. 

[jmcclure]

Hi Andrew, 

We went ahead and throttled up our max_error_connects.... and its very much not ideal. 

Out of curiosity what MySQL version are you running?  I am starting to wonder if this is a legacy MySQL version problem in particular, as I would expect there to be more people experiencing this issue given the pervasive use of MySQL for data warehousing. 

My organization is (painfully) still on MySQL 5.5.  We are upgrading to 8.0 in the next two weeks, afterwhich ill check back here to let you know if upgrading was a solution in any way. 

Good luck to you, and hopefully we hear from MS soon :/.

Best, 

John Mc.

[Anonymous]

@jmcclure I was just wondering if you had any luck switching to MySQL 8.0. I have been having this issue for some time now with MySQL 5.5 and nothing has worked! Thanks!

@andrew_adcock did changing your connector to an older version help at all? I would prefer not to do this but will do so if it's the only solution. Thanks!

[andrew_adcock]

Hi John, Thanks for the fast reply.

We are also on MYSQL 5.5.60

Maybe thats the reason because as you say it should be affecting more people. 

Maybe I need to see if there is an older SQL Connector which would be more stable. Sadly i cannot upgrade. 

I logged a call with Microsoft and they spent 2 hours changing settings and gateways, but in the end no solution... Would be great to see if upgrading to SQL 8.0 fixes the issue.  

[herbemischung]

I had the same issue for a very long time now and (in my case) it turned out, that I had to update my MySQL connector. Everything used to work fine until (without my knowledge) one of our MySQL sources was updated. Since updating the connector everything okay again. 

[rezalty]

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.

[duggy]

Hi,

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?

[JohnsonWesleyT]

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?

[jmcclure]

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. 

[MHongisto]

We had this very same issue with connections errors on mysql. We made the unadvised change to max_connection_errors to get to refrsehing going. Fixing this should be high on the priority list, as there must be many mysql users with this issue.

[chaitanyagandhi]

Any solution to it. I cannot flush everytime it does autorun on schedule refresh. If this has to be done, it does not lower the burden. Please someone suggest solution on this.

[Anonymous]

I have contacted Microsoft Support about thsi issue and they said that they are aware of it, however they do not have a solution.

What we did internally is set up a periodic job to flush the hosts of the server automatically. Did not have any issues since.

[Anonymous]

Hi everybody,

I'm having the same problem that It's expose on this post: every few days my online reports give me this error when refresh.

I have some PowerBI online dashboards conected to a mysql db by Personal Gateway.

Any news about?

Thanks!

[pbiuser31]

we're experiencing same issue using the On-premise data gateway when trying to sync from a local MySQL database.

We used some 3rd party software to monitor the connections of the MySQL database and what we noticed is that every time the On-prem data gateway attempted a sync, it would log in correctly with the configured Power BI user, but also would make an unauthenticated user attempt.

Because each unauthenticated user attempt counts towards the MySQL connection error, that's what will eventually cause MySQL to stop accepting any connection and give the error to FLUSH HOSTS.

A dangerous and temporary fix is to change the max connection error flag on the MySQL database to 10,000... but this is far from ideal...

[domaky]

Hi all,

I see the last entry is already a few months old. Did anybody find a fix for the problem? Because I encounter exactly the same issue when connecting to our (on premise) mysql database. And I agree : setting the max connection error flag to 10.000 is not a good solution. 

So if you happen to know a better way to solve the problem, your help would be greatly appriciated.

Thanks,

Martin

[Anonymous]

I'm having the same exact problem. The connection starts working again after I ask the admins to flush the hosts, but now it's happening once every few days (I'm sure the credentials are correct). I'll have them check out the logs, but maybe there is something on the Power BI side that I could do?

[JonnaGalloway]

Hi Sarunaskas,

Did you manage to resolve this at all?

I'm connecting to hosted MySQL through Power BI desktop which works perfect, but publishing to Power BI on a scheduled refresh is nothing but a nightmare at the moment - having the same issue as below.

Thanks

[Anonymous]

Hi!

I asked the sys admins to increase the MySQL server connection threshold, so didn't have any problems ever since.

Make sure you have plenty of RAM and a powerful CPU on the server that has Enterprise gateway running. After I monitored resource usage, it was apparent that it was struggling with RAM, so that could be an option you could check.

[JonnaGalloway]

Hi,

Thanks for the prompt response!

Ah right. I have Enterprice Gateway running on a virtual machine sat on our main server, the virtual machine has 8GB RAM (showing 6GB available) and is always online.

Just to check, I need the enterprise gateway to connect to:

* hosted MySQL on VPS (data pulled from multiple CRM sources)

* on-premise SQL (Sage 200 Manufacturing)

Like I say, I can connect to both sources no issues, just getting the refresh online connecting through the web seems to be the issue.

Help appreciated in advance

[Anonymous]

So what error message, exactly, are you receiving?