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

Sometimes the following code is giving a "fetchrow_hashref failed: fetch() without execute()" error. How is it possible, given the execute()'s position within the if()? Thanks for any advice. 
                my $sth = $db->{ 'dbh' }->prepare( $sql );
                if ( $sth->execute() ) {
                        my @rows = ();
                        while ( my $ref = $sth->fetchrow_hashref() ) {
                                push @rows, $ref;
                        }
                        return @rows;
                }

[download]
UPDATE: The problem was found - that $sth->execute() returns -1 on a deadlock, which is boolean true and so the fetchrow_hashref() runs. We need to check for a return code less than zero.

Replies are listed 'Best First'.
Re: fetchrow_hashref failed problem
by mje (Curate) on Jul 21, 2015 at 07:57 UTC

    What you described doesn't make sense to me unless DBD::mysql or DBI is broken as execute is supposed to return undef on an error. What do you get if you change the if statement to assign the execute return to a variable and then dump it when fetchrow_hashref fails?

    Have you tried enabling DBI tracing to see what is going on?

[reply]
Re: fetchrow_hashref failed problem
by 1nickt (Canon) on Jul 21, 2015 at 01:17 UTC

    what does the sql say? What is the return value of the execute call?

    The way forward always starts with a minimal test.
[reply]
[d/l]
      We get: 
      DBD::mysql::st execute failed: Deadlock found when trying to get lock;
+ try restarting transaction at /path/to/Database.pm line 313.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /
+path/to/Database.pm line 315.

      [download]
      Now the deadlock is not unexpected - we just want to deal with it gracefully and not get the fetchrow_hashref error.
[reply]
[d/l]

        are you sure they are from the same call? Assuming you are looping through something, could this be relevant from the docs? 

        If execute() is called on a statement handle that's still active ($sth
+->{Active} is true) then it should effectively call finish() to tidy 
+up the previous execution results before starting this new execution.

        [download]
        The way forward always starts with a minimal test.
[reply]
[d/l]
        Is RaiseError set? If you get the deadlock error, you should not fetch again, you should start the whole transaction over again.
[reply]
Re: fetchrow_hashref failed problem
by vinoth.ree (Monsignor) on Jul 21, 2015 at 05:46 UTC

    If you are using InnoDB or any row-level transactional RDBMS, then it is possible that any write transaction can cause a deadlock, even in perfectly normal situations. Larger tables, larger writes, and long transaction blocks will often increase the likelihood of deadlocks occurring.

    The only way to truly handle deadlocks is to write your code to expect them. This generally is not very difficult if your database code is well written. Often you can just put a try/catch around the query execution logic and look for a deadlock when errors occur. If you catch one, the normal thing to do is just attempt to execute the failed query again.

    Read How to Cope with Deadlocks

    Source from Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction"

    All is well. I learn by answering your questions...
[reply]
      Thank you, but the deadlock is not the problem. It's the fetchrow_hashref error following the execute.
[reply]

Back to Seekers of Perl Wisdom