Re: [PHP-DB] MySQLi not closing connections
I was thinking the same bug, except that I'm not using real_connect (pass login params when initializing the class), and also the issue only occurs when logging is enabled. I'm really at a loss here :-\ -- Jonathan Langevin PHP Site Solutions http://www.phpsitesolutions.com On Wed, Nov 26, 2008 at 2:50 PM, Fergus Gibson <[EMAIL PROTECTED]> wrote: > On Wed, Nov 26, 2008 at 10:36 AM, Jonathan Langevin > <[EMAIL PROTECTED]> wrote: >> I would normally think there were problems elsewhere in the code, if >> the issues didn't stop once I comment out the logging functionality. > > I don't see anything that would account for your symptoms in that code > snippet, but it's important to remember that sometimes code in one > place can interact with code in another place to expose a bug. When > your application uses ext/mysql, it uses only built-in functions. > It's only when it uses ext/mysqli that it uses a customized sub-class. > I would look in that subclass for the problem. > > But honestly, I'm flummoxed, Jon. Since non-persistent connections > should automatically close when the script ends (normally or for an > error), I don't understand why the connections are remaining open only > when logging is enabled. I would expect it to be a by-product of the > connection process instead or a bug. > > Does the application use mysql_real_connect() or mysqli_real_connect() > at all? If so, are you using a version of PHP pre-5.3? There is a > bug and a bug fix for it. > > http://bugs.mysql.com/bug.php?id=33831 > http://bugs.php.net/bug.php?id=39457 > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: [PHP-DB] MySQLi not closing connections
-- Forwarded message -- From: Fergus Gibson <[EMAIL PROTECTED]> Date: Wed, Nov 26, 2008 at 11:50 AM Subject: Re: [PHP-DB] MySQLi not closing connections To: Jonathan Langevin <[EMAIL PROTECTED]> On Wed, Nov 26, 2008 at 10:36 AM, Jonathan Langevin <[EMAIL PROTECTED]> wrote: > I would normally think there were problems elsewhere in the code, if > the issues didn't stop once I comment out the logging functionality. I don't see anything that would account for your symptoms in that code snippet, but it's important to remember that sometimes code in one place can interact with code in another place to expose a bug. When your application uses ext/mysql, it uses only built-in functions. It's only when it uses ext/mysqli that it uses a customized sub-class. I would look in that subclass for the problem. But honestly, I'm flummoxed, Jon. Since non-persistent connections should automatically close when the script ends (normally or for an error), I don't understand why the connections are remaining open only when logging is enabled. I would expect it to be a by-product of the connection process instead or a bug. Does the application use mysql_real_connect() or mysqli_real_connect() at all? If so, are you using a version of PHP pre-5.3? There is a bug and a bug fix for it. http://bugs.mysql.com/bug.php?id=33831 http://bugs.php.net/bug.php?id=39457 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: Fwd: [PHP-DB] MySQLi not closing connections
-- Forwarded message --
From: Fergus Gibson <[EMAIL PROTECTED]>
Date: Wed, Nov 26, 2008 at 11:34 AM
Subject: Re: Fwd: [PHP-DB] MySQLi not closing connections
To: Chris <[EMAIL PROTECTED]>
On Tue, Nov 25, 2008 at 1:39 PM, Chris <[EMAIL PROTECTED]> wrote:
> Not really true.
>
> pconnect leaves the connection open ('persistent'). From the manual: "when
> connecting, the function would first try to find a (persistent) link that's
> already open with the same host, username and password."
Here's a citation from Zend.com:
"The mysql_pconnect() function was designed to provide a mechanism for
reducing the cost of establishing and closing connections to the MySQL
server. Unfortunately, due to an interaction between the architecture
of the Apache server and the architecture of PHP, high traffic on a
site that used pconnects could quickly clog up the MySQL server with
many unused connections that could prevent many of the active
connections from accessing the database."
http://devzone.zend.com/node/view/id/686#fn1
I understand how mysql_pconnect() is meant to work, which is what you
quote from the documentation, but I also believe the claims that there
is an incompatibility between Apache specifically and
mysql_pconnect().
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQLi not closing connections
Hi Fergus, you're correct, the original code was developed in a hasty
fashion by the developer that I am now working alongside.
It's a situation where he's been rushed for a year, and hadn't had the
time to get more organized.
I've been going through the code, and cleaning up where possible,
while also attempting to retain compatibility with existing code.
The mysql/mysqli implementation in the central config.php, only allows
either solution (mysql or mysqli) to be instantiated once by
config.php, based on the existence of certain constants. (And this is
regardless of how many times a script may try to include config.php)
I have not yet found *any* instance of pconnect being used anywhere.
Additionally, as mentioned, this issue only happens when I have the
logging functionality enabled, which is 4-5 lines of code that exist
in the shutdown function.
I would normally think there were problems elsewhere in the code, if
the issues didn't stop once I comment out the logging functionality.
Hopefully someone can see an error on my part. Here is my shutdown
function (instantiated in config.php):
query_log);
$activity_log['query_log'] =
serialize($mysqli->query_log);
$mysqli->set('date_added', 'NOW()', false);
$mysqli->insert('activity_log', $activity_log);
$mysqli->close();
}else{
$activity_log = array_map('mysql_real_escape_string',
$activity_log);
$activity_log['date_added'] = 'NOW()';
$sql = 'INSERT INTO `activity_log` (`' . implode('`,`',
array_keys($activity_log)) . '`) VALUES ("' . implode('","',
$activity_log) . '")';
mysql_query($sql);
mysql_close();
}
exit;
}
/* /Snippet */
?>
--
Jonathan Langevin
PHP Site Solutions
http://www.phpsitesolutions.com
On Tue, Nov 25, 2008 at 3:02 PM, Fergus Gibson <[EMAIL PROTECTED]> wrote:
> On Tue, Nov 25, 2008 at 6:31 AM, Jonathan Langevin
> <[EMAIL PROTECTED]> wrote:
>> The problem is, this activity log, when enabled, results in all MySQL
>> connections being used, and we run out of open connections. I'm not
>> sure where the error is.
>>
>> Our config.php, when included, initializes the MySQL or MySQLi
>> connection as decided by the script that included the config.
> [...]
>
> Hi, Jon. It's difficult to offer specific replies without specific
> code, but I did raise my eyebrow at the notion of mixing the mysql and
> mysqli extensions in your application. I'm guessing you've inherited
> some pretty messy code that is time-consuming to refactor.
>
> Is any of the code working against the mysql extension calling
> mysql_pconnect() to create a persistent database connection?
> Connections so opened CANNOT be closed using mysql_close().
>
> I have never written an application using persistent connections, but
> I did get hired at a company where the other programmers describe the
> same problem you're having (a proliferation of connections that
> overwhelmed the server). They blamed mysql_pconnect() and the lead
> programmer said that after he banned the use of that function, the
> problem went away completely. A comment to the PHP documentation
> suggests this is the result of a bad interaction between Apache and
> PHP.
>
>
>> Within the same file, a shutdown function is registered to
>> automatically close the MySQL or MySQLi connection at the end of
>> script execution (based on which connection was initialized to begin
>> with).
>
> This should not be necessary. The script will close all open
> resources when it terminates anyway. The reason for having and using
> close functions is to free resources while the script is still
> running. As mentioned above, persistent connections created by
> mysql_pconnect() cannot be closed by mysql_close() under any
> circumstances, nor will they automatically close on completion of the
> script. I believe that's your problem.
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: [PHP-DB] MySQLi not closing connections
I have never written an application using persistent connections, but
I did get hired at a company where the other programmers describe the
same problem you're having (a proliferation of connections that
overwhelmed the server). They blamed mysql_pconnect() and the lead
programmer said that after he banned the use of that function, the
problem went away completely. A comment to the PHP documentation
suggests this is the result of a bad interaction between Apache and
PHP.
Not really true.
pconnect leaves the connection open ('persistent'). From the manual:
"when connecting, the function would first try to find a (persistent)
link that's already open with the same host, username and password."
If you're on a shared host with lots of different mysql
username/passwords floating around, pconnect will be a very bad idea -
because you'll end up with lots of connections sitting around doing
nothing (and never being re-used).
If you're on dedicated hardware, pconnect can cut down the connection
time - but when using them, you need to tune apache as well.
There's lots of notes here:
http://www.php.net/manual/en/features.persistent-connections.php
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQLi not closing connections
On Tue, Nov 25, 2008 at 10:27 AM, Jack Mays <[EMAIL PROTECTED]> wrote: > I'm not sure why the connections are staying open, but I would suggest using > mysqli_real_connect with the flag to timout connections. [...] > If this is way off base, let me know. Jack, I think Jon shouldn't implement this suggestion. Adjusting the timeout isn't a good way to address spawning a plethora of connections. Jon should find and fix the bug that is causing so many connections to be opened or to persist in the first place rather than simply asking the database to close connections more quickly (your suggestion). Opening or maintaining all those connections is a big waste of resources in the first place. Logically, it's also bad form to make the connections close faster than they should because that means more resources will be used setting up and tearing down connections. I'm all for timeout tuning, but it's a separate issue here. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: [PHP-DB] MySQLi not closing connections
Darn it. Didn't change the e-mail recipient to be the list. -- Forwarded message -- From: Fergus Gibson <[EMAIL PROTECTED]> Date: Tue, Nov 25, 2008 at 12:02 PM Subject: Re: [PHP-DB] MySQLi not closing connections To: Jonathan Langevin <[EMAIL PROTECTED]> On Tue, Nov 25, 2008 at 6:31 AM, Jonathan Langevin <[EMAIL PROTECTED]> wrote: > The problem is, this activity log, when enabled, results in all MySQL > connections being used, and we run out of open connections. I'm not > sure where the error is. > > Our config.php, when included, initializes the MySQL or MySQLi > connection as decided by the script that included the config. [...] Hi, Jon. It's difficult to offer specific replies without specific code, but I did raise my eyebrow at the notion of mixing the mysql and mysqli extensions in your application. I'm guessing you've inherited some pretty messy code that is time-consuming to refactor. Is any of the code working against the mysql extension calling mysql_pconnect() to create a persistent database connection? Connections so opened CANNOT be closed using mysql_close(). I have never written an application using persistent connections, but I did get hired at a company where the other programmers describe the same problem you're having (a proliferation of connections that overwhelmed the server). They blamed mysql_pconnect() and the lead programmer said that after he banned the use of that function, the problem went away completely. A comment to the PHP documentation suggests this is the result of a bad interaction between Apache and PHP. > Within the same file, a shutdown function is registered to > automatically close the MySQL or MySQLi connection at the end of > script execution (based on which connection was initialized to begin > with). This should not be necessary. The script will close all open resources when it terminates anyway. The reason for having and using close functions is to free resources while the script is still running. As mentioned above, persistent connections created by mysql_pconnect() cannot be closed by mysql_close() under any circumstances, nor will they automatically close on completion of the script. I believe that's your problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQLi not closing connections
Jonathan Langevin wrote: Hoping someone may have some insight. I'm a PHP developer for my current employer. Recently, a coworker and myself started revamping our PHP-based intranet to add more OO functionality and replace some of the repetitive procedural code that was in place. In this revamp, we also converted the majority of our scripts to use MySQLi instead of MySQL. We're using the MySQLi class, which I've extended to add some logging functionality, and also incorporated some query builder methods (similar to what you'll see in CodeIgniter's Active Record class). To better troubleshoot semi-random bugs in our intranet, I've implemented an activity log in the database, to record all data in/out, current page, queries used, etc, which logs at the end of each page load. The problem is, this activity log, when enabled, results in all MySQL connections being used, and we run out of open connections. I'm not sure where the error is. Our config.php, when included, initializes the MySQL or MySQLi connection as decided by the script that included the config. Within the same file, a shutdown function is registered to automatically close the MySQL or MySQLi connection at the end of script execution (based on which connection was initialized to begin with). We never have connection issues during normal usage, but once the activity log is enabled (which will execute during shutdown), that is when we see the connections fill up fast. The weird thing is, to my knowledge, the same DB connection is being used, never duplicated. I've seen a PHP <5.3 bug that results in multiple connections being allowed when using the OO mysqli methods, but the mysqli class is only instantiated *once* per page load, same for the mysql procedural instance. The only possibility I can think of, is that normal mysql is being instantiated outside of the config.php include, but then we'd have connections disappearing continuously, not just when the activity log is enabled. I realize this is a long, and probably none-to-helpful email. If anyone can assist, I'd appreciate it. Any info you need, let me know. -- Jon L. I'm not sure why the connections are staying open, but I would suggest using mysqli_real_connect with the flag to timout connections. http://www.php.net/manual/en/mysqli.real-connect.php If this is way off base, let me know. -- Jack -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQLi not closing connections
Hoping someone may have some insight. I'm a PHP developer for my current employer. Recently, a coworker and myself started revamping our PHP-based intranet to add more OO functionality and replace some of the repetitive procedural code that was in place. In this revamp, we also converted the majority of our scripts to use MySQLi instead of MySQL. We're using the MySQLi class, which I've extended to add some logging functionality, and also incorporated some query builder methods (similar to what you'll see in CodeIgniter's Active Record class). To better troubleshoot semi-random bugs in our intranet, I've implemented an activity log in the database, to record all data in/out, current page, queries used, etc, which logs at the end of each page load. The problem is, this activity log, when enabled, results in all MySQL connections being used, and we run out of open connections. I'm not sure where the error is. Our config.php, when included, initializes the MySQL or MySQLi connection as decided by the script that included the config. Within the same file, a shutdown function is registered to automatically close the MySQL or MySQLi connection at the end of script execution (based on which connection was initialized to begin with). We never have connection issues during normal usage, but once the activity log is enabled (which will execute during shutdown), that is when we see the connections fill up fast. The weird thing is, to my knowledge, the same DB connection is being used, never duplicated. I've seen a PHP <5.3 bug that results in multiple connections being allowed when using the OO mysqli methods, but the mysqli class is only instantiated *once* per page load, same for the mysql procedural instance. The only possibility I can think of, is that normal mysql is being instantiated outside of the config.php include, but then we'd have connections disappearing continuously, not just when the activity log is enabled. I realize this is a long, and probably none-to-helpful email. If anyone can assist, I'd appreciate it. Any info you need, let me know. -- Jon L. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
