Attempting to trap a DBI->connect error

Hammy has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, Occasionally my users get errors when they are trying to use my application when database connections are exhausted. It happens infrequently enough that I want to trap the error and try to open the DB again. For some reason I get the old "Internal Server Error". Inside the Apache error log I get the appropriate error - Can't connect to MySQL server on <ipaddress>:<port>. Before you see the code, I have two points. First, if on the DBI->connect I put

DBI->connect(....) || fatal(error message)
[download]

the fatal piece is invoked, with the code below, I get the "Internal Server Error". Second, fatal is a custom error routine that emails me and puts a nice message up for the user. I only want that error to show up after I have tried a few times. Here is the code:

sub openDB {
    my ($database, $user_id, $password) = ();

    open( CFG, $db_cfg ) or fatal("Cannot open databse config file.");
    ($database, $user_id, $password, $pwkey) = split( ',', <CFG>);
    $done = 0;
    $num_tries = 0;
    while (!$done) {
        $num_tries += 1;

        #  If we exceeded the custom amount of attempts call error rou
+tine
        if ($num_tries > 4) {
            fatal (" Could not open database: $DBI::errstr");
            $done = 1;
        }
        else {

            #  Make the database connection
            eval {
                $dbh = DBI->connect("DBI:mysql:$database:<ipaddress:po
+rt>", $user_id, $password);
            }

            #  If there is an error try again
            if ($@) {
                next;
            }

            #  No error drop out of loop
            else {
                $done = 1;
            }
        }
    }
}
[download]

Thanks in advance for your suggestions

Comment on Attempting to trap a DBI->connect error Select or Download Code

Replies are listed 'Best First'.
Re: Attempting to trap a DBI->connect error by mifflin (Curate) on Jan 06, 2007 at 03:46 UTC
By default DBI does not throw exceptions. If you want it to you need to use the 4th argument to pass in a hashref telling it to do so like... `my $dbh = DBI->connect('db conn info here','userid','password',{RaiseE +rror => 1});` [download] This will force DBI to throw errors with a die that you must catch with an eval. This will also have the effect of making your code cleaner looking as you will not have to check for errors after every prepare, bind, execute, fetch... etc method you call. Just make sure you catch the errors.	[reply] [d/l]
Re^2: Attempting to trap a DBI->connect error by Hammy (Scribe) on Jan 07, 2007 at 16:57 UTC
Thank you very much for the insight, I will give this a try.	[reply]
Re: Attempting to trap a DBI->connect error by kyle (Abbot) on Jan 06, 2007 at 03:50 UTC
Your eval "block" needs a semicolon after it. `$ perl -ce 'eval { 1 } sleep 1;' syntax error at -e line 1, near "} sleep" -e had compilation errors. $ perl -ce 'eval { 1 }; sleep 1;' -e syntax OK` [download]	[reply] [d/l]
Re: Attempting to trap a DBI->connect error by Cabrion (Friar) on Jan 06, 2007 at 11:51 UTC
You probably want a call to sleep in the connection attempt loop, or perhaps some extended diagnostics. No point in just trying 4 times in a row without waiting for the connection problem to get resolved, of doing some connection diagnositcs. You might consider: `DBI->connect(....) \|\| fatal(DBI->errstr)` [download]	[reply] [d/l]


Perl Monk, Perl Meditation
	PerlMonks