DBI sudden exit on MySQL insert

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

Howdy bros. I have written the following sub to take an object and insert its contents into MySQL via DBI using placeholders:

sub insertsql {
    my ($dbh,$table,$data) = @_;
    my @qm;
    my @keys;
    my @values;
    my $i = -1;
    foreach my $k (keys %$data) {
    $i++;
    $keys[$i] = $k;
    $values[$i] = $data->{$k};
    $qm[$i] = '?';
    }
    my $keylist = join(",",@keys);
    my $qlist = join(",",@qm);
    my $sqlstatement = "insert into $table ($keylist) values ($qlist)"
+;
    my $sth = $dbh->prepare($sqlstatement);
    $sth->execute(@values) || die "Could not execute MySQL statement: 
+$sqlstatement";
    $sth->finish();
    return $dbh->{'mysql_insertid'};
}
[download]

This has worked fine for me for some time, but lately I have encountered situations where when I execute the sub, the whole Perl script containing it goes directly to exit, no error message, nothing--just ends. I have determined in one case this happened when one of the values that was supposed to be in @values was null.

But my question is, why does it just stop rather than DIEing on the $sth->execute method and giving me the specified error message? That would make it a whole lot easier to find and correct the offending input. Have I not set it up right somehow?

TIA...Steve

Comment on DBI sudden exit on MySQL insert Download Code

Replies are listed 'Best First'.
Re: DBI sudden exit on MySQL insert by TOD (Friar) on Apr 19, 2007 at 01:42 UTC
`my $dbh = DBI->connect($data_source, $username, $password, { RaiseErro +r => 1 });` [download] maybe you just forgot the RaiseError attribute?	[reply] [d/l]
Re^2: DBI sudden exit on MySQL insert by cormanaz (Deacon) on Apr 19, 2007 at 15:21 UTC
I indeed did not have that set, so I will give it a try. I'm not sure that explains the bad behavior though. The DBI docs say it will "force errors to raise exceptions rather than simply return error codes in the normal way." I'm not getting error codes or anything--just an unexplained halt. Docs also say "Typically RaiseError is used in conjunction with eval { ... } to catch the exception that's been thrown and followed by an if ($@) { ... } block to handle the caught exception" and I'm not trying to trap errors in an eval.	[reply]
Re: DBI sudden exit on MySQL insert by SheridanCat (Pilgrim) on Apr 19, 2007 at 15:17 UTC
Are you sure it's getting to the execute call? You should be checking the err() and errstr() methods on your handles to get database specific error messages.	[reply]
Re^2: DBI sudden exit on MySQL insert by cormanaz (Deacon) on Apr 19, 2007 at 15:25 UTC
I can't really do that because the interpreter just goes away during the execute call. I have watched it happen with the debugger. The Perl job just exits with no message or anything. It's really strange.	[reply]
Re: DBI sudden exit on MySQL insert by mreece (Friar) on Apr 19, 2007 at 16:54 UTC
i can't answer your question, but i recommend you check for errors from prepare as well. (one possibility is that one of your keys is not a column of $table, but i would expect to see an error trying to call execute on an undefined value.) `my $sth = $dbh->prepare($sqlstatement) or die "cannot prepare: $DBI::errstr";` [download] unrelated, but you can also simplify the top part of your function a bit: `my @values = values %$data; my $keylist = join ',', keys %$data; my $qlist = join ',', ('?') x @values;` [download]	[reply] [d/l] [select]
Re: DBI sudden exit on MySQL insert by rhesa (Vicar) on Apr 19, 2007 at 18:53 UTC
Your example code is quite probably innocent. It's pretty clear that everybody agrees you should see an exception of some kind, instead of perl silently crashing. My suspicion is that there's a problem in one of the binary components involved, and I'd guess DBD::mysql would be the prime suspect. Which versions of DBI, DBD::mysql and mysqld are you running? And on which OS?	[reply]
Re^2: DBI sudden exit on MySQL insert by cormanaz (Deacon) on Apr 19, 2007 at 23:31 UTC
I am running on windoze XP. When I check PPM it lists two installed versions of DBI, ver 1.54 installed in area "site" and ver 1.52-r1 installed in area "perl." I'm not too sure how this happened, unless it was during a recent upgrade to activeperl 5.8.x when I resored a snapshot of the modules from the old installation. Maybe this has something to do with it? DBD-mysql is ver 3.0002.	[reply]
Re: DBI sudden exit on MySQL insert by Anonymous Monk on Mar 12, 2014 at 20:39 UTC
I know this post is 7yrs old, but I was wondering if you ever found a solution. I'm having the same problem and I can't seem to find any information about it anywhere. Thank you.	[reply]