'max_user_connections'设置为200 - 仍然会收到错误 [英] 'max_user_connections' set to 200 - still getting error

问题描述

这是mysql错误：
连接失败：用户'db2498'已经超出'max_user_connections'资源（当前值：200）。

我设置了my.cnf：

  [mysqld] 
 max_connections = 500 
 max_user_connections = 200 
  

我将mysql中的max_user_connections设置为用户到200也。大约10-20分钟内我已经有1400人撞上了网站。每次平均停留14秒，我收到了大约1400条消息。

我正在使用PHP / Mysql。这是数据库类：

  class DB {
 
 public function __construct（）{
 $ this-> conn = new mysqli（$ this-> host，$ this-> user，$ this-> pass，$ this-> db）; 
 / *检查连接* / 
} 
 public function selectSomething（）{
 / * select data& return * / 
} 
 public function __destruct（）
 {
 $ this-> conn-> close（）; 
} 
} 
  

这就是我所说的：

  $ conn = new DB（）; 
 $ result = $ conn-> selectSomething（）; 
 / *做某事* / 
 
 $ result = $ conn-> selectSomething（）; 
 / *做某事* / 
 
 $ result = $ conn-> selectSomething（）; 
 / *做某事* / 
  

用户在网站上平均为14秒。为什么我得到这个错误？是毁灭吗我的数据库包装器设置错了吗？我迷路了，技术支持并没有多大帮助。

解决方案

关于您的最大连接问题，可能是三件事之一：

1）服务器已经有太多的打开连接。 MySQL服务器只能处理特定数量的开放连接，然后拒绝再允许，并且此限制在服务器的所有用户之间共享。通常设置得相当高，尽管人们很容易通过进行很多连接（但见下文）来有效地DoS一个MySQL服务器。

2）您的用户帐户有每小时允许的连接数量有限 - 该小时内的任何进一步连接将被拒绝。这是按每个用户设置的。

3）您的用户帐户的数量有限，可允许的打开连接数 - 任何进一步的连接都将被拒绝。这是按每个用户设置的。

阅读连接尝试时返回的错误消息总是很重要的，因为在大多数情况下，这将精确确定

如果您的帐户具有最大连接数限制（方案＃3），则错误将是：
代码：

 错误1226（42000）：用户'mysqldba'已经超过'max_user_connections'资源（当前值：1）
  

mysqldba将是您的用户名，当前值是此用户允许的最大打开连接数。 / p>

如果您的帐户具有每小时限制的最大连接数（方案＃2），则错误将是：
代码：

 错误1226（42000）：用户'mysqldba'已经超过'max_connections_per_hour'资源（当前值：1）
  

再次，'mysqldba'将是y我们的用户名和当前值是该用户允许的最大连接数。

如果您收到错误消息（代码1040），则表示整个MySQL服务器已经用尽了连接槽 - 这是上面提到的DoS场景。

你能做些什么？从您所说的，您在此服务器上没有超级用户权限，除了向负责该服务器的SysAdmin的投诉外，没有任何内容。它们可能会增加允许的最大连接数，这可以短期解决问题，但是如果使用该服务器的其他人创建了一个愚蠢数量的数据库连接，这些插槽将再次填满。他们可能应该做的还是强制执行每个用户最大的开放连接限制 - 这将阻止重型用户堵塞服务器。在像您这样的共享服务器情况下，这将是最有意义的 - 强力用户将/应该拥有自己的服务器，或者可以/应该支付增加其最大连接数。

Here is the mysql error: Connect failed: User 'db2498' has exceeded the 'max_user_connections' resource (current value: 200).

I set the my.cnf:

[mysqld]
max_connections = 500
max_user_connections = 200

I set the max_user_connections in mysql for the user to 200 also. I've had 1400 people hit the site in about 10-20 minutes. Each stay on for an average of 14 seconds, and I got about 1400 of these messages.

I'm using PHP/Mysql. This is the database class:

class DB{

public function __construct(){
    $this->conn = new mysqli($this->host,$this->user,$this->pass,$this->db);
    /* check connection */      
}
public function selectSomething(){
    /* select data & return */
}
public function __destruct()
{
$this->conn->close();
} 
}

This is how I call it:

$conn = new DB();
$result = $conn->selectSomething();
/* do something */

$result = $conn->selectSomething();
/* do something */

$result = $conn->selectSomething();
/* do something */

The users are on the site for an average of 14 seconds. Why am I getting this error? Is it the destruct? Do I have the database wrapper set up wrong? I'm lost, and tech support isn't much help.

解决方案

As for your "max connections" problem, it could be one of three things:

1) 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 (but see below)

2) Your user account has a limited number of connections allowed per hour - any further connections within that hour would be rejected. This is set on a per-user basis.

3) Your user accounts has a limited number of allowable open connections - any further connections would be rejected. This is set on a per-user basis.

It's always important to read the error message you get returned on the connection attempt, as in most cases this will pinpoint the exact reason for failure.

If your account has a maximum number of connections limit (scenario #3), the error would be: Code:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_user_connections' resource (current value: 1) 

Where 'mysqldba' would be your username, and the 'current value' is the maximum number of open connections allowed from this user.

If you account has a maximum number of connections per hour limit (scenario #2), the error would be: Code:

ERROR 1226 (42000): User 'mysqldba' has exceeded the 'max_connections_per_hour' resource (current value: 1) 

Where, once again, 'mysqldba' would be your username, and the 'current value' is the maximum number of connections per hour allowed for this user.

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

What can you do about it? From what you've said, you don't have superuser privileges on this server, so nothing, apart from complain to the SysAdmin responsible for that server. They might increase the maximum number of connections allowed, which could solve the problem short-term, but if someone else using the server is creating a stupid number of database connections the slots would just fill up again. What they probably should do is to also enforce a per-user maximum open connection limit as well - this would stop the heavy users clogging up the server. In a shared-server situation like yours, this would make the most sense - 'power users' would/should have their own server, or could/should pay to increase their maximum open connections.

这篇关于'max_user_connections'设置为200 - 仍然会收到错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！