Re: Sarge problems with MySQL and DBI / DBD::mysql

[Michael Stassen <Michael.Stassen@verizon.net>]

Oliver Elphick wrote:

> We have an application that has just upgraded to Debian sarge with mysql
> upgrading from 3.23.49 to 4.0.24.  As far as I know I have followed all
> the procedures for upgrading the database.
>
> Some queries always fail in DBD::mysql (in DBI) although they work OK
> with the mysql client.
>
> For example, this SQL command:
>
> 	SELECT description
> 		FROM IncoCountry
> 		WHERE value = "EW";
>
> which returns 0 rows, works correctly in mysql.  But when it is passed
> through this statement in Perl:
>
>         my @row = $dbh->selectrow_array($sql) or
>                 &failed(__FILE__, __LINE__,
>                  "Failed to run the sql statement with error
>                   $DBI::errstr");
>
> it fails inside DBD::mysql without giving any message in $DBI::errstr:

This does not fail.  Your logic is wrong.  You are evaluating 
$dbh->selectrow_array($sql) in a scalar context (selectrow_array or ...). 
According to `perldoc DBI`, in the section on selectrow_array, "in a scalar 
context, an "undef" is returned if there are no more rows".  Your query 
successfully returns 0 rows, so $dbh->selectrow_array($sql) is "undef".  undef 
is false, so the part of your query after the "or" gets run.  You've coded an 
error message which claims the query produced a DBI error, but it did not.

> DBD::mysql::db::prepare(/usr/lib/perl5/DBD/mysql.pm:208):
> 208:        my $sth = DBI::_new_sth($dbh, {'Statement' => $statement});
>   DB<2> n
> DBD::mysql::db::prepare(/usr/lib/perl5/DBD/mysql.pm:211):
> 211:        if (!DBD::mysql::st::_prepare($sth, $statement, $attribs)) {
>   DB<2> s
> DBD::mysql::db::prepare(/usr/lib/perl5/DBD/mysql.pm:215):
> 215:        $sth;
>   DB<2> s
> main::failed(/home/domain/www.chpresenters.co.uk/web/cgi-bin/ocms-multi/common/cgi-lib.pl:117):
>
> If a row is added to the table to be returned by this query, the problem
> goes away.
>
> How can I debug or fix this problem, please?

You need to revise your code to distinguish between DBI errors and 0 rows 
returned.

Michael