CODEWITHSUNDEEP
phpmysql
Jagadeesan
Jagadeesan

Reputation: 1097

How to close unclosed mysql connections?

My clients hosting provider blocks the site because of too much unclosed mysql connections. the site was previously created by someone and currently I'm maintaining it.

I have addded the mysql_close function at the end of the pages. It is closing the connections good but still I'm getting some connections left unclosed. I may left some where..

What I need is to close the unclosed mysql connections in the server using a cron file or some thing..

What do I have to do?

Is it possible to close all the connections at once? if so, how?

Upvotes: 7

Views: 22778

Answers (3)

Nanne
Nanne

Reputation: 64399

Are you using persistent connections? IF not, then you really shouldn't worry too much about closing your connections. From the manual

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution. See also freeing resources.

Instead of too_much unclosed connections, couldn't it be (which is essentially the same ofcourse) that you have too many open connections? For instance, too many users on your site at once?

If you do have persistent connections, do not forget this:

mysql_close() will not close persistent links created by mysql_pconnect().

As said in the comment, it is highly unlikely that a mysql_connect() resource is not freed at the end of the script. From another manual page

Freeing resources

Thanks to the reference-counting system introduced with PHP 4's Zend Engine, a resource with no more references to it is detected automatically, and it is freed by the garbage collector. For this reason, it is rarely necessary to free the memory manually.

Note: Persistent database links are an exception to this rule. They are not destroyed by the garbage collector. See the persistent connections section for more information.

There could be a sidenote however, from the comments on the mysql_close page

At least with PHP5.3.2 and Windows connecting by tcp, you should always use this mysql_close() function to close and free up the tcp socket being used by PHP. Garbage collection after script execution does not close the tcp socket on its own. The socket would otherwise remain in 'wait' state for approximately 30 seconds, and any additional page loads/connection attempts would only add to the total number of open tcp connections. This wait time does not appear to be configurable via PHP settings.

Upvotes: 6

jishi
jishi

Reputation: 24614

mysql_connect would return an identifier for the actual connection, which you can use in your mysql_close call.

$conn1 = mysql_connect(.....);
mysql_close($conn1);

You need to use the identifier if the page itself opens more than one connection, because otherwise mysql_close() would only close the last opened connection.

But as Nanne said, PHP usually cleans up connections after itself after page execution, so the question is if you don't close them, or if you open too many simultaneously. Usually you would only need 1 connection per request, unless you open and iterate through multiple resultsets at the same time.

Upvotes: 2

BugFinder
BugFinder

Reputation: 17858

If you are getting some unclosed connections, chances are you missed some pages, maybe via includes etc, or functions calling "exit" so you dont reach your close code.

I dont believe you can boot other connections from mysql using cron.

Upvotes: 0

Related Questions