[Maria-discuss] Updating user's max_connections without restart
Hi, I've tried to change a user's max_connections after the limit has been reached (connection attempts return "has exceeded the 'max_connections_per_hour' resource"). With MariaDB 10.2.18, running using the official docker image, we have "select @@max_connections;" returning 100. We have a user created "WITH MAX_CONNECTIONS_PER_HOUR 50". Now if we bump in to this limit I fail to allow more connections by increasing it. I've tried the following methods (using both 0 and a high value): ALTER USER 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0; UPDATE mysql.user SET max_connections = 0 WHERE user='site_devkit_live_index'; FLUSH PRIVILEGES; GRANT USAGE ON *.* TO 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0; The following indicates that the updates work: USE mysql; SELECT host, user, max_user_connections, max_connections FROM user; I've also tried deleting and re-creating the user. What would it take to make these updates affect the current instance? We'd like to avoid restarts. regards /Staffan
I've found a simple repro case so I reported
https://jira.mariadb.org/browse/MDEV-17852
/Staffan
On Mon, Nov 26, 2018 at 9:14 PM Staffan
Hi,
I've tried to change a user's max_connections after the limit has been reached (connection attempts return "has exceeded the 'max_connections_per_hour' resource").
With MariaDB 10.2.18, running using the official docker image, we have "select @@max_connections;" returning 100.
We have a user created "WITH MAX_CONNECTIONS_PER_HOUR 50".
Now if we bump in to this limit I fail to allow more connections by increasing it. I've tried the following methods (using both 0 and a high value):
ALTER USER 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0;
UPDATE mysql.user SET max_connections = 0 WHERE user='site_devkit_live_index'; FLUSH PRIVILEGES;
GRANT USAGE ON *.* TO 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0;
The following indicates that the updates work:
USE mysql; SELECT host, user, max_user_connections, max_connections FROM user;
I've also tried deleting and re-creating the user.
What would it take to make these updates affect the current instance? We'd like to avoid restarts.
regards /Staffan
I wonder if the new value in the `user` table will take effect if you FLUSH
PRIVELEGES. This may be a workaround.
On Tue, Nov 27, 2018, 2:39 PM Staffan I've found a simple repro case so I reported
https://jira.mariadb.org/browse/MDEV-17852 /Staffan On Mon, Nov 26, 2018 at 9:14 PM Staffan Hi, I've tried to change a user's max_connections after the limit has been
reached (connection attempts return "has exceeded the
'max_connections_per_hour' resource"). With MariaDB 10.2.18, running using the official docker image, we have
"select @@max_connections;" returning 100. We have a user created "WITH MAX_CONNECTIONS_PER_HOUR 50". Now if we bump in to this limit I fail to allow more connections by
increasing it. I've tried the following methods (using both 0 and a high
value): ALTER USER 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0; UPDATE mysql.user SET max_connections = 0 WHERE
user='site_devkit_live_index';
FLUSH PRIVILEGES; GRANT USAGE ON *.* TO 'site_devkit_live_index'@'%'
WITH MAX_CONNECTIONS_PER_HOUR 0; The following indicates that the updates work: USE mysql;
SELECT host, user, max_user_connections, max_connections FROM user; I've also tried deleting and re-creating the user. What would it take to make these updates affect the current instance?
We'd like to avoid restarts. regards
/Staffan _______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
participants (2)
-
Justin Swanhart
-
Staffan