Description:
The server has some user, one per database. Without any connections, I'm facing error "User promo already has more than 'max_user_connections' active connections"
There is few connections only: replication, and some monitoring queries :
kadoc-12:~# netstat -ntp | grep 3306 | wc -l
3
PROCESS LIST is quite empty :
kadoc-12:~# mysqladmin processlist
+-----------+-------------+-----------------+------+---------+---------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----------+-------------+-----------------+------+---------+---------+-----------------------------------------------------------------------------+------------------+
| 1 | system user | | | Connect | 1831803 | Waiting for master to send event | |
| 2 | system user | | | Connect | 0 | Slave has read all relay log; waiting for the slave I/O thread to update it | |
| 313068604 | sysadmin | 10.0.0.10:45487 | test | Sleep | 0 | | |
| 313173606 | sysadmin | 10.0.0.10:49800 | test | Sleep | 0 | | |
| 313572883 | root | localhost | | Query | 0 | | show processlist |
+-----------+-------------+-----------------+------+---------+---------+-----------------------------------------------------------------------------+------------------+
Some users :
mysql> show grants for 'partner'@'10.0.%' ;
+---------------------------------------------------------------------------------------------------------------------------------+
| Grants for partner@10.0.% |
+---------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO 'partner'@'10.0.%' IDENTIFIED BY PASSWORD'*****************************************' |
| GRANT SELECT, EXECUTE ON `partner`.* TO 'partner'@'10.0.%' |
+---------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show grants for 'promo'@'10.0.%' ;
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for promo@10.0.% |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO 'promo'@'10.0.%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, EXECUTE ON `promo`.* TO 'promo'@'10.0.%' |
+-------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Configuration :
mysql> show variables like 'max%conn%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_connect_errors | 10 |
| max_connections | 1024 |
| max_user_connections | 992 |
+----------------------+-------+
3 rows in set (0.00 sec)
mysql> show variables like 'skip_net%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
kadoc-12:~# lsof -i | grep mysqld
mysqld 4343 mysql 10u IPv4 874 0t0 TCP *:mysql (LISTEN)
Trying to connect with partner :
kadoc-12:~# mysql -hkadoc-12.local -upartner -pthiebeimir -e 'select 1' partner
+---+
| 1 |
+---+
| 1 |
+---+
But with user promo, it's not possible :
kadoc-12:~# mysql -hkadoc-12.local -upromo -pfoeshoogez promo
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
Even with a long running loop :
kadoc-12:~# while ! mysql -hkadoc-12.local -upromo -pfoeshoogez promo; do sleep 0.1; done
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
ERROR 1203 (42000): User promo already has more than 'max_user_connections' active connections
^C
The only fix I've found was to restart mysql, then I was able to connect with user 'promo'.
How to repeat:
don't know how.
