DBI DESTROY problem

skef has asked for the wisdom of the Perl Monks concerning the following question:

I recently upgraded from a rather old Perl/DBD::Sybase/FreeTDS installation to the current release versions, and I started hitting a problem where, on a DB error, my perl processes would expand to take up all the RAM on the system. I eventually traced the problem to the exception I throw in my DBI HandleError subroutine.

The memory problem might relate to the stack trace the exception tries to record (although disabling it doesn't seem to help), but the underlying cause seems to be with DBD::Sybase or perhaps DBI itself. When I feed selectrow_arrayref (or other similar functions) SQL that is a) wrong (e.g. references nonexistent columns) and b) contains placeholders, and then call "die" on the error string in the HandleError routine, I sometimes get a result like this:

	(in cleanup) DBD::Sybase::st DESTROY failed: Server message number=8179 severity=16 state=2 line=1 server=XXX text=Could not find prepared statement with handle 0.  at lib/LSPDB.pm line 27.
DBD::Sybase::db selectrow_hashref failed: Server message number=8179 severity=16 state=2 line=1 server=XXX text=Could not find prepared statement with handle 0.  at lib/LSPDB.pm line 27.

I'm no expert on Perl internals, but from poking around it seems that that "(in cleanup)" (which isn't from my code) implies that my error handler is being called while (or after?) a statement handler internal to the selectrow_hashref call is DESTROYed. And I can see why throwing an exception in that kind of context could lead to trouble (although just dying seems to work OK, except for the extra message.)

I would rather not completely restructure my code, especially because I'm not sure it would help. I'm stuck with this SQL Server DB, and I've tried using ODBC with Microsoft's driver, and that's its own nightmare. So what I'd like to know is:

Am I just doing something dumb?
If I'm not just doing something dumb, is there some way of detecting that code is running as part of a DESTROY routine? (If I assume the database handle error will come through, I could presumably just skip the statement handle error, but sometimes I do prepare/execute so I don't want to skip it in every case.)

Here's a code snippet for reference, where calling make_trouble() will reproduce the error (although similar code doesn't always, so there's probably a race condition). And obviously without an actual DBD::Sybase compatible server it won't run.

package LSPDB;

use DBI;

sub make_trouble {
  my $dbh = _connect();

  return $dbh->selectrow_hashref("SELECT bar FROM course where foo = ?
+", {}, 1);
}

sub _connect {
  my $db_options = {
                    HandleError => \&_db_handle_error,
                     AutoCommit => 1,
                   };
  my $_dbh = DBI->connect(
                          'DBI:Sybase:server=foo;database=bar', 'theus
+er', 'thepass',
                          $db_options,
                          );

  return $_dbh;
}

sub _db_handle_error {
   my ($errstr, $dbh, $ret) = @_;

   die $errstr;
}

1;
[download]

Comment on DBI DESTROY problem Download Code

Replies are listed 'Best First'.
Re: DBI DESTROY problem by thanos1983 (Parson) on Oct 11, 2014 at 18:40 UTC
Hello skef, Welcome to the community. Well I do not see something extremely wrong with your code. So I have a few minor suggestions and possible problem solutions. I was looking online and maybe this is the reason/solution to your problem AutoInactiveDestroy Taken from the documentation: Type: boolean, inherited The "InactiveDestroy" attribute, described above, needs to be explicitly set in the child process after a fork(). This is a problem if the code that performs the fork() is not under your control, perhaps in a third-party module. Use AutoInactiveDestroy to get around this situation. If set true, the DESTROY method will check the process id of the handle and, if different from the current process id, it will set the InactiveDestroy attribute. This is the example it's designed to deal with: `my $dbh = DBI->connect(...); some_code_that_forks(); # Perhaps without your knowledge # Child process dies, destroying the inherited dbh $dbh->do(...); # Breaks because parent $dbh is now broken` [download] Caution: The AutoInactiveDestroy attribute was added in DBI 1.614. So in case you want to add this feature you need to verify/update your version. Second Objervation: From Programming with the DBI By Alligator Descartes & Tim Bunce: What happens if you don't explicitly disconnect? Since DBI handles are references to Perl objects, Perl's own garbage collector will move in and sweep up any object trash you leave lying around. It does that by calling the object's DESTROY method when there are no longer any references to the object held by your script, or when Perl is exiting. Third Objervation: Personally I do not like Manual Error Checking so I do it automatically by setting `$h->{PrintError} = 1;` and `$h->{RaiseError} = 1;`. You can read on the same reference about Automatic Versus Manual Error Checking. Taken all this in consideration a sample script written in MySQL but with minor modifications should work also for SQL. I have added functions to check if the DB or the TABLE exist else create them. I am also using `conf.ini` file just in case I want to connect to multiple devices so I can use a for loop. Personally I found it extremely useful in case you do not like it just import your data straight. conf.ini `[MySQL] user=username pass=password host=hostname port=3306 db=database_name table=table_name` [download] MySQL.pl Script Read more... (7 kB) Hope this helps and solves your problem. Seeking for Perl wisdom...on the process of learning...not there...yet!	[reply] [d/l] [select]
Re^2: DBI DESTROY problem by skef (Novice) on Oct 11, 2014 at 20:09 UTC
Thanks for the welcome and reply! Unfortunately, InactiveDestroy doesn't fix the problem, and AutoInactiveDestroy seems only to be applicable to situations where the process is forking (and, in any case, doesn't fix the problem). The test code I posted was the shortest I could come up with that generated the double error, so it doesn't really capture the use case. My actual code runs in the context of a Catalyst web server, and I'm throwing the exception to get the error message and the location in the code up to the request handler code so that it can be prettied up and sent back to the browser. So while I could give up on that and put a `die` every call, that handling would end up being much more explicit, not less (especially because of the added code to dig out the last SQL statement).	[reply]
Re: DBI DESTROY problem by skef (Novice) on Oct 11, 2014 at 21:54 UTC
Well, I poked around a bit more, and it turns out that when the error handler is called with the statement handle, that latter is pretty much already destroyed. So much so that "Type" (which should be 'st' for that kind of handle, according to the DBI documentation) isn't defined. And I guess that serves as a de facto version of the check I was asking about. If I just return without doing anything in that case, I still get the doubled error message, but the exception throws just fine from the other call. I guess that's what I'll go with for now.	[reply]
Re^2: DBI DESTROY problem by erix (Prior) on Oct 11, 2014 at 21:59 UTC
Perhaps you need PrintError off (i.e.: `$dbh->{PrintError} = 0;`) because you are doing that already yourself (by calling die + message).	[reply] [d/l]