Re: mod_perl/DBI problem
Even when using Apache::DBI, I still have the same problem - If it times out once, it won't try again. I set Apache::DBI::DEBUG = 2, and here's the log: When I first load the page and get the timeout: 197345 Apache::DBI need ping: yes 197345 Apache::DBI new connect to 'Database username password PrintError=1 RaiseError=1 AutoCommit=1' [Tue Jul 31 09:31:45 2001] [error] DBI-connect(Database) failed: [Microsoft][ODBC SQL Server Driver]Timeout expired (SQL-S1T00)(DBD: db_login/SQLConnect err=-1) at DatabaseStuff.pm line 32 Compilation failed in require at (eval 8) line 9. BEGIN failed--compilation aborted at (eval 8) line 9. Try to reload the page: [Tue Jul 31 09:33:11 2001] [error] Can't call method quote on an undefined value at DatabaseStuff.pm line 660. Try again: [Tue Jul 31 09:33:14 2001] [error] Can't call method quote on an undefined value at DatabaseStuff.pm line 660. And so on. The only thing that seems to fix it is restarting the server, which by the way is Apache/1.3.20 (Win32) with mod_perl/1.26_01-dev. Apache::DBI is version 0.87, DBD::ODBC (which I use for my database connections) is version 0.28. Any ideas? Curtis H. - Original Message - From: Ged Haywood [EMAIL PROTECTED] To: Curtis Hawthorne [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 27, 2001 4:19 PM Subject: Re: mod_perl/DBI problem Hi there, On Fri, 27 Jul 2001, Curtis Hawthorne wrote: So, how can I have it try to connect to the database again if it fails, but keep the connection persistent if it doesn't? Have a look in the Guide, there's lots of stuff in there about Apache::DBI. http://perl.apache.org/guide. 73, Ged.
Re: mod_perl/DBI problem
That looks like that will do exactly what I need. I tried it my code and it
caught the server timeout and wrote an error to the log file using
$r-server-log_error, but when I try to do $r-child_terminate, I get this
error:
[Tue Jul 31 11:16:35 2001] [error] Can't locate object method
child_terminate
via package Apache (perhaps you forgot to load Apache?) at
DatabaseStuff.pm line 56.
But I have loaded Apache. Would my running Apache in a win32 environment
(which uses threads instead of processes) have anything to do with that?
Curtis H.
- Original Message -
From: Geoffrey Young [EMAIL PROTECTED]
To: 'Curtis Hawthorne' [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, July 31, 2001 10:04 AM
Subject: RE: mod_perl/DBI problem
snip
the way most people handle this is by separating out the connect routine,
wrapping it in an eval, and calling $r-child_terminate if $dbh is undef.
It's not ideal, but it takes that child out of the pool instead of having
it
lingering with a bad connection for the next 5000 requests (or whatever
your
MaxRequestPerChild is).
this is what I use for Oracle:
eval {
$dbh = DBI-connect($dbase, $user, $pass,
{RaiseError = 1, AutoCommit = 1, PrintError = 1});
};
if ($@) {
# if we could not log in, then there is the possibility under
# Apache::DBI that the child may never recover...
$r-server-log_error(Doh! We may have a TNS error: $DBI::errstr ,
Scheduling child $$ termination NOW...);
$r-child_terminate;
}
HTH
--Geoff
Re: mod_perl/DBI problem
Well, that works perfectly, but doesn't do anything :-).
As far as I can tell, because my database connection code is at the top of
my module that the CGI script uses, the code is only run the first time that
the script runs. If the connection times out and is caught by the eval code
you suggested below, the script writes an error to the log but is still
running because I apparently don't have $r-terminate_child. The next time
the script runs, it has already evaluated all the code in the module, so it
doesn't try to connect to the database again, and doesn't consult the
%Connected hash and doesn't realize that it doesn't have a connection. My
script merrily goes on its way calling subs in the module and quickly errors
out.
Pseudo code of my module:
eval {
connect_to_database;
};
if($@) {
writeerror;
clear(%Connected);
}
sub dostuff {
return manipulatedatabase;
}
sub dothings {
return querydatabase;
}
So, should I rewrite it so that it makes a new connection (but not really
because of Apache::DBI) in each sub, or have the script call a subroutine
that gives it a database handle to pass to each sub, go at it a completely
different way, or am I way off in my analysis of the problem?
Really confused,
Curtis H.
- Original Message -
From: Geoffrey Young [EMAIL PROTECTED]
To: 'Curtis Hawthorne' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, July 31, 2001 11:49 AM
Subject: RE: mod_perl/DBI problem
-Original Message-
From: Geoffrey Young [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 31, 2001 12:46 PM
To: 'Curtis Hawthorne'; [EMAIL PROTECTED]
Subject: RE: mod_perl/DBI problem
yup, looks like mod_perl doesn't offer that to windows.
well, I don't do windows, but maybe something like this would work...
call $dbh-all_handlers, which should return a reference to
%Connected. my
thought is that since windows has only one process, that
%Connected will
only contain a single entry. removing that entry should have
the desired
effect...
well, I can see the flaw in this already - it won't work if you connect to
more than one database, using more than one userid, etc.
but for a single connection it may work ok, otherwise you would have to
follow the algorithm in Apache::DBI to get the $Idx for your connection
and
delete that key...
anyway, HTH
--Geoff
Re: mod_perl/DBI problem
Well, I think the eval is working exactly as it should (or at least like
perl thinks it should), in that it is evaluated the first time the module is
loaded, because it is at the top, but after that it has no reason it run
because the script is still running and it isn't in a subroutine that is
called or anything like that.
I think I'll write a subroutine that passes back a dbh handle and call that
sub from every sub I use that interacts with the database - is this the
accepted way to do things?
I should probably reread the camel book chapter on OO and start using that
more, just haven't gotten around to it :-)
Anyways, much thanks for all your help!
Curtis H.
- Original Message -
From: Geoffrey Young [EMAIL PROTECTED]
To: 'Curtis Hawthorne' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, July 31, 2001 3:14 PM
Subject: RE: mod_perl/DBI problem
snip
hmph, it's been a while since I've done any Registry.pm work...
basically, all my stuff is OO now, so I use a method handler that inherits
from a base Util class and call
my $dbh = $self-connect;
where Util::connect() contains the eval() construct. that works like a
charm.
I don't understand why the eval() isn't working as it should, but maybe
its
a windows thing or maybe I'm missing something - using eval() as an
exception handling mechansim ought to execute the code every time,
regardless of whether perl has seen it already. perhaps its a nested
eval()
thing...
at any rate, your suggestions below are good ones to follow - I think
you're
on the right path. try creating a util package, or at least a subroutine
within your script that contains the eval() and passes $dbh back to you
and
see if that helps. almost there :)
--Geoff
Pseudo code of my module:
eval {
connect_to_database;
};
if($@) {
writeerror;
clear(%Connected);
}
sub dostuff {
return manipulatedatabase;
}
sub dothings {
return querydatabase;
}
So, should I rewrite it so that it makes a new connection
(but not really
because of Apache::DBI) in each sub, or have the script call
a subroutine
that gives it a database handle to pass to each sub, go at it
a completely
different way, or am I way off in my analysis of the problem?
Really confused,
Curtis H.
snip
mod_perl/DBI problem
I'm writing a script that will run under mod_perl that uses DBI to connect to an MS SQL server. The script works fine and mod_perl speeds it up quite a bit. Every so often, when the script tries to connect to the SQL server, the connection times out (I think the SQL server's a little slow), and I get an Internal Server Error message. My problem is that, instead of trying to connect to the server again, the next time the script is run, the database connection handle stays the same and I get the following error in my logs over and over again Can't call method quote on an undefined value (the first thing the script does is a $dbh-quote of a variable). The code that does the connection to the database is in a module that is used by the script, but not in a subroutine. So, how can I have it try to connect to the database again if it fails, but keep the connection persistent if it doesn't? Would there be some way to have the script exit completely when it gets an error like that, even though it's under mod_perl? Thanks! Curtis H.
