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=-2147467259I'm using amazon rds. I did flush my hosts and even rebooted the rds instance.What else can I do to fix the problem?

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.

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.

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?

Hi Viera, Thanks for your response. I got to the bottom of the problem so I best mark this complete. Basically the MySQL is hosted through Go Daddy and the spec on data gateways (for automatic data refresh) if it's Microsoft Azure hosted MySQL - which is a massive shame. On another note I had personal gateway set up to attempt to refresh this data from hosted MySQL, but I also had on-premise SQL data which uses the enterprise gateway and they were conflicting just like you said. Thanks very much

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

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

32 Replies

MHongisto
Frequent Visitor
8 years ago
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.
Anonymous
Not applicable
10 years ago
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?
- viera00
  Helper II
  9 years ago
  Hello Everyone,
  
  I'm having the same exact problem. When refreshing from powerbi desktop, it works, but from the gateway, it crashes.
  
  My perception is that the gateway is opening one DB connection per mysql table, and the mysql server do not likes that.
  
  Is there a way to avoid this 1 connection per table ?
  Error at the service.
  
  Processes from the gateway
  
  Any clues on how to solve it ?
  
  Regards,
  
  GV
- JonnaGalloway
  Regular Visitor
  9 years ago
  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
    Not applicable
    9 years ago
    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.
andrew_adcock
New Member
7 years ago
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
  Frequent Visitor
  7 years ago
  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.
  - andrew_adcock
    New Member
    7 years ago
    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.
Greg_Deckler
Community Champion
10 years ago
Just to clarify, you are getting this error when you are trying to connect to your MySQL database from Power BI Desktop?
- Hike_N
  Regular Visitor
  10 years ago
  Hi smoupre
  It happens that I am trying to refresh the data:
  
  The error attached in a previous post
  When I open the Query Editor
  - Greg_Deckler
    Community Champion
    10 years ago
    Turn on Tracing by going to File | Options and settings | Options | Diagnostics and click Tracing checkbox. Generate the error and then click the smiley face icon to send a frown and include screen shot, trace logs, etc.
chaitanyagandhi
Regular Visitor
9 years ago
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
  Not applicable
  9 years ago
  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
    Not applicable
    9 years ago
    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!
rezalty
Regular Visitor
8 years ago
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
  Advocate II
  7 years ago
  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?
  - jmcclure
    Frequent Visitor
    7 years ago
    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.
jasiluka12
Helper I
1 year ago
we are in 2025 and still facing this issue. Unbelievable.
jasiluka12
Helper I
1 year ago
anyone found a long term fix to this? flushing hosts daily is not ideal.