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
-
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
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.
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.
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.
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
Thanks for the report.
The change was intentional:
commit ca0c96fc8925d36ea0c5121520bf7d8f12ad90a5Author: Monty <monty@mariadb.org>Date: Thu Mar 22 20:03:54 2018 +0200Adjust 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 limitsdoes not work when open-files-limit option is providedFixed by adjusting tc_size downwards if there is not enough filedescriptors 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 smallertable cache than getting error 24- Increase minimum of max_connections and table_open_cache from 1 to 10as 1 is not usable for any real application, only for testing.The documentation needs to be updated.