MYSQLi错误:用户已经有超过'max_user_connections'的活动连接

时间:2022-10-03 09:29:06

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已经有超过‘max_user_connections’的活跃连接,在/主页/9/d322397966/htdocs/dump/models/class_database中。php在第11行,所以这是脚本中的第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.

谢谢。

5 个解决方案

#1


7  

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.

另一种选择是在这个类上实现单例模式,这样它就重用了相同的连接池,并且不会破坏限制。

#2


5  

If you get this max_user_connections message first optimize your database table.

如果您获得了这个max_user_connections消息,首先优化数据库表。

How to optimize database table and query:

如何优化数据库表和查询:

  1. Index your table field in mysql
  2. 在mysql中索引表字段
  3. In select query remove `*` and write which you need field
  4. 在select query中删除' * '并写入需要的字段
  5. Closed mysql_connection
  6. 关闭mysql_connection

#3


3  

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服务器上的maxuser_connections设置。在phpadmin mygo中,访问服务器页面(单击服务器:<>),在子菜单中单击priviledges。编辑用户dbo343879423,最大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.

我不确定如何使用数据库类,但是如果要多次实例化类,请考虑在数据库类中创建一个私有静态变量数据库,并创建一个公共静态方法getDatabase(),如果数据库连接为null,则该方法实例化数据库连接并返回实例。

#4


1  

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.

此外,我还进行了大量查询。我怀疑这个错误和大量的查询一起导致了这个问题。当我修复查询时,连接在离开时发出。

#5


1  

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值。要找到它,单击Server<>,然后单击菜单栏中的变量。我的是200。

#1


7  

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.

另一种选择是在这个类上实现单例模式,这样它就重用了相同的连接池,并且不会破坏限制。

#2


5  

If you get this max_user_connections message first optimize your database table.

如果您获得了这个max_user_connections消息,首先优化数据库表。

How to optimize database table and query:

如何优化数据库表和查询:

  1. Index your table field in mysql
  2. 在mysql中索引表字段
  3. In select query remove `*` and write which you need field
  4. 在select query中删除' * '并写入需要的字段
  5. Closed mysql_connection
  6. 关闭mysql_connection

#3


3  

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服务器上的maxuser_connections设置。在phpadmin mygo中,访问服务器页面(单击服务器:<>),在子菜单中单击priviledges。编辑用户dbo343879423,最大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.

我不确定如何使用数据库类,但是如果要多次实例化类,请考虑在数据库类中创建一个私有静态变量数据库,并创建一个公共静态方法getDatabase(),如果数据库连接为null,则该方法实例化数据库连接并返回实例。

#4


1  

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.

此外,我还进行了大量查询。我怀疑这个错误和大量的查询一起导致了这个问题。当我修复查询时,连接在离开时发出。

#5


1  

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值。要找到它,单击Server<>,然后单击菜单栏中的变量。我的是200。