Solved

Apache::DBI Wont Reconnet?

Posted on 2004-08-11
6
270 Views
Last Modified: 2006-11-17
For some reason whenever I restart my Database process the web server loses it's connection and doesn't reconnect after the database comes back up.

This wasn't a problem when I had the database and web server on the same machine. I had a script that restart the web server whenever the databse restarted. However now the web server and database server are on diffrent boxes.

Bellow is the Apache Startup script, The connect module I use, and the connection string I use in my perl scripts.

Any suggestion on why Apache::DBI won't reconnect ?

David Hofmann

--- My Apache Startup Script ---

# make sure we are in a sane environment.
$ENV{MOD_PERL} or die "not running under mod_perl!";

use strict;
use Carp ();
use Apache::DBI ();
DBI->install_driver("Pg");
# $Apache::DBI::DEBUG = 0;
Apache::DBI->connect_on_init("DBI:Pg:dbname=SERVERNAME;host=HOSTNAME;port=PORT;","USERNAME", "PASSWORD")
      or die "Cannot connect to database: $DBI::errstr";
1; #return true value

-- End Starup --

-- My Connect Module --

package MY::DBConnect;
 
use strict;
use Carp;
if ($ENV{MOD_PERL}) {
      use Apache::DBI;
}
use DBI ();
use MY::Error;

sub connect {
      if (defined $MY::DBConnect::conn) {
            eval {
                  $MY::DBConnect::conn->ping;
            };
            if (!$@) {
                  return $MY::DBConnect::conn;
            }
      }
 
      ### Perform the connection using the driver
       $MY::DBConnect::conn = DBI->connect( "dbi:Pg:dbname=SEVERNAME;host=HOSTNAME-;port=PORT","USERNAME", "PASSWORD")
            or &error('0','EN','CONNECT_SQL','CONNECT_SQL','The Support site is currently experiencing technical difficulties. Please try again later.  We apologize for any inconvenience.',1 );
      return $MY::DBConnect::conn;
}
 
1;

-- End Connect Module --

-- Connection sting in scripts --

use DBI ();
use MY::DBConnect;

### Perform the connection using the driver
my $dbh ||= &MY::DBConnect::connect;

-- End Scripts stuff --
0
Comment
Question by:elmic
  • 2
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
kandura earned 500 total points
ID: 11779576
Hi elmic,

Why do you need the whole MY::DBConnect wrapper, when Apache::DBI handles this for you transparently? Your connect method should simply do the DBI->connect, and let Apache::DBI handle the caching and connection checking.

That being said, it looks from the source of DBD::Pg that the ping() method only returns true or false. It doesn't throw an exception, so your eval{} will never set $@. This means that your connection is never reinstated after a database restart.
I expect that this will work for you:

    if (defined $MY::DBConnect::conn and $MY::DBConnect::conn->ping) {
        return $MY::DBConnect::conn;
    }

HTH,
Kandura
0
 
LVL 1

Author Comment

by:elmic
ID: 11841633
The reason for the wrapper is so I can change the connection string in one spot and it effect all of my programs. Otherwise when we change the password now and then I have to change it in about 100 programs that run on the server.

I'll give code a try and post back later today. Sorry for the delay, I'm down in florida and the hurican caused things to be fun for a bit.
0
 
LVL 18

Expert Comment

by:kandura
ID: 11846686
elmic,
> The reason for the wrapper is so I can change the connection string in
> one spot and it effect all of my programs. Otherwise when we change the
> password now and then I have to change it in about 100 programs that run
> on the server.

That makes perfect sense. Obviously I do something similar, only I let Apache::DBI worry about reconnecting, that's why I was a bit off track.

> I'll give code a try and post back later today. Sorry for the delay, I'm
> down in florida and the hurican caused things to be fun for a bit.

I saw that on the news here in Holland, and it looked devastating. Hope you're still in one piece!
0
 
LVL 1

Author Comment

by:elmic
ID: 12095303
Sorry, Sill dealing with storms and stuff so I haven't been checking back here like a should.  

kandura is the closest and I'm glad he got the points. What I have found in the mean time is that the Perl Modules I've writen is causing the failures. I'm not sure why. I also made change to my Connect module:

--
package MY::DBConnect;
use Carp;
use strict;
use Apache::DBI;
use DBI ();
use MY::ErrorPage;

sub connect {
      my $ErrorSystem = MY::ErrorPage->new();
      my $i = 0;
      while ($i <= 10) {
            if ($MY::DBConnect::conn) {
                  if ($MY::DBConnect::conn->ping) {
                           $MY::DBConnect::conn->{PrintError} = 1; # warn() on errors
                           $MY::DBConnect::conn->{RaiseError} = 0; # don't die on error
                           $MY::DBConnect::conn->{AutoCommit} = 0; # commit executes immediately
                        return $MY::DBConnect::conn;
                  } else {
                        warn("Ping Failed: Loop $i for ID $$ Error: $DBI::errstr")
                  }
            }
            $i++;
            ### Perform the connection using the driver
             $MY::DBConnect::conn = DBI->connect("dbi:Pg:dbname=DATABASENAME;host=HOSTIP;port=5432;","USERNAME", "PASSWORD")
                   or warn("Unable to Connect - Loop $i for ID $$ Error: $DBI::errstr");
      }
      $ErrorSystem->PrintError(dbierror => $DBI::errstr, error => 'CONNECT FAILED', errorloc => 'Connect Module Admin');
}
--

For inside my programs I found the command I was using to call the connect module fails now and then. I fixed this by doing this instead:

my $dbh = &MY::DBConnect::connect;

The pipe screw things up cause $dbh is defined, it's just lost it's connection and for some reason Apache::DBI won't always  reconnect.

As I said the perl modules I found are messing things up main. For some reason when they call connect Apache::DBI always fails to reconnect.

The fix I found for this is using:

---
unless ($MY::DBConnect::conn) {
      $ErrorSystem->PrintError(error => 'Connect String', errorloc => 'Common Module');
}
$self->{'dbh'} = $MY::DBConnect::conn;
---

The thing I found odd about this was if I call connect inside a module before I call it in the main program it fails to reconnect now and then when the database restarts. If I call connect in the main program and then call it the above it works fine. So I simple include it in my New section, and bless in in self.

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now