jmo has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: Reconnecting to a mysqldb
by jbert (Priest) on Nov 07, 2006 at 10:54 UTC
    The DBD::mysql driver supports auto-reconnect. See perldoc DBD::mysql for all the details, as a small taster:
    "DBD::mysql has a "reconnect" feature that handles the so-called MySQL "morning bug": If the server has disconnected, most probably due to a timeout, then by default the driver will reconnect and attempt to execute the same SQL statement again. However, this behaviour is disabled when AutoCommit is off: Otherwise the trans‐action state would be completely unpredictable after a reconnect."

    So, you could try just ensuring you have {AutoCommit => 1} included as the last argument to DBI::connect.

    If AutoCommit isn't correct for your purposes, then you'll have the same problems as described in the docs if you attempt to seamlessly reconnect behind the scenes.

[reply]
[d/l]
[select]
      However, any per connection variables that you have set will have been forgotten in the reconnect, eg 

        SET NAMES "utf8";

      [download]

      After the reconnect, NAMES will be back to the default.

[reply]
[d/l]
      Yes {AutoCommit => 1} helps me if the database is up at the time of the query but lets say it's down at the exact time of the query, then I want to sleep for say 5 sec and try again, a few times since (in my case) time isn't an issue.

      The only way I can think of that would solve that is by doing my own sub class.

      Sorry for not being clear about that.
[reply]
Re: Reconnecting to a mysqldb
by pajout (Curate) on Nov 07, 2006 at 14:40 UTC
    In my point of view, if you start the project, you can use something similar to following very simplified module: 
    package MyProject::Database;

use DBI;

our $dbh;

sub getDbh {
  #you should catch the exceptions and try to reconnect here
  if ($dbh) {
    $dbh->ping();
  } else {
    $dbh = DBI->connect();
  }
  return $dbh;
}

sub doSQL {
  my $dbh = getDbh();
  return $dbh->do(@_);
}

sub query2aref {
  my $dbh = getDbh();
  return $dbh->selectall_arrayref(@_);
}

1;

    [download]

    Of course, for some reasons it is better to implement it as object instance, there is many ways how to do it...

[reply]
[d/l]
Re: Reconnecting to a mysqldb
by perrin (Chancellor) on Nov 07, 2006 at 15:26 UTC
    The connect_cached method does this, but it doesn't do the sleep that you want. To get that, I might just wrap the calls to DBI in an eval and handle the sleep and reconnect at a higher level. It will be less trouble than inheritance.
[reply]
      I might be thinking totaly wrong but the reason I'm thinking inheritance is that I have lots of code using plain DBI. With inheritance I'd only need to change the use DBI and $dbh = DBI->connect.

      The only thing (hopefully) I really need to know to get this working is how DBD::mysql does the reconnect (or what it calls) since it can realize the connection is lost and can try to reconnect transparent to the script. Basicly what it does today is: 
      $sth = $dbh->prepare ($sql);
# im doing a mysql restart
$sth->execute (); # DBD::mysql magically reconnects keeping the prepar
+ed statement and executes

      [download]
      While (in case of stoped db): 
      $sth= $dbh->prepare ($sql);
# stoping mysql
$sth->execute (); # DBD::mysql tries, but no connection, reconnecting 
+and trying again will lose the $dbh->prepare

      [download]
      So the way I see it it should be a sane and nice way of doing it: 
      $sth= $dbh->prepare ($sql);
# stoping mysql
$sth->execute (); # entering reconnect mode in MyDBI::st/db: 
while (!$dbh->ping) { 
sleep 5; 
MyDBI->connect_like_mysql_reconnects;
}

      [download]
      Prefarable giving arguments like { reconnect_tries => 10, wait_time => 5 } so the scripts can configure themselves how long they are prepared to wait.

      I guess it all boils down to that I think DBI should be responsible for really trying to reconnect and me not having to surround all my db calls with evals, pings and reconnects ...
[reply]
[d/l]
[select]
        Fair enough. I just think you may run into problems if you try to use other tools that want to subclass DBI, like Apache::DBI or Class::DBI. There is an official way to do subclassing, which is discussed in the DBI docs. You can look at the connect_cached() method to see how it handles keeping the connection parameters around.
[reply]
Re: Reconnecting to a mysqldb
by graff (Chancellor) on Nov 08, 2006 at 02:39 UTC
    You said:
    I have a long running script that needs to be able to reconnect to the database (mysql) since the db is restarted sometimes.

    This makes me wonder: do there tend to be long intervals during which the long-running script isn't really doing anything with the database?

    Have you considered "modularizing" the database connection and transactions, rather than creating and sustaining a single, constant connection? That is, if the usage pattern involves long intervals with no activity between occasional queries/updates/inserts, you might just want to open a fresh connection for each transaction. In this kind of situation, the overhead of creating a new connection each time is insignificant, and applying a suitable holding-loop ("keep trying till the server responds") would be pretty easy to nail down.

    Of course, if the activity is fairly evenly distributed (and dense), then you need a sustained connection, with the kind of error trapping/auto-reconnect facility being discussed in the other replies.

[reply]

Back to Seekers of Perl Wisdom