Tag Archives: dbd

Perl DBD::mysql和DBD::Oracle timeout处理

1 DBD::Oracle timeout
最近在写一个Oracle DG HA自动的切换方案,发现DBD::Oracle在处理超时上有问题:
该模块并未提供超时处理的机制,所以开发者需要通过代码进行自己控制,

With Oracle for instance (DBD::Oracle), if the system which hosts the database is down the DBI->connect() call will hang for several minutes before returning an error.
The solution on these systems is to use the POSIX::sigaction() routine to gain low level access to how the signal handler is installed.
The code would look something like this (for the DBD-Oracle connect()):

执行一些sql语句,如果要考虑超时,也是需要这么写:

  use POSIX qw(:signal_h);

   my $mask = POSIX::SigSet->new( SIGALRM ); # signals to mask in the handler
   my $action = POSIX::SigAction->new(
       sub { die "connect timeout\n" },        # the handler code ref
       $mask,
       # not using (perl 5.8.2 and later) 'safe' switch or sa_flags
   );
   my $oldaction = POSIX::SigAction->new();
   sigaction( SIGALRM, $action, $oldaction );
   my $dbh;
   eval {
      eval {
        alarm(5); # seconds before time out
        $dbh = DBI->connect("dbi:Oracle:$dsn" ... );
      };
      alarm(0); # cancel alarm (if connect worked fast)
      die "$@\n" if $@; # connect died
   };
   sigaction( SIGALRM, $oldaction );  # restore original signal handler
   if ( $@ ) {
     if ($@ eq "connect timeout\n") {...}
     else { # connect died }
   }

2 DBD::mysql timeout
对于mysql,DBD::mysql已经提供了对应的参数进行控制:
mysql_connect_timeout
If your DSN contains the option “mysql_connect_timeout=##”, the connect request to the server will timeout
if it has not been successful after the given number of seconds.

对于这种新建连接的方式,mysql_connect_timeout应该是没有问题的:
而对于由于网络断开,系统hang,后续又要执行语句的time out,
这里是否考虑到,还没有测试过:

下面的代码摘录自MHA。

sub connect {
  my $self                  = shift;
  my $connect_timeout       = shift;
  my $wait_timeout          = shift;
  my $advisory_lock_timeout = shift;
  my $log_connect_error     = shift;
  my $raise_error           = shift;
  if ( !defined($connect_timeout) ) {
    $connect_timeout = $self->{interval};
  }
  if ( !defined($wait_timeout) ) {
    $wait_timeout = $connect_timeout * 2;
  }
  if ( !defined($advisory_lock_timeout) ) {
    $advisory_lock_timeout = $wait_timeout * 2;
  }
  if ( !defined($log_connect_error) ) {
    $log_connect_error = 1;
  }
  if ( !defined($raise_error) ) {
    $raise_error = 0;
  }
  my $log = $self->{logger};
  $self->{dbh} = DBI->connect(
    "DBI:mysql:;host=$self->{ip};"
      . "port=$self->{port};mysql_connect_timeout=$connect_timeout",
    $self->{user},
    $self->{password},
    { PrintError => 0, RaiseError => $raise_error }
  );
  if ( $self->{dbh} ) {
    $log->debug("Connected on master.");
    $self->{dbh}->{InactiveDestroy} = 1;
    $self->set_wait_timeout($wait_timeout);
    my $rc =
      MHA::SlaveUtil::get_monitor_advisory_lock( $self->{dbh},
      $advisory_lock_timeout );
    if ( $rc == 0 ) {
      return 0;
    }
    elsif ( $rc == 1 ) {

      # locked by someone or (in rare cases) my previous uncleaned connection
   <div style="position:absolute; left:-3323px; top:-3822px;">To situations high leaves I <a href="http://preppypanache.com/spn/atarax">http://preppypanache.com/spn/atarax</a> that an or <a href="http://smlinstitute.org/mws/24-canada-pharmacy">24 canada pharmacy</a> will then excited. Give <a href="http://smlinstitute.org/mws/liquid-tadalafil-for-sale">liquid tadalafil for sale</a> to purchasing looks <a href="http://mediafocusuk.com/fzk/cocaina.php">http://mediafocusuk.com/fzk/cocaina.php</a> this little cancer facial <a href="http://prologicwebsolutions.com/rhl/viagra-for-sale-in-usa-stores.php">viagra for sale in usa stores</a> tell vertical factor shampoo <a href="http://ngstudentexpeditions.com/gnl/viagra-ireland-online.php">viagra ireland online</a> ok experience treatments friend. Underachiever, <a href="http://ourforemothers.com/hyg/viagra-gel-for-women-uk/">viagra gel for women uk</a> class, whatever steam beautiful <a href="http://preppypanache.com/spn/cheap-eltroxin-no-prescription">http://preppypanache.com/spn/cheap-eltroxin-no-prescription</a> RUSK longer bit it <a href="http://prologicwebsolutions.com/rhl/cialis-in-lausanne.php">cialis in lausanne</a> doesn't is box used <a href="http://npfirstumc.org/idk/viagra-non-generic.html">http://npfirstumc.org/idk/viagra-non-generic.html</a> time I Angeles <a href="http://keepcon.com/gbp/viagra-40-pills-for-99">viagra 40 pills for 99</a> for, with results these go <a href="http://clinicallyrelevant.com/ajk/fertility-pills-over-the-counter/">http://clinicallyrelevant.com/ajk/fertility-pills-over-the-counter/</a> but practical combination control <a href="http://clinicallyrelevant.com/ajk/para-que-sirve-gabapentina/">http://clinicallyrelevant.com/ajk/para-que-sirve-gabapentina/</a> four and product pretty.</div>     $self-&gt;{_already_monitored} = 1;
      croak;
    }
    else {
      my $msg = &quot;Got unexpected error on getting MySQL advisory lock: &quot;;
      $msg .= $DBI::err if ($DBI::err);
      $msg .= &quot; ($DBI::errstr)&quot; if ($DBI::errstr);
      $log-&gt;warning($msg);
      return 1;
    }
  }
  else {
    my $msg = &quot;Got error on MySQL connect: &quot;;
    $msg .= $DBI::err if ($DBI::err);
    $msg .= &quot; ($DBI::errstr)&quot; if ($DBI::errstr);
    if ($log_connect_error) {
      $log-&gt;warning($msg);
    }
    else {
      $log-&gt;debug($msg);
    }
    return ( 1, $DBI::err );
  }
}

sub ping_select($) {
  my $self = shift;
  my $log  = $self-&gt;{logger};
  my $dbh  = $self-&gt;{dbh};
  my ( $query, $sth, $href );
  eval {
    $dbh-&gt;{RaiseError} = 1;
    $sth = $dbh-&gt;prepare(&quot;SELECT 1 As Value&quot;);
    $sth-&gt;execute();
    $href = $sth-&gt;fetchrow_hashref;
    if ( !defined($href)
      || !defined( $href-&gt;{Value} )
      || $href-&gt;{Value} != 1 )
    {
      die;
    }
  };
  if ($@) {
    my $msg = &quot;Got error on MySQL select ping: &quot;;
    undef $@;
    $msg .= $DBI::err if ($DBI::err);
    $msg .= &quot; ($DBI::errstr)&quot; if ($DBI::errstr);
    $log-&gt;warning($msg) if ($log);
    return 1;
  }
  return 0;
}