Ajax - asynchronous queries with MySQLPerl DBI

Questions

It is possible that my question title is misleading, but here goes --

http://perldancer.org http://perldancer.org

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 proxied through Apache to Perl Starman running via plackup started like so

    $ plackup -E production -s Starman -w 10 -p 5000 path/to/app_A/app.pl
    $ plackup -E production -s Starman -w 10 -p 5001 path/to/app_B/app.pl
    $ plackup -E production -s Starman -w 10 -p 5002 path/to/app_C/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::st fetchall_arrayref 
     failed: fetch() without execute() at 
     <path/to/app_B/app.pm> line 79.
"}

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 Starman -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: Handle 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.

http://search.cpan.org/~capttofu/DBD-mysql4.019/lib/DBD/mysql.pm#ASYNCHRONOUS_QUERIES http://search.cpan.org/~capttofu/DBD-mysql4.019/lib/DBD/mysql.pm#ASYNCHRONOUS_QUERIES

I would love to learn what is going on here, and what is the right way to solve this.

Answers

How are you managing your database handles? If you are opening a connection before starman forks your code then multiple children may be trying to share one database handle and are confusing MySQL. You can solve this problem by always running a DBI->connect in your methods that talk to the database, but that can be inefficient. Many people switch over to some sort of connection pool, but I have no direct experience with any of them.

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/6721057/asynchronous-queries-with-mysql-perl-dbi

Related