counting mySQL connections

[httpwebwitch]

I've got a script that opens various MySQL connections, using mysql_connect(blah,blah,blah,true).

I need to keep track of which ones are being opened.

At the end of the script, before all the connections are closed, I need a way to count the number of open MySQL connections... and perhaps see what they are connected to.

is there a way to do that?

[httpwebwitch]

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.

source: [php.net...]

I am, indeed, opening multiple connections using the same parameters.

I need to know if this is in fact happening or not, in my staging and production environments.

[httpwebwitch]

a related question, explained in code:

if($conditionA)
$linkA = mysql_connect(blah,blah,blah,true);
}

if($conditionB){
$linkB = mysql_connect(yadda,yadda,yadda,true);
}else{
$linkB = $linkA;
}

// then at the end of the script:

if(isset($linkA)){
mysql_close($linkA);
}

if(isset($linkB)){
mysql_close($linkB);
}

* * *

explanation

if $conditionA is true, then $linkA is a connection to database A.
if $conditionB is true, then $linkB is a connection to database B... else it's going to make its queries to database A.

The easiest way to reuse tons of code throughout my app is to assign $linkB = $linkA.

However, as this object is destructed, the second mysql_close() command fails, because $linkA gets closed before $linkB. You can't close something that's closed.

The warning is:

Warning: mysql_close(): 18 is not a valid MySQL-Link resource in yadda yadda yadda, line xx.

To see if a connection exists, "isset" isn't cutting it. Evidently it's easy to end up with a connection link that is closed.

How can I tell if a connection is open? Do I have to send it a query and see if it returns an error? That's just silly.

[httpwebwitch]

I have discovered that enumerating open MySQL connections isn't possible, or at least there's no obvious or documented method in PHP for doing it. Instead, I need to manage connections carefully in my code, close them manually when appropriate, and then rely on PHP to close the rest when the script completes.

It's likely possible using exec() shell commands, but that's not the direction I want to go with this script

[Matthew1980]

Hi there httpwebwitch,

I'm pleased as you have followed this thread up as I was quite interested in how this actually panned out, In theory you can or at least should be able to count the amount of active connections to a DB, but as yet I haven't found a way either; though the concession is I am using a program that I have written in VB for use as bespoke software at the company I work at - and actually struggling to find a method of getting that info to an admin screen.

I suppose that it is just naming the connection handles appropriately and good house keeping that will keep you aware of your position throughout your code.

Hopefully there is a method of doing this specifically for php/mysql which could be really useful.

Good luck anyway.

Cheers,
MRb

[Demaestro]

Is it not possible to add to your code something that logs connections?

I assume that you are using a common piece of connection code that you include in many places. In that connection code make a call to a new function that logs info at time of connection.

Something like

logConnection(req_page_name) {
$myFile = "connection_log.txt";
$fh = fopen($myFile, 'w') or die("can't open file");
$log_entry = "Page Requsting:"
$log_entry += THE_PAGE
$log_entry += " Time Requsted:"
$log_entry += NOW()
$log_entry += " More info..."
$log_entry += '\n'
fwrite($fh, $stringData);
}

Something else you could do is find all the places that close the connection and add in a line that makes a call to "logClosedConnection()"

And you can make an entry in the log about when the connection was closed.

Once you have that data being recorded you can go in and see all the connections that were opened, when connections got closed and you can count how many are open at any given time.

It might get you some of what you are looking for.

[httpwebwitch]

I agree Demaestro, I can overcome the deficiencies of PHP by wrapping the open and close in a custom function... if I just keep track of opening and closing things by pushing and popping keys into an array.

I did something like that, but didn't bother logging it - I just echoed stuff out while debugging, ran it a few times, saw that it was doing what I wanted, then removed the tracer echoes.

oh btw, putting a try/catch around the mysql_close() statement didn't help either, the warning still shows up in my error log. It's just a warning, no fatal deal... but I like having my error reporting set to "super sensitive" - it forces me to deal with these things and smooth out my code. I am happier when the PHP error log has a size of 0Kb

[httpwebwitch]

hey - has anyone ever tried using mysql_ping() ?

[php.net...]

it's supposed to tell if a connection is open. So (as in the example above) if I have $linkB where the connection was closed by $linkA, then mysql_ping($linkB) should return false.

I've never used it but I'd be willing to try it out