Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Mysql handle not in asynchronous mode

by punkish (Priest)
on Jul 16, 2011 at 02:58 UTC ( #914782=perlquestion: print w/ replies, xml ) Need Help??
punkish has asked for the wisdom of the Perl Monks concerning the following question:

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?



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

Comment on Mysql handle not in asynchronous mode
Select or Download Code
Re: Mysql handle not in asynchronous mode
by Anonymous Monk on Jul 16, 2011 at 06:39 UTC

    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
Re: Mysql handle not in asynchronous mode
by sundialsvc4 (Monsignor) on Jul 16, 2011 at 11:34 UTC

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

      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
Re: Mysql handle not in asynchronous mode
by punkish (Priest) on Jul 17, 2011 at 01:02 UTC
    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 +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; }

    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?



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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (15)
As of 2014-07-24 17:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (163 votes), past polls