This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Apache::DBI Wisdom Sought
From: "Mark D Wolinski" <mark@mrmark.com>
Date: Thu, 31 Aug 2000 11:12:55 -0400
Hi all,
You'll pardon me a little, I hope for this message does tend to lap over out
of ModPerl, but if it'll make you feel at ease, I shall only expect wisdom
on the modperl side.
I run a service of free message boards where users can create their own
message boards. Currently and in the past, I have used BerkeleyDB to store
everything.
I have decided to move towards mySQL and run under ModPerl.
My current plan is to have one DB which stores everyones settings, etc in
tables.
Then I was going to create a database for each message board.
Under ModPerl, I was going to utilize Apache::DBI to create a persistent
connection to the main DB. However, how will this affect the connections
being made to individual DBs? I have a nagging feeling that they'll connect
until mySQL reaches it's concurrent connection limit then stop being able to
connect to service since it would be filled with persistent connections of
the first 150 (or so) forums accessed.
Am I correct in my feelings on that?
Secondly, you may be asking why I don't just store the messages in a single
table in the master database. My thoughts on that are that when a message
is posted, I create a list of the thread and write those numbers to the
messages to allow quicker retrieval when the message list looked at. When I
do this, I write lock the DB to prevent concurrent posts from messing up the
threading of another post. On that point, I may well be over zealously
protective and while this paragraph is so far off topic I would love your
thoughts on that issue, but would understand if shall pass it up to respond
only to the modperl portion of this message. And I can seek council via the
mysql lists as well on this issue.
Much thanks...
===
Subject: Re: Apache::DBI Wisdom Sought
From: Chris Winters <cwinters@intes.net>
Date: Thu, 31 Aug 2000 11:31:07 -0400
Mark D Wolinski (mark@mrmark.com) [000831 11:24]:
> Hi all,
>
> You'll pardon me a little, I hope for this message does tend to lap over out
> of ModPerl, but if it'll make you feel at ease, I shall only expect wisdom
> on the modperl side.
>
> I run a service of free message boards where users can create their own
> message boards. Currently and in the past, I have used BerkeleyDB to store
> everything.
>
> I have decided to move towards mySQL and run under ModPerl.
>
> My current plan is to have one DB which stores everyones settings, etc in
> tables.
>
> Then I was going to create a database for each message board.
>
> Under ModPerl, I was going to utilize Apache::DBI to create a persistent
> connection to the main DB. However, how will this affect the connections
> being made to individual DBs? I have a nagging feeling that they'll connect
> until mySQL reaches it's concurrent connection limit then stop being able to
> connect to service since it would be filled with persistent connections of
> the first 150 (or so) forums accessed.
>
> Am I correct in my feelings on that?
You could always connect to the 'mysql' database in your connection
string, and then 'use' the database you'd like once you get the
connection back:
my $dbh = DBI->connect( 'DBI:mysql:database=mysql', 'user', 'pass',
{ RaiseError => 1 } )
|| die $DBI::errstr;
$dbh->do( "use $the_proper_database" );
... continue ...
I use this all the time, no problems.
Chris
===
Subject: Apache crashes with Apache::DBI
From: Pires Claudio <CPIRES@nec.com.ar>
Date: Tue, 5 Sep 2000 11:11:41 -0300
Hi, I have problems to load Apache DBI. I added the PerlModule Apache::DBI
into httpd.conf and when I restarted Apache I got this message
bash# /etc/rc.d/init.d/httpd restart;
Shutting down http: [ OK ]
Starting httpd: [ OK ]
bash# /etc/rc.d/init.d/httpd status;
httpd dead but subsys locked
it seems like Apache crashes... I dont know why... I could saw in the
Apache::DBI doc that mod_perl must be configured with PERL_CHILD_INIT=1 and
PERL_STACKED_HANDLERS=1. How can I know if mod_perl was compiled with this
directives? How can I know what directives were used to compile mod_perl? I
download mod_perl as an RPM from RedHat... And I havent posibilities to
recompile it... should I recompile it? or there is any way to modify the
configuration without compilation?
Thanks a lot
===
Subject: RE: Apache crashes with Apache::DBI
From: Geoffrey Young <gyoung@laserlink.net>
Date: Tue, 5 Sep 2000 10:17:37 -0400
Original Message-----
> From: Pires Claudio [mailto:CPIRES@nec.com.ar]
> Sent: Tuesday, September 05, 2000 10:12 AM
> To: modperl
> Subject: Apache crashes with Apache::DBI
>
>
> Hi, I have problems to load Apache DBI. I added the
> PerlModule Apache::DBI
> into httpd.conf and when I restarted Apache I got this message
>
> bash# /etc/rc.d/init.d/httpd restart;
> Shutting down http: [ OK ]
> Starting httpd: [ OK ]
>
> bash# /etc/rc.d/init.d/httpd status;
> httpd dead but subsys locked
>
> it seems like Apache crashes... I dont know why... I could saw in the
> Apache::DBI doc that mod_perl must be configured with
> PERL_CHILD_INIT=1 and
> PERL_STACKED_HANDLERS=1. How can I know if mod_perl was
> compiled with this
> directives?
http://localhost/perl-status?hooks (if configured)
>How can I know what directives were used to
> compile mod_perl?
> I
> download mod_perl as an RPM from RedHat... And I havent
> posibilities to
> recompile it... should I recompile it?
probably :) IIRC, Apache::DBI had problems with the redhat DSO for 6.1, but
it could have been another 6.X version. At any rate, compiling from source
isn't too difficult. See perl.apache.org/guide for lots of pointers...
> or there is any way to
> modify the
> configuration without compilation?
no
===
Subject: Auto rollback using Apache::DBI
From: Nicolas MONNET <nico@monnet.to>
Date: Thu, 7 Sep 2000 16:03:04 +0200 (CEST)
Hi there,
I might get something wrong, but while in non-autocommit, if a script dies
before rollbacking or commiting, looks like the transaction never gets
cancelled until I kill -HUP httpd! Quite a problem ...
Is there any known way to catch this?
===
Subject: Re: Auto rollback using Apache::DBI
From: Honza Pazdziora <adelton@informatics.muni.cz>
Date: Thu, 7 Sep 2000 16:17:49 +0200
On Thu, Sep 07, 2000 at 04:03:04PM +0200, Nicolas MONNET wrote:
>
> I might get something wrong, but while in non-autocommit, if a script dies
> before rollbacking or commiting, looks like the transaction never gets
> cancelled until I kill -HUP httpd! Quite a problem ...
>
> Is there any known way to catch this?
Looking at the code in Apache::DBI 0.87, the handle is only rollbacked
if the AutoCommit is set to zero during connect, not if you do
$dbh->{'AutoCommit'} = 0;
in your script.
I wonder if the $needCleanup test is wanted at all. We could make it
a configuration option, not to push the cleanup handler, but I believe
that generally the rollback is wanted thing in all cases.
===
Subject: Re: Auto rollback using Apache::DBI
From: Matt Sergeant <matt@sergeant.org>
Date: Thu, 7 Sep 2000 16:54:57 +0100 (BST)
On Thu, 7 Sep 2000, Nicolas MONNET wrote:
>
>
> Hi there,
>
> I might get something wrong, but while in non-autocommit, if a script dies
> before rollbacking or commiting, looks like the transaction never gets
> cancelled until I kill -HUP httpd! Quite a problem ...
>
> Is there any known way to catch this?
Yes, use some exception handling. That way, the only way your script will
ever not end up in the exception trap is if your httpd seg faults, in
which case the connection will die and the database will roll back the
transaction for you.
See the guide/perl.html for more details.
===
Subject: Re: Auto rollback using Apache::DBI
From: Perrin Harkins <perrin@primenet.com>
Date: Thu, 7 Sep 2000 09:23:59 -0700 (PDT)
On Thu, 7 Sep 2000, Nicolas MONNET wrote:
> I might get something wrong, but while in non-autocommit, if a script dies
> before rollbacking or commiting, looks like the transaction never gets
> cancelled until I kill -HUP httpd! Quite a problem ...
>
> Is there any known way to catch this?
Well, Apache::DBI does push a cleanup handler that does a rollback if
auto-commit is off. Are you saying this isn't working?
===
Subject: Re: Auto rollback using Apache::DBI
From: Nicolas MONNET <nico@monnet.to>
Date: Thu, 7 Sep 2000 18:27:36 +0200 (CEST)
On Thu, 7 Sep 2000, Perrin Harkins wrote:
|On Thu, 7 Sep 2000, Nicolas MONNET wrote:
|> I might get something wrong, but while in non-autocommit, if a script dies
|> before rollbacking or commiting, looks like the transaction never gets
|> cancelled until I kill -HUP httpd! Quite a problem ...
|>
|> Is there any known way to catch this?
|
|Well, Apache::DBI does push a cleanup handler that does a rollback if
|auto-commit is off. Are you saying this isn't working?
I've run into a situation where it was'nt. I wanted to make sure
it's not the desired behaviour, before I can dig more into it to look how
it's heppening.
===
Subject: Re: Auto rollback using Apache::DBI
From: Perrin Harkins <perrin@primenet.com>
Date: Thu, 7 Sep 2000 11:06:00 -0700 (PDT)
On Thu, 7 Sep 2000, Nicolas MONNET wrote:
> |Well, Apache::DBI does push a cleanup handler that does a rollback if
> |auto-commit is off. Are you saying this isn't working?
>
> I've run into a situation where it was'nt. I wanted to make sure
> it's not the desired behaviour, before I can dig more into it to look how
> it's heppening.
With AutoCommit off, you should definitely get a rollback on every
request, provided you actually called DBI->connect on that request. Turn
on the debug flag ($Apache::DBI::DEBUG = 2) and see if the cleanup handler
is being run or not.
===
Subject: Re: Auto rollback using Apache::DBI
From: Honza Pazdziora <adelton@informatics.muni.cz>
Date: Fri, 8 Sep 2000 13:45:59 +0200
On Thu, Sep 07, 2000 at 11:06:00AM -0700, Perrin Harkins wrote:
> On Thu, 7 Sep 2000, Nicolas MONNET wrote:
> > |Well, Apache::DBI does push a cleanup handler that does a rollback if
> > |auto-commit is off. Are you saying this isn't working?
> >
> > I've run into a situation where it was'nt. I wanted to make sure
> > it's not the desired behaviour, before I can dig more into it to look how
> > it's heppening.
>
> With AutoCommit off, you should definitely get a rollback on every
> request, provided you actually called DBI->connect on that request. Turn
> on the debug flag ($Apache::DBI::DEBUG = 2) and see if the cleanup handler
> is being run or not.
The code
my $needCleanup = ($Idx =~ /AutoCommit[^\d]+0/) ? 1 : 0;
if(!$Rollback{$Idx} and $needCleanup and Apache->can('push_handlers')) {
print STDERR "$prefix push PerlCleanupHandler \n" if $Apache::DBI::DEBUG > 1;
Apache->push_handlers("PerlCleanupHandler", \&cleanup);
of Apache::DBI line 90 suggests that if AutoCommit isn't zero upon
_connect_, the cleanup won't even be called. So if you do
my $dbh = DBI->connect('dbi:Oracle:sid');
$dbh->{'AutoCommit'} = 0;
such a $dbh won't be rollbacked.
===
Subject: Re: Auto rollback using Apache::DBI
From: Nicolas MONNET <nico@monnet.to>
Date: Fri, 8 Sep 2000 13:43:54 +0200 (CEST)
On Fri, 8 Sep 2000, Honza Pazdziora wrote:
|The code
|
| my $needCleanup = ($Idx =~ /AutoCommit[^\d]+0/) ? 1 : 0;
| if(!$Rollback{$Idx} and $needCleanup and Apache->can('push_handlers')) {
| print STDERR "$prefix push PerlCleanupHandler \n" if $Apache::DBI::DEBUG > 1;
| Apache->push_handlers("PerlCleanupHandler", \&cleanup);
|
|of Apache::DBI line 90 suggests that if AutoCommit isn't zero upon
|_connect_, the cleanup won't even be called. So if you do
|
| my $dbh = DBI->connect('dbi:Oracle:sid');
| $dbh->{'AutoCommit'} = 0;
|
|such a $dbh won't be rollbacked.
I did the AutoCommit upon connect. I don't manage to reproduce the error
condition, though. That's a problem.
===
Subject: Re: Auto rollback using Apache::DBI
From: "Jeff Horn" <jeff@intralect.com>
Date: Fri, 8 Sep 2000 15:22:24 -0500
Yes, I ran into this while I was making a version of Apache::DBI which uses
'reauthenticate' to maintain a single connection per Apache child (per
database) and simply reauthenticate on that connection. It turned out that
I modified what $Idx was composed of and didn't understand why I was not
getting rollbacks when sessions ended without commits.
I too think that the cleanup handler should ALWAYS be pushed and that the
handler itself should check for the AutoCommit status before issuing a
rollback. Should be easy enough to implement.
===