Re: Reconnecting to mysql

On 11/9/06, Christian Hoermann <0oo0oo0.c@> wrote:
> Hello Jon,

Hi there

>
> > {mysql_auto_reconnect
> > => 1} isn't enough since the database can be down at the time of my
> > query and that makes DBD::mysql sad.
>
> Sounds like you are using real MySQL server side prepared statements,
> rather than the DBD emulation. So, one of the things you should
> consider is that, although MySQL supports automatic reconnection, the
> MySQL sever side components of the prepared statements are lost when
> MySQL exits. Therefore, all of your DBI statement handles are
> invalid/worthless, without their MySQL counterparts, as
> mysql_auto_reconnect doesn't re-prepair the statements. If you were to
> use driver emulated prepared statements, auto_reconnect should be
> sufficient, to the best of my knowledge.
>

auto_reconnect is fine if there's been a restart or something like
that, the point is if the DB happends to be unreachable (down for
maintainance) at the time of the reconnect will it fail, also losing
any prepared statements. Losing the statements isn't a big issue in it
self but it won't try to reconnect again if it fails once.

> So, what you may be able to do:
> * disable mysql_auto_reconnect
> * have a connect sub, where you also (re)prepair all of your prepaired
> statements one after the other (you could save them in a global hash).
> You could loop the connect() part every few seconds until it succeeds
> and then re-prepair the statements.
> * If you don't like/use evals around your DBI code, you need to set
> Raise Error to 0 and check the return value from connect, prepare and
> execute for errors and call the connect sub again if necessary and
> then re-execute the statement.
> * You can use $dbh->ping to check whether your connection is still
> live (as long as mysql_auto_reconnect is off). However, rather than
> checking that before every execute/prepare, you should probably just
> check the return value from DBI to catch any errors.
>


Yes this is also my conclution, but the problem is that I want the
reconnect to happend at MyDBI::st->execute (and fetch*) but all I get
into those functions is the statement handle. What I can't figure out
is:

1. How to get a hold of the database handle used in the script using the module
2. How to modify the database handle (reconnect) changing the dbh in
the script, like mysql_auto_reconnect does

That is (in MyDBI::st):

sub execute
{
 my $sth = shift;
 my (@bind_vars) = @_;

 # 1. how to see if I have a connection to the database with the sth?
 # 2. How to modify $dbh that is used in the script so I won't have to
have a local dbh in every MyDBI::st function?

 return $sth->SUPER::execute (@bind_vars);
}

Thanks
Jon

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]