Tag Archives: perl

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;
}

linux+eclipse+perl的安装

perl在数据库管理中是很通用的,而数据库比较多的部署在linux、unix下。如果要开发相关环境下的脚本,最好有相关环境的脚本编写和调试器。如果你是高手,一个vi也是可以的。但是对于那种比较大型一点的程序,估计就比较难了。

最近在研究mysql 的 ha 工具MHA,所以顺便搭建一套linux下的调式环境,我的环境是oel 5.8 + java 6 + eclipse 3.7 + epic + PadWalker-1.96,以下是我的安装步骤:

1、安装Java环境
查看是否有安装Java Runtime Environment,如果没有,需要安装成功,另外如果版本较老,建议选用一个比较新的,我这里选的是jre-6u43-linux-i586.bin。 运行自解压文件 ./jre-6u43-linux-i586.bin即可。同时把对应目录加入env。

2、安装Eclipse
先下载eclipse-jee-indigo-linux-gtk.tar.gz,需要eclipse3.2至3.7的,因为epic(org.epic.updatesite_0.6.35_20090521.zip)目前只能在这个上面运行。然后上传到某个目录,tar解压缩后进入相应目录直接运行./eclipse

[root@oel58 eclipse]# pwd
/java/eclipse
[root@oel58 eclipse]# ls -al
total 760
drwxrwsr-x 9 cimsrvr users 4096 Apr 3 10:47 .
drwxr-xr-x 8 root root 4096 Mar 16 13:41 ..
drwxrwsr-x 2 cimsrvr users 4096 Jun 15 2011 about_files
-rw-rw-r– 1 cimsrvr users 18909 Jun 14 2011 about.html
-rw-rw-r– 1 cimsrvr users 229996 Mar 15 23:33 artifacts.xml
drwxrwsr-x 10 cimsrvr users 4096 Apr 3 10:47 configuration
drwxrwsr-x 2 cimsrvr users 4096 Jun 15 2011 dropins
-rwxr-xr-x 1 cimsrvr users 62814 Mar 22 2011 eclipse
-rw-rw-r– 1 cimsrvr users 407 Mar 15 23:33 eclipse.ini
-rw-rw-r– 1 cimsrvr users 59 Jul 29 2010 .eclipseproduct
-rw-rw-r– 1 cimsrvr users 16536 Feb 26 2005 epl-v10.html
drwxrwsr-x 140 cimsrvr users 20480 Mar 15 23:33 features
-rw-rw-r– 1 cimsrvr users 9022 Dec 12 2006 icon.xpm
-rwxr-xr-x 1 cimsrvr users 266168 Dec 12 2006 libcairo-swt.so
-rw-rw-r– 1 cimsrvr users 8951 Feb 5 2011 notice.html
drwxrwsr-x 5 cimsrvr users 4096 Jun 15 2011 p2
drwxrwsr-x 31 cimsrvr users 73728 Mar 15 23:33 plugins
drwxrwsr-x 2 cimsrvr users 4096 Jun 15 2011 readme
[root@oel58 eclipse]# ./eclipse

3、安装EPIC支持插件
在命令提示符后输入eclipse就可以进入eclipse开发了,但是现在还不支持perl,所以我们需要安装一个插件,让eclipse来认识perl语言世界的规则。

以下是网上很多资料都是这么写的:
(1)在打开的eclipse操作界面上找到Help,然后选择Install New Software…
Help->Install New Software..
(2)在Work with:栏中输入
http://e-p-i-c.sf.net/updates/testing
(3)选择下面显示的 EPIC Main Componets,点击Next>
(4)选择EPIC,点击Finish

但是这个是有问题的,http://e-p-i-c.sf.net好像一直访问不了,既然不能自动安装,就只能手工安装了:先下载org.epic.updatesite_0.6.35_20090521.zip,然后上传的系统,解压缩。在上面的步骤中选择本地目录就可以了。

不过还没有算完,要调试perl还需要安装PadWalker,我下载的是PadWalker-1.96.tar.gz,这个需要编译安装。等这个步骤完成后我们就可以进行perl的开发和调式了:perldebug
另外,你可能需要一个Xmanage,这样的话,我们直接可以在本机上操作虚拟机上的东西,就跟我们在windows下运行eclipse一下的,这样一个高效的开发环境就ok了,如果你想学习perl,这绝对是你值得拥有的。