Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

puzzling problem with access to DB when using mod_perl

by ted.byers (Scribe)
on Dec 28, 2012 at 04:25 UTC ( #1010610=perlquestion: print w/ replies, xml ) Need Help??
ted.byers has asked for the wisdom of the Perl Monks concerning the following question:

I followed one set of instructions for writing a handler for use with mod_perl. Here is what I added to the end of apache2.conf (on Ubuntu 12.04):

PerlRequire /home/ted/modperlhandler/Hello.pm <Location /time> SetHandler perl-script PerlResponseHandler Hello </Location>

And here is the handler package code:

package Hello use strict; use Apache2::RequestRec (); use Apache2::RequestIO (); use Apache2::Request; use DBI; use Apache2::Const -compile => qw(OK); sub handler { my $db = 'nutrientsdb'; my $hostname = '192.168.2.24'; my $user = 'rejbyers'; my $dbpwd = 'xxxxxxxxxxx'; my $dbh = DBI->connect("DBI:mysql:database=$db;host=$hostname",$user +,$dbpwd,{RaiseError => 1}) or die "Failed to connect to the DB.\n"; my $r = shift; my $req = Apache2::Request->new($r); my $table = $req->param; my %p = %$table; my $name = $p{'name'}; if ((defined $name) && (length($name) > 0)) { print "Hello $p{'name'}, the time is " . localtime() . "\n\n\n"; foreach my $k (keys %p) { print "$k <=> $p{$k}\n"; } } else { print "Hello World, the time is " . localtime() . "\n"; } return Apache2::Const::OK; } 1;

Obviously, the example I followed did not involve the use of DBI. If the DBI code is commented out, the Hello handler works as the example implied (though I successfully extended the example to include getting the request parameters).

I know the above example does not (yet) do anything with $dbh. I first wanted to see if I could successfully connect to the DB before running any queries. Alas, I could not take this further because Apache gives me a 500 error. On examining the logs, I see the following entry (after a timestamp)): "DBI connect ('database==192.168.2.24','rejbyers',...) failed: Access denied for user 'rejbyers'@'localhost' (using password: YES) at /home/ted/modperlhandler/Hello.pm line 22."

Now, to verify that the database server is in fact reachable, I wrote the following CGI script and placed it in /usr/lib/cgi-bin (on Ubuntu):

#!perl use CGI; use CGI::Carp qw(fatalsToBrowser); use DBI; use CGI::Carp::DebugScreen ( engine => 'TT', debug => 1, lines => 5, modules => 1, environment => 1, raw_error => 1, overload => 1, ); my $query = new CGI; print $query->header; my $db = 'nutrientsdb'; my $hostname = '192.168.2.24'; my $user = 'rejbyers'; my $dbpwd = 'xxxxxxxxxxx'; my $dbh = DBI->connect("DBI:mysql:database=$db;host=$hostname",$user,$ +dbpwd,{RaiseError => 1}) or die "Failed to connect to the DB.\n"; my $title = 'My CGI Script'; &print_html_header($title); &print_body; &print_end; exit(0); sub print_html_header { print $query->start_html(shift); } sub print_end { print $query->end_html; } sub print_body { my $sql = 'SELECT * FROM FD_GROUP'; my $sth = $dbh->prepare($sql); $sth->execute; print '<table border>'; while (my $aref = $sth->fetchrow_arrayref) { my ($code,$desc) = @$aref; print "<tr><td>$code</td><td>$desc</td></tr>"; } print '</table>'; $sth->finish; }

This CGI script worked as expected. Hence, the puzzle. On the same Ubuntu (virtual) machine, my mod_perl handler fails to connect to the database server, but my cgi script, using the same credentials, does connect to the database server. Why?

As an aside, I have, in addition to mod_perl, fastcgi enabled, and Apache::DBI is installed (and, unless I misunderstood the configuration instructions, apache's httpd server is configured to use it). My worry is that all the instructional materials for using Apache::DBI and fastcgi that I have found are quite old. While I am still studying effective use of mod-perl, do fastcgi and Apache::DBI remain good options? If so, how do they fit with what I have been doing with CGI and what I am trying to learn for using mod_perl?

Thanks

Ted

Comment on puzzling problem with access to DB when using mod_perl
Select or Download Code
Re: puzzling problem with access to DB when using mod_perl
by Anonymous Monk on Dec 28, 2012 at 04:34 UTC

    Why?

    Ask DBI :) If your error messages aren't detailed enough, increase verbosity (logging level, trace level , debug level ... ) DBI->trace(3)

      Thanks. Now the problem is solved.

      Do you know what trace level will tell me whether or not Apache::DBI was involved in a given request for a connection to the DB?

      Thanks again

      Ted

