[MDEV-18252] Can't decrease max_connections below 10 Created: 2019-01-15  Updated: 2023-09-04  Resolved: 2019-06-10

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Server, Variables
Affects Version/s: 10.1.33, 10.2.15, 10.3.6, 10.3.10, 10.2.21, 10.3.12
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Karl Levik Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: 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.)



 Comments   
Comment by Elena Stepanova [ 2019-01-16 ]

Thanks for the report.
The change was intentional:

commit ca0c96fc8925d36ea0c5121520bf7d8f12ad90a5
Author: Monty <monty@mariadb.org>
Date:   Thu Mar 22 20:03:54 2018 +0200
 
    Adjust table_open_cache to avoid getting error 24 (too many open files)
    
    MDEV--15609 engines/funcs.crash_manytables_number crashes with error 24
               (too many open files)
    MDEV-10286  Adjustment of table_open_cache according to system limits
                does not work when open-files-limit option is provided
    
    Fixed by adjusting tc_size downwards if there is not enough file
    descriptors to use.
    
    Other changes:
    - Ensure that there is 30 (was 10) extra file descriptors for other usage
    - Decrease TABLE_OPEN_CACHE_MIN to 200 as it's better to have a smaller
      table cache than getting error 24
    - Increase minimum of max_connections and table_open_cache from 1 to 10
      as 1 is not usable for any real application, only for testing.

The documentation needs to be updated.

Comment by Karl Levik [ 2019-01-17 ]

I must admit I struggle to see the reasoning behind this change. max_connections < 10 may not be a sensible choice in a typical production environment, but there could be use-cases you're not accounting for, and MariaDB is also used in dev/testing environments where requirements can be different. So why limit the range of this variable?

Possible use-case: setting max_connections = 1 as a quick way to lock the database for other users while doing database schema updates.

Comment by Elena Stepanova [ 2019-01-17 ]

monty, do you want to clarify?

Comment by Ian Gilfillan [ 2019-01-18 ]

Documentation has been updated.

Comment by Michael Widenius [ 2019-01-25 ]

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.

Comment by Karl Levik [ 2019-02-04 ]

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

Comment by Ian Gilfillan [ 2019-06-10 ]

Closing as documentation updated, no other issue.

Comment by Karl Levik [ 2023-08-22 ]

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.

Comment by Sergei Golubchik [ 2023-09-04 ]

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

Generated at Thu Feb 08 08:42:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.