Page 1 of 1
Should I close the MySQL connection?
Posted: Wed Jul 26, 2006 4:03 am
by JasonMWaldo
I'm currently learning classes and writing a set of programs that will be accessing a MySQL database. I have been coding PHP for about six months, four of that working with MySQL. I decided to teach myself classes since they sounded pretty neat. I have adapted a couple of functions already into a database class and was actually about to post an issue I was having when I had an epiphany moment and resolved it.
As a word of warning, when calling a method that either opens or closes a database connection and uses a conditional that is driven by a variable passed to it to determine whether to open or close a database connection, make sure you put the statement to open a new instance of the class within the conditional and not before it, if you stick it before it, you will open a new instance and it will result in a complaint about there being no database connection to close.
Now for the question. Is it better to close the connection to the database in your script, or just to leave it hanging there until it times out and is closed by the server?
Posted: Wed Jul 26, 2006 4:05 am
by Benjamin
The trusty PHP Manual wrote:
Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.
&
mysql_close() will not close persistent links created by mysql_pconnect().
Posted: Wed Jul 26, 2006 4:12 am
by JasonMWaldo
Yep, I read that, but I also noticed in all of the sample scripts that I saw in the MySQL portion of it, they always closed that connection. I think I remember seeing something about a 15 to 30 second timeout on the connection also.
I guess the gist of the question is, will the overhead of waiting for the session to close, if it not explicitly closed, run into the possiblity of a user coming into a live internet site not being able to access the database because: a) your scripts are overloading the server with connections that should already be closed because they are no longer needed, or b) all of your allowed connections to the database are occupied by connections that should have already been closed.
To my mind, I think closing the connection explicitly overcomes the possiblity of Murphy biting you in the tail end because the script handler did not clean up after itself.
Posted: Wed Jul 26, 2006 4:20 am
by Benjamin
It's better to be safe than sorry. My gut instinct would tell me to use mysql_pconnect() if I was concerned about that though. I'm not sure there is a 15 second timeout, although if there is you can probably specify that in my.cnf, and it might only be for persistant connects. I would guess that php's garbage collection routines automatically close connections opened with mysql_connect().
EDIT I just noticed that in the manual for mysql_connect() it says..
The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close().
Posted: Wed Jul 26, 2006 4:29 am
by JasonMWaldo
Good pints on the issue, thanks. I think I will continue to explicitly close the connection for safeties sake. I'm not quite up to the point where I have seen a need for a more persistant connection than that provided by a single page load and the queries handled by that one page. Is there any security risk to leaving the connection open through mysql_pconnect() when you are using the connection for something like a session during which the user is likely to call fifteen or twenty pages that will all be handling connections to the database? Also, does it make a difference in the overhead if a persistent connection is used as opposed to openning a new connection each time and then closing that connection after all queries are executed?
Posted: Wed Jul 26, 2006 4:33 am
by Benjamin
Posted: Wed Jul 26, 2006 4:43 am
by JasonMWaldo
Thanks astions, after reading that and skimming through the comments on the mysql_pconnect() page in the online PHP manual, I think I will avoid them on anything that I am putting on the web. I'll probably play around with them on my own system just to see what the differences in performance look like though.
Well, after beating my head against the keyboard over the original issue in the word of warning section I posted earlier for the last 5 hours, I think it is time to get some sleep. It's 5:30am here.