7

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?

Jagadeesan
  • 1,087
  • 3
  • 9
  • 24
  • you could try using `mysql_pconnect`, which has sideeffects though as described in http://stackoverflow.com/questions/1340859/which-is-better-mysql-connect-or-mysql-pconnect – konsolenfreddy Jun 12 '11 at 07:43
  • how to close the connection opened in mysql_pconnect let try that.. – Jagadeesan Jun 12 '11 at 07:52

3 Answers3

6

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.

Nanne
  • 64,065
  • 16
  • 119
  • 163
  • connections seems to be opened with mysql_connect() only.. how to close the opened even in mysql_pconnect let try that.. – Jagadeesan Jun 12 '11 at 07:48
  • 1
    But to be sure: you shouldn't need to close every connection in the end of your files: it will be closed automatically there. You only need to close it if you want to do this halfway, to free resources. Do not waste and effort adding `mysql_close` to all your scripts :D. If you don't use `pconnect`, you don't need to worry about closing those ofcourse – Nanne Jun 12 '11 at 07:51
  • No it is not closing by itself :( – Jagadeesan Jun 12 '11 at 08:01
  • 2
    It is HIGHLY unlikely that they are not closing. EXTREMELY even. If the page stops running, the connection gets closed. Your problem could be that the page doesn't stop running, too many pages are running at once, you are using pconnect etc etc, but the chance that you have a normal php script with `mysql_connect()` that doesn't close its connection in the end seem nil to me. You'd have found an unknown bug in php. – Nanne Jun 12 '11 at 08:05
2

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.

jishi
  • 24,126
  • 6
  • 49
  • 75
0

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.

BugFinder
  • 17,474
  • 4
  • 36
  • 51