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

Hi,

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.

so - I finally started digging into the Apache::DBI, DBD::mysql and
DBD::Oracle packages -- I put in warn messages all over the place and
turned on various debugs. I see my debug messages from Oracle's ping
call in the apache error log. But I can never get the mysql ping to
show its warn messages. not via Apache::DBI's calls to it or my own
forced ping call on the dbh. So I can only conclude that it's not
getting to the ping() method in DBD/mysql.pm

any idea where the mysql ping happens or why it's not? is the top-level
DBI package interfering?

(inserted right after the prepare method in the DBD::mysql::db package
section)

sub ping {
my ($dbh) = @_;
my $ret = 0;
warn "DANDAN: mysql ping!!";
eval {
local $SIG{__DIE__} = sub { return (0); };
local $SIG{__WARN__} = sub { return (0); };
# adapt the select statement to your database:
$ret = $dbh->do('select 1');
warn "DANDAN: selected 1";
};
return ($@) ? 0 : $ret;
}

similar warn messages in DBD::Oracle's ping show up just fine in Apache
error log.

Dan

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

well, I think I figured this one out on my own. Though I've used Perl
for a number of years, I've never gotten to the XS file coding. and so
after running this under perl debug, I finally get the hint that maybe
the mysql ping() is done in C. And so it is.

Dan


On 11-02-23 11:28 AM, Daniel Manley wrote:
> Hi,
>
> 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.
>
> so - I finally started digging into the Apache::DBI, DBD::mysql and
> DBD::Oracle packages -- I put in warn messages all over the place and
> turned on various debugs. I see my debug messages from Oracle's ping
> call in the apache error log. But I can never get the mysql ping to
> show its warn messages. not via Apache::DBI's calls to it or my own
> forced ping call on the dbh. So I can only conclude that it's not
> getting to the ping() method in DBD/mysql.pm
>
> any idea where the mysql ping happens or why it's not? is the
> top-level DBI package interfering?
>
> (inserted right after the prepare method in the DBD::mysql::db package
> section)
>
> sub ping {
> my ($dbh) = @_;
> my $ret = 0;
> warn "DANDAN: mysql ping!!";
> eval {
> local $SIG{__DIE__} = sub { return (0); };
> local $SIG{__WARN__} = sub { return (0); };
> # adapt the select statement to your database:
> $ret = $dbh->do('select 1');
> warn "DANDAN: selected 1";
> };
> return ($@) ? 0 : $ret;
> }
>
> similar warn messages in DBD::Oracle's ping show up just fine in
> Apache error log.
>
> Dan
>

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

On Wed, Feb 23, 2011 at 4:07 PM, Daniel Manley w=
rote:
> well, I think I figured this one out on my own. =A0Though I've used Perl =
for a
> number of years, I've never gotten to the XS file coding. =A0and so after
> running this under perl debug, I finally get the hint that maybe the mysq=
l
> ping() is done in C. =A0And so it is.

FYI, you should be able to define your own ping() and see it called.
The Apache::DBI docs show how.

- Perrin

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

On Thu, 24 Feb 2011 03:28:19 +1100, Daniel Manley
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