Hackery

MySQL blocked hosts

Wed 31 Oct 2007

Here's an interesting one: one of my clients has been seeing mysql db connections from one of their app servers (and only one) being periodically locked out, with the following error message reported when attempting to connect:

Host _hostname_ is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'.

There's no indication in any of the database logs of anything untoward, or any connection errors at all, in fact. As a workaround, we've bumped up the max_connect_errors setting on the mysql instance, and haven't really had time to dig much further.

Till tonight, when I decided to figure out what was going on.

Turns out there's plenty of other people seeing this too, although MySQL seems to be in "it's not a bug, it's a feature" mode - see this bug report.

That thread helped clue me in, however. Turns out that mysql counts any connection to the database, even ones that don't attempt to make an actual database connection, as a connection error, but they only log ones that attempt to login. So there's a nice class of silent errors - and in fact, a nice DOS attack against MySQL - if you make standard TCP connections to mysql without logging in.

We, being clever and careful, were doing exactly that with nagios - making a simple TCP connection to port 3306 - in order to simply and cheaply check that mysql was listening on that port. Hmmmm.

Easy enough to remedy, of course, once you figure out what's going on. I even had a nice nagios plugin lying around to let me do more sophisticated database checks - check_db_query_rowcount - so just had to replace the simple check_tcp check with that, and all is right with the world.

But it's a plain and simple bug, and MySQL need to get it fixed. Personally I think a simple tcp connection should not count as a connection error at all without a login attempt (assuming it's not left half-open etc.). Alternatively, if you do want to count that as a connection error fine, but at least log some kind of error so the issue is discoverable and can be handled by someone.

Silent errors are deadly.