Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

DBI fails to return an error code

by djten (Novice)
on Oct 04, 2011 at 18:51 UTC ( #929639=perlquestion: print w/ replies, xml ) Need Help??
djten has asked for the wisdom of the Perl Monks concerning the following question:

This one has got me scratching my head. It involves running some Perl code on two computers. One is my desktop computer, running Windows 7 Professional SP1, everything up to date. The other is my laptop computer, running Vista Home Premium SP2, everything up to date.

Everything else is the same - I purposely made it so. I set them both up at the same time, in the same way, because I wanted to have as close to identical development environments as possible between working in my office and going on the road. ActiveState Perl v5.12.4, Apache v2.2.17, MySQL v5.5.8, Perl DBI v1.616, and all configuration settings identical on both computers.

Yesterday I generated an error on my desktop computer in a project I am working on. I had changed a column name in a table, but forgot to update the references to that column in my code. So executing the code generated an error. I have code to display any database access errors, relying on $DBI::err and $DBI::errstr. In this case the error was of course "unknown column."

What I noticed was, when I use my laptop to run the same code on the identically structured table (that still had the column name discrepancy), I got the same error, but where my same error-catching code trys to display the contents of $DBI::err and $DBI::errstr, I get nothing. An empty string, I guess. The error is definitely generated, but $DBI::err and $DBI::errstr are empty. Only on my laptop, with all the same versions of everything.

I have an identical copy of all the projects I am working on or maintaining on both computers. So on my desktop I go to a different project, put an error into some database access code, and purposelly generate an error. The err and errstr values show up. Then I make the same purposeful error in the same project on my laptop, and err and errstr are still blank. So it's not specific to one project - I would guess that it is system-wide in some way. But what, where, how?

At this point someone will certainly wonder, when did you start noticing this happening on the laptop? Did you install something new on that laptop recently? Well, I just installed both machines last week (identical everyting, as I described above), and this is the first time I have had a database error come up in coding since then. I did not install anything new or different on the laptop.

Can any of the Perl Monks think of a situation that would cause this? Because I am currently stumped.

Comment on DBI fails to return an error code
Re: DBI fails to return an error code
by jdrago999 (Pilgrim) on Oct 04, 2011 at 20:30 UTC

    I am currently stumped.

    Are you using RaiseError => 1 when you connect?

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

      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.

Re: DBI fails to return an error code
by mje (Deacon) on Oct 05, 2011 at 10:00 UTC

      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;

      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"; }

      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 +.');

      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' ...

      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: - ...

      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;

        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?

Reaped: Re: DBI fails to return an error code
by NodeReaper (Curate) on Oct 05, 2011 at 13:03 UTC
Re: DBI fails to return an error code
by Neighbour (Friar) on Oct 05, 2011 at 14:06 UTC
    Only use $DBI::err (and errstr) when connecting to a database.
    Use $dbh->err (and $dbh->errstr) for actions performed using this $dbh.
    When working with statement handles (like prepared queries), use $sth->err.
    Always try to use the err-function in the smallest scope possible, because the rest can be altered by other parts running in your code (in other threads or wherever).

      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

      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).
Reaped: Re: DBI fails to return an error code
by NodeReaper (Curate) on Oct 09, 2011 at 12:35 UTC

      Don't know if its bad form to revive this post but I have found a sort of workaround. Note that this is the top link on google search, so its still relevant imho.

      Having finally said goodbye to XP I have had the same problem and it's kicking my butt. Not having errors kick out is very inconvenient, not to mention possibly professionally dangerous! Also adding in die to the execute call creates a false negative!

      All my research points to it being a bug with Windows 7 64 bit. Having just upgraded EVERYTHING in my cpan collection, it looks like its still live.

      The (partial) workaround I found is to call $DBI::rows if $DBI::rows is <0 - specifically -1 then the sql call failed. if it is positive then the call is OK. Have it print out your SQL to the console if -1 and test it in myphpamin or whatever to get the error.

      Point is, for insert/update at least its a reliable way to at least get some idea of how your statement worked!

      Now I haven't tested much further than inserting. But I imagine that in other cases it should perform in a similar way.

      It's not groundbreaking, I know, but I hope it helps someone else who banged their head against the wall over this!

Re: DBI fails to return an error code
by rtillian (Initiate) on May 10, 2013 at 09:12 UTC

    Hi, the solution can be found at:

    http://search.cpan.org/~capttofu/DBD-mysql-4.023/lib/DBD/mysql.pm

    There you find:

    DATABASE HANDLES

    The DBD::mysql driver supports the following attributes of database handles (read only):
    $errno = $dbh->{'mysql_errno'}; <---- SOLUTION!
    $error = $dbh->{'mysql_error'};
    $info = $dbh->{'mysql_hostinfo'};
    $info = $dbh->{'mysql_info'};
    $insertid = $dbh->{'mysql_insertid'};
    $info = $dbh->{'mysql_protoinfo'};
    $info = $dbh->{'mysql_serverinfo'};
    $info = $dbh->{'mysql_stat'};
    $threadId = $dbh->{'mysql_thread_id'};

    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";

    you will get the answer:

    Problem: Table 'not_existing_table' doesn't exist

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://929639]
Approved by ikegami
Front-paged by ikegami
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2014-07-11 06:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (220 votes), past polls