Re: puzzling problem with access to DB when using mod_perl
by Anonymous Monk on Dec 28, 2012 at 04:52 UTC

      Thanks

      No, I haven't heard of PSGI/Plack, before you mentioned it. It sounds intriguing, so it is tacked onto a rather long list of things I ought to investigate.

      I wasn't asking why others lstudy these things. Rather, I was asking how these options relate to each other, if at all. For example, I have read that I need not change my use of DBI in order to use Apache::DBI, as DBI will use it automagically if mod_perl and Apache::DBI are both installed (I guess, with what I read since my question, it reduces to a question of whether or not I'll see a payoff by trying to use fastcgi too). But, how, for example, do fastcgi and mod_perl relate? Are they competitors? Complementary? I have been using CGI for a while, and want to improve the responsiveness of my scripts. For that reason, in addition to studying mod_perl, I am also investigating use of JavaScript+jQuery+ajax (and I am thinking possible using mod_perl handlers to return XML to my JavaScript AJAX event handlers).

      I have now read some of the references you cite, and am working on the others? :-)

      Thanks again

      Ted

        You could say they are competitors.

        mod_perl is a way to embed Perl code into Apache. It also opens up some of Apache's internals for Perl. FastCGI uses a persistent process to handle CGI requests. Both reduce the overhead in running a dynamic script, but mod_perl encompasses much more than FastCGI.

        I have played around with mod_perl a bit and think it is too heavy-weight for what people usually call "web programming". I would say it was made for doing pre-request stuff (things like rate limiting, filtering, request rewriting, and whatnot), but it's not the best way to do the things you expect for "plain" web programming. Using it that way also makes your Apache instance much fatter if you ever use much memory in any single one of your scripts.

        Of course, FastCGI is not much better in the memory use case, but since it is a separate process, its restarting is easier (and usually automatic). Another bonus point for it is that it is not dependent on Apache, but can run on pretty much all the common web servers.

        If you are looking for an easy way of boosting the speed of some existing CGI scripts, try mod_perl -- it has a pre-written handler for those kinds of scripts. Otherwise, the current fad is PSGI/Plack and some sort of backend such as FastCGI to go with it.

Re: puzzling problem with access to DB when using mod_perl
by Anonymous Monk on Dec 28, 2012 at 14:01 UTC
    Usually one uses a DB connection-handle pooler: it keeps persistent handles for a short while for re-use, and re-establishes connections on demand. Much more efficient than doing it per-request. Look in CPAN.

      Thanks.

      Yes, I am aware of this.

      Now, my question regarding this, becomes, which of the available options would you recommend? In addition to Apache::DBI, I have seen Class::DBI, Rose::DB::*, and DBIx::Class. And I am sure there are others that I haven't seen. According to what I have read so far, Rose::DB::* will use Apache::DBI if it is available (i.e.installed and loaded), but the others as far as I can tell would be competitors that do the pooling and more. But it is my understanding that each of these provide connection pool handling, and are thus competitors that do more. Are you aware of anyone who has done an objective comparison of the available options, looking at feature sets, relative performance, code maturity, &c.? It would be useful for guys like me to see such a review, in order to focus on studying those packages that will provide the biggest benefit for the time spent. NB: This isn't a question of which is better, but, rather, which provides the best fit to what a given developer is looking for (much as I would choose C++ for some applications and Perl for others, and Java for still others, depending on the problem domain).

      Thanks

      Ted

        Just a quick update.

        Based on my research, including a post by the chap responsible for some of these packages, it would appear that Class::DBI and DBIx::Class are obsolete, and that if one is to use a Perl ORM , one should use Rose::DBI::*. That said, if the objective is primarily speed, the benchmarking page I found for Rose::DB::* would suggest that though it is much faster than any other ORM tested, one is still better off using DBI itself.

        I do not know if the benchmark tests used are inadequate to show the benefits of using an ORM, or if ORM systems are just a convenience for developers that may or may not justify the poorer performance (the cost of development is, after all, a major consideration alongside the speed of an application, when deciding what to do to support the functional requirements defined for the application). I will thus study Rose::DB::*, and reserve judgement as to whether or not it's use is warranted at least in some circumstances. (Maybe someone with experience with it, or a similar ORM, would like to contribute some insight based on observations when using it in real world applications?)

        Cheers

        Ted

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (7)
As of 2014-09-02 03:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (18 votes), past polls