[RESOLVED] mysqli database class causing max connections error

knowj · Jul 14, 2008

I am using the singleton pattern for my database class. Instances are created in each class on $this->database and queries called using $this->database->query("SOME QUERY");

For some reason though each connection is not being closed and just sleeping cauing the max connections limit to be reached and taking every site down on the sever.

<?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());
			}
	}
//public create instance function to create singleton
public function getinstance()
	{
		if(self::$instance === null)
			{
				$c = __CLASS__;
				self::$instance = new $c;
			}
		return self::$instance;
	}
//prevent SQL injection
function antisql($data)
	{
		if (is_array($data))
			{
				foreach ($data as $name=>$value)
					{
						if (is_array($data[$name]))
							{
								foreach ($data[$name] as $name2=>$value)
									{
										$data[$name][$name2] = $this->real_escape_string($value);
									}
							}
						$data[$name2] = $this->real_escape_string($value);
					}
			}
		else
			{
				$data = $this->real_escape_string($data);
			}
		return $data;
	}
//prevent clone
public function __clone()
	{
		throw new Exception("Cannot clone ".__CLASS__." class");
	}

public function __destruct()
	{
		$this->close();
	}
}
?>

show process:

mysql> SHOW PROCESSLIST;
+------+-----------+-----------+-----------+---------+------+-------+------------------+
| Id   | User      | Host      | db        | Command | Time | State | Info             |
+------+-----------+-----------+-----------+---------+------+-------+------------------+
|  360 | table001 | localhost | table001 | Sleep   | 3409 |       | NULL             | 
|  361 | table001 | localhost | table001 | Sleep   | 3409 |       | NULL             | 
|  362 | table001 | localhost | table001 | Sleep   | 3409 |       | NULL             | 
|  363 | table001 | localhost | table001 | Sleep   | 3409 |       | NULL             | 
|  364 | table001 | localhost | table001 | Sleep   | 3408 |       | NULL             | 
|  365 | table001 | localhost | table001 | Sleep   | 3408 |       | NULL             | 
|  366 | table001 | localhost | table001 | Sleep   | 3408 |       | NULL             | 
|  367 | table001 | localhost | table001 | Sleep   | 3408 |       | NULL             | 
|  523 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  524 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  525 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  526 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  535 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  536 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  537 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  538 | table001 | localhost | table001 | Sleep   | 3237 |       | NULL             | 
|  540 | table001 | localhost | table001 | Sleep   | 3236 |       | NULL             | 
|  541 | table001 | localhost | table001 | Sleep   | 3236 |       | NULL             | 
|  542 | table001 | localhost | table001 | Sleep   | 3236 |       | NULL             | 
|  543 | table001 | localhost | table001 | Sleep   | 3236 |       | NULL             | 
|  544 | table001 | localhost | table001 | Sleep   | 3235 |       | NULL             | 
|  545 | table001 | localhost | table001 | Sleep   | 3235 |       | NULL             | 
|  546 | table001 | localhost | table001 | Sleep   | 3235 |       | NULL             | 
|  547 | table001 | localhost | table001 | Sleep   | 3235 |       | NULL             | 
|  551 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  552 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  553 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  554 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  555 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  556 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  557 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  558 | table001 | localhost | table001 | Sleep   | 3229 |       | NULL             | 
|  561 | table001 | localhost | table001 | Sleep   | 3227 |       | NULL             | 

....shortened to allow post!
+------+-----------+-----------+-----------+---------+------+-------+------------------+
125 rows in set (0.00 sec)

thanks in advance

NogDog · Jul 14, 2008

I can't see why your class should matter in this (but that doesn't mean it can't, of course ). Found this bug, which sounds related, but has to do with mysqli::real_connect().

knowj · Jul 14, 2008

Cheers for the info nogdog.

Does anything look out of the ordinary with how i have written this then? I just need to rule out me being a complete idiot within my code. But 8 connections for each page seems excessive. It does seem to be similar to the bug you linked to.
class products (extract)

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();
	}

public function getinstance()
	{
		if(self::$instance === null)
			{
				$c = __CLASS__;
				self::$instance = new $c;
			}
		return self::$instance;
	}

//prevent clone
public function __clone()
	{
		throw new Exception("Cannot clone ".__CLASS__." class");
	}
public function getallproducts($order='name')
		{
			$order = $this->database->antisql($order);
			if ($results = $this->paging->query("SELECT categories.name, categories.url, products.id, products.product_code, products.style_name, products.category_id, products.colour_code, products.colour_description, products.size_code, products.style_code, products.collection_code, products.product_group, products.net_weight, products.rrp, products.offer_price, products.stock, products.points, products.video, products.description, products.live FROM `products` INNER JOIN categories ON products.category_id=categories.id ORDER BY $order ASC"))
				{
					return $results;
				}
			return 0; 
		}
}
?>

product range

				$products = products::getinstance();
				$product = $products->getallproducts();
				while ($row = $product->fetch_array(MYSQLI_ASSOC))
					{
						$image = $products->getprodimage($row['id']);
						?>
						<li class="product">
							<a href="<?=$products->producturlbuilder($row['id'])?>"><img src="<?=$image['front']['range']?>" alt="<?=$row['style_name']?>"/></a><br />
							<h2><?=$row['style_name']?></h2>
							<span class="price">&pound;<?=money_format('%i', $row['rrp'])?></span>
							<span class="details"><a href="<?=$products->producturlbuilder($row['id'])?>">view details</a></span>
						</li>
						<?php
					}

[RESOLVED] mysqli database class causing max connections error

Kknowj

NogDog

Kknowj