How to reconnect a dead DBD::Sybase Connection

It is important to know that this is just one method of reconnecting a dead connection.  Note that we are handling the errors manually for the individual query.  It wouldn’t take much to create a db_exec subroutine so we just call the db_exec() subroutine and just worry about the reconnect in one place.

Notice that because of DBD::Sybase bug # 616, the error 151 will be printed to STDERR.

./test_sybase
OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (151)
Message String: ct_cancel(): user api layer: external error: A connection to the server must exist on the connection structure before this routine can be called.
ERROR: Connection to DBMS died
syb_db_disconnect(): ct_close() failed
MSG: ERROR: Reconnected

#!/usr/bin/perl

use strict;
use warnings;

use lib “/home/jfroebe/lib”;

use DBI;
use File::Basename;

our $SYBDBA1_login = ‘login’;
our $SYBDBA1_password = ‘password’;

our $dbh = connect_dbms();

sub report_err {
my $msg = shift;
my $type = shift;

if ($type eq ‘error’) {
print “ERROR: $msgn”;
} else {
print “MSG: $msgn”;
}
}

sub exit_on_error {
my $msg = shift;

report_err($msg, “error”);
return -1;
}

sub connect_dbms {
my $loc_dbh;
my $script = basename($0);

if ($loc_dbh = DBI->connect(“dbi:Sybase:server=SISDBA1;loginTimeout=10;timeout=30;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1”, $SYBDBA1_login, $SYBDBA1_password, { PrintError => 0, RaiseError => 1 } )) {
return $loc_dbh;
}

report_err(“unable to connect to SISDBA1”, “error”);
return;
}

sub syb_loop {
my $query = “exec sp_helpdb”;

RETRY: for (my $i = 0; $i < 10000; $i++) { my $array_ref; eval { $array_ref = $dbh->selectall_arrayref($query);
};

if ($@) {
if ($dbh->err == 151 || $dbh->err == 60) {
report_err(“Connection to DBMS died”, “error”);
undef($dbh);

for (my $i = 0; $i < 10; $i++) { if( $dbh = connect_dbms() ) { report_err("Reconnected", "msg"); redo RETRY; } sleep 3; } exit_on_error("Unable to reconnect after $i attempts!"); } } } } syb_loop(); [/perl]

Share Button

DBD::Sybase – reconnect through err_handler (feature request)

I received an email from Michael Peppler, the creator/maintainer of DBD::Sybase. In it, he says that this is a limitation of Sybase’s OpenClient CT-Library not DBD::Sybase.

The error handler is called by Sybase’s ct_callback() function. Sybase has restricted what CT-Library calls that can be made. Apparently while ct-connect() can be called, memory allocation of the connection will be disallowed.

I’m opening a number of feature requests with Sybase to make reconnecting, when the connection dies, easier to do.

I’m writing an application with a business requirement of reconnecting a dead db connection to Sybase through the syb_err_handler() error handler routine.

In the past, I would just run $dbh->DBDEAD() just prior to running the queries. If the connection was dead, I would just reconnect and run the query (plus any initialization db routines).

The syb_err_hander() can easily catch a dead connection but calling $dbh->connect() in the error handler fails

./test_sybase 
ERROR: Connection dropped ct_config(CS_SET, CS_LOGIN_TIMEOUT) failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ct_config(CS_SET, CS_TIMEOUT) failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ct_con_alloc failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ERROR: unable to connect to SISDBA 

Has anyone done this? I’m starting to think that this can’t be done through the error handler of DBD::Sybase

#!/usr/bin/perl
use strict;
use warnings;
use lib "/home/jfroebe/lib";
use DBI;
use File::Basename;
our $SYBDBA_login = ‘login’;
our $SYBDBA_password = ‘password’;
our $dbh = connect_dbms();
sub report_err {
        my $msg = shift;
        my $type = shift;
        if ($type eq ‘error’) {
          print "ERROR: $msgn";
        } else {
          print "MSG: $msgn";
        }
}
sub syb_err_handler {
    my($err, $sev, $state, $line, $server, $proc, $msg, $sql, $err_type) = @_;
    my %ignore_errors = map { $_, 1 } (6, 183, 208, 2056, 2057, 14024, 17001);
    if ($err == 151 || $err == 50 || $err == 60) {
       report_err("Connection dropped", "error");
       for (my $attempt = 0; $attempt < 10; $attempt++) {
          if ($dbh = connect_dbms()) {
             report_err("Reconnected", "error");
             return 0;
          }
          sleep 2;
       }
       return 1;
    } elsif ($err == 63 || ($err == 4 && $sev == 5) ) {
        report_err("Connection timed out", "error");
        return 1;
    } elsif ($ignore_errors{$err}) {
        # ignore
        return 0;
    }
    print "error: $err, $sev, $state : $msgn";
    return 1;
}
sub connect_dbms {
    my $script = basename($0);
    if ($dbh = DBI->connect("dbi:Sybase:server=SISDBA;loginTimeout=10;timeout=30;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1", $SYBDBA_login, $SYBDBA_password, { PrintError => 0, RaiseError => 0, syb_err_handler => &syb_err_handler } )) {        return $dbh;    }
    report_err("unable to connect to SISDBA", "error");
    return;
}
sub syb_loop {
        my $query = "exec sp_helpdb";
        for (my $i = 0; $i < 10000; $i++) {
                my $array_ref = $dbh->selectall_arrayref($query);
        }
}
syb_loop();
Share Button