sybperl-l Archive

From: "Avis, Ed" <avised at kbcfp dot com>
Subject: Infinite loop calling error handler
Date: Jun 11 2004 3:47PM

When using DBD::Sybase to call a stored procedure I found that my
program would hang and very rapidly start chewing up memory.  This
didn't happen for every call but only for one which failed (a
raiserror and negative exit status from the stored procedure).  It may
also be relevant that I had two statement handles open, one querying
a table and one running the proc.

I got some way to debugging this but then it mysteriously stopped
being reproducible.  However I did find out that the error handler was
being called again and again, and probably recursively since the
memory usage of the program rapidly rose to 1600 megabytes and CPU
use was at 100% when I killed it.  My program looks like

use warnings;
use strict;
use DBI qw(SQL_INTEGER);
my $dbh = DBI->connect("dbi:Sybase:server=$server;database=$database",
                       $login, $password,
                       {
                            RaiseError => 1,
                            ShowErrorStatement => 1,
                            AutoCommit => 0,
                            syb_err_handler => sub {
                                if ($_[0] == 0) {
                                    # Print to stdout and then ignore.
                                    print "$_[6]\n";
                                    return 0;
                                }
                                print STDERR "error handler called with: @_\n";
                                sleep 1;
                                return 1;
                            },
                        });
$dbh->{AutoCommit} = 1;

my $sth_query = $dbh->prepare('select some columns');
execute $sth_query;

$sql = <<'END'
exec my_proc @a = ?,
             @b = ?,
             @c = ?,
             @d = ?,
             @e = ?
END
    ;
my $sth_update = $dbh->prepare($sql);
$sth_update->bind_param($_, undef, SQL_INTEGER) for 1, 2, 3;

while (my @row = $sth_query->fetchrow_array()) {
    print "calling proc\n";
    $sth_update->execute(some parameters);
    print "successfully called proc\n";
}
finish $sth_query;
finish $sth_update;
disconnect $dbh;

The stored procedure does at some point

    raiserror 60020, 'message', 'some more message'

The output looks like

calling proc
error handler called with: 60020 16 1 189 DATATECH_NY my_proc message some more message exec my_proc @a = ?,
                  @b = ?,
                  @c = ?,
                  @d = ?,
                  @e = ?
 server
error handler called with: 155 1 0 0   ct_fetch(): user api layer: external error: This routine cannot be called when the command structure is idle. exec my_proc @a = ?,
                  @b = ?,
                  @c = ?,
                  @d = ?,
                  @e = ?
 client
error handler called with: 155 1 0 0   ct_fetch(): user api layer: external error: This routine cannot be called when the command structure is idle. exec my_proc @a = ?,
                  @b = ?,
                  @c = ?,
                  @d = ?,
                  @e = ?
 client
error handler called with: 155 1 0 0   ct_fetch(): user api layer: external error: This routine cannot be called when the command structure is idle. exec my_proc @a = ?,
                  @b = ?,
                  @c = ?,
                  @d = ?,
                  @e = ?
 client

and so on forever.  If it were not for the sleep(1) in the error
handler the process would start sucking up RAM very quickly.

However, I am no longer able to reproduce this, I don't know why.
But I hope that the above provides enough clues.  Strangely, when
I stepped through with 's' in the debugger it looked like the error
handler was calling itself:

KBCdbiUtil::CODE(0x84673b4)(/prod/datatech/inferno/bin/KBCdbiUtil.pm:149):
149:	                                    return 1; # handle other errors normally
  DB<3> 
KBCdbiUtil::CODE(0x84673b4)(/prod/datatech/inferno/bin/KBCdbiUtil.pm:144):
144:	                                    if ($_[0] == 0) {
  DB<3> 
KBCdbiUtil::CODE(0x84673b4)(/prod/datatech/inferno/bin/KBCdbiUtil.pm:149):
149:	                                    return 1; # handle other errors normally
  DB<3> 
KBCdbiUtil::CODE(0x84673b4)(/prod/datatech/inferno/bin/KBCdbiUtil.pm:144):
144:	                                    if ($_[0] == 0) {

in other words 's' showed just these two lines of code repeating
over and over.  Perhaps I have hit a bug in perl, who knows.

DBI-1.40, DBD::Sybase-1.02.6, perl-5.8.0 on Red Hat 8.

-- 
Ed Avis