MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability

MySQLmysql-5.6

Lately the server's been going a bit haywire and we've spent several weeks chasing down leads to no avail. All we can tell is that when the websites go down (and come back up, and go down, and come back up…) the aborted_connects MySQL variable starts increasing somewhat quickly. The only way we've been able to restore order has been to restart the server. After that, it can take as little as an hour or as long as a few days, but the problem always comes cropping up again. This began immediately after upgrading from 5.1 to 5.6.

I've looked through the log files, specifically for threads which have a Connect logged, but no corresponding Quit, and I've found something. The culprit appears to be the following two lines:

43375 Connect    Access denied for user 'test'@'ch.websitepulse.net' (using password: NO)
43375 Connect    test@ch.websitepulse.net as  on

I assume that "websitepulse" is a service we use to monitor the database and make sure it's up, but the only guy who would know for sure isn't here today. We likely knowingly use (and maybe pay for) this service.

Throughout the log file, there are a good number of lines pretty much identical to the two above, and the thread has no corresponding quit message. It would seem that the first line, access being denied, would indicate that there wasn't even a successful connection established, so there would be no reason for a Quit. The second line, however, has me wondering. It appears that despite access being denied, it still connects somehow. No queries are ever executed, and it's always just these same two lines.

The two spaces on the second line between "as" and "on" in the second line are no mistake. There is nothing there, or after "on", as I suspect there should be. Our database guy said that the number of aborted connections corresponded to the number of connections by user NULL@NULL. The as <blank> on <blank> would seem to indicate that this user is our NULL.

Does this sound right to anyone? If so, how might we go about fixing it? I'm really in the dark, and Googling it hasn't turned up anything promising.

Thanks so much.

Best Answer

Sometimes, running FLUSH HOSTS; will clear up the host cache when you can no longer connect. Why ?

According to the MySQL Documentation on FLUSH HOSTS

Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

When you restart mysql, it clears the host cache in a very heavy-handed manner. Therefore, to apply some preventative to this problem, you should be setting max_connect_errors to 1000000000000 (Yes, that's a trillion). Since max_connect_errors is dynamic, a mysql restart is not needed. Just run

mysql> SET GLOBAL max_connect_errors = 1000000000000;

then add the variable to my.cnf like this

[mysqld]
max_connect_errors = 1000000000000

Give it a Try !!!

Related Question