Too many connection

[pascal_22]

Hello,

I had a problem last week and last month and i'm pretty sure that will happen again... so i want definitely solve it.

 

My site is made in PHP and MySql, 99% i use mysql_connect,mysql_query.... and to be honest, 85% of the time in DON'T CLOSE my connexion.. because it suppose closing by itseft at the end of the script.

 

In my useronline... i dont see any change... so it seems to be constant...

 

and last month ans last friday, i try to visite my site and i got a white page with only TOO MANY CONNEXION..... so i call the support i they restart my server....

 

since fiew week, i thought to change mysql_ to mysqli_... because it offer better performance and advantage...

 

So is that is a good idea to change mysql to mysqli?

 

Also, i think that will not solve my TOO MANY CONNEXION message, so is it true that all connexion are close a the end of the script for mysql_?

 

Do you have any suggest to me?

 

thanks

Pascal

[mac_gyver]

two questions -

 

1) is your script using only one database connection in it or do you perhaps have multiple connections (each with different credentials or you are forcing a new connection by specifying the 4th parameter in mysql_connect())?

 

2) what does your access log show during these times? are there a large number of concurrent requests being made, each one using one concurrent database connection?

 

except for a few buggy versions of php, that didn't perform garbage collection when errors occurred, the database connection should be destroyed when each instance of your script ends.

 

if you happen to have scripts that take a long time to run after they have finished using their database connection, you can close that that connection in your code so that you don't tie up one of the alloted connections to make it available to another instance of your script.

 

also, what is the maximum number of connections your database server has been configured to allow? if this is your server and it is set to a small default, you may simply need to set it to a larger more reasonable value for the amount of traffic your site gets.

Edited August 12, 2013 by mac_gyver

[pascal_22]

Hello Mac_gyver!

 

Thanks for your answer!

Here are my answer!!

1) I use only one database sometime i include my mysql_info_connect that open a conection: 
$cn=mysql_connect("localhost",user,pwd) or die(......);

mysql_select_db("myDB",$cn);

on some pages i call it more than onece

 

2)I didn't check my access log... but i'll check now and i come back with the answer

 

I dont have a lot of big script... i have some cronjob but i close my connection for cronjob

The support guy told me that tey change the max number of connection to 250

 

Thanks a lot! Ill go now check my access log!!

 

But is it a good idea to switch to mysqli_ ?

 

Thanks a lot!

[kicken]

1) I use only one database sometime i include my mysql_info_connect that open a conection: 

$cn=mysql_connect("localhost",user,pwd) or die(......);

mysql_select_db("myDB",$cn);

on some pages i call it more than onece

You should try and change your code so that you are only calling mysql_connect (or mysqli_connect) once. Calling it multiple times (with the same info) is unnecessary, though PHP should just return the existing connection rather than open a new one so it shouldn't be causing your issue.

 

But is it a good idea to switch to mysqli_ ?

Yes, for the simple fact that mysql_* functions are deprecated and slated to be removed:

It is not recommended to use the old mysql extension for new development, as it has been deprecated as of PHP 5.5.0 and will be removed in the future.

PDO or Mysqli are the current recommended connection methods.

 

 

Another thing to consider: if you are on a shared host, your issue may not have anything to do with your scripts. There could be scripts for one of their other customers that is eating up all the available connections, leaving your scripts (and others) unable to connect. This is something your host would have to investigate and correct, you would not have the capability.

[pascal_22]

hi!

 

Ok perfect! I will correct my code.

And i'll switch to mysqli. Thanks for the info !

 

I'm looking at my access log and i 'll come back in fiew minutes to tell you what i found!

 

But i have a question in my mind...

 

Exemple: if i take my index.php

0- i have include to update/insert onlinemember table

1- i have include for top menu

2- i have include for menu

3- i have include for left menu

4- then the content

6- include footter.

 

so in each include, i can use query(s). So for performance, is it better to open a connection,do the query,close the connection, and  open a connection,do the query,close the connexion,.................

OR

open connection in header and close it in footer...?

 

NOTE: in my code, i have function that i use query too...

 

Thanks!

[jazzman1]

The rule is: You should open db connection only, when you need to communicate with DB server.

Also, as kicken said, you must calling mysql_connect (or mysqli_connect) once.  

 

 

open connection in header and close it in footer...?

 

Php is a server side language, which means that every single line of the php code should be executed on the server first, before to send any outputs on the page (browser), unlike JavaScript for example. 

Php does not know, what is a header, sidebar, footer and so on, inside your html structure.

