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):
?php
/* Snippet */
function shutdown() {
global $time_start, $_COOKIE;
$load_time = microtime(true) - $time_start;
$activity_log = array();
$activity_log['username'] = $_COOKIE['ID_my_site'];
$activity_log['server'] = $_SERVER['SERVER_NAME'];
$activity_log['url'] = $_SERVER['PHP_SELF'];
$activity_log['referer'] = $_SERVER['HTTP_REFERER'];
$activity_log['load_time'] = $load_time;
$activity_log['server_var'] = serialize($_SERVER);
if(!empty($_POST)) {
$activity_log['post_var'] = serialize($_POST);
}
if(!empty($_GET)) {
$activity_log['get_var'] = serialize($_GET);
}
if(defined('USE_MYSQLI') USE_MYSQLI==true) {
global $mysqli;
$query_count = count($mysqli-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.
--
RE: [PHP-DB] Unable to Login to Oracle 9i-R2 Database Using PHP 5.2.5
Chris/Rick/Neil, I put together a test page (test.php) with content ?php php_info(); ?. When the page is displayed, I don't see anything related to OCI8. To Rick's point, the module isn't being uploaded. I believe I know why it isn't loading. I don't believe the ISAPI PHP module is loading the right PHP.ini (ini) file. When I run php at the CLI, it loads the ini in the same directory as PHP's executable (c:\php\) i.e., it loads c:\php\php.ini. C:\php --ini Configuration File (php.ini) Path: C:\WINDOWS Loaded Configuration File: C:\PHP\php.ini Scan for additional .ini files in: (none) Additional .ini files parsed: (none) When I look at the php information displayed by my test page, it looks like php is looking for an ini-file in the C:\Windows\ directory. Configuration File (php.ini) Path - C:\WINDOWS Loaded Configuration File - (none) This gets better. When process the page on the CLI (e.g., php -f file), it processes without an issue. As stated before, it (the same page) fails when processed thru IIS. I need to do some more work to get this fixed, but your comments were a big help. Thanks! A- -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Monday, November 24, 2008 5:54 PM To: Fortuno, Adam Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Unable to Login to Oracle 9i-R2 Database Using PHP 5.2.5 Rick, when I delve into the php_info() shmaz, I see this snipet - see below. I'm not sure if I should be looking for something else or not. Try doing this from a web page. Maybe it has a different php.ini or something. Anything in the IIS error log (probably goes to the windows logs) ? -- Postgresql php tutorials http://www.designmagick.com/ -- 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
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 connections
-- Forwarded message -- From: Fergus Gibson [EMAIL PROTECTED] Date: Wed, Nov 26, 2008 at 11:55 AM Subject: Re: Fwd: [PHP-DB] MySQLi connections To: J. Hill [EMAIL PROTECTED] On Tue, Nov 25, 2008 at 3:12 PM, J. Hill [EMAIL PROTECTED] wrote: I am used to creating a class and a database handle for functions to use, but I inherited an intranet that just uses a single $mysqli = mysqli_connect in a global main file and the just uses global $mysqli in all of it's functions (several hundred) that interact with the database. [...] Could anyone point me towards any documentation on why such a structure is bad? It's bad if you ever want to use something other than mysqli! Imagine your company switching to another database server. You'd have to rewrite code in hundreds of functions! This is where a database wrapper class could have saved a lot of headache. As it is, the least expensive way to migrate databases permitted by the original programmer's choice would be to write an adapter class and substitute it in place of mysqli and hope there are no SQL incompatibilities. Not very efficient though. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Fwd: Fwd: [PHP-DB] MySQLi connections
Fergus Gibson wrote: -- Forwarded message -- From: Fergus Gibson [EMAIL PROTECTED] Date: Wed, Nov 26, 2008 at 11:55 AM Subject: Re: Fwd: [PHP-DB] MySQLi connections To: J. Hill [EMAIL PROTECTED] On Tue, Nov 25, 2008 at 3:12 PM, J. Hill [EMAIL PROTECTED] wrote: I am used to creating a class and a database handle for functions to use, but I inherited an intranet that just uses a single $mysqli = mysqli_connect in a global main file and the just uses global $mysqli in all of it's functions (several hundred) that interact with the database. [...] Could anyone point me towards any documentation on why such a structure is bad? It's bad if you ever want to use something other than mysqli! Imagine your company switching to another database server. You'd have to rewrite code in hundreds of functions! This is where a database wrapper class could have saved a lot of headache. As it is, the least expensive way to migrate databases permitted by the original programmer's choice would be to write an adapter class and substitute it in place of mysqli and hope there are no SQL incompatibilities. Not very efficient though. You're going to have a lot more problems with changing db servers than just search/replac'ing mysqli calls. Eg - 'LIMIT' - the format is different for postgres, mssql doesn't have it nor does oracle. -- 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: Fwd: Fwd: [PHP-DB] MySQLi connections
On Wed, Nov 26, 2008 at 1:45 PM, Chris [EMAIL PROTECTED] wrote: It's bad if you ever want to use something other than mysqli! Imagine your company switching to another database server. You'd have to rewrite code in hundreds of functions! [...] You're going to have a lot more problems with changing db servers than just search/replac'ing mysqli calls. Eg - 'LIMIT' - the format is different for postgres, mssql doesn't have it nor does oracle. In case it was unclear, that's precisely my point. Abstraction would make it much easier to change databases later if need be because all the SQL could be contained in a class with a simple interface exposed to the rest of the application. Switching databases would only involve making changes to that class then, which is much easier than trying to update many functions in many different places. In the current scheme Mr. Hill describes, each function is tightly coupled to the database because they all feed SQL to the mysqli class directly. He asked what's wrong with the scheme, and I think it's fair to say that's the problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
