CODEWITHSUNDEEP
perlsessionfirebirddbimod-perl
Yasuto S
Yasuto S

Reputation: 19

Solved: DBI cached statements gone and CGI::Session is stucked

I'm using Apache2.2(worker)/mod_perl 2.0.4/Apache::DBI/CGI::Session and Firebird RDBMS.

I also wrote CGI::Session::Driver::firebird.pm to work with Firebird RDBMS. DB connection is pooled by Apache::DBI and give connection handle to CGI::Session {Handle=>$dbh}.

Number of DB connection is equals to number of worker processes.

I posted Programming with Apache::DBI and firebird. Get Stucked httpd on exception 3 month ago. I found a reason of that issue, and want to know how to fix it.

$dbh = DBI->connect("dbi:Firebird:db=$DBSERVER:/home/cdbs/xxnet.fdb;
ib_charset=UTF8;ib_dialect=3",$DBUSER,$DBPASS,{
    AutoCommit=>1,
    LongReadLen=>8192,
    RaiseError=>1
});
my $session = new CGI::Session('dbi:firebird',$sessid,{Handle=>$dbh});
my $ses_p1 = $session->param('p1');

eval { $dbh->begin_work()

  my $sql = "SELECT * FROM SAMPLETABLE"
  my $st = $dbh->prepare($sql);
  $st->execute();
  while (my $R = $st->fetchrow_hashref()) {
   ...
  }
  $st->finish();
}; warn $@ if $@;
if ($@) {
  $dbh->rollback();
}else{
  $dbh->commit();
}
$session->flush();

When an sql error is occured, an eval block catches exception and rollback transaction. After that, CGI::Session does not retrieve session object no more.

Because prepare_cached statement failes at CGI::Session::DBI.pm. CGI::Session::DBI.pm use prepare_cached($sql,undef,3). '3' is safest way of using cached statement, but it never find broken statement at this situation.

How to fix this ? raise request to change CGI::Session::DBI.pm to use prepare() statement ? write store(),retrieve(),traverse() function in firebird.pm to use prepare() statement ?

It may other prepare_cached() going to fail after catch exception...

1) I add die statement on CGI::Session->errstr() I got an error "new(): failed: load(): couldn't retrieve data: retrieve(): $sth->execute failed with error message" 2) I flush session object after session->load() if $session is valid, changes are stored to DB. 3) I replace begin_work() to {AutoCommit}=0 results are same. I can use $dbh normally after catching exception and rollback, BUT new CGI::Session returns error. ------------------------------------------ added 2017/07/26 18:47 JST

Please give me your suggestion.

Thank you.

Upvotes: 1

Views: 450

Answers (1)

ameave
ameave

Reputation: 426

There are various things you could try before request changes to CGI::Session::Driver::DBI.pm ...

First, change the way new CGI::Session is called in order to diagnose if the problem happens when the session is created or loaded:

my $session = CGI::Session->new('dbi:firebird',$sessid,{Handle=>$dbh}) or die CGI::Session->errstr();

The methods param or delete stores changes to the session inside $session handle, not in DB. flush stores in DB the changes made inside the session handle. Use $session->flush() only after a session->param set/update or a session delete:

$session->param('p1','someParamValue');
$session->flush() or die 'Unable to update session storage!';

# OR
$session->delete();
$session->flush() or die 'Unable to update session storage!';

The method flush does not destroy $session handle (you still can call $session->param('p1') after the flush). In some cases mod_perl caches $session causing problems to the next attempt to load that same session. In those cases it needs to be destroyed when it's not needed anymore:

undef($session)

The last thing i can suggest is avoid using begin_work method, control the transaction behavior with AutoCommit instead (because the DBD::Firebird documentation says that's the way transactions should be controlled) and commit inside the eval block:

eval {
    # Setting AutoCommit to 0 enables transaction behavior
    $dbh->{AutoCommit} = 0;

    my $sql = "SELECT * FROM SAMPLETABLE"
    my $st = $dbh->prepare($sql);
    $st->execute();

    while (my $R = $st->fetchrow_hashref()) {
        ...
        }

    $st->finish();
    $dbh->commit();
    };
if ($@) {
     warn "Tansaction aborted! $@";
     $dbh->rollback();
     }

# Remember to set AutoCommit to 1 after the eval
$dbh->{AutoCommit} = 1;

You said you wrote your own session driver for Firebird... You should see how the CGI/Driver/sqlite.pm or CGI/Driver/mysql.pm are made, maybe you need to write some fetching method you are missing...

Hope this helps!!

Upvotes: 2

Related Questions