You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Helmut Zeilinger <hz...@hzlabs.de> on 2007/03/29 13:46:22 UTC
Apache::DBI (another) question
Hi all,
Software (Apache 2.2.4, mpm prefork / 8 children, mp 2.0.3, Apache::DBI
1.06)
On Apache startup i am loading a module via "startup.pl", which establishes
a database (mysql) connection:
[..]
package SomeModule;
..
sub new {
my $this = shift;
my $class = ref($this) || $this;
my $self = {};
bless $self, $class;
$self->{'dbh'} = DBI->connect ("DBI:mysql:somedb:localhost", "root",
"", {RaiseError => 1});
return $self;
}
my $object = HZLabs::DBInit->new ();
[..]
"$object" lives until the server is killed again.
startup.pl:
[..]
use Apache::DBI;
use DBI;
use SomeModule;
[..]
While running the server there is only one apache - mysql connection
visible via
"mysqladmin processlist" and not one connection for each apache child
process
as usually.
I have found no irregularities, but this statement in the Apache::DBI
manpage makes me
worried:
"...Also it is not possible to create a database handle upon startup of
the httpd and then inheriting this handle to every subsequent server.
This will
cause clashes when the handle is used by two processes at the same time. .."
Questions:
Do i have to expect problems, when there will be more traffic on the
server leading
to a situation where this connection will be used by more than one
process at the "same time"?
Is the mysql_function "last_insert_id()" still working correct in this
situation?
Thanks
Helmut
Re: Apache::DBI (another) question
Posted by Perrin Harkins <ph...@gmail.com>.
On 3/29/07, Jonathan Vanasco <jv...@2xlp.com> wrote:
> You're one of those people using cache_connect in dbi itself, right ?
Yes, I needed something more customized than what Apache::DBI was
doing. I use DBI->connect_cached and add my own automatic rollback
handler.
- Perrin
Re: Apache::DBI (another) question
Posted by Jonathan Vanasco <jv...@2xlp.com>.
On Mar 29, 2007, at 5:42 PM, Perrin Harkins wrote:
>> it used to not do that. perhaps by bug not design, but I had to get
>> very aggressive ~2004/2005 to keep connections from caching.
>
> I haven't seen that, but I also haven't used Apache::DBI much on
> mod_perl 2. If you can reproduce the problem, please post some
> information (or a patch) on the RT queue.
I have the workaround code in my SVN repo dating back to 2004. i'll
try taking it out this weekend and see what happens - i have the
slightest suspicion that its going to work fine now.
You're one of those people using cache_connect in dbi itself, right ?
// Jonathan Vanasco
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
Re: Apache::DBI (another) question
Posted by Perrin Harkins <ph...@gmail.com>.
On 3/29/07, Jonathan Vanasco <jv...@2xlp.com> wrote:
>
> On Mar 29, 2007, at 1:01 PM, Perrin Harkins wrote:
>
> > Apache::DBI handles this for you. It checks if you are connection
> > during startup and does not make your connection persistent if you
> > are.
>
> is this new-ish ( like within a year or so ?)
No, it's a very old feature.
> it used to not do that. perhaps by bug not design, but I had to get
> very aggressive ~2004/2005 to keep connections from caching.
I haven't seen that, but I also haven't used Apache::DBI much on
mod_perl 2. If you can reproduce the problem, please post some
information (or a patch) on the RT queue.
- Perrin
Re: Apache::DBI (another) question
Posted by Clinton Gormley <cl...@traveljury.com>.
On Thu, 2007-03-29 at 17:31 -0400, Jonathan Vanasco wrote:
> On Mar 29, 2007, at 1:01 PM, Perrin Harkins wrote:
>
> > Apache::DBI handles this for you. It checks if you are connection
> > during startup and does not make your connection persistent if you
> > are.
>
> is this new-ish ( like within a year or so ?)
There was a bug in the code for modperl 2.
I submitted a patch here :
http://marc.info/?l=apache-modperl&m=113849464207840&w=2
and at some time since then it has been included in Apache::DBI.
>
> it used to not do that. perhaps by bug not design, but I had to get
> very aggressive ~2004/2005 to keep connections from caching.
>
clint
Re: Apache::DBI (another) question
Posted by Jonathan Vanasco <jv...@2xlp.com>.
On Mar 29, 2007, at 1:01 PM, Perrin Harkins wrote:
> Apache::DBI handles this for you. It checks if you are connection
> during startup and does not make your connection persistent if you
> are.
is this new-ish ( like within a year or so ?)
it used to not do that. perhaps by bug not design, but I had to get
very aggressive ~2004/2005 to keep connections from caching.
> The problem here is that Helmut is caching the database handle
> himself, instead of calling DBI->connect on each request and letting
> Apache::DBI cache it. That foils Apache::DBI's attempts to prevent
> connections made in the parent from persisting.
agreed.
> Caching database handles in your own object that persists across
> requests is usually a bad idea. It means that the things Apache::DBI
> tries to do for you (like ping'ing the connection to see if it's still
> good, and issuing a rollback after the request in case your code died
> during a transaction) will not happen. Don't do it unless you have a
> solid understanding of the problems Apache::DBI addresses and a plan
> for how to handle them yourself.
agreed.
Re: Apache::DBI (another) question
Posted by Perrin Harkins <ph...@gmail.com>.
On 3/29/07, Jonathan Vanasco <jv...@2xlp.com> wrote:
> If you need to connect on startup to pull configuration vars:
> a- connect before using Apache::DBI - not entirely necessary, but
> can avoid edge cases.
> b- connect using an alternate connection string 'user=myapp.config'
> - not entirely necessary, but can avoid edge cases.
> c- make sure you destroy the connection before init - entirely
> necessary
Apache::DBI handles this for you. It checks if you are connection
during startup and does not make your connection persistent if you
are.
The problem here is that Helmut is caching the database handle
himself, instead of calling DBI->connect on each request and letting
Apache::DBI cache it. That foils Apache::DBI's attempts to prevent
connections made in the parent from persisting.
Caching database handles in your own object that persists across
requests is usually a bad idea. It means that the things Apache::DBI
tries to do for you (like ping'ing the connection to see if it's still
good, and issuing a rollback after the request in case your code died
during a transaction) will not happen. Don't do it unless you have a
solid understanding of the problems Apache::DBI addresses and a plan
for how to handle them yourself.
- Perrin
RE: Apache::DBI (another) question
Posted by Sa...@barclayscapital.com.
I think you can do it on startup just not startup of the parent httpd
process.
What you can do is use a PerlChildInitHandler (the name may have changed
in mod_perl2, sorry I'm too lazy to check).
This is a hook into the part of the httpd lifecycle that gets executed
when the _child_ process starts up. Doing your connection here will give
you a warm connection before that child serves a page (hence first hit
is not slow) but still ensures no sharing of handles between child
processes and the issue you're having.
Why not give that a go.
I'm sure this was documented somewhere a few years ago, if you nag me
I'll have a go at finding the origianl document/article.
> -----Original Message-----
> From: Helmut Zeilinger [mailto:hz@hzlabs.de]
> Sent: 29 March 2007 16:28
> To: Jonathan Vanasco
> Cc: modperl@perl.apache.org
> Subject: Re: Apache::DBI (another) question
>
> I see, i will change my strategy towards
> avoiding the conncetion on startup.
>
> Thank you!
>
> Helmut
>
> Jonathan Vanasco schrieb:
> >
> > On Mar 29, 2007, at 7:46 AM, Helmut Zeilinger wrote:
> >
> >> $self->{'dbh'} = DBI->connect ("DBI:mysql:somedb:localhost",
> >> "root", "", {RaiseError => 1});
> > connecting as root?
> >>
> >> While running the server there is only one apache - mysql
> connection
> >> visible via
> >> "mysqladmin processlist" and not one connection for each
> apache child
> >> process
> >> as usually.
> >>
> >> I have found no irregularities, but this statement in the
> Apache::DBI
> >> manpage makes me
> >> worried:
> >>
> >> "...Also it is not possible to create a database handle
> upon startup
> >> of the httpd and then inheriting this handle to every subsequent
> >> server. This will
> >> cause clashes when the handle is used by two processes at the same
> >> time. .."
> >
> > Thats the wrong way to do things. You should expect to have every
> > kind of problem imaginable.
> > You're starting the persistent connection in the 'main' server, and
> > sharing it with the children.
> >
> > If you need to connect on startup to pull configuration vars:
> > a- connect before using Apache::DBI - not entirely
> necessary, but
> > can avoid edge cases.
> > b- connect using an alternate connection string
> > 'user=myapp.config' - not entirely necessary, but can
> avoid edge cases.
> > c- make sure you destroy the connection before init - entirely
> > necessary
> >
> > in your modperl code, use Apache::DBI->connect_on_init(
> %kw_args ) to
> > queue the database connect ( or make your first connection in code
> > that is only executed when a page is requested ).
> > That will give you 1 connection per child, which is local to that
> > child and persists throughout the life of the child.
> >
> > last_insert_id will not work reliably in that situation,
> and neither
> > will transactions. all connections are using the same db handle,
> >
> >
> > // Jonathan Vanasco
> >
> > | - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - -
> > - - - - - - - - - - - - - - - - - - -
> > | SyndiClick.com
> > | - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - -
> > - - - - - - - - - - - - - - - - - - -
> > | FindMeOn.com - The cure for Multiple Web Personality Disorder
> > | Web Identity Management and 3D Social Networking
> > | - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - -
> > - - - - - - - - - - - - - - - - - - -
> > | RoadSound.com - Tools For Bands, Stuff For Fans
> > | Collaborative Online Management And Syndication Tools
> > | - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - -
> > - - - - - - - - - - - - - - - - - - -
> >
> >
>
>
>
------------------------------------------------------------------------
For more information about Barclays Capital, please visit our web site at http://www.barcap.com.
Internet communications are not secure and therefore the Barclays Group does not accept legal responsibility for the contents of this message. Although the Barclays Group operates anti-virus programmes, it does not accept responsibility for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of the Barclays Group. Replies to this email may be monitored by the Barclays Group for operational or business reasons.
------------------------------------------------------------------------
Re: Apache::DBI (another) question
Posted by Helmut Zeilinger <hz...@hzlabs.de>.
I see, i will change my strategy towards
avoiding the conncetion on startup.
Thank you!
Helmut
Jonathan Vanasco schrieb:
>
> On Mar 29, 2007, at 7:46 AM, Helmut Zeilinger wrote:
>
>> $self->{'dbh'} = DBI->connect ("DBI:mysql:somedb:localhost",
>> "root", "", {RaiseError => 1});
> connecting as root?
>>
>> While running the server there is only one apache - mysql connection
>> visible via
>> "mysqladmin processlist" and not one connection for each apache child
>> process
>> as usually.
>>
>> I have found no irregularities, but this statement in the Apache::DBI
>> manpage makes me
>> worried:
>>
>> "...Also it is not possible to create a database handle upon startup
>> of the httpd and then inheriting this handle to every subsequent
>> server. This will
>> cause clashes when the handle is used by two processes at the same
>> time. .."
>
> Thats the wrong way to do things. You should expect to have every
> kind of problem imaginable.
> You're starting the persistent connection in the 'main' server, and
> sharing it with the children.
>
> If you need to connect on startup to pull configuration vars:
> a- connect before using Apache::DBI - not entirely necessary, but
> can avoid edge cases.
> b- connect using an alternate connection string
> 'user=myapp.config' - not entirely necessary, but can avoid edge cases.
> c- make sure you destroy the connection before init - entirely
> necessary
>
> in your modperl code, use Apache::DBI->connect_on_init( %kw_args ) to
> queue the database connect ( or make your first connection in code
> that is only executed when a page is requested ).
> That will give you 1 connection per child, which is local to that
> child and persists throughout the life of the child.
>
> last_insert_id will not work reliably in that situation, and neither
> will transactions. all connections are using the same db handle,
>
>
> // Jonathan Vanasco
>
> | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - - - - - - - - - -
> | SyndiClick.com
> | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - - - - - - - - - -
> | FindMeOn.com - The cure for Multiple Web Personality Disorder
> | Web Identity Management and 3D Social Networking
> | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - - - - - - - - - -
> | RoadSound.com - Tools For Bands, Stuff For Fans
> | Collaborative Online Management And Syndication Tools
> | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> - - - - - - - - - - - - - - - - - - -
>
>
Re: Apache::DBI (another) question
Posted by Jonathan Vanasco <jv...@2xlp.com>.
On Mar 29, 2007, at 7:46 AM, Helmut Zeilinger wrote:
> $self->{'dbh'} = DBI->connect ("DBI:mysql:somedb:localhost",
> "root", "", {RaiseError => 1});
connecting as root?
>
> While running the server there is only one apache - mysql
> connection visible via
> "mysqladmin processlist" and not one connection for each apache
> child process
> as usually.
>
> I have found no irregularities, but this statement in the
> Apache::DBI manpage makes me
> worried:
>
> "...Also it is not possible to create a database handle upon
> startup of the httpd and then inheriting this handle to every
> subsequent server. This will
> cause clashes when the handle is used by two processes at the same
> time. .."
Thats the wrong way to do things. You should expect to have every
kind of problem imaginable.
You're starting the persistent connection in the 'main' server, and
sharing it with the children.
If you need to connect on startup to pull configuration vars:
a- connect before using Apache::DBI - not entirely necessary, but
can avoid edge cases.
b- connect using an alternate connection string 'user=myapp.config'
- not entirely necessary, but can avoid edge cases.
c- make sure you destroy the connection before init - entirely
necessary
in your modperl code, use Apache::DBI->connect_on_init( %kw_args ) to
queue the database connect ( or make your first connection in code
that is only executed when a page is requested ).
That will give you 1 connection per child, which is local to that
child and persists throughout the life of the child.
last_insert_id will not work reliably in that situation, and neither
will transactions. all connections are using the same db handle,
// Jonathan Vanasco
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -