Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Apr 16 '13 at 17:59

This question came from our site for professional and enthusiast programmers.

    
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. –  Havenard Apr 16 '13 at 15:33
    
No, I'm using vanilla MySQL, connecting from an ASP.NET website –  JP. Apr 16 '13 at 15:36
    
You should add details on how exactly you are using it. –  Havenard Apr 16 '13 at 16:07

2 Answers 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

share|improve this answer

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.

share|improve this answer
    
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. –  Masood Alam Apr 19 '13 at 19:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.