Re: Morning bug w/out using Apache::DBI?

[Perrin Harkins]

Your problem doesn't sound like something that Apache::DBI would cause. 
  Deadlock problems are caused by conflicting updates, which could only 
be coming from your code.

> I'm not positive but maybe it seemed like there were 
> stale handlers lying around that weren't being closed

If you put database handles in globals or in closures, or in some data 
structure (like an object) that gets put into a global, it will stay 
around just like it does with Apache::DBI.  Is it possible you did 
something like partially read a set of results and leave the rest 
dangling?  You could try putting some $sth->finish() statements in your 
code to deal with this.

> Regardless, I was wondering what a solution would be for this situation. 
> Currently, I do not do a ping if a handler goes stale and attempt a 
> reconnect.  Should I implement this feature?

No, you would just be rewriting Apache::DBI.

- Perrin

Morning bug w/out using Apache::DBI?

[Keith Watanabe]

Hi!  I recently made an attempt to upgrade other web software to 
mod_perl 1.26 (compiled statically, running on Solaris 2.6).  We're 
using Sybase 11.9.2 for this application as the db backend.  When I 
initially converted the site over to mod_perl, I started off using 
Apache::DBI by placing it in the startup.pl script that I have Apache 
use when loading mod_perl.  Apparently, that night, the dataserver began 
experiencing deadlocking.  Here are some of the error messages 
encountered in the apache logs and through one of our cron jobs:

DBD::Sybase::db rollback failed: OpenClient message: LAYER = (1) ORIGIN 
= (1) SEVERITY = (1) NUMBER = (49)
Message String: ct_send(): user api layer: external error: This routine 
cannot be called
because another command structure has results pending.

 DBD::Sybase::db rollback failed: OpenClient message: LAYER = (1) ORIGIN 
= (1) SEVERITY = (1) NUMBER = (50)
Message String: ct_cmd_alloc(): user api layer: external error: The 
connection has been marked dead. DBD::Sybase::db prepare failed: 
OpenClient message: LAYER = (4) ORIGIN = (1)

SEVERITY = (5) NUMBER = (1)
Message String: ct_results(): protocol specific layer: external error: 
There is a tds protocol error. Premature end of the datastream was 
encountered.

Your server command (family id #0, process id #16) was deadlocked with another
process and has been chosen as deadlock victim. Re-run your command.


When I restarted the Apache server, it seemed to have cleaned this 
situation up temporarily.  I ended up removing Apache::DBI from the 
startup.pl script to see if this would resolve this issue.  This morning 
apparently, similar problems were encountered.  I didn't see the same 
error messages arise this morning in the Apache logs, but the same error 
appeared this morning:

DBI->connect(interfaces=;server=;database=) failed: 
OpenClient message: LAYER = (5) ORIGIN = (3) SEVERITY = (5) NUMBER = (6)
Message String: ct_connect(): network packet layer: internal net library 
error: Net-Library operation terminated due to disconnect

These connect errors seemed to have affected the Sybase server in terms 
of connectivity.  I'm not positive but maybe it seemed like there were 
stale handlers lying around that weren't being closed, perhaps 
preventing other handlers from being able to connect since the maximum 
number of open connections was being exceeded (well, that's my theory. 
 Please correct me if I'm wrong here).  Also, these errors didn't come 
about until roughly 5 hours after the Apache server was restarted with 
the mod_perl applications in place.  There was little activity on the 
web server side in relation to those mod_perl applications.

Regardless, I was wondering what a solution would be for this situation. 
 Currently, I do not do a ping if a handler goes stale and attempt a 
reconnect.  Should I implement this feature?  Are there other 
considerations to deal with in this situation?

Note that this is an internal server which isn't heavily used so I'm not 
worried about receiving a few million hits a second.  

Thanks for any help.

--Keith