Max_user_connections error

PHP
1

I wonder if somebody can help me please, I keep getting a ‘max_user_connections’ I think because I’m not closing the connection. At the moment I can’t check my script and just wanted to ask if this okay going to work or if I’m doing something wrong?

Thanks

  ### create database connection
  $dbLinkInt=new mysqli( "p:".$dbAddrInt,$dbUserInt,$dbPassInt,$dbBaseInt );
  if( $dbLinkInt->connect_error ) {
    if( $dbdebug ) {
      die( 'Database Connect Error ('.$dbLinkInt->connect_errno.') '.$dbLinkInt->connect_error );
    } else {
      logdberr( 'Connecting to internal database...','Connect Error ('.$dbLinkInt->connect_errno.') '.$dbLinkInt->connect_error );
   //   header( "Location: /error/dberror.php" );
      exit();
    }
  }
  mysql_close($dbLinkInt);


  ### database select
  function dbselect( $query,$dblink ) {
    if( $result=$GLOBALS[$dblink]->query( $query ) ) {
      while( $row=$result->fetch_array( MYSQLI_ASSOC ) ) { $dbdata[]=$row; }
      if( !isset( $dbdata ) ) { $dbdata=false; }
      if( is_object( $result ) ) {
        $result->close();
      }
      return( $dbdata );
    } else {
      if( $GLOBALS['dbdebug'] ) {
        die( 'Query failed:<br /><br />'.$query.'<br /><br />('.$GLOBALS[$dblink]->error.')' );
        exit();
      } else {
        logdberr( $query,'Query failed ('.$GLOBALS[$dblink]->error.')' );
    //    header( "Location: /error/dberror.php" );
        exit();
      }
    }
  mysql_close($dbLinkInt);
  }
2

Any particular reason you’re using persistent connections?

3

Persistent connections come from requesting too much from the database. If you are on a free hosting provider, you will see this because they typically disable more than one connection since they want you to pay for more service.

I recommend going back and redoing your code. I can see that you are mix matching mysqli_* and mysql_*. Please don’t do this as it may cause problems later in the future.

1 Like
4

I’m paying for my hosting and with the package it says I get 20 x1gb databases (I’ve only used 2% of one of them) with low contention (I don’t know what that means) so I don’t think that’s the case, or at least I hope that’s not the case.

I’m need to request a lot of information from the database because I’m working on a site that compares various products so there’s a lot of filters that people can search via. Without any of those filters working I’m using this to get the results from the database (multiple tables need to be searched):

$query = "SELECT * from feeds, product_categories_map, timing, flavour, source, dietary, ingredients, sweeteners, type WHERE %s %s %s feeds.price >=%s and feeds.price <=%s and feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 GROUP BY feeds.product_name ORDER BY `%s` %s %s";

$query = sprintf($query, $brand_added, $weight_added, $freesearch, $_GET['budgetFrom'], $_GET['budgetTo'], $_GET['sortedBy'], $_GET['sortDirection'],$sql_limit);
    $product_results=dbselect( $query,"dbLinkInt" );

I’ve changed the mysql_close() to mysqli_close() now so hope that will prevent the connection from being open?

I’m getting desperate to get this working and am really grateful for you helping with this.

Thank you

5

Don’t close the database connection. Let PHP do this for you. It’s not good practice to keep opening a new connection every time you want to make a database call. I think the problem actually likes in the logic of the code.

Don’t be. The end result will be more satisfying if you don’t rush it. The more you rush it, the more errors you’ll end up trying to fix.

Can you please provide more code? It would be more helpful seeing what is causing the max persistents.

6

That query looks completely wrong and I don’t think it does what you think it does. I’m fairly certain since you’re not joining on anything all the data is being exponentially duplicated.

7

Yes. It certainly does.

I looks like it’s using “%s” from printf format as parameter markers instead of "?"s. And using parameter markers for both values and identifiers.

http://php.net/manual/en/mysqli.prepare.php

they are not allowed for identifiers (such as table or column names)

8

It is, but the code uses sprintf() to replace the values, possibly just because it would allow order, limit and so on to be specified. A bit scary that it uses some user-supplied values without validation.

2 Likes
9

Thank you for helping with this, the full code is:


$arr = $arrayResults;

