Re: Apache::DBI and DBD::mysql ping not working

On Thu, 24 Feb 2011 03:28:19 +1100, Daniel Manley  
<[email protected]> wrote:

> I've been digging around in my mod_perl-based apps and trying to figure  
> out why setting up the DB connections for pinging is still randomly  
> producing first-thing-in-the-morning "mysql server has gone away"  
> errors.  I read up about the morning bug and such (adding ping() to  
> mysql.pm based on examples on the net) and still can't get it working.   
> I'm using apache 2.2.17 and mod_perl 2.0.4.

I would suggest looking into your mysql server "wait_timeout".

Calling $dbh->ping() on a handle is not enough to have
seamless recover from stale db connections. At least on MySQL.

Our code, simplifying, looks like:

----------------- 8< -----------------------------------

sub is_alive {
     my ($dbh) = @_;

     return if not $dbh;

     if (not eval { $dbh->FETCH('Active') && $dbh->ping() }) {
         warn "Found stale dbh handle: $dbh ($@)\n";
         return;
     }

     return 1;
}

sub db_connect {

    my $dbh = cached_dbh();
    return $dbh if is_alive($dbh);

    $dbh = new_db_connection();
    return $dbh;
}

----------------- 8< -----------------------------------

-- 
Cosimo