Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hike_N
Regular 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?

30 REPLIES 30

Ok doki, just took a screenshot below:

Untitled-1.jpg

Anonymous
Not applicable

In this case as previously posted in the thread, you should ask your admins to run the mysqladmin flush-hosts command from the MySQL administration shell. If the problem persists you can try asking them to increase the max_connect_errors variable for that MySQL instance.

I'm kind of repeating what Eric_Zhang already said, but hopefully this is clearer.

Hi,

 

Apologies i must have missed that. I tried both solutions there, the first one briefly showed as 'connected' on Power BI online, but immediatly gave me the error on data refresh. Same error as previous.

 

I've uninstalled the gateway and on re-install, you get a big message saying: "A Gateway acts as a bridge between on-premises data (not in the cloud), and Power BI, PowerApps, Logic Apps, and Microsoft Flow."

 

A little contrasting to the support documents online saying it connects to MySQL, but by the sounds of it - not any cloud MySQL?

 

Thank you

Hi

 

I was not able to solve the problem (with the Enterprise Gateway) but i decided to uninstall it and use de "Personal Gateway". Now the refresh is working fine. The error stoped. Analyzing the traffic, it seams that the personal gateway "closes" the connection appropriatelly (and the enterprise gateway dont).

 

It is worth to try. Let me know if it worked for you.

 

Regards,

 

GV

German Viera
http://slidemodel.com/

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

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.Error at the service.

 

Processes from the gatewayProcesses from the gateway

 

Any clues on how to solve it ?

 

Regards,

 

GV

German Viera
http://slidemodel.com/
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.