CODEWITHSUNDEEP
mysql
Ivan Pereira
Ivan Pereira

Reputation: 2239

Mysql max user connections bug

I'm facing a really weird problem to me.

I'm trying to connect with an user to my mysql 5.5 instance but it keep saying to me that user already exceeded the max_user_connections

ERROR 1226 (42000): User 'xpto' has exceeded the 'max_user_connections' resource (current value: 100)

But,

show processlist

doesn't shows any connection used by that user. I'm pretty sure that user is not using any connection at all. If i increase the current value to 110 for example i can connect. Then if i lower i can't connect.

EDIT: the global connections usage is 500 and only a few (10/20) are being used.

Any clue?

Upvotes: 1

Views: 3231

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53880

Are you on MySQL 5, and you've set the user's MAX_USER_CONNECTIONS, and the user recently disconnected?

From MySQL docs:

For the MAX_USER_CONNECTIONS limit, an edge case can occur if the account currently has open the maximum number of connections permitted to it: A disconnect followed quickly by a connect can result in an error (ER_TOO_MANY_USER_CONNECTIONS or ER_USER_LIMIT_REACHED) if the server has not fully processed the disconnect by the time the connect occurs. When the server finishes disconnect processing, another connection will once more be permitted.

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80653

The server has too many open connections already. A MySQL server can only handle a specific number of open connections before refusing to allow any more, and this limit is shared amongst all users of the server. It's usually set quite high, although it's easily possible for someone to effectively DoS a MySQL server by making lots of connections.

If you got the error message (code 1226) indicates that the entire MySQL server has run out of connection slots - this is the DoS scenario.

  1. Your database must have a user ‘A’ using which you have configured your wordpress blog.
  2. Now the problem is that user ‘A’ has exceeded maximum_questions resource (current value: 40)
  3. Create a new user ‘B’ in your database through your domain control panel and update the same user name you wp-config.php file in wordpress installation directory.
  4. Now you got the problem fixed in few minutes. It will not disturb the database or the posts you have in your wordpress or phpbb.

Copied from neuronring blog

Upvotes: 1

Related Questions