Todd's MySQL Blog

Tiny tidbits of trivia from Todd

MySQL 5.6 users – prevent host blocked errors

The much-improved PERFORMANCE_SCHEMA in MySQL 5.6 provides visibility into MySQL’s host cache, including the ability to monitor for impending blocked hosts.  You can do this with the following query:

mysql> SELECT
    ->  ip,
    ->  host,
    ->  host_validated,
    ->  sum_connect_errors
    -> FROM performance_schema.host_cache\G
*************************** 1. row ***************************
                ip: 192.168.2.4
              host: TFARMER-MYSQL.wh.oracle.com
    host_validated: YES
sum_connect_errors: 3
1 row in set (0.02 sec)

That’s helpful information, and allows DBAs to identify problematic hosts before they are blocked.  Due to Bug#69807, it’s also something MySQL 5.6 users will want to do.  This bug causes the counter maintained in the host cache for failed connections – against which max_connect_errors is compared – to never be reset by a valid connection.  The end result is that over time, hosts may reach the max_connect_errors threshold and be blocked.

This bug is a regression from earlier behavior, and is already fixed for MySQL 5.6.14.  The original developers of this feature (back before MySQL 4.1 days) never provided any meaningful tests around max_connect_errors functionality, so it wasn’t noticed when it changed.  Marc Alff exposed the host cache via PERFORMANCE_SCHEMA.HOST_CACHE, and in doing so, he added myriad test cases that really expose the inner workings of max_connect_errors in ways that were never previously tested – you can find them in source distributions under the mysql-test/suite/perfschema directory.  Unfortunately, the expected behavior that a successful connection would reset the counter was not part of any legacy test, and wasn’t incorporated into the many new tests written with 5.6.

For MySQL 5.6 users worried about whether they will be affected by this defect, I can offer three suggestions:

  1. Consider setting max_connect_errors to maximum value; it probably doesn’t do what you expect it to do anyways.
  2. Consider whether you can eliminate DNS reverse lookups entirely with –skip-name-resolve – this not only eliminates the possibility of being blocked, but may also result in faster initial connections.
  3. Monitor your host cache and periodically flush it when counters start to approach max_connect_errors.  Here’s a query you might find useful to do this (note I set max_connect_errors to 4 for test purposes):
mysql> SELECT
    ->  ip,
    ->  host,
    ->  host_validated,
    ->  sum_connect_errors,
    ->  @@global.max_connect_errors - sum_connect_errors until_blocked,
    ->  (@@global.max_connect_errors - sum_connect_errors) *
    ->    (gs.variable_value / sum_connect_errors) est_seconds_until_blocked
    -> FROM performance_schema.host_cache hc
    -> JOIN information_schema.global_status gs
    ->  ON (gs.variable_name = 'UPTIME')
    ->  \G
*************************** 1. row ***************************
                       ip: 192.168.2.4
                     host: TFARMER-MYSQL.wh.oracle.com
           host_validated: YES
       sum_connect_errors: 3
            until_blocked: 1
est_seconds_until_blocked: 204085
1 row in set (0.00 sec)

Single Post Navigation

Leave a Reply

Your email address will not be published. Required fields are marked *


seven − 6 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>