SEO

I'm using DBD::Sybase with prepared statements. However, after executing
several hundred statements (including SELECT, INSERT, UPDATE) the
database connection gets terminated by the server.

# opening the connection
my $dbh = DBI->connect('dbi:Sybase:server=ISSERVER;database=medu sadb',
'sa', 'geheim');

# a typical SELECT statement
my $sth = $dbh->prepare_cached('SELECT objectid, classid, parentid,
alias, guid, inherit FROM object WHERE objectid=?');
$sth->execute(1);
my $results = $sth->fetchall_arrayref;
$sth->finish;


After some hundreds of similar statements I get the
following error:

DBD::Sybase::st execute failed: Server message number=21
severity=26 state=1 line=65535 procedure=DBD_2204_9
text=WARNING - Fatal Error 8211 occurred at Apr 2 2004 5:00PM.
Please note the error and time, and contact a user with System
Administrator (SA) authorization.
Server message number=5702 severity=10 state=1 line=65535
procedure=DBD_2204_9 text=The SQL Server is terminating this
process.
at D:\epages5\Cartridges/DE_EPAGES/Database/API/DBISybase.pm line 63.
DBD::Sybase::db disconnect failed: OpenClient message: LAYER = (5)
ORIGIN = (2) SEVERITY = (7) NUMBER = (12)


An attempt to run subsequent SQL statements on the same connection
gives this error:

Message String: ct_cmd_drop(): user api layer: external error: The
connection has been marked dead.
at D:\epages5\Cartridges/DE_EPAGES/Database/API/DBI.pm line 65.
execute failed : Server message number=21 severity=26 state=1
line=65535 procedure=DBD_2204_9 text=WARNING - Fatal Error 8211
occurred at Apr 2 2004 5:00PM. Please note the error and time,
and contact a user with System Administrator (SA) authorization.
Server message number=5702 severity=10 state=1 line=65535
procedure=DBD_2204_9 text=The SQL Server is terminating this
process.
at SELECT objectid, classid, parentid, alias, guid, inherit FROM
object WHERE objectid=?
at D:\epages5\Cartridges/DE_EPAGES/Database/API/DBISybase.pm line 63.


The Sybase error log contains the following message that corresponds
with the above SQL statement:

server Error: 8211, Severity: 26, State: 1
server Mismatch found between the name and id descriptor hash table.
Descriptor hashed by name = 0x0 and hashed by id = 0x203f2e8c.
kernel ************************************
kernel SQL causing error : create proc DBD_2204_9 as SELECT objectid,
classid, parentid, alias, guid, inherit FROM object WHERE objectid=?
kernel ************************************
server SQL Text: create proc DBD_2204_9 as SELECT objectid, classid,
parentid, alias, guid, inherit FROM object WHERE objectid=?
kernel curdb = 2 tempdb = 2 pstat = 0x40010000
kernel lasterror = 8211 preverror = 0 transtate = 1
kernel curcmd = 193 program = install.pl


When I replace '$dbh->prepare_cached' by '$dbh->prepare' this error
does not occur. The error happens only with ASE 12.5.1, but not with
ASE 12.5. Any ideas?

Wolfgang

On Mon, 05 Apr 2004 14:44:36 +0200, Wolfgang Loch wrote:

> I'm using DBD::Sybase with prepared statements. However, after executing
> several hundred statements (including SELECT, INSERT, UPDATE) the database
> connection gets terminated by the server.
>
> # opening the connection
> my $dbh = DBI->connect('dbi:Sybase:server=ISSERVER;database=medu sadb',
> 'sa', 'geheim');
>
> # a typical SELECT statement
> my $sth = $dbh->prepare_cached('SELECT objectid, classid, parentid,
> alias, guid, inherit FROM object WHERE objectid=?');

I would not use prepare_cached() with DBD::Sybase (actually I should
probably just alias it to prepare()). I have never validated that
prepare_cached() works correctly.

> After some hundreds of similar statements I get the following error:
>
> DBD::Sybase::st execute failed: Server message number=21 severity=26
> state=1 line=65535 procedure=DBD_2204_9 text=WARNING - Fatal Error 8211
> occurred at Apr 2 2004 5:00PM. Please note the error and time, and
> contact a user with System Administrator (SA) authorization.

> The Sybase error log contains the following message that corresponds with
> the above SQL statement:
>
> server Error: 8211, Severity: 26, State: 1 server Mismatch found
> between the name and id descriptor hash table.
> Descriptor hashed by name = 0x0 and hashed by id = 0x203f2e8c.
> kernel ************************************ kernel SQL causing error :
> create proc DBD_2204_9 as SELECT objectid,
> classid, parentid, alias, guid, inherit FROM object WHERE objectid=?

This may be a Sybase bug. If you can recreate the same problem using a
plain C program then you could submit a case/bug report to Sybase TS.

In the meantime I suggest not using prepare_cached() - my guess is that
the gains are minimal, and the potential problems are significant (as
you've seen).

Could you please also submit this error to http://www.peppler.org/bugdb/
please? That way I have a track record of the issue.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler@peppler.org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html