Details

    • Type: Task
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      Rather than setting max_connect_errors really high, some users would like to skip host blocking entirely. Can we implement someway to allow unlimited max_connect_errors? If so, how would we implement it?

      1.) Set max_connect_errors to 0 for unlimited.

      2.) Create a new setting, such as "skip_max_connect_errors".

      MySQL has said that they don't want to implement this:

      https://bugs.mysql.com/bug.php?id=7695

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            elenst Elena Stepanova added a comment -

            Their explanation about 0 being a valid choice sounds reasonable given the current definition of the variable, only it's wrong, because as the bug report says, you cannot set max_connect_errors=0.
            On the other hand, if the definition is changed slightly, 0 could just as well mean unlimited.
            Currently it's

            If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.

            In this case, 0 indeed should be a valid value meaning after the very first connection error the host gets blocked. Only, the value is not allowed.

            But if it were

            If this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.

            then 1 would mean that after the very first connection error the host gets blocked; and then 0 could mean unlimited.

            That said, I don't see much problem with setting a really high value, what's wrong with that? We do it for pretty much all variables, e.g cache sizes, log sizes and what not. And if a user does not like big numbers, they can set it to -1, it will be adjusted.

            Show
            elenst Elena Stepanova added a comment - Their explanation about 0 being a valid choice sounds reasonable given the current definition of the variable, only it's wrong, because as the bug report says, you cannot set max_connect_errors=0. On the other hand, if the definition is changed slightly, 0 could just as well mean unlimited. Currently it's If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. In this case, 0 indeed should be a valid value meaning after the very first connection error the host gets blocked. Only, the value is not allowed. But if it were If this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. then 1 would mean that after the very first connection error the host gets blocked; and then 0 could mean unlimited. That said, I don't see much problem with setting a really high value, what's wrong with that? We do it for pretty much all variables, e.g cache sizes, log sizes and what not. And if a user does not like big numbers, they can set it to -1, it will be adjusted.
            Hide
            GeoffMontee Geoff Montee added a comment -

            That said, I don't see much problem with setting a really high value, what's wrong with that? We do it for pretty much all variables, e.g cache sizes, log sizes and what not.

            I don't personally see anything wrong with that.

            In this case, I think disabling the blocking is about peace of mind, rather than anything technical. The costs of having a host blocked would be very high, so the user would prefer to disable host blocking entirely.

            And if a user does not like big numbers, they can set it to -1, it will be adjusted.

            Should that automatically be cast to 2^64-1? It doesn't seem to work for me. Am I doing something wrong?

            MariaDB [(none)]> SET GLOBAL max_connect_errors=-1;
            Query OK, 0 rows affected, 1 warning (0.00 sec)
            
            MariaDB [(none)]> SHOW WARNINGS;
            +---------+------+----------------------------------------------------+
            | Level   | Code | Message                                            |
            +---------+------+----------------------------------------------------+
            | Warning | 1292 | Truncated incorrect max_connect_errors value: '-1' |
            +---------+------+----------------------------------------------------+
            1 row in set (0.00 sec)
            
            MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_connect_errors';
            +--------------------+-------+
            | Variable_name      | Value |
            +--------------------+-------+
            | max_connect_errors | 1     |
            +--------------------+-------+
            1 row in set (0.00 sec)
            
            Show
            GeoffMontee Geoff Montee added a comment - That said, I don't see much problem with setting a really high value, what's wrong with that? We do it for pretty much all variables, e.g cache sizes, log sizes and what not. I don't personally see anything wrong with that. In this case, I think disabling the blocking is about peace of mind, rather than anything technical. The costs of having a host blocked would be very high, so the user would prefer to disable host blocking entirely. And if a user does not like big numbers, they can set it to -1, it will be adjusted. Should that automatically be cast to 2^64-1? It doesn't seem to work for me. Am I doing something wrong? MariaDB [(none)]> SET GLOBAL max_connect_errors=-1; Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [(none)]> SHOW WARNINGS; +---------+------+----------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------+ | Warning | 1292 | Truncated incorrect max_connect_errors value: '-1' | +---------+------+----------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_connect_errors'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1 | +--------------------+-------+ 1 row in set (0.00 sec)
            Hide
            elenst Elena Stepanova added a comment -

            I meant on startup.

            mysqld <...> --max-connect-errors=-1
            
            2015-07-17 20:12:11 139936639149920 [Warning] option 'max_connect_errors': unsigned value 18446744073709551615 adjusted to 4294967295
            
            MariaDB [test]> select @@max_connect_errors;
            +----------------------+
            | @@max_connect_errors |
            +----------------------+
            |           4294967295 |
            +----------------------+
            1 row in set (0.00 sec)
            
            MariaDB [test]> select @@version;
            +----------------------+
            | @@version            |
            +----------------------+
            | 10.1.6-MariaDB-debug |
            +----------------------+
            1 row in set (0.00 sec)
            
            Show
            elenst Elena Stepanova added a comment - I meant on startup. mysqld <...> --max-connect-errors=-1 2015-07-17 20:12:11 139936639149920 [Warning] option 'max_connect_errors': unsigned value 18446744073709551615 adjusted to 4294967295 MariaDB [test]> select @@max_connect_errors; +----------------------+ | @@max_connect_errors | +----------------------+ | 4294967295 | +----------------------+ 1 row in set (0.00 sec) MariaDB [test]> select @@version; +----------------------+ | @@version | +----------------------+ | 10.1.6-MariaDB-debug | +----------------------+ 1 row in set (0.00 sec)
            Hide
            GeoffMontee Geoff Montee added a comment -

            Oh, OK. Thanks for clarifying!

            Show
            GeoffMontee Geoff Montee added a comment - Oh, OK. Thanks for clarifying!

              People

              • Assignee:
                Unassigned
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: