I am a web developer, born in PERU
and now living in PDX.
My platform of choice is CFML
but I'm always open to learn anything. I currently work for Mentor Graphics.
I also do freelance web development and have a few open source projects that I maintain.
Getting MySQLNonTransientConnectionException errors? Then Your MySQL Server Might Be Angry At You!
October 28, 2010
Today I experienced one of those errors that are not related to the actual stuff that you are working on, but because of it you can't work on anything else. The error I was getting was:
Could not create connection to database server.
Attempted reconnect 3 times. Giving up.
This started happening while I was testing the DSN creation feature on my employer's website. You know the drill, trying different things, run again, put the wrong settings, run again, put the right settings, run again, and so on; until at one point I started getting the above error.
No problem, I thought, lets restart Tomcat/ColdFusion.... Nope, same error... Let's check with the other devs to see if they have the same problem... Nope, everyone is fine.... Let's manually kill the connections from my host... Nope, same error... Let's drop the database and create it again.... Nope, same error... Let's check the number of connections on the MySQL server.... Nope, they are fine...
The only thing I couldn't do or even try was restarting the MySQL server since it is shared by other users and applications. It was "very" frustrating.
So after a couple of hours of trying different things, I fired up a terminal and tried this:
ERROR 1129 (HY000): Host '<my_host>' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'
Lo and behold, finally some light into the issue! The MySQL server was actually blocking my host from connecting to it. I imagine that this was because of the repeated errors while toying with the create dsn functionality. Anyway, after logging into the server from another host and running that command (mysqladmin flush-hosts) I was able to connect again with no problems.
So there you have, if you see that error, it might be that your MySQL server is not liking you at the moment :)
Rafael Ulloa ...this has happened to me before. brought me a lot of headaches. Very useful info indeed, thanks for sharing Oscar :)
Wandile ...It will do that if the login server host in MySQL is different in your code.. ensure it is the same.. ie: if you log on with 'localhost', use that all the time or 127.0.0.1
Terry ...Hello, I am having the same problem so I want to run $ mysql -h <the_host_name> -u <the_user> -p and see what happens... Could you tell me what should be in the <my_host> section? If it should be the mysql server, is there a way to know the host name for mysql server? Thanks.
Oscar Arevalo ...Terry, that depends on how your application is connecting to the DB. If you are using ColdFusion, then you can get the hostname from the datasource definition.
Mohammad Faisal ...I'm trying to install jadasite from http://jadasite.com on my localhost which is using MySQL database.
I'm unable to solve the issue due to the limited knowledge in MySQL