So, I recently debugged an error in some code I inherited. It was a relatively bizarre corner case that I thought might be interested.
The code is Perl running on windows, accessing a sybase database using DBI and DBD::Sybase.
This was the basic structure of the code:
sub process_record {
my $record = shift;
if (is_record_in_db($record)) {
if (mark_existing_record_deleted($record)) {
insert_record($record);
}
} else {
insert_record($record);
}
}
There was some other stuff in there too, but it doesn’t matter for this story.
This code had been in production for about a year, and it’s been working fine. Suddenly we start getting this error:
Panic: Can't have multiple statement handles on a single database handle when AutoCommit is OFF
It also gives a line number from the bowels of DBD::Sybase. I try to get a full stack trace by running it under -MCarp=verbose . But, DBD::Sybase doesn’t croak, it dies, so I can’t see which call of mine is killing it. I don’t feel like hitting the debugger yet, so I think some more.
Now, this error comes only after we’ve processed 5000 or so records, so I take a look to see what’s different about the record where it fails, and I notice that it takes the path which calls mark_existing_record_deleted, while none of the others do. (Inserts heavily outnumber updates on this application.)
(Note: all of the called routines access the database.) DBD::Sybase doesn’t allow you to have multiple statement handles. So I look in the code and see that, sure enough, mark_existing_record_deleted doesn’t have a call to $sth->finish . But neither does is_record_in_db! Now I’m wondering, “how does this thing work at all?”
The answer is, that DBD::Sybase automatically finishes the handle for you when you read past the last row. So when there’s no data in the database, the query in is_record_in_db returns no rows. One fetch is “past the end,” so the handle gets closed. But when there is data, the query handle is open, causing the application to die when you try to open another handle in mark_existing_record_deleted.
So, watch out for that when you write code against Sybase.