I have a long running script that needs to be able to reconnect to the database (mysql) since the db is restarted sometimes. My plan was to subclass DBI (like in the t/ dir) and add checks in execute and the fetch methods of MyDBI::st, and if the DB has gone away reconnect and re-run the prepare and then execute.

I've reached the point where I do execute and see that the DB is gone. My problem is that at that point (in the MyDBI::st::execute) is all I have a reference to myself, not to the dbh used to do the prepare so what I wonder is how should I go about changing the handle? What I've done is basicly: 
package MyDBI;

use strict;
use DBI;
use DBD::mysql;
use vars qw(@ISA);
@ISA = qw(DBI);

sub connect
{
   my $self = shift;
   my ($dsn, $uname, $passwd) = @_;
    my $this = $class->SUPER::connect($dsn, $uname, $passwd);
   $this->set_dsn ($dsn, $uname, $passwd); # so I can reconnect easily

   return $this;
}

package MyDBI::db;
@MyDBI::db::ISA = qw(DBI::db);
my ($_uname, $_passwd, $_dsn);

sub set_dsn
{
   my $this = shift;
   ($_dsn, $_uname, $_passwd) = @_;
}


sub reconnect
{
  # somthing like MyDBI->connect ($_dsn, $_uname, $_passwd);
 # and then rerun prepare
}

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

sub execute
{
   my $this = shift;
   $this->SUPER::execute (@_);
   if ($this->errstr =~ /server has gone away/)
   {
       MyDBI::db->reconnect;
       # and then rerun execute with some sleep to avoid insane bahvio
+ur
   }
}

1;

[download]
And I want to use it like if it was DBI, except of course the original connect: 
use MyDBI;
my $dbh= MyDBI->connect (...);
my $sth = $dbh->prepare ("select now()");
sleep (10); # while sleeping the db goes away
$sth->execute; # at this point MyDBI is supposed to reconnect and reru
+n prepare/execute

[download]
Thanks

In reply to Reconnecting to a mysqldb by jmo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.