MySQL - Connection blocked because of many connection errors - How to find root cause?

Question

I am developing a web application and am running into the dreaded host xxx blocked because of many connection errors message. There have been numerous questions on SO pertaining to this error and common wisdom is to flush hosts to clear the error and then bump up MySQL's max_connections setting in order to avoid this issue in the future. A summary of the issue from the MySQL manual is as follows:

The number of interrupted connect requests allowed is determined by the value of the max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement. See Section 5.1.3, "Server System Variables".

I'd like to go a step further and determine the root cause of the issue for my application. I am relatively new to MySQL and would like to know what is the best way to trap such errors - does MySQL provide any logs or information as to which connections or queries failed or are the any tracing tools I can use to ascertain this information?

Thank you

JP

• Havenard: Are you using MySQLi? Because when not properly configured, MySQLi will create and leave open a bunch of connections with the MySQL server instead of reusing a single one as is intented.
• JP.: No, I'm using vanilla MySQL, connecting from an ASP.NET website
• Havenard: You should add details on how exactly you are using it.

Answer 1

If you're using MySQL >= 5.6, then you can potentially use the information I did to resolve my situation without increasing max_connect_errors.

Run select * from performance_schema.host_cache; chances are, you'll have records for the host that is experiencing problems. you can use this page to figure out what each column means. In my case, I had EC2 instances communicating across a VPC Peering connection and my databases were failing the hosts with COUNT_NAMEINFO_PERMANENT_ERRORS, so I added skip-name-resolve to my my.cnf, restarted the instance, and everything worked great.

Answer 2

The advice you've seen about max_connections is a distraction from the issue you're seeing, because it has nothing to do with connection errors.

You can log the failed connections to the mysql error log by issuing this:

SET GLOBAL log_warnings = 2;

You can also make this a permanent setting in my.cnf so that it persists after a restart.

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings

Answer 3

Starting with MySQL 5.6, the content of the server internal 'host cache' can be inspected in the performance schema.

http://dev.mysql.com/doc/refman/5.6/en/host-cache-table.html

For every possible root cause, a dedicated column counts the number of error seen.

Answer 4

Give a try by executing the same mysqladmin flush-hosts

pt-heartbeat showing "Host is blocked because of many connection errors"...but connections are well under limit

Answer 5

If connections are left open and not killed by application they would also use up resources but would reach a limit of you defined max connections.

mysql> show processlist;

This command will show you if there are dead connections with time specified, you can kill those manually by kill command followed by id or use this script below to kill them with a cronjob.

SECONDS_TOO_LONG=500
QUERIES_RUNNING_TOO_LONG=`/path/mysql -u user -p'pass' -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
 KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
 /path/mysql -u user -p'pass' -ANe"${KILLPROC_SQLSTMT}" | /path/mysql -u user -p'pass'
fi;

But you must fix your application as this is a temporary fix.

• Masood Alam: you did not mentioned anything like what would log warnings do to resolve the situation. So i think you are making the guy more confused and did not answered in resolving the max connect error problem.