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
