Mysql handle not in asynchronous mode

[punkish]

I asked this question on the dbi list (no response yet), so am posting it here for collective wisdom of the monks.

I have three MySQL/Perl Dancer http://perldancer.org powered web apps.

The user goes to app A which serves up a Google maps base layer. On document ready, app A makes three jQuery ajax calls -- two to app B like so

        http://app_B/points.json
        http://app_B/polys.json

and one to app C

        http://app_C/polys.json

Apps B and C query the MySQL database via DBI, and serve up json packets of points and polys that are rendered in the user's browser.

All three apps are proxies through Apache to Perl Starman running via plackup started like so

     $ plackup -E production -s Starman -w 10 -p <port> path/to/respec
+tive/app.pl

From time to time, I start getting errors back from the apps called via Ajax. The initial symptoms were

        {"error":"Warning caught during route execution: DBD::mysql::s
+t 
        fetchall_arrayref failed: fetch() without execute() at <path/t
+o/app.pm> line 
        79.\n"}

The offending lines are

        71> my $sql = qq{
        72>     ..
        73>
        74>
        75> };
        76> 
        77> my $sth = $dbh->prepare($sql);
        78> $sth->execute();
        79> my $res = $sth->fetchall_arrayref({});

This is bizarre... how can execute() not take place above? Perl doesn't have a habit of jumping over lines, does it? So, I turned on DBI_TRACE

        $DBI_TRACE=2=logs/dbi.log plackup -E production -p 5001 -s Sta
+rman -w 
10 -a bin/app.pl

And, following is what stood out to me as the potential culprit in the log file

        > Handle is not in asynchronous mode error 2000 recorded: Hand
+le is 
        > not in asynchronous mode
        >    !! ERROR: 2000 CLEARED by call to fetch method

What is going on? Basically, as is, app A is non-functional because the other apps don't return data "reliably" -- I put that in quotes because they do work correctly occasionally, so I know I don't have any logic or syntax errors in my code. I have some kind of intrinsic plumbing errors.

quick update: I did find the following http://search.cpan.org/~capttofu/DBD-mysql-4.019/lib/DBD/mysql.pm#ASYNCHRONOUS_QUERIES and am wondering if this is the cause and the solution of my problem. Would a DBI/mysql savvy monk kindly elucidate?

[sundialsvc4]

Another possible strategy would be to wrap those low-level DBI calls in logic that specifically catches error #2000, and responds by sleeping for a short-but-random period of time and trying the request again a certain number of times before giving up.   This becomes a targeted response to a specific error condition, applied only when DBI informs you that the error in question just did occur.

  sub doit {
    my $dbh = shift;

    # IF ASYNC ERROR OCCURS, RETRY THIS MANY TIMES
    for my $retry (1..10) {   
     
      $dbh->execute();

      # LEAVE NOW IF THE ERROR (IF ANY) IS OTHER THAN "ASYNC"
      return $dbh->err unless $dbh->err == 2000;  

      # ASYNC ERROR ... TAKE A RANDOM SHORT NAP
      sleep(100*rand());      
    }

    # OH DEAR ...
    die "asynchronous I/O error";
  }

Something like that ...

[punkish]

Thanks. I will implement your suggested workaround for now, but I would really like to understand why this problem is happening, and how I can avoid it in the first place.

[Anonymous Monk]

and am wondering if this is the cause and the solution of my problem. Would a DBI/mysql savvy monk kindly elucidate?

That would seem to be the cause of your problems -- turning on this async feature, means you have to wait after execute before you can fetch

So the solution would be to comb your code (or whatever abstraction you're using), to make sure async is turned off

Or add the loop as per async docs, to wait before fetching

[punkish]

I am not using any abstraction... just pure DBI. And, I have done nothing to turn on the async feature. In fact, now your reply has confused me even more, as you seem to imply that I might have that feature on, and that I have to try and turn it off. I thought it was just the other way around.

I am making Ajax calls. It could be that Dancer, which tends to use a lot of global cars, is causing this problem. But, these calls are to separate Starman instances, albeit on the same computer, and they are making separate calls the the db, albeit, the same db.

addendum: the above note is confusing, so I would appreciate some explanation. Wouldn't it be normal to first execute and then fetch? After all, that is how my code is written. As I said above in my OP, Perl doesn't just jump over lines of code. So, the only conclusion I can derive is that one call to the db is made, and before the db can respond to it, another call comes in. The db gets confused, and tries to fetch (from the first call) on the second call that hasn't executed yet. This would imply that there is namespace pollution going on... vars from different web calls are clobbering each other.

[punkish]

Strange... a simple script like so works like a champ... no async nonsense, no errors. All 1000 requests are effortlessly returned by the same set up.

use LWP::UserAgent;

my $BBOX = "-96.09299316406248+38.96034339396338%2C+-83.39279785156248
++38.96034339396338%2C+-83.39279785156248+44.33256692562997%2C+-96.092
[phone]+44.33256692562997%2C+-96.09299316406248+38.96034339396338
+";

for (1 .. 1000) {
    open my $fh, ">>", 'foo.txt';
    
    my $ua = LWP::UserAgent->new;
    $ua->agent("MyApp/0.1 ");
    
    my $req = HTTP::Request->new(GET => "http://localhost:5001/points.
+json?callback=foo&BBOX=$BBOX");
    my $res = $ua->request($req);
    
    # Check the outcome of the response
    if ($res->is_success) {
        say $fh $res->content;
    }
    else {
        say $fh $res->status_line;
    }
    
    close $fh;
}

So, it seems that both MySQL and Starman are very capable of handling this. THe only element missing here is Dancer, and the browser. Thoughts anyone?

[Anonymous Monk]

Did you got any solution to this? I am also getting same error.

[ff]

I was having issues with mysql. I was saving individual hashrefs into an array and my script wasn't providing any results. I started running via:

 $ DBI_TRACE=2 perl get_data.pl

and saw messages such as:

Handle is not in asynchronous mode error 2000 recorded: Handle is not in asynchronous mode

A post in this thread: https://www.nntp.perl.org/group/perl.dbi.dev/2015/07/msg7950.html suggested increasing the trace level to 4 for more insight. (It also seemed to think DBD::mysql was buggy.)

I tried DBI_TRACE with 4 and didn't see anything new. However, changing to using fetchrow_arrayref started showing data again. :-)

[tultalk]

My code does same but seems to clear itself and fetch the data. level 3 trace

-> dbd_st_FETCH_attrib for 1f5e150, key NAME
       ERROR: 2000 'Handle is not in asynchronous mode' (err#0)
1   <- FETCH= [ 'id' 'user_id' 'username' 'password' 'pin' 'position' 
+'forename' 'lastname' 'business' 'address1' 'address2' 'city' 'state'
+ 'zip' 'email' 'phone_home' 'phone_cell' 'comments' 'MJ' 'MD' 'DD' 'D
+P' ] at mysql.pm line 869 via  at update_tables.cgi line 412
    !! The ERROR '2000' was CLEARED by call to fetch method
    -> dbd_st_fetch
        dbd_st_fetch for 1da3a00, chopblanks 0
    dbd_st_fetch result set details
    imp_sth->result=1f5f780
    mysql_num_fields=22
    mysql_num_rows=1
    mysql_affected_rows=1
    dbd_st_fetch for 1da3a00, currow= 1