Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Class::DBI::AbstractSearch and SpeedyCGI

by zigdon (Deacon)
on Oct 03, 2005 at 22:24 UTC ( #497114=perlquestion: print w/replies, xml ) Need Help??

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

I've a problem that's just driving me up a wall - not only is it very intermittant, but I can't think of how to debug it properly. I'm using CGI::SpeedyCGI for one of my larger scripts, and have been very happy with how it's working out. Now, I've migrated part of the script to use Class::DBI instead of my custom DB code. Most of the time, it works wonderfully. But sometimes, only sometimes, it blows up with the following error:
[Mon Oct 3 22:15:21 2005] script: MyApp::Sets can't SELECT id, id, se +tnumber, setrev, name, theme, year, pcs, figs, picture, msrp, instruc +tions, inventory [Mon Oct 3 22:15:21 2005] script: FROM SETS [Mon Oct 3 22:15:21 2005] script: WHERE ( ID = ? ) [Mon Oct 3 22:15:21 2005] script: : Not an ARRAY reference at /home/u +ser/lib/modules/Class/DBI.pm line 1124. [Mon Oct 3 22:15:21 2005] script: at /home/user/lib/modules/Class/DB +I/AbstractSearch.pm line 31
How would I track this down? The problem doesn't seem to be in my code, and even when I added some debugging code to CDBI:AbstractSearch, everything looked right:
[Mon Oct 3 21:21:58 2005] script: phrase = $VAR1 = '( Theme = ? )'; [Mon Oct 3 21:21:58 2005] script: [Mon Oct 3 21:21:58 2005] script: bind = $VAR1 = [ [Mon Oct 3 21:21:58 2005] script: 0 [Mon Oct 3 21:21:58 2005] script: ]; [Mon Oct 3 21:21:58 2005] script: MyApp::Sets can't SELECT id, id, se +tnumber, setrev, name, theme, year, pcs, figs, picture, msrp, instruc +tions, inventory [Mon Oct 3 21:21:58 2005] script: FROM SETS [Mon Oct 3 21:21:58 2005] script: WHERE ( Theme = ? ) [Mon Oct 3 21:21:58 2005] script: : Not an ARRAY reference at /home/u +ser/lib/modules/Class/DBI.pm line 1124. [Mon Oct 3 21:21:58 2005] script: at /home/user/lib/modules/Class/DB +I/AbstractSearch.pm line 33
The problem goes away if I stop using SpeedyCGI, but the performance hit is too much at this point. (yes, I should migrate to mod_perl, one day). Any ideas what I could try next to debug this? Thanks!

-- zigdon

Replies are listed 'Best First'.
Re: Class::DBI::AbstractSearch and SpeedyCGI
by shemp (Deacon) on Oct 04, 2005 at 00:06 UTC
    If you're not doing it already, trap errors with ALL queries. Set the RaiseError flag on your db handle, and then make all executes look like this, or with some other useful error handling:
    ... my $query_handle = $db_handle->prepare($query); eval { $query_handle->execute(); }; if ( $@ ) { confess "Query Error $DBI::errstr\nQuery: $query\n"; }
    And if you are using Bind Params, dump them too.
    I use the most powerful debugger available: print!
      I would, and did, when I execute my own queries... But with CDBI, it's all done so many layers lower than my code, that I'm not even sure where the eventual execute happens! I do believe RaiseError is set though, since the code does die when the error occures.

      -- zigdon

Re: Class::DBI::AbstractSearch and SpeedyCGI
by perrin (Chancellor) on Oct 04, 2005 at 01:25 UTC
    What makes you think that the bind variable is the problem? What's on that line in your Class/DBI.pm?
      The reason I thought it was the bind variable is that that's the only array I can see involved in this query - see the code below. The stack, afaik, is something like this:
      MyApp::Sets: my @res = Peeron2::Sets->search_where({ID => "8880-1"}); Class::DBI::AbstractSearch->search_where: return $class->retrieve_from_sql("( id = ? )", "8880-1"); # Class::DBI->retrieve_from_sql: $class->sth_to_objects($class->sql_Retrieve("id = ?")), ["8880-1"]); Class::DBI->sth_to_objects: $sth->execute("8880-1") unless $sth->{Active};
      Here's the relevant lines in the PMs. Class::DBI::AbstractSearch:
      sub search_where { my $class = shift; my $where = (ref $_[0]) ? $_[0] : { @_ }; my $attr = (ref $_[0]) ? $_[1] : undef; my $order = ($attr) ? delete($attr->{order_by}) : undef; # order is deprecated, but still backward compatible if ($attr && exists($attr->{order})) { $order = delete($attr->{order}); } $class->can('retrieve_from_sql') or do { require Carp; Carp::croak("$class should inherit from Class::DBI >= 0.90"); }; my $sql = SQL::Abstract->new(%$attr); my($phrase, @bind) = $sql->where($where, $order); $phrase =~ s/^\s*WHERE\s*//i; return $class->retrieve_from_sql($phrase, @bind); # <------------- +--31 }
      Class::DBI:
      sub retrieve_from_sql { my ($class, $sql, @vals) = @_; $sql =~ s/^\s*(WHERE)\s*//i; return $class->sth_to_objects($class->sql_Retrieve($sql), \@va +ls); } ... sub sth_to_objects { my ($class, $sth, $args) = @_; $class->_croak("sth_to_objects needs a statement handle") unle +ss $sth; unless (UNIVERSAL::isa($sth => "DBI::st")) { my $meth = "sql_$sth"; $sth = $class->$meth(); } my (%data, @rows); eval { $sth->execute(@$args) unless $sth->{Active}; # <------ +--------- 1124 $sth->bind_columns(\(@data{ @{ $sth->{NAME_lc} } })); push @rows, {%data} while $sth->fetch; }; return $class->_croak("$class can't $sth->{Statement}: $@", er +r => $@) if $@; return $class->_ids_to_objects(\@rows); }

      -- zigdon

        I just tried to add a
        warn Dumper $args;
        right before the ->execute line... When everything works, it returns what you'd expect ( [ "8880-1" ] ), but when it dies, it didn't output anything? This is just messing me up!

        -- zigdon

Re: Class::DBI::AbstractSearch and SpeedyCGI
by barrachois (Pilgrim) on Oct 04, 2005 at 11:51 UTC
    I've been seeing the same problem using Apache/HTML::Mason/mod_perl/Class::DBI/MySQL on Mac OS X, and have had similar trouble diagnosing exactly what the problem is. I'm not using Speedy, so this bug isn't specific to that choice. Nor am I using AbstractSearch.

    Since the current platform I'm working on isn't what I'm going to deploy on, I've been mostly ignoring this, hoping that moving to my deployment platform and upgrading Class:DBI etc would fix this.

    It feels like a bug in Class::DBI v0.96 to me. My experience is that it happens when there are multiple concurrent MySQL requests; I'm more likely to see the problem when the server gets busy. It's very frustating when Class::DBI just crashes rather than reporting an error or retrying. But as I say, I haven't tracked down the root cause.

    If you do figure out what the fix is, I'd be very interested to know.

    Update:
    Just installed most recent Class::DBI, which is 3.0.1. Now that I see how old the version I was I was using, I'll have to do some more testing and see what it looks like now.

    Yet another update.
    Now that I see my error again (3.0.1 didn't help), I'm not so sure it is the same problem as yours. Mine is reported from DBD::mysql :

    DBD::mysql::st fetchrow_array failed: fetch() without execute() [for Statement "SELECT active, name, directory, start\nFROM Course\nWHERE id=?\n"] at /Library/Perl/5.8.6/DBIx/ContextualFetch.pm line 87.\n
    I seem to remember something related along the lines of yours, but can't find it now.
      I've seen such errors only when the script lost it's handle to the DB... Since I have it verify at the beginning of processing that the handle is still valid, reloading always seems to solve that issue for me.

      -- zigdon

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2019-05-24 00:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you enjoy 3D movies?



    Results (147 votes). Check out past polls.

    Notices?
    • (Sep 10, 2018 at 18:53 UTC) Welcome new users!