I am currently stumped.

Are you using RaiseError => 1 when you connect?

my $dbh = DBI->connection('DBI:mysql:db:localhost', 'user', 'pass', {
  RaiseError => 1
});
[download]

I had not tried that before, so I added that in. Then I created an error on purpose in one place in my code (an unknown column name).

On my desktop machine, it threw the error to the Perl "software error" handling (bypassing my error display subroutine entirely), and reported:

"DBD::mysql::st execute failed: Unknown column 'DCredits' in 'field list'".

Exactly right!

On my laptop, it still tried to run my error display subroutine, with the error code and string still blank (no difference there), and Perl's "software error" reported:

"DBD::mysql::st fetchrow_array failed: fetch() without execute()".

Which was not the actual problem, really, so no help there.

Are you by any chance the same person that posted on stackoverflow at http://stackoverflow.com/questions/7432944/undefined-dbierrstr-using-dbi-perl-and-mysql-on-windows-7-x64/7434259 and who posted to dbi-users mailing list at http://www.mail-archive.com/dbi-users@perl.org/msg34081.html and reported https://rt.cpan.org/Public/Bug/Display.html?id=71047?

If so I have tried to help on some of those forums but you need to come up with something demonstrable that anyone else can run. The problem is that every pseudocode version you've posted works fine for me so I can only assume a) you have different versions or b) I've misinterpreted your pseudocode.

Write a small self contained real example that fails, post it along with your Perl, DBD::mysql and DBI versions and show the exact output of the script which demonstrates $DBI::errstr is not defined. Then if we cannot reproduce it we can help you debug it or perhaps trace it but we need to see a real life full example.

No, that was not me that created any of those other posts. Interesting though, it looks like that person is having the same issue. Actually, that person is having the reverse of my problem. It's my Windows 7 Pro SP1 (32bit) installation that has the DBI error values working as expected, and my Vista Home Premium SP2 laptop that is not.

In any event, you raise a good point, I should have included some explicit examples along with my initial rambling.

Keep in mind, however, this is not a matter of one isolated piece of code giving me trouble. On my Win7 desktop computer, all of the DBI error reporting works properly, everywhere throughout the entire project, and on my Vista laptop, all of the DBI reporting FAILS to work throughout the entire project.

Here's some sample code. I will use the snippet I purposely messed up, that I referenced in my reply to the previous post.

my $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost", $dbuid, $dbpw) || 
+db_err("connecting to database", $sql, $DBI::err, $DBI::errstr);
my $sql = "SELECT DCredits, Price, Description FROM $dbname.CUSTOMER_P
+LAN WHERE Name='$planname'";
my $sqlh = $dbh->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;
$dbh->disconnect;
[download]

The actual column is really named just Credits, not DCredits.

I might not be handling the above code in the best way (feel free to let me know), but I want to catch any possible error at each point of preparation, and pass it to a subroutine db_err. It's actually a method in a little module I made for this project. The simple code for that is as follows.

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";
}
[download]

Presenting any database errors that may occur in this way is one of the requirements of the project.

To duplicate the data, here is a series of MySQL statements.

## Table
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;
## Data
INSERT INTO `CUSTOMER_PLAN` VALUES(1, 'Free',     0,  0.00, 0.75, 'A',
+ '001', 'Try us out - receive 3 free recipes.');
INSERT INTO `CUSTOMER_PLAN` VALUES(2, 'Shine',   12,  9.75, 0.75, 'A',
+ '002', 'Shine with a small introduction to Vibrant Healthy Living.')
+;
INSERT INTO `CUSTOMER_PLAN` VALUES(3, 'Seeker',  35, 18.75, 0.75, 'A',
+ '003', '<i>-noun.</i> An adventurous person who is seeking new exper
+iences and new information to create a higher level of understanding 
+and enlightenment.');
INSERT INTO `CUSTOMER_PLAN` VALUES(4, 'Sage',    60, 47.00, 0.75, 'A',
+ '004', '<i>-noun.</i> A generous, lively person on the journey for t
+he possession of wisdom and experience. This person is energetic, vib
+rant and famed for health and wisdom.');
INSERT INTO `CUSTOMER_PLAN` VALUES(5, 'Guru',   120, 89.00, 0.75, 'A',
+ '005', '<i>-noun.</i> A divine teacher. This person has vast knowled
+ge and practices vibrant living while sharing with their community to
+ create a greater understanding and manifestation of truth and beauty
+.');
[download]

The versions I am using on both desktop and laptop: ActiveState Perl v5.12.4, Apache v2.2.17, MySQL v5.5.8, DBI v1.616, DBD::mysql v4.018.

Using the above back in my project, I get the following on my Win7 desktop computer.

There was a problem: executing query.
Attempted command: SELECT DCredits, Price, Description FROM hlr.CUSTOM
+ER_PLAN WHERE Name='Free'
Error: 1054 - Unknown column 'DCredits' in 'field list'
...
[download]

etc. for the other rows. Clearly passing on what the error was.

On my Vista laptop, I get the following.

There was a problem: executing query.
Attempted command: SELECT DCredits, Price, Description FROM hlr.CUSTOM
+ER_PLAN WHERE Name='Free'
Error: - 
...
[download]

etc. No error information at all.

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;
[download]

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>
[download]

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''
[download]

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

I'm not entirely sure you are right when you suggest there is a separate err/errstr for each handle. I believe there is only one despite it looking like there is one per handle via the pod.

perl -le 'use strict; use warnings; use DBI; my $h = DBI->connect("dbi
+:mysql:database=test", "xxx","yyy", {RaiseError => 0, PrintError => 0
+}) or die $DBI::errstr; my $s = $h->prepare(q/select * from does_not_
+exist/) or die "prepare: " . $DBI::errstr; $s->execute or print "\nex
+ecute DBI:" . $DBI::errstr . " sth:", $s->errstr . " dbh:", $h->errst
+r; $h->do(q/rubbish/) or print $s->errstr, "\n";

execute DBI:Table 'test.does_not_exist' doesn't exist sth:Table 'test.
+does_not_exist' doesn't exist dbh:Table 'test.does_not_exist' doesn't
+ exist
You have an error in your SQL syntax; check the manual that correspond
+s to your MySQL server version for the right syntax to use near 'rubb
+ish' at line 1
[download]

Notice you get the same error from DBI, dbh and sth. Also, if you error on a dbh you can access the same error from the sth. I have this recollection from a dbi-dev posting from way back I could probably dig out.

Initially, all errors are set in $DBI::err. However, since all actions involving DBI (which can be from multiple DBI-sources, multiple databases, multiple queries using the same $dbh, $DBI::err will be overwritten quite a lot.
To avoid getting an error that is generated by an entirely different bit of code (which runs in another thread/process but shares the DBI environment, or even the $dbh), it is recommended to use the handle with the smallest scope (because that way you can be sure to a much better degree that nothing else has overwritten your errorcode/errorstring).

Hi, the solution can be found at:

There you find:

Use $dbh->{'mysql_error'} instead of $DBI::errstr like here:

my $sql = "delete from not_existing_table where ai = 12";
$dbh->do( $sqlbef ) || die "Problem: $dbh->{'mysql_error'}\n";
[download]

you will get the answer:

Nonesense. The problem was a bug in DBD::mysql. You should be able to rely on DBIs err and errstr methods.