You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Daniel Manley <da...@points.com> on 2011/02/23 17:28:19 UTC
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
Posted by Perrin Harkins <pe...@elem.com>.
On Wed, Feb 23, 2011 at 4:07 PM, Daniel Manley <da...@points.com> wrote:
> 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.
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
Posted by Daniel Manley <da...@points.com>.
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
Posted by Cosimo Streppone <co...@streppone.it>.
On Thu, 24 Feb 2011 03:28:19 +1100, Daniel Manley
<da...@points.com> 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