Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18252

Can't decrease max_connections below 10

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.1.33, 10.2.15, 10.3.6, 10.3.10, 10.2.21, 10.3.12
    • N/A
    • Documentation, Server, Variables
    • None

    Description

      Setting max_connections to 1 is possible on MySQL 5.7 and 8.0, but doesn't work with recent versions of MariaDB. The documentation seems to indicate it should work as it gives the range from 1 to 100000. 

      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_connections';
      		 
      +-----------------+-------+
      		 
      | Variable_name   | Value |
      		 
      +-----------------+-------+
      		 
      | max_connections | 100   |
      		 
      +-----------------+-------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SET GLOBAL max_connections=1;
      		 
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> show warnings;
      		 
      +---------+------+------------------------------------------------+
      		 
      | Level   | Code | Message                                        |
      		 
      +---------+------+------------------------------------------------+
      		 
      | Warning | 1292 | Truncated incorrect max_connections value: '1' |
      		 
      +---------+------+------------------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'max_connections';
      		 
      +-----------------+-------+
      		 
      | Variable_name   | Value |
      		 
      +-----------------+-------+
      		 
      | max_connections | 10    |
      		 
      +-----------------+-------+
      		 
      1 row in set (0.00 sec)

      (I've tagged the specific versions I've tested, but it's obviously likely to affect other 10.2 and 10.3 versions as well.)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          View 4 older comments
          monty Michael Widenius added a comment -

          I changed the limit to ensure that a user doesn't do anything wrong by accident.

          There is no reason to assume that an application will only make one connection to the database.
          For example, even the mysql client will on occasions open another connection to the server (for kill).

          Another issue, is that if you do anything wrong with one connection (starting a long transaction), there
          is now to fix it than killing the serve.

          In practice, limiting the connection to 1, may cause unexpected results for the user, so I thought it's better to avoid the issue.
          Logging in as the super user and making the server read only is a better way to avoid conflicts than relying on max_connections. Another way is to start server with bootstrap, in which case you know that you are alone.

          monty Michael Widenius added a comment - I changed the limit to ensure that a user doesn't do anything wrong by accident. There is no reason to assume that an application will only make one connection to the database. For example, even the mysql client will on occasions open another connection to the server (for kill). Another issue, is that if you do anything wrong with one connection (starting a long transaction), there is now to fix it than killing the serve. In practice, limiting the connection to 1, may cause unexpected results for the user, so I thought it's better to avoid the issue. Logging in as the super user and making the server read only is a better way to avoid conflicts than relying on max_connections. Another way is to start server with bootstrap, in which case you know that you are alone.
          karll Karl Levik added a comment -

          I suppose that makes sense - thank you for the explanation!

          karll Karl Levik added a comment - I suppose that makes sense - thank you for the explanation!
          greenman Ian Gilfillan added a comment -

          Closing as documentation updated, no other issue.

          greenman Ian Gilfillan added a comment - Closing as documentation updated, no other issue.
          karll Karl Levik added a comment -

          Thought I should add that in PostgreSQL in addition to max_connections they have a system variable superuser_reserved_connections.

          Perhaps that could be an idea for MariaDB as well.

          karll Karl Levik added a comment - Thought I should add that in PostgreSQL in addition to max_connections they have a system variable superuser_reserved_connections . Perhaps that could be an idea for MariaDB as well.
          serg Sergei Golubchik added a comment -

          MariaDB always have one reserved connection for a superuser. Additionally it can listen on a separate port, it'll be available even when max_connections limit is reached

          serg Sergei Golubchik added a comment - MariaDB always have one reserved connection for a superuser. Additionally it can listen on a separate port, it'll be available even when max_connections limit is reached

          People

            monty Michael Widenius
            karll Karl Levik
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.