foreach ($arr['budgetFrom'] as $budgetFrom) {
}
foreach ($arr['budgetTo'] as $budgetTo) { 
}
foreach ($arr['brand'] as $brand) { 
	$brand_joined = "\"" . implode("\" OR \"", $arr['brand']) . "\"";
	if (count($arr['brand']) >= 2) {
		$brand_added = ' brand_name IN ('.$brand_joined.') and ';
	}
}
foreach ($arr['timing'] as $timing) { 
	$timing_joined = "\"" . implode("\" OR \"", $arr['timing']) . "\"";
	if (count($arr['timing']) >= 2) {
		$timing_added = ' timing_id = '.$timing_joined.' and timing.product_id = feeds.product_feed_id ';
	}
}
foreach ($arr['weight'] as $weight) { 
	$weight_joined = "\"" . implode("\" OR \"", $arr['weight']) . "\"";
		$weight_added = ' weight_id IN ('.$weight_joined.') and ';
}
foreach ($arr['flavour'] as $flavour) { 
	$flavour_joined = "\"" . implode("\" OR \"", $arr['flavour']) . "\"";
	if (count($arr['flavour']) >= 2) {
		$flavour_added = ' flavour_id = '.$flavour_joined.' and flavour.product_id = feeds.product_feed_id ';
	}
}
foreach ($arr['source'] as $source) { 
	$source_joined = "\"" . implode("\" OR \"", $arr['source']) . "\"";
	if (count($arr['source']) >= 2) {
		$source_added = ' source_id = '.$source_joined.' and source.product_id = feeds.product_feed_id ';
	}
}
foreach ($arr['dietary'] as $dietary) { 
	$dietary_joined = "\"" . implode("\" OR \"", $arr['dietary']) . "\"";
	if (count($arr['brand']) >= 2) {
		$dietary_added = ' dietary_id = '.$dietary_joined.' and dietary.product_id = feeds.product_feed_id ';
	}
}
foreach ($arr['ingredients'] as $ingredients) {
	$ingredients_joined = "\"" . implode("\" OR \"", $arr['ingredients']) . "\"";
	if (count($arr['ingredients']) >= 2) {
		$ingredients_added = ' ingredients_id = '.$ingredients_joined.' and ingredients.product_id = feeds.product_feed_id ';
	}
}
foreach ($arr['sweeteners'] as $sweeteners) { 
	$sweeteners_joined = "\"" . implode("\" OR \"", $arr['sweeteners']) . "\"";
	if (count($arr['sweeteners']) >= 2) {
		$sweeteners_added = ' sweeteners_id = '.$sweeteners_joined.' and sweeteners.product_id = feeds.product_feed_id ';
	}
}
foreach ($arr['ptype'] as $type) { 
	$type_joined = "\"" . implode("\" OR \"", $arr['ptype']) . "\"";
	if (count($arr['ptype']) >= 2) {
		$type_added = ' type_id = '.$type_joined.' and type.product_id = feeds.product_feed_id ';
	}
}

$start = (int) $_GET['pageNumber'] * $_GET['tableLength'];

if ($_GET['pageNumber']=="1"){ 
	$sql_limit = " LIMIT 20";
}
if ($_GET['pageNumber']>="2"){ 
	$sql_limit = " LIMIT $start,20";
}
if ($_GET['freesearch']<>""){ 
	$freesearch = " `product_name` LIKE '%" . $_GET['freesearch'] . "%' and "; 
} 
$query = "SELECT * from feeds, product_categories_map, timing, flavour, source, dietary, ingredients, sweeteners, type WHERE %s %s %s price >=%s and price <=%s and enabled=1 and stock=1 and deleted=0 GROUP BY product_name ORDER BY `%s` %s %s";

$query = sprintf($query, $brand_added, $weight_added, $freesearch, $_GET['budgetFrom'], $_GET['budgetTo'], $_GET['sortedBy'], $_GET['sortDirection'],$sql_limit);
    $product_results=dbselect( $query,"dbLinkInt" );

 $json_data = json_encode($product_results);
10

I don’t see much problem other than the sprintf function being used. It’s better off to use either PDO or mysqli_ to deal with database calls. Do you access to your php.ini file?

11

Unfortunately I don’t have access to that, I’m on a shared hosting package with limited access - I don’t even have cPanel!

13

If it’s a possibility, I’d say to switch to one that supports this because it may be a solution to your max persistent connections. I had the same error a while ago. I fixed mine by setting it in my php.ini file.

14

I have considered that to be honest, I think that it’s getting to the point were I may actually have to

15

Though there also could be something in your code snippet that may be slowing the connection.

16

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.