Do you understand me?

 

PS: Of course, that is true if you don't use output_buffering to cache the data, just to be clear

Edited August 12, 2013 by jazzman1

[pascal_22]

Hi!

 

If i understand you? yes and no...

 

i understand that the server will read all line of code before sending it to webbrowser, and i understand that i should open db connection when i need it... but my interrogation is: what if i use db connection in the beginning, in the middle and at the end(or near of at the end)

 

and between db connection use, i display the result. or i do some process

 

exemple:at the beginning of my code, i check user location by ip,i update/insert in online members, i check for new messages if user is logged in, i may have  include file to display specific things that needs database connetion, after it's the main content.. so i get data in db.

so at different place in a php file, i need db connection.

 

For to have the best performance, and use less server memory....

 

so i'm a bit lost! open i when needed and close it at the end in footer page...

 

thanks!

[kicken]

Open your connection at the beginning of the request. Don't close it yourself, let PHP handle closing it at script end.

 

That is the basic rule when it comes to managing a database connection.

[pascal_22]

Ok thanks!

 

But the fact is sometimes i receive the messages : TOO MANY CONNECTION.... so my website is not working..... i should restart my server....

It happens two time in 1 month and half... and i didnot reach my ,ax number connection.... 

 

thanks

[jazzman1]

To keep the things pretty simple, let's say that our web site has only 1 page, represent by display.php file. 

Inside this file we want to include header.php, home.php and footer.php files.

But, we also need to use a database in our project displaying some info inside the header and the home files.

So, if we try to open db connections inside header and home files then to include them inside display.php file, we will get in trouble and may be after awhile we'll receive a message for too many connecions.

To avoid it, we should open this db connection once on the top of the display file then just include all files that you need to be displayed.

[pascal_22]

Yes perfect! I understand it and that waht i start to correct my code....

 

but i close it in the footer? i read on the web that connection will close after the end of the script but in somes cases... they dont close....

so should i always close it in the footer?

 

should i check if the db is open, and if yes... i close it?

 

Thant for your gratefull help to all of you!!

 

thanks!

[jazzman1]

According docs of mysql and mysqli libraries, the connection will be closed automatically when the script ends unless your connection is persistent.

So, for PDO according docs, again, php won't be closed automaticaly the connection and it still remains active until the object is detroyed.

Assuming we have next files:

 

header.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
   "http://www.w3.org/TR/html4/strict.dtd">
<HTML>
   <HEAD>
      <TITLE>Some title</TITLE>
   </HEAD>
   <?php 
   
   // send the query to db server and display some result 
   
   ?>

home.php

 <BODY>
     
     <?php 
     
    // send the query string and display something 

?>

footer.php

   <?php 
   
  // do we have to close the connaection here or to the end of every including php file? 
   
   ?>
    </BODY>
</HTML>

display.php

<?php
// open the db connection once

$conn = mysql_connect('db_server','userName','userPass');

$db_name = mysql_select_db('db_name');

include 'header.php';

include 'home.php';

include 'footer.php';

What do you thing? Where should we have to close the connection?

Edited August 13, 2013 by jazzman1

[pascal_22]

Hello Jazzman1!

 

To answer your question, i should close the connection at the end of footer.php page!

right?

 

i know the connection will be close automatically but i think it's a good practice to close it!!

right?

 

thanks

Edited August 14, 2013 by pascal_22

[jazzman1]

No, the right answer is after the last function of this mysql* (pdo) library, but on the top of the footer file will be OK too

[pascal_22]

thanks!! i appreciate your help!!!!

 

Really appreciate!!!!

 

May i ask you one more question about the exemple of the template you show me?

[jazzman1]

Why don't you consider of using some template engine. I reccomend twig!

[pascal_22]

Yeah... that why i want to ask you.... i just create a new post on phpfreaks about that... and someone suggest me twig too....

 

but what does it make using twig?

 

i mean for now... i have a lot of pages and all display pages have their own tags(html,head,body).... like this

<html>
<head><title></title>all others thing that goes here</head>
<body>
   <div><?php  include 'topmenu.php';   ?>   </div>
  <div><?php  include 'leftmenu.php';   ?>   </div>
   <div>//content of active page</div>
 
   <div><?php  include 'footer.php';   ?>   </div>
 
</body>
</html>

it'S a short exemple but...... it's very long  when i change my desing or other,,,,,

 

what twig will make for me.....? should i install in on my online server or develpment server.....

 

all information about that will help me!!

 

thanks!!!