Apache::DBI and CGI::Application with lots of modules.

[Eric Frazier <ef...@kwinternet.com>]

Hi,

I am glad to see the list traffic has been picking up lately. It makes me
have higher hope about posting this. 

First some background info.

I have a fairly large CGI::Application module about 30 run modes that pretty
much follows the example mailform module. I am also using HTML::Template
within the module. I am running on, FreeBSD 4.6 1G mem mysql 4.02 with
Innodb tables.

A typical run mode looks like this.

sub doug_holds {

my $self = shift;
my $q = $self->query();
my $holdtype = $q->param('holdstate');

my $holdsearch = new holds();
$holdsearch->HoldType($holdtype); # set hold type for the query

my $header = parse_header($self);
return $header . $holdsearch->getAllHolds();    


}


Of course many of other subs look like this 

sub customer_name_search {

my $self = shift;

my $index_page = $self->param('CUSTOMER_NAME_SEARCH_TMPL');
        
        my $output='';
        
        my $tmpl_obj = $self->load_tmpl($index_page, 
                 die_on_bad_params => 0,
                 cache => 1,
                 stack_debug =>$debug
            ) or confess("could not create template");
          $tmpl_obj->param(base => $self->param('base'));
          $tmpl_obj->param(RUNMODE => 'customer_display');      
          $tmpl_obj->param(USER  =>  $selected_user);
          my $header = parse_header($self);
          
          
          return $header . $tmpl_obj->output;
         
}

But that isn't relavent to my problem. 


In the first sub, I create a new holds instance. Each of these modules like
holds work like this 

package Holds;

use strict;
use Carp;
use warnings;
use QueryPrint;
use vars qw($dbh $processed_hnd $status_hnd);
use gentimeid; # generate time id based


sub new {         
            my $invocant = shift;
            my $class = ref($invocant) || $invocant;
            my $self  = { @_ };
            bless ($self, $class);
            $dbh = db_connect();         
            #die "$self->{OrdNum}, $self->{HoldReason}";
            return $self;            
}


sub OrdNum {
          
          my $self = shift;
          if (@_) { $self->{OrdNum} = shift }
          return $self->{OrdNum};
        }

