Re^3: DBI fails to return an error code

[mje]

I installed DBD::mysql 4.018 and DBI 1.616 and ran the following based on your posting (note I had to invent $planname and substitute $dbname. Also I took out the fetch as it cannot work:

use strict;
use DBI;

my $h = DBI->connect('dbi:mysql:database=test', "xxx", "yyy");

eval {
    $h->do(q/drop table CUSTOMER_PLAN/);
};
$h->do(<<EOT);
CREATE TABLE `CUSTOMER_PLAN` (
   `RowId` int(11) unsigned NOT NULL auto_increment,
   `Name` varchar(6) default NULL,
   `Credits` smallint(6) unsigned default NULL,
   `Price` decimal(8,2) default NULL,
   `Credit_Value` decimal(3,2) default NULL,
   `Status` char(1) default NULL,  # A=Active, I=Inactive
   `Display_Order` char(3) default NULL,
   `Description` varchar(255) default NULL,
   PRIMARY KEY (RowId),
   KEY planname (Name),
   KEY descript (Description(15)),
   KEY dispord (Display_Order)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
EOT

$h->do(q/INSERT INTO `CUSTOMER_PLAN` VALUES(1, 'Free',     0,  0.00, 0
+.75, 'A', '001', 'Try us out - receive 3 free recipes.')/);
$h->do(q/INSERT INTO `CUSTOMER_PLAN` VALUES(2, 'Shine',   12,  9.75, 0
+.75, 'A', '002', 'Shine with a small introduction to Vibrant Healthy 
+Living.')/);
$h->do(q|INSERT INTO `CUSTOMER_PLAN` VALUES(3, 'Seeker',  35, 18.75, 0
+.75, 'A', '003', '<i>-noun.</i> An adventurous person who is seeking 
+new experiences and new information to create a higher level of under
+standing and enlightenment.')|);
$h->do(q|INSERT INTO `CUSTOMER_PLAN` VALUES(4, 'Sage',    60, 47.00, 0
+.75, 'A', '004', '<i>-noun.</i> A generous, lively person on the jour
+ney for the possession of wisdom and experience. This person is energ
+etic, vibrant and famed for health and wisdom.')|);
$h->do(q|INSERT INTO `CUSTOMER_PLAN` VALUES(5, 'Guru',   120, 89.00, 0
+.75, 'A', '005', '<i>-noun.</i> A divine teacher. This person has vas
+t knowledge and practices vibrant living while sharing with their com
+munity to create a greater understanding and manifestation of truth a
+nd beauty.')|);


sub db_err
{
  my ($pmsg, $psql, $perrnum, $perrstr) = @_;
  print "<p>There was a problem: $pmsg.</p>\n";
  print "<p><i>Attempted command:</i> $psql</p>\n";
  print "<p><i>Error:</i> $perrnum - $perrstr</p>\n";
}

my $dbname = 'test';
my $planname = 'Free';
my $sql = "SELECT DCredits, Price, Description FROM $dbname.CUSTOMER_P
+LAN WHERE Name='$planname'";
my $sqlh = $h->prepare($sql) || db_err("preparing statement handle", $
+sql, $DBI::err, $DBI::errstr);
$sqlh->execute || db_err("executing query", $sql, $DBI::err, $DBI::err
+str);
#####while (@row = $sqlh->fetchrow_array())
#####{
#####  ($credits, $price, $descript) = @row;
#####  # process the data on each loop here
#####}
#####$sqlh->finish;
$h->disconnect;

It fails with:

DBD::mysql::st execute failed: Unknown column 'DCredits' in 'field lis
+t' at /home/martin/930402.pl line 45.
<p>There was a problem: executing query.</p>
<p><i>Attempted command:</i> SELECT DCredits, Price, Description FROM 
+test.CUSTOMER_PLAN WHERE Name='Free'</p>
<p><i>Error:</i> 1054 - Unknown column 'DCredits' in 'field list'</p>

When I rerun it with DBI_TRACE=15 I get the following near the end:

    -> prepare for DBD::mysql::db (DBI::db=HASH(0x9f482a0)~0x9f483c0 '
+SELECT DCredits, Price, Description FROM test.CUSTOMER_PLAN WHERE Nam
+e='Free'') thr#9dd4008
    New 'DBI::st' (for DBD::mysql::st, parent=DBI::db=HASH(0x9f483c0),
+ id=undef)
    dbih_setup_handle(DBI::st=HASH(0x9f48180)=>DBI::st=HASH(0x9e50408)
+, DBD::mysql::st, 9ece010, Null!)
    dbih_make_com(DBI::db=HASH(0x9f483c0), 9f3e808, DBD::mysql::st, 25
+2, 0) thr#9dd4008
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), Err, DBI::db=HASH(0x9f4
+83c0)) SCALAR(0x9ecdf30) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), State, DBI::db=HASH(0x9
+f483c0)) SCALAR(0x9ecdfb0) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), Errstr, DBI::db=HASH(0x
+9f483c0)) SCALAR(0x9ecdf70) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), TraceLevel, DBI::db=HAS
+H(0x9f483c0)) 0 (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), FetchHashKeyName, DBI::
+db=HASH(0x9f483c0)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), HandleSetErr, DBI::db=H
+ASH(0x9f483c0)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), HandleError, DBI::db=HA
+SH(0x9f483c0)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), ReadOnly, DBI::db=HASH(
+0x9f483c0)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x9e50408), Profile, DBI::db=HASH(0
+x9f483c0)) undef (not defined)
    -> dbd_st_prepare MYSQL_VERSION_ID 50083, SQL statement: SELECT DC
