mysqli Database class - MYSQL Persistent Connections Error < PHP5.3

[knowj]

I have recently developed a OOP/PHP store system which uses the mysqli this worked fine until I was near the end of the development process working on a busy server and got an error stating that there were too many connections taking every site on the server down.

 

http://bugs.php.net/bug.php?id=37560

 

To solve this we had to make changes to the php.ini and mysql settings to prevent mysqli from making persistent connections and killing the server.

 

The thing i cannot understand is the lack of documentation on this bug I would have thought with mysqli being the database class to be using now (from MYSQL 5.0 onwards) more people would have been posting about it in forums.

 

Has anyone here come across this problem?

Is anyone waiting to upgrade to 5.3 to fix it?

Is anyone using a different potentially better database interface class?

[tmbrown]

I've never had any problems using MySQLi much less anything like this.  I've got a few questions for you...

 

A) Are you using a main wrapper class for your application?

    If so is it wrapping the MySQLi call into a method?

 

B) Is your site hosted or are you running your own server?

 

C) What OS are you running?

[knowj]

A) Are you using a main wrapper class for your application?

If so is it wrapping the MySQLi call into a method?

I have a singleton database class which is an extension of the mysqli class then the database class is called within each functional class

<?php
//Database class to implement singleton pattern on top of mysqli
class database extends mysqli
{
//create a singleton instance
private $report;
private static $instance = NULL;

//private constructor to prevent direct access
private function __construct()
	{
		parent::__construct(constants::DBHOST, constants::DBUSER, constants::DBPASS, constants::DBNAME);
		if(mysqli_connect_errno())
			{
				throw new Exception(mysqli_connect_error(), mysqli_connect_errno());
			}
		parent::options(MYSQLI_OPT_CONNECT_TIMEOUT, 10);
	}
//public create instance function to create singleton
public function getinstance()
	{
		if(self::$instance === null)
			{
				$c = __CLASS__;
				self::$instance = new $c;
			}
		return self::$instance;
	}

class products
{
private $database;
private $paging;

//create a singleton instance
private static $instance = NULL;

private function __construct()
	{
		$this->database = database::getinstance();
		$this->paging = paging::getinstance();
	}
//...........

 

B) Is your site hosted or are you running your own server?

Yes its on a dedicated server

 

C) What OS are you running?

Fedora Core 6, PHP Version 5.1.2

[PFMaBiSmAd]

You are using php5.1, you need to upgrade to the latest 5.2.

 

The bug report you linked to indicates the #37560 bug was fixed in the latest (at the time) 5.2 version and the co-bug #36949 mentioned at the end of that bug report is listed in the php change log as being fixed in php 5.2.0.

[tmbrown]

I'm running PHP 5.1 on Debian

 

I'm still trying to work through your class, however in the meantime try using this and see what happens

 

 

<?php
class Application{
        public static function MySQLi(){
              $srv = "Server";
              $usr = "Username";
              $pwd = "Password";
              $dbs = "Database";
                    return new mysqli($srv,$usr,$pwd,$dbs);;
        }
}
?>

 

To Call It:

 

<?php
$mysqli = Application::MySQLi();
    $res = $mysqli->query("Your SQL");
        while($r = $res->fetch_assoc()){
            //Code to Execute
        }

?>

 

I'm going to give this some more attention, but my response may be delayed, because im at work, so bare with me.

[knowj]

The server admin was supposed to update PHP but im am still hassling him. I would do it myself but I do not want to be responsible for 40+ domains going down many of them e-commerce.

 

My knowledge of linux is still limited and updating software is not something i am comfortable with. The bug report on the mysql website said it was going to be fixed within PHP version 5.3.

 

I am unable to test this further now because of the measures we took prevent the bug from reoccurring. The downside is if the database connection is initiated at the beginning of a class and a long process is then performed (such as a large image upload) the database connection times out.

[tmbrown]

In theory and i mean in theory as long as you do not call the

mysqli_close($mysqli);

the connection should stay open, but we all know how that goes as far as opening the connection you should look at your interface and schematic of the application in that regard.

 

1)  The form is submitted

2)  The method/class is called to handle the image/file

3)  The mysql conn is initiated to store only the needed info (pic name, pic dir, id of the user who uploaded it)

4)  The mysql conn is closed

5)  Redirect

[knowj]

Sorry I forgot to add that i call $this->close within the database class __destruct.

 

The connections should stay open if you do not call $this->close(); but (if im right) it should reuse idle connections and not max out the database connections maximum. Please correct me if im wrong this is just my understanding of it.

[tmbrown]

You're right, as long as you do open a new connection.  That's why I like to wrap the mysqli class inside of a static class, it only truly opens it once.