What are "many successive interrupted connection requests" in MySQL?

Question

I regularly have the following error:

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1129] Host 'MY SERVER' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

It is easy to solve the problem with a regular (like crontab) mysqladmin flush-hosts command or increasing the max_connect_errors system variable, as written here.

BUT ! What are "many successive interrupted connection requests", why is this happening?

I'd rather prevent the problem upstream, rather than correcting blocking.

MySQL version : 5.5.12. I'm using Zend Framework 1.11.10 and Doctrine 2.1.6.

There are no mysql_close() nor mysqli_close() in my PHP Code.

max_connect_errors has the default value, 10, and I don't want to increase it yet, I want to understand why I've got the errors. I use a cron, every 5 minutes which does a mysqladmin flush-hosts command.

Answer 1

This response is by design as a security measure, and is the result of reaching the max_connection_errors value for mysql. Here's a link Oracle provides which details most of the possible causes and solutions.

Ultimately this means that there are so many successive connection failures that MySql stops responding to connection attempts.

I use a cron, every 5 minutes which does a mysqladmin flush-hosts command.

As you are reaching this limit so quickly, there are only a few likely culprits:

1. Server is not correctly configured to use PDO.
2. Running code includes very frequently creating new connections.
   • Results in quickly reaching the max_connections value, causing all subsequent connection attempts to fail... thus quickly reaching the max_connection_errors limit.
3. Code is hitting an infinite loop, or cascading failure.
   • Obvious possibility, but must be mentioned.
   • (i.e: pageA calls pageB and pageC, and pageC calls PageA)
4. PDO is running fine, but some scripts take a long time to run, or never end.
   • Easiest way to catch this is turn down the max_execution_time.

It is likely that whatever the case, this will be difficult to track down.

1. Log a stack-trace of every mysql connection attempt to find what code is causing this.
2. Check the mysql.err logfile

While PDO does not require explicitly closing mysql connections, for cases like this there's a few practices that can prevent such ServerAdmin hunts.

• Always explicitly close mysql connections.
  • Build a simple Class to handle all connections. Open, return array, close.
  • The only time you need to keep a connection open is for cursors.
• Always define connection arguments in one and only one file included everywhere it is needed.
• Never increase max_execution_time unless you know you need it and you know the server can handle it. IF you need it, explicitly increase the value only for the script that needs it. php.net/manual/en/function.set-time-limit.php
  • If you increase max_execution_time, increase max_connections.

dev.mysql.com/doc/refman/5.0/en/cursors.html