DBD::RDB locking RDB databases

[J***@aksteel.com]

All -
I don't know if anyone here will be able to help, but I figured I'd give it a shot.

We're running:
OpenVMS 7.2-1
Compaq Secure Web Server (CSWS) 1.2 (based on Apache 1.3.20)
Perl 5.6.1 (gotten from Compaq's site)
CSWS Perl V1.1 (based on mod_perl 1.25)
DBI 1.201
DBD::RDB 1_15
RDB V7.0-31

We are using the RDB module in Perl to access an RDB database on the same machine. The problem that I'm having is that if there is
an error in the script after the connect statement, then Apache will stay connected to the database indefinately. If Apache is not
restarted to get it to detach from the database, then it will eventually lock up RDB to the point that no one will be able to access
any of the databases. What can I do to keep this from happening?

Thanks,
Jen Landberg

[Craig A. Berry]

Jen,

I don't have an answer but I do have a few suggestions.

Post by J***@aksteel.com
OpenVMS 7.2-1
Compaq Secure Web Server (CSWS) 1.2 (based on Apache 1.3.20)
Perl 5.6.1 (gotten from Compaq's site)
CSWS Perl V1.1 (based on mod_perl 1.25)

Note that all of those things are supported by HP,

Post by J***@aksteel.com
DBI 1.201
DBD::RDB 1_15

whereas whatever support there is for these will be here and/or on
the dbi-users list,

Post by J***@aksteel.com
RDB V7.0-31

and of course this is supported by Oracle. Determining which
component is really having the problem might be your first step in
getting a resolution.

Post by J***@aksteel.com
We are using the RDB module in Perl to access an RDB database on the
same machine. The problem that I'm having is that if there is an
error in the script after the connect statement, then Apache will
stay connected to the database indefinately.

Without more detail it's impossible to say with certainty where the
problem is. I would start by examining the script for proper error
handling. Ideally the script should detect errors and (depending on
the script's design) possibly disconnect from the database rather
than simply blowing up, exiting early, or looping to get the next
request. Perhaps it disconnects at the end of a successful run but
possibly (just a guess) neglects to disconnect when it encounters an
error? Do you have a 10-20 line reproducer that illustrates the
problem? What exactly do you mean by "an error in the script"?

Post by J***@aksteel.com
If Apache is not restarted to get it to detach from the database,
then it will eventually lock up RDB to the point that no one will be
able to access any of the databases. What can I do to keep this
from happening?

There may be some less drastic managerial mechanisms for getting
unhung when this is going on. I'm quite rusty on Rdb and never
really dealt with it from the DBA perspective, but there's got to be
a way for the DBA to view what connections are active and disconnect
one that is causing trouble. For Apache, it's probably possible to
shut down one of the APACHE$xxxxx processes without restarting the
whole server, but check your docs and/or other support channels on
that.

Don't neglect standard analytical tools to examine the state and
resource consumption of the database connection and associated web
server process. Depending on the origin of the problem these may
give insight into it, or they may be red herrings and only show the
consequences of the problem and not the problem itself.

[Dan Sugalski]

Post by J***@aksteel.com
We are using the RDB module in Perl to access an RDB database on the
same machine. The problem that I'm having is that if there is
an error in the script after the connect statement, then Apache will
stay connected to the database indefinately.

Sounds like you're getting bitten by mod_perl's persistent nature.
When the perl program errors out the apache/mod_perl process it was
running under doesn't die, and sometimes doesn't get properly cleaned
up after. That means that while the perl code's dead, the connection
to the database is still alive and thus holding the locks. This can
happen if the perl code stores the DB handle in a global that's
shared across CGI invocations, since that global won't get destroyed
when the CGI connection ends.

What you should do is see about wrapping some error handling that
forces a disconnect on the handle at exit, or at least don't store
the handle in a global so it goes out of scope and gets cleaned up
properly.

It's also possible to change over to using AutoCommit to make each
SQL statement commit at the end, but that's generally a nasty thing
to do and I don't recommend it.

You may also want to set the maximum lifetime of Apache processes so
they die and get respawned with some frequency so that locks don't
get held for too long if something does go wrong.

FWIW, this isn't particularly RDB or VMS specific--you can make it
happen with almost any DB with real locks.

[J***@aksteel.com]

I'm getting the feeling that we may have problems in more than one area. I am new to most of this, so I am learning as I go.

I can give you an example of when Apache will stay connected. If I connect to the database and, before the disconnect, I have a
call to a subroutine that does not exist, then the script will blow up. So, it never gets to the disconnect statement resulting in
the Apache process staying attached to the server. In a case like this, it may just have been a typo, but the programmer that wrote
the script probably is not authorized to stop the Apache processes. The more I think about it, I may end up having to contact
Oracle to find out if there is a way to keep any single process from being connected to the database over a specified amount of
time.

Below is a part of a script that if ConvertNumToDate() is not in the package UTILITIES, then the script would never detach from the
database.

my $dbh = DBI->connect("dbi:RDB:ATTACH FILENAME meltdbdir:melt_rdb")
or die "Can't connect to Melt Shop database: $DBI::errstr\n";

my $statement = "SELECT heat_number, tap_grade, met_final_ht_grade, bof_tap_date_time,
blow_time
FROM master_heats
WHERE heat_number = $heatToGet ";
my $sth = $dbh->prepare($statement);

#Run it.
$sth->execute();

while( @data = $sth->fetchrow_array()) {

..........

if( $data[3] ne "" ) {
$totalDate1 = UTILITIES::ConvertNumToDate($data[3]);
........
}

Thank you so much for your help,
Jen Landberg





"Craig A. Berry" <***@mac.com> on 03/21/2003 03:38:59 PM

To: Jennifer Landberg/***@AKSTEEL
cc: ***@perl.org

Subject: Re: DBD::RDB locking RDB databases



Jen,

I don't have an answer but I do have a few suggestions.

Post by J***@aksteel.com
OpenVMS 7.2-1
Compaq Secure Web Server (CSWS) 1.2 (based on Apache 1.3.20)
Perl 5.6.1 (gotten from Compaq's site)
CSWS Perl V1.1 (based on mod_perl 1.25)

Note that all of those things are supported by HP,

Post by J***@aksteel.com
DBI 1.201
DBD::RDB 1_15

whereas whatever support there is for these will be here and/or on
the dbi-users list,

Post by J***@aksteel.com
RDB V7.0-31

and of course this is supported by Oracle. Determining which
component is really having the problem might be your first step in
getting a resolution.

Post by J***@aksteel.com
We are using the RDB module in Perl to access an RDB database on the
same machine. The problem that I'm having is that if there is an
error in the script after the connect statement, then Apache will
stay connected to the database indefinately.

Without more detail it's impossible to say with certainty where the
problem is. I would start by examining the script for proper error
handling. Ideally the script should detect errors and (depending on
the script's design) possibly disconnect from the database rather
than simply blowing up, exiting early, or looping to get the next
request. Perhaps it disconnects at the end of a successful run but
possibly (just a guess) neglects to disconnect when it encounters an
error? Do you have a 10-20 line reproducer that illustrates the
problem? What exactly do you mean by "an error in the script"?

Post by J***@aksteel.com
If Apache is not restarted to get it to detach from the database,
then it will eventually lock up RDB to the point that no one will be
able to access any of the databases. What can I do to keep this
from happening?

There may be some less drastic managerial mechanisms for getting
unhung when this is going on. I'm quite rusty on Rdb and never
really dealt with it from the DBA perspective, but there's got to be
a way for the DBA to view what connections are active and disconnect
one that is causing trouble. For Apache, it's probably possible to
shut down one of the APACHE$xxxxx processes without restarting the
whole server, but check your docs and/or other support channels on
that.

Don't neglect standard analytical tools to examine the state and
resource consumption of the database connection and associated web
server process. Depending on the origin of the problem these may
give insight into it, or they may be red herrings and only show the
consequences of the problem and not the problem itself.
--
________________________________________
Craig A. Berry
mailto:***@mac.com

"... getting out of a sonnet is much more
difficult than getting in."
Brad Leithauser

[Dan Sugalski]

Post by J***@aksteel.com
I'm getting the feeling that we may have problems in more than one
area. I am new to most of this, so I am learning as I go.
I can give you an example of when Apache will stay connected. If I
connect to the database and, before the disconnect, I have a
call to a subroutine that does not exist, then the script will blow
up. So, it never gets to the disconnect statement resulting in
the Apache process staying attached to the server. In a case like
the script probably is not authorized to stop the Apache processes.
The more I think about it, I may end up having to contact
Oracle to find out if there is a way to keep any single process from
being connected to the database over a specified amount of
time.

Dunno about RDB, but plain Oracle does have a timeout parameter the
server can set on the connection.

Apache can also have timeouts on individual mod_perl processes (and
Apache processes in general) that will shoot processes after a while,
you might want to take a look at that.

Post by J***@aksteel.com
Below is a part of a script that if ConvertNumToDate() is not in the
package UTILITIES, then the script would never detach from the
database.
my $dbh = DBI->connect("dbi:RDB:ATTACH FILENAME meltdbdir:melt_rdb")
or die "Can't connect to Melt Shop database: $DBI::errstr\n";

That ought to go out of scope when the program errors out, and get
cleaned up after. That it isn't makes me think there's something odd
going on. Try setting the logica PERL_DESTRUCT_LEVEL to 2, in some
logical table that the Apache process can see and see what happens. I
don't remember off-hand if mod_perl overrides this, but it might help
in the short term, at least until a better solution can be found.

Is this program a mod_perl handler, a plain CGI program running under
mod_perl, something using Apache::Registry, or somethin gelse?

[Brad Hughes]

Jennifer Landberg wrote:
[...]

Post by J***@aksteel.com
I can give you an example of when Apache will stay connected. If I connect to the database and, before the disconnect, I have a
call to a subroutine that does not exist, then the script will blow up. So, it never gets to the disconnect statement resulting in
the Apache process staying attached to the server.

Will adding (using the proper disconnect syntax)

END{ $dbh->disconnect if defined $dbh }

to the script work?

brad