sub GetProcessed {

my $self = shift;

        #### This has a bug, somtimes the cached query doesn't stick around.
        
        $processed_hnd->execute($self->{OrdNum}) or confess ("can't execute
processed");
        
        my ($isprocessed) = $processed_hnd->fetchrow_array;
        $processed_hnd->finish();
        
        if ($isprocessed){      
                $self->{ProcessStatus} = 1;     
                return "#4EEE94";
        }else{
                $self->{ProcessStatus} = 0;     
                return "FFFFFF";
        }

}
   

......



sub db_connect {

require DBI;

my $dbname = 'CS';
my ($dbuser, $dbpasswd) = ('myuser', 'mypass');

my $dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpasswd)
   or die "can't connect: $DBI::errstr\n";
   
   # we need these waiting for queries, so we are going to prepare them ahead of
 time, and yes
   # horror of horror they will be global. Sorry Mom I tried :( 
   $processed_hnd = $dbh->prepare_cached("select ord_tpak_processed from orders
where ord_num=?") or confess("can't get tpak processed");
   $status_hnd = $dbh->prepare_cached("select is_hold_set,holdstate from
holds where ord_num=?") or confess("can't get hold status");
   #DBI->trace(2,"/usr/local/apache/htdocs/out.log");
   return $dbh;     

}


Most of the modules just have simple subs called db_connect that don't have
prepared statments sitting like this. I did this because I have to check the
status of a LOT of rows and return the display fast. This seemed to work
well at the time. It was defiantly faster that preparing the statement over
and over. 



I am running under mod perl 1.x Apache 1.3x, and loading my CGI::App module
and other modules from a start.pl
I am using Apache::DBI and connect_on_init. So I have these problems, they
all seem to be related, but how?? 

1. Connections are getting lost. I get errors in the log about fetch without
an execute which indicate this. Either the user sees an internal server
error, or else I believe DBI will try to reconnect and the query will then
succeed. But that slows things down when it happens. All I have to do to
these kinds of errors is reload a page very quickly. click, click, click fast.. 

2. Every once in a while I get an out of memory error. 

3. My main search result page is getting cached, the closure type of
problem. ***Sometimes*** All I have read says that because I am using oop
modules and use strict along with use vars that should not happen. I have
not gotten any "this variable will not stay shared" types of warnings.
for this I have tried specificly undefing the display scalars, the result
sets etc. I just can't seem to find out what var is causing the problem, and
I can't find any examples of closures. 
4. I know the way I have done these db connects is sloppy. But I can't seem
to find a better way. Could I make one db_connect sub,and inherite it all
though my modules? 

5. I am also using Innodb tables and it seems I am having problems with some
commits happening. I don't get a error and I am checking to see if they
succeed, but the commit doesn't happen unless I go with AUTOCOMMIT=1 which I
don't want to do long term. 

All of this makes me think, hmm it all sounds pretty fishy, like fix one
thing and I may fix it all. And all of those modules with all of those
db_connect methods are the first thing  I am afraid of. I had to make these
work under CGI as well, so I wanted each module to be totaly independent.
That is not so important now as far as the DB connection goes. 


I am posting this to mod_perl list as well. So why post to the CGI::App
list? Well I think there is a good chance that a CGI::App person will know
what is happening, just because of having a knowledge of CGI::App as used
with mod_perl. But then it is most likely a mod perl type of problem.


I am kind of desparate, so if anyone is looking for cash, rewards, or other
forms of kudos, please get in touch with me. I really need to solve this
problem. 


Thanks,


Eric 


PS in the process of writing this email I found one stupid thing I did. 

$processed_hnd->finish();

I don't know what I was thinking there :) since I want to keep this hnd
open. But I doubt very much that is the hole problem.. 






http://www.kwinternet.com/eric
(250) 655 - 9513 (PST Time Zone)

"Inquiry is fatal to certainty." -- Will Durant 

Re: Apache::DBI and CGI::Application with lots of modules.

[Perrin Harkins <pe...@elem.com>]

I'm just going to point out a few problems.  These are not all related 
to your questions.

>package Holds;
>

The case of "Holds" doesn't match the example sub you posted above.  I'm 
assuming that was a typo.

>use strict;
>use Carp;
>use warnings;
>use QueryPrint;
>use vars qw($dbh $processed_hnd $status_hnd);
>use gentimeid; # generate time id based
>
>
>sub new {         
>            my $invocant = shift;
>            my $class = ref($invocant) || $invocant;
>

That looks like voodoo code copied from a man page.  If you call this as 
Holds->new(), you don't need that junk about ref.  (And most people 
recommend against the "new Holds" syntax.)

>            my $self  = { @_ };
>            bless ($self, $class);
>            $dbh = db_connect();
>

You don't seem to need this.  You aren't using the database handle for 
anything in this sub and you aren't gaining anything by calling it here.

>sub GetProcessed {
>
>my $self = shift;
>
>        #### This has a bug, somtimes the cached query doesn't stick around.
>

If you lose your database connection, Apache::DBI will reconnect.  Any 
prepared queries will be lost.  You *must* prepare every time, but see 
below...

>sub db_connect {
>
>require DBI;
>

You don't need that.  You should have already loaded it in startup.pl.

>my $dbname = 'CS';
>my ($dbuser, $dbpasswd) = ('myuser', 'mypass');
>

Probably should be in a config file, rather than buried in here.

>my $dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpasswd)
>   or die "can't connect: $DBI::errstr\n";
>   
>   # we need these waiting for queries, so we are going to prepare them ahead of
> time, and yes
>   # horror of horror they will be global. Sorry Mom I tried :( 
>   $processed_hnd = $dbh->prepare_cached("select ord_tpak_processed from orders
>where ord_num=?") or confess("can't get tpak processed");
>   $status_hnd = $dbh->prepare_cached("select is_hold_set,holdstate from
>holds where ord_num=?") or confess("can't get hold status");
>   #DBI->trace(2,"/usr/local/apache/htdocs/out.log");
>   return $dbh;
>

Don't put those in globals.  The prepare_cached call already stores them 
for the life of your database connection.  Apache::DBI will keep that 
connection alive (in a global hash) as long as it can and reconnect if 
the connection is lost.  If the connection does get lost, the statement 
handles in these globals will stop working.  You do recreate them every 
time since you call this sub every time, but you could lose the 
connection between the time this sub is called and the time you use 
these handles.

> 2. Every once in a while I get an out of memory error.
>

You can control process growth over time in a number of ways.  They are 
documented in the mod_perl guide.

>3. My main search result page is getting cached, the closure type of
>problem.
>

Are you using any modules that have subs with sub ref prototypes, like 
Error.pm?  That can do it.

>All I have read says that because I am using oop
>modules and use strict along with use vars that should not happen.
>

It's actually really easy to create closures.  Here is a closure:

my $holdtype = $q->param('holdstate');
display_holdtype();

sub display_holdtype {
    print "holdtype: $holdtype in process $$\n";
}

This will always print whatever the value was the first time, no matter 
what you change it to later.  (The first time for that process, that 
is.)  Watch out for things like that.  You should always pass params 
explicitly.

>4. I know the way I have done these db connects is sloppy. But I can't seem
>to find a better way. Could I make one db_connect sub,and inherite it all
>though my modules? 
>

Make one sub that returns a database handle and use it from everywhere. 
 Doesn't need to be inherited, you can just stick it in a module that 
all the other modules call.

Hope some of that was helpful,
Perrin