+redits, Price, Description FROM test.CUSTOMER_PLAN WHERE Name='Free'
    >- dbd_st_free_result_sets
    <- dbd_st_free_result_sets RC -1
    <- dbd_st_free_result_sets
>count_params statement SELECT DCredits, Price, Description FROM test.
+CUSTOMER_PLAN WHERE Name='Free'
    <- dbd_st_prepare
    <- prepare= DBI::st=HASH(0x9f48180) at /home/martin/930402.pl line
+ 44
    >> execute     DISPATCH (DBI::st=HASH(0x9f48180) rc1/1 @1 g0 ima10
+41 pid#19440) at /home/martin/930402.pl line 45
    -> execute for DBD::mysql::st (DBI::st=HASH(0x9f48180)~0x9e50408) 
+thr#9dd4008
 -> dbd_st_execute for 09ece030
    >- dbd_st_free_result_sets
    <- dbd_st_free_result_sets RC -1
    <- dbd_st_free_result_sets
mysql_st_internal_execute MYSQL_VERSION_ID 50083
>parse_params statement SELECT DCredits, Price, Description FROM test.
+CUSTOMER_PLAN WHERE Name='Free'
        --> do_error
Unknown column 'DCredits' in 'field list' error 1054 recorded: Unknown
+ column 'DCredits' in 'field list'
        <-- do_error
IGNORING ERROR errno 0
 <- dbd_st_execute returning imp_sth->row_num 18446744073709551614
    !! ERROR: 1054 'Unknown column 'DCredits' in 'field list'' (err#0)
    <- execute= undef at /home/martin/930402.pl line 45
DBD::mysql::st execute failed: Unknown column 'DCredits' in 'field lis
+t' at /home/martin/930402.pl line 45.
    -> $DBI::err (*) FETCH from lasth=HASH
    <- $DBI::err= 1054
    -> $DBI::errstr (&) FETCH from lasth=HASH
    >> DBD::mysql::st::errstr
    <- $DBI::errstr= 'Unknown column 'DCredits' in 'field list''

What does your trace output look like on the failing machine?

[djten]

I have come across a solution for this. Which I find a relief, because it means I wasn't really slipping even further into insanity. This time.

The trace suggestion was a good one, even though the trace output on the two computers did not reveal anything definitive. The trace on my desktop computer showed the error being processed, and the trace on the laptop showed everything processing right through as if it was an error-free transaction. However, it did get me thinking about possible module bugs, since I had ruled out everything else I could think of.

I noticed that ActivePerl package manager showed that there was already a new version of DBD::MySQL available (4.020). So I updated both computers to that version, and when I ran the identical code on both computers again, they both caught and reported the error successfully and identically. So my laptop will once again be useful when DB-related debugging is necessary.

I noticed on CPAN, the DBD::MySQL bug report had some recent references to "Undefined $DBI::errstr," and the change log for v4.020 says "Numerous fixes for prepared statements," without much detail beyond that.

At this point I am going to go forward on the assumption that this was some kind of DBD::MySQL bug, and with v4.020 installed I am good to go.

Thank you to all who offered your wisdom to help with my problem.

[mje]

It was a bug but I don't think you are totally out of the woods yet. See post to the [e-mail] list from Ralf Neubauer at DBD::mysql: no errstr under Windows.

It may well be worth you subscribing to that list. BTW, you can see diffs between different versions of CPAN modules from CPAN tools e.g., http://search.cpan.org/tools/DBD-mysql-4.020