MYSQLi error: User already has more than 'max_user_connections' active connections

Question

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.

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!

$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?

<?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.

Solution 1

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!

Other option is to implement a Singleton pattern on this class, so it reuses same pool of connections, and don't explode the limit.

Solution 2

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.

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.

Solution 3

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

How to optimize database table and query:

1. Index your table field in mysql
2. In select query remove `*` and write which you need field
3. Closed mysql_connection

Solution 4

to check numbers of connection login to mysql using command line and run this command

SHOW VARIABLES LIKE 'max_user_connections'

STEP #1 : Look setting in /etc/my.cnf

max_user_connections = <set number that you want>

max_user_connections =

you can set from mysql command line too

SET GLOBAL max_user_connections = 0;

restart your apache server

Solution 5

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.

View more solutions

Comments

[Brian Driscoll]

what version of PHP is your host using? Destructors are only supported as of PHP5

[Fernando Barrocal]

I am not confident on GC's ... invoke destruct by hand when finished using the class

[Jahmic]

This is not the same question as the supposed duplicate, especially when some of the solution discuss increasing the potential active connections available.

[Run]

thanks for the reply. how do I 'implement a Singleton pattern on this class, so it reuses same pool of connections, and don't explode the limit'? thanks!

[Run]

hi thanks for the reply. I just edited my OP thread above, adding in the part where how I use the database connection class - have I been practising it incorrectly?? thanks

[Fernando Barrocal]

Give a look at odi.ch/prog/design/singleton.php on how to implement. On other hand there are plenty of PHP Connection pools around

[Alex Hadley]

You can also set the values in my.cnf: [mysqld] max_connections = 500 max_user_connections = 200

[Tycon]

I did a little more testing. My observation is that PDO seems to be less tolerate of many consecutive connections - that is connections being created in a loop. I know this is bad practice it the first place. When I was using mysql_* my looped queries seemed to be reasonably fast. However when I switched to PDO I noticed much longer response times for these types of queries. TL;DR; - If you switch to PDO and you call queries in a PHP loop you may need to rewrite the application to call one single query rather than many consecutive queries.

[Heitor]

Could you explain WHY we need first optimize the database table??

[Gellie Ann]

I tried editing the field via the Variables too but I encounter a #1227 - Access denied error. Do you have an idea on how to fix this?