cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hike_N Frequent Visitor
Frequent Visitor

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

 

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?

29 REPLIES 29
Super User
Super User

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

Just to clarify, you are getting this error when you are trying to connect to your MySQL database from Power BI Desktop?


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Hike_N Frequent Visitor
Frequent Visitor

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

Hi smoupre

It happens that I am trying to refresh the data:

 

unnamed111.jpg

 

The error attached in a previous post
When I open the Query Editor

 

Super User
Super User

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

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator Eric_Zhang
Moderator

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


@Hike_N wrote:

Hi smoupre

It happens that I am trying to refresh the data:

 

unnamed111.jpg

 

The error attached in a previous post
When I open the Query Editor

 


@Hike_N

 

The MySQL Online book says

  • If the following error occurs, it means that mysqld has received many connection requests from the given host that were interrupted in the middle:
    Host 'host_name' is blocked because of many connection errors.
    Unblock with 'mysqladmin flush-hosts'
    The value of the max_connect_errors system variable determines how many successive interrupted connection requests are permitted. (See Section 6.1.4, “Server System Variables”.) After max_connect_errors failed requests without a successful connection, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command.

So in my opinion, the first thing before "unblock" is to analysis the failed connections, I don't know about MySQL but I think there should be any log feature indicating the failed connection details.

Do you connect to Amazon RDS via "getdata"->"database"->"MySQL" and and install a gateway? When saying refresh, if you schedule the refresh through a gateway, as per the bold part of the quoted, try to monitor the connections from the server where the gateway is hosted in your MySQL database. By the way, have you confirmed that the database credential is set up correctly? I doubt the invalid crendential keeps failing the connection to your RDS from gateway.

 

Anonymous
Not applicable

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

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?

Capture.PNG

viera00 Regular Visitor
Regular Visitor

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

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 ?

screenshot-mysql.jpgError at the service.

 

screenshot-mysql-2.jpgProcesses from the gateway

 

Any clues on how to solve it ?

 

Regards,

 

GV

German Viera
http://slidemodel.com/
JonnaGalloway Frequent Visitor
Frequent Visitor

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

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

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

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

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,287)