Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Apache::DBI Wont Reconnet?

Posted on 2004-08-11
6
Medium Priority
?
303 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
4 Comments
 
LVL 18

Accepted Solution

by:
kandura earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
Six Sigma Control Plans

773 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