Reconnecting to mysql

Hi list,

I'm trying (against sanity it seems) to make DBI/DBD::mysql reconnect
to my mysql server by subclassing DBI.

The reason for this is that I have a script that runs over a long
period of time doing a steady flow of sql-statements and sometimes
during this time is the database stoped for maintainance/backup. I
don't want my script to go nuts at that point. {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.

What I want to do feels like it ought to be fairly simple if I only
knew how to/what to do/call in the DBD::mysql. The scenario looks like
this:

my_script.pl:
...
my $dbh = MyDBI->connect ();
my $sth = $dbh->prepare ($sql);
# the database is taken down for maintaince
$sth->execute; # the database is still down when MyDBI::st gets the call


MyDBI.pm:
package MyDBI;
use strict;
use DBI;
use DBD::mysql;
use vars qw(@ISA);
@ISA = qw(DBI);

sub connect
{
    # I only have this function to be able to add default timouts and
amount of retries
    my $this = $class->SUPER::connect($dsn, $uname, $passwd);
    return $this;
}

package MyDBI::db;
@MyDBI::db::ISA = qw(DBI::db);


package MyDBI::st;
@MyDBI::st::ISA = qw(DBI::st);

sub execute
{
    my $this = shift;
     # here be my problems. If database is down I want to try to
reconnect transparent like DBD::mysql does with mysql_auto_reconnect
and if I can't reconnect sleep and try again untill i succeed or
exceed some default timeout
   return $this->SUPER::execute (@_);
}


So finaly, the problem as i se it is:
Figure out what to call from MyDBI::st->execute with my $sth ($this),
that will make the MAIN::dbh  and the MAIN::sth change (to be the
"new" reconnected sth and dbh instead).

I've noticed that auto_reconnect forgets the prepared statement if the
database is down when one tries execute, takes up db and tries to run
execute again. This is also something I'd like to not have.

Does anyone in the list know how to do this? I don't want to make a
MyDB module that has some of the feautures of DBI/DBD by forwarding
the calls since it's likely there will be feautures missed and it'll
lead to a cluttered module, and I don't want to call
connect_cached/have eval blocks around all my db-calls since I
consider that bad and ugly.

Thanks
Jon

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