php MYSQLi 错误：用户已经有多个“max_user_connections”活动连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4079531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQLi error: User already has more than 'max_user_connections' active connections
提问by laukok
I have this error below on a site I am running. I don't understand why is that as it works fine on my localhost. Is it something to do with the host? I am on an Unix server.
我正在运行的网站上出现以下错误。我不明白为什么会这样，因为它在我的本地主机上运行良好。跟楼主有关系吗？我在 Unix 服务器上。
Warning: mysqli::mysqli() [mysqli.mysqli]: (42000/1203): User dbo343879423 already has more than 'max_user_connections' active connections in /homepages/9/d322397966/htdocs/dump/models/class_database.php on line 11
Connect failed: User dbo343879423 already has more than 'max_user_connections' active connections
Warning: mysqli::close() [mysqli.close]: Couldn't fetch mysqli in /homepages/9/d322397966/htdocs/dump/models/class_database.php on line 160
the error says 'User dbo343879423 already has more than 'max_user_connections' active connections in /homepages/9/d322397966/htdocs/dump/models/class_database.php on line 11', so this is the line 11 in the script - I can't see anything wrong!
错误显示“用户 dbo343879423 在第 11 行的 /homepages/9/d322397966/htdocs/dump/models/class_database.php 中已经有超过 'max_user_connections' 的活动连接”，所以这是脚本中的第 11 行 - 我可以'没有看到任何错误！
$this -> connection = new mysqli($hostname,$username,$password,$database);
below is the entire class in class_database.php, is it wrong in other part of script and I should change?
下面是 class_database.php 中的整个类，脚本的其他部分是错误的，我应该更改吗？
<?php
#connects the database and handling the result
class __database {
protected $connection = null;
protected $error = null;
#make a connection
public function __construct($hostname,$username,$password,$database)
{
$this -> connection = new mysqli($hostname,$username,$password,$database);
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
}
#fetches all result rows as an associative array, a numeric array, or both
public function fetch_all($query)
{
$result = $this -> connection -> query($query);
if($result)
{
return $result -> fetch_all(MYSQLI_ASSOC);
}
else
{
$this -> error = $this -> connection -> error;
return false;
}
}
#fetches a result row as an associative array, a numeric array, or both
public function fetch_assoc_while($query)
{
$result = $this -> connection -> query($query);
if($result)
{
while($row = $result -> fetch_assoc())
{
$return_this[] = $row;
}
if (isset($return_this))
{
return $return_this;
}
else
{
return false;
}
}
else
{
$this -> error = $this -> connection -> error;
return false;
}
}
#fetch a result row as an associative array
public function fetch_assoc($query)
{
$result = $this -> connection -> query($query);
if($result)
{
return $result -> fetch_assoc();
}
else
{
$this -> error = $this -> connection -> error;
return false;
}
}
#get a result row as an enumerated array
public function fetch_row($query)
{
$result = $this -> connection -> query($query);
if($result)
{
return $result -> fetch_row();
}
else
{
$this -> error = $this -> connection -> error;
return false;
}
}
#get the number of rows in a result
public function num_rows($query)
{
$result = $this -> connection -> query($query);
if($result)
{
return $result -> num_rows;
}
else
{
$this -> error = $this -> connection -> error;
return false;
}
}
#performs a query on the database
public function query($query)
{
$result = $this -> connection -> query($query);
if($result)
{
return $result;
}
else
{
$this -> error = $this -> connection -> error;
return false;
}
}
#escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection
public function real_escape_string($string)
{
$result = $this -> connection -> real_escape_string($string);
if($result)
{
return $result;
}
else
{
$this -> error = $this -> connection -> error;
return false;
}
}
#display error
public function get_error()
{
return $this -> error;
}
#closes the database connection when object is destroyed.
public function __destruct()
{
$this -> connection -> close();
}
}
?>
or should I just change the host for good!??
还是我应该永远改变主机！？？
below is the implementation of the database connection class. If I take this part out, the error won't appear anymore, but I do the same at other parts of the site as well and they won't cause any problem!
下面是数据库连接类的实现。如果我把这部分去掉，错误就不会再出现了，但我在网站的其他部分也做同样的事情，他们不会造成任何问题！
<!-- side-video-library -->
<div id="side-video-library" class="round-corner">
<h4><a href="<?php echo HTTP_ROOT;?>videos"><span>ENER VIDEO LIBRARY</span></a></h4>
<?php
$sql = "
SELECT *
FROM root_pages
WHERE root_pages.parent_id = '8'
AND root_pages.pg_highlight = '1'
AND root_pages.pg_hide != '1'
ORDER BY rand() DESC
LIMIT 1
";
#instantiate the object of __database class
$object_item = new __database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$item = $object_item -> fetch_assoc($sql);
#instantiate the object of __database class
$object_item_num = new __database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$total_item = $object_item_num -> num_rows($sql);
//echo $total_item;
?>
<?php
if ($total_item > 0)
{
$sql = "
SELECT *
FROM root_tagged
LEFT JOIN root_tags ON ( root_tags.tag_id = root_tagged.tag_id )
WHERE root_tagged.pg_id = '".$item['pg_id']."'
";
#instantiate the object of __database class
$object_tagname = new __database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$item_tagname = $object_tagname -> fetch_assoc($sql);
#instantiate the object of __database class
$object_tagname_num = new __database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$total_tagname = $object_tagname_num -> num_rows($sql);
?>
<p class="item-video">
<object style="width: 183px; height: 151px;" width="183" height="151" data="http://www.youtube.com/v/<?php echo get_video_id($item['pg_content_1']) ;?>" type="application/x-shockwave-flash">
<param name="wmode" value="transparent" />
<param name="src" value="http://www.youtube.com/v/<?php echo get_video_id($item['pg_content_1']) ;?>" />
</object>
</p>
<h3><a href="<?php echo HTTP_ROOT.str_replace(' ', '-', 'videos').'/'.$item_tagname['tag_name'].'/'.str_replace(' ', '-', strtolower($item['pg_url']));?>"><?php if(strlen($item['pg_title']) > 20) echo substr($item['pg_title'], 0,20).'...'; else echo $item['pg_title'];?></a></h3>
<p class="item-excerpt-video"><?php if(strlen($item['pg_content_2']) > 100) echo substr($item['pg_content_2'], 0,100).'...'; else echo $item['pg_content_2'];?></p>
<a href="<?php echo HTTP_ROOT;?>videos" class="button-arrow"><span>More</span></a>
<?php
}
?>
</div>
<!-- side-video-library -->
Have I been implementing the class incorrectly??
我是否错误地实施了课程？
thanks.
谢谢。
采纳答案by Fernando Barrocal
Probably the problem is that you have only a handful connections allowed and when your class tries to get a new connection you have this error.
问题可能是您只允许少数几个连接，并且当您的班级尝试获得新连接时，您会遇到此错误。
This is not a programming problem, just quantity of resources available. And any other script that uses this class are subject to have the error.
这不是编程问题，只是可用资源的数量。使用此类的任何其他脚本都可能出现错误。
You have to configure more connections on mysql config file on the server. If you don't have this access, ask the support to do it or change for a hosting company with more connections allowed!
您必须在服务器上的 mysql 配置文件上配置更多连接。如果您没有此访问权限，请要求支持人员执行此操作或更改为允许更多连接的托管公司！
Other option is to implement a Singleton pattern on this class, so it reuses same pool of connections, and don't explode the limit.
另一种选择是在此类上实现单例模式，因此它重用相同的连接池，并且不会破坏限制。
回答by Turcogj
Check the MAX USER_CONNECTIONS setting on your MySQL server for the user. In PHPMyAdmin go to the server page (Click on the Server:<>) and in the sub-menu click on priviledges. Edit the user dbo343879423 and the MAX USER_CONNECTIONS will be on the right side. By default I believe it is set to 0 (unlimited), yours maybe restricted depending on who setup the server.
检查 MySQL 服务器上用户的 MAX USER_CONNECTIONS 设置。在 PHPMyAdmin 中，转到服务器页面（单击服务器：<>）并在子菜单中单击权限。编辑用户 dbo343879423，MAX USER_CONNECTIONS 将在右侧。默认情况下，我认为它设置为 0（无限制），您的设置可能会受到限制，具体取决于谁设置了服务器。
I'm not sure how your Database class is being used but if you are instantiating the class multiple times consider creating a private static variable Database in the database class and creating a public static method getDatabase() which instantiates the database connection if it is null and returns the instance.
我不确定您的 Database 类是如何使用的，但是如果您多次实例化该类，请考虑在数据库类中创建一个私有静态变量 Database 并创建一个公共静态方法 getDatabase() 如果它为 null 则实例化数据库连接并返回实例。
回答by Gulameabbas Hannan Bandibar
If you get this max_user_connections message first optimize your database table.
如果您收到此 max_user_connections 消息，请首先优化您的数据库表。
How to optimize database table and query:
如何优化数据库表和查询：
- Index your table field in mysql
- In select query remove `*` and write which you need field
- Closed mysql_connection
- 在 mysql 中索引你的表字段
- 在选择查询中删除 `*` 并写下您需要的字段
- 关闭 mysql_connection
回答by Tycon
For what its worth I wanted to include a situation I ran into where I received this message due to an incorrect placeholder:
对于它的价值，我想包括我遇到的由于占位符不正确而收到此消息的情况：
$sql_str = 'SELECT prod_id FROM ' . $this->table_name["product"] . ' WHERE prod_sku =:p_sku';
$arr[':prod_sku'] = $s_sku;
In addition I was doing a large number of queries. I suspect that the error compounded with the large number of queries caused this issue. When I fixed the query the connections issue when away.
此外，我正在做大量的查询。我怀疑错误与大量查询相结合导致了这个问题。当我在离开时修复查询连接问题。
回答by styl3r
For this on Godaddy shared hosting, you cant change the MAX_USER_CONNECTION value. To find it, click Server<>, then click Variables in the menu-bar. Mine is set to 200.
为此，在 Godaddy 共享主机上，您不能更改 MAX_USER_CONNECTION 值。要找到它，请单击服务器<>，然后单击菜单栏中的变量。我的设置为200。
回答by Ajay Gadhavana
to check numbers of connection login to mysql using command line and run this command
使用命令行检查连接登录到 mysql 的数量并运行此命令
SHOW VARIABLES LIKE 'max_user_connections
STEP #1 : Look setting in /etc/my.cnf
第 1 步：查看 /etc/my.cnf 中的设置
max_user_connections = <set number that you want>
max_user_connections =
max_user_connections =
you can set from mysql command line too
您也可以从 mysql 命令行设置
SET GLOBAL max_user_connections = 0;
restart your apache server
重启你的 apache 服务器
