PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Doty, Kathy" <doty at bnl dot gov>
Subject: Re: PLEASE HELP: DBPROCESS DEAD
Date: Sep 13 2002 3:36PM

Michael,

Thanks so much for responding while on your trip.  I appreciate this.
A followup question, regarding the issue of sybase users logins....
When the problem I described happens, in addition to the
volume of messages in the httpd server log (BTW, I will apply the
fix you suggested and hopefully this problem will go away :-)
there is the problem of the number of sybase users for the remote sybase
server.
The admin of the remote sybase server says that the number of user slots
gets
maxed out by what I assume are repeated instances of attempting to run
the application in question.  I also see this same behavior on the local
side.
We cannot kill these using the sybase kill command
but have to stop and start the server.  This is not acceptable as the remote
server is
used by many many other applications that cannot be interrupted any time my
remote application gets it connection interrupted.
Would you know of a way to kill these sybase user processes? Or, would you
know the proper place to post these kinds of questions regarding my troubles
with
CIS?

Thanks again for any help you can offer.
Kathy
Michael Peppler wrote:

> On Mon, 2002-09-09 at 12:18, Doty, Kathy wrote:
> > The table I am reading is in a remote database.(I have a proxy table
> set
> > up locally)
> > The table I am accessing has approx 10000 rows (not too big)
> > Normally this query works just fine and is very quick.
> > Occasionally, though, this database has gone off line while the
> > above query is in progress.
> > This is causing lots and lots of trouble for me.
> > The webserver error log overflows with the DBPROCESS DEAD... type
> > message
> >
> > I also end up with processes in my sybase server that I cannot kill
> > AWAITING COMMAND  remote I/O (or something to that effect - Ive
> managed
> > to
> > crash the server at the moment and I cannot do an sp_who to see
> > the exact phrase of the status for the web user)
>
> The problem that you are seeing is related to CIS issues between the two
> Sybase servers rather than a direct problem with the DBlib code itself.
> Unfortunately CIS (i.e. the bit of Sybase code that allows you to create
> proxy tables for a remote database/server) is rather bad at recovering
> from a lost connection to the remote server.
>
> Now I suspect that your specific problem stems from using the nsql()
> call, which probably goes off into a loop of some sort if the connection
> ends up being marked "dead". I don't really have the tools to find the
> real problem right now (as I've mentioned before I'm on a trip in Europe
> - and although I do have a Sybase server on my laptop it's not an ideal
> development environment :-) but you may want to try the following fix to
> the nsql() code (in DBlib.pm):
>
> Around line 449 you have:
>
>         while ( $db->dbresults != $db->NO_MORE_RESULTS ) {
>
>             if ( $nsql_deadlock_retrycount && $DB_ERROR =~ /Message:
> 1205\b/m )
> {
>                 if ( $retrycount < 0 || $retrycount-- ) {
>                     carp "SQL deadlock encountered.  Retrying...\n" if
> $retryverbose;
>
> Try changing that first line to something like this:
>
> my $ret;
>
> while(($ret = $db->dbresults) != $db->NO_MORE_RESULTS) {
>     if($ret == $db->FAIL) {
>         if($db->DBDEAD) {
>             warn "DBPROCESS is DEAD";
>             return;
>         }
>     }
>     etc - same as above
>
> This *should* take care of the excess of error messages in your http
> server error log, although it won't fix the problem with your proxy
> table becoming disconnected from the primary server.
>
> Michael
> --
> Michael Peppler / mpeppler@peppler.org / http://www.mbay.net/~mpeppler
> mpeppler@zetatools.com / ZetaTools, Inc / http://www.zetatools.com
> ZetaTools: Call perl functions as Sybase stored procedures!