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

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.


when small people start casting long shadows, it is time to go to bed

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

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.



when small people start casting long shadows, it is time to go to bed

use LWP::UserAgent;

my $BBOX = "-96.09299316406248+38.96034339396338%2C+-83.39279785156248
++38.96034339396338%2C+-83.39279785156248+44.33256692562997%2C+-96.092
+99316406248+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;
}
[download]

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?

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

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