mysql - DBI - Perl - 记录 MySQL 警告-6ren

mysql - DBI - Perl - 记录 MySQL 警告

转载作者：可可西里更新时间：2023-11-01 07:28:55

我正在使用 DBI 并希望将 MySQL WARNINGS 的日志记录添加到此脚本中。我能够毫无问题地记录真正的 MySQL 错误，但此时我需要追踪正在生成的 MySQL 警告。如果 mysql 语句失败，我可以将错误的 sql 语句打印到 bad_sql.txt 并自动生成一封电子邮件。我需要做两个改变，我真的卡住了1)如果语句执行但有mysql警告我想将其捕获到last_sql_warning.txt2)如果语句失败导致锁定超时，我想最多重新提交查询两次。

这就是现在转储到日志中的内容。

MiscLibs::MySQL::MySQLDoCmd, MySQL.pm line 564:<br/> -->UPDATE tbl_xxx_files SET ReloadStart=123" WHERE (FileName="image.txt")<--

相关代码块

#=====================================================================================
# Execute MySQL commands and handle errors
#=====================================================================================

sub MySQLDoCmd ($;$) {

my ($MySQLCmd, $Quite) = @_;

 if ( eval { $DBHandle->do($MySQLCmd) } ) {
   open (MYFILE2, '>/bb/bin/fa/logs/last_sql_warning.txt');
   # trying to write warning to log.  As a first pass I was attempting to write 
   # each statement to the log and include any warnings.  What I would like code to
   # do is check if there is a warning and only then write that statement to the log. 

   print MYFILE2 MySQLMakeID() . ": $DBHandle->errstr\n-->$MySQLCmd<--\n";

return 0;

} elsif ( ! $Quite ) {
  open (MYFILE, '>>/bb/bin/fa/logs/badsql.txt');
  print MYFILE  MySQLMakeID() . ": $@\n-->$MySQLCmd<--\n";

#=========SENDS EMAIL ON STATEMENT FAILURE===================
 while (my ($addressee, $address) = each (%emailList))
 {
     print STDERR "INFO: Sending email to $addressee at address $address\n";
     $message =~ s/ADDRESSEE/$addressee/g;
     $message =~ s/ERRORREASON/$errMessage/g;
     &sendMail($addressee, $address, $message);
     $message =~ s/$addressee/ADDRESSEE/g;
     $message =~ s/$errMessage/ERRORREASON/g;
  }

return 1;

 } else {
  return 1;

 }

}

完整代码块

use strict;                        # Everything must be defined before it is used
use warnings;                      # Print warnings
use POSIX;                         # Provides POSIX functions
use English '-no_match_vars';      # Provides access to English version of builtin variables
use Net::SMTP;

#===================================================================================================

package MiscLibs::MySQL;
use Exporter ();

our ($VERSION, @ISA, @EXPORT, @EXPORT_OK);

$VERSION = '1.00';
@ISA = qw(Exporter);
@EXPORT_OK = ( );
@EXPORT = qw( 

MySQLOpenConnection
MySQLCloseConnection

MySQLCloseHandle

MySQLErrNo
MySQLError
MySQLHostInfo
MySQLInfo
MySQLInsertID
MySQLProtoInfo
MySQLServerInfo
MySQLStat
MySQLThreadID
MySQLDBDStats
MySQLAutoReconnect
MySQLUseResult
MySQLShowTables
MySQLShowColumns
MySQLValidateFields
MySQLNumOfFields
MySQLErrStr
MySQLUseResults

MySQLDoCmd

MySQLDeleteRows
MySQLDeleteRowsICS
MySQLInsertRow
MySQLUpdateRows

MySQLSelectRows
MySQLGetHashRef
MySQLGetArray
MySQLSetLastUpdate
MySQLSetReportState

MySQLTruncateTable
MySQLDisableKeys
MySQLEnableKeys
MySQLOptimizeTable

MySQLCacheAddTable
MySQLCacheAddBuffer
MySQLCacheFlush

);
#=====================================================================================
my %emailList = (
                   "bob"=>"\@bog.com",
               );

my $errMessage = "error_message";
my $message = "MySQl Query Timed out - check logs/";

#======================================================================================
 sub sendMail()
{
 #not relevant - smtp code

     print "EXECUTE: Mail sent successfully\n";
}

#======================================================================================

use File::Basename;                # Provides basename, dirname and fileparse functions
use Data::Dumper;
use DBI;                           # Interface to MySQL

my $DBHandle;

my %CacheFieldNameStrings;
my %CacheFieldNameArrays;
my %CacheSizes;
my %CacheFieldValues;
my %CacheDupKeyCmds;

my %ValidateFieldNames;

my $MaxCacheSize = 50;

1;

#=====================================================================================
# Create an ID string for error reporting
#=====================================================================================

sub MySQLMakeID {

  my ($package, $filepath, $line, $subroutine, $hasargs,
      $wantarray, $evaltext, $is_require, $hints, $bitmask) = caller(1);

  $subroutine =~ s/main:://;

  my $filename = basename($filepath);

  my $id = "$subroutine, $filename line $line";

  # print "ID: '$id'\n";

  return $id;

}

#=====================================================================================
# Open MySQL connection and get reference information
#=====================================================================================

sub MySQLOpenConnection (;$) {

  my ( $NewMaxCacheSize ) = @_;

  my $Database;
  my $Host;
  my $Port;
  my $DSN;
  my $User;
  my $Password;
  my %Options;

  if ( defined($NewMaxCacheSize) && ($NewMaxCacheSize > 1) ) { $MaxCacheSize = $NewMaxCacheSize }

  $Database = $ENV{MySQLDatabase}; if ( ! defined($Database) ) { $Database = "database" }
  $Host     = $ENV{MySQLHost};     if ( ! defined($Host)     ) { $Host     = "host" }
  $Port     = $ENV{MySQLPort};     if ( ! defined($Port)     ) { $Port     = 123 }

  $DSN = "DBI:mysql";
  $DSN = $DSN . ":$Database";
  $DSN = $DSN . ";host=$Host";
  $DSN = $DSN . ";port=$Port";
  $DSN = $DSN . ";mysql_compression=1";

  $User     = 'user';
  $Password = 'pw';

  %Options = ( RaiseError => 1 );

  $DBHandle = DBI->connect($DSN, $User, $Password, \%Options);

  return $DBHandle;

}

#=====================================================================================
# Close MySQL connection opened above or a handle that is passed
#=====================================================================================

sub MySQLCloseConnection {
  if ( $DBHandle ) { $DBHandle->disconnect }
  return 0;
}

sub MySQLCloseHandle ($) { 
  my ($sh) = @_;
  if ( $sh ) { $sh->finish() }
  return 0;
}

#=====================================================================================
# Return a various database handle values and conditions
#=====================================================================================

sub MySQLErrNo      () { return $DBHandle->{'mysql_errno'}      }
sub MySQLError      () { return $DBHandle->{'mysql_error'}      }
sub MySQLHostInfo   () { return $DBHandle->{'mysql_hostinfo'}   }
sub MySQLInfo       () { return $DBHandle->{'mysql_info'}       }
sub MySQLInsertID   () { return $DBHandle->{'mysql_insertid'}   }
sub MySQLProtoInfo  () { return $DBHandle->{'mysql_protoinfo'}  }
sub MySQLServerInfo () { return $DBHandle->{'mysql_serverinfo'} }
sub MySQLStat       () { return $DBHandle->{'mysql_stat'}       }
sub MySQLThreadID   () { return $DBHandle->{'mysql_thread_id'}  }
sub MySQLDBDStats   () { return $DBHandle->{'mysql_dbd_stats'}  }

#=====================================================================================
# Optionally set but always return various database handle values and conditions
#=====================================================================================

sub MySQLAutoReconnect (;$) { 
  my $val = $_[0];
  if ( defined($val) ) { $DBHandle->{'mysql_auto_reconnect'} = $val }
  return $DBHandle->{'mysql_auto_reconnect'};
}

sub MySQLUseResult (;$) { 
  my $val = $_[0];
  if ( defined($val) ) { $DBHandle->{'mysql_use_result'} = $val }
  return $DBHandle->{'mysql_use_result'};
}

#=====================================================================================
# Execute MySQL commands and handle errors
#=====================================================================================

sub MySQLDoCmd ($;$) {

  my ($MySQLCmd, $Quite) = @_;

  if ( eval { $DBHandle->do($MySQLCmd) } ) {
       open (MYFILE2, '>>/bb/bin/fa/logs/last_sql.txt');
       #!!!!! trying to write warning to log 
       print MYFILE2 MySQLMakeID() . ": $DBHandle->errstr\n-->$MySQLCmd<--\n";
      return 0;

  } elsif ( ! $Quite ) {
      open (MYFILE, '>>/bb/bin/fa/logs/badsql.txt');
      print MYFILE  MySQLMakeID() . ": $@\n-->$MySQLCmd<--\n";

#=========SENDS EMAIL ON STATEMENT FAILURE===================    
 while (my ($addressee, $address) = each (%emailList))
     {
         print STDERR "INFO: Sending email to $addressee at address $address\n";
         $message =~ s/ADDRESSEE/$addressee/g;
         $message =~ s/ERRORREASON/$errMessage/g;
         &sendMail($addressee, $address, $message);
         $message =~ s/$addressee/ADDRESSEE/g;
         $message =~ s/$errMessage/ERRORREASON/g;
      }

    return 1;

  } else {
    return 1;

  }

}

#=====================================================================================
# Delete rows from a MySQL table
#=====================================================================================

sub MySQLDeleteRows ($;$) {

  my ($Table, $WhereRef) = @_;

  my $WhereList;

  my $MySQLCmd = 'DELETE FROM ' . $Table;

  if ( $WhereRef ) {
    $WhereList = BuildLists ('where', $WhereRef);
    $MySQLCmd = $MySQLCmd . $WhereList;
  }

  return MySQLDoCmd($MySQLCmd);

}


#=====================================================================================
# Select rows from a MySQL table and return a statement handle
#=====================================================================================

sub MySQLSelectRows ($$;$$) {

  my ($What, $Table, $WhereRef, $OrderRef) = @_;

  my $MySQLCmd = "SELECT $What FROM $Table";

  if ( $WhereRef ) { $MySQLCmd = $MySQLCmd . BuildLists ('Where'  , $WhereRef) }
  if ( $OrderRef ) { $MySQLCmd = $MySQLCmd . BuildLists ('OrderBy', $OrderRef) }

  # print "MySQLSelectRows: MySQLCmd '$MySQLCmd'\n";

  my $StmtHandle;

  if ( ! eval {  $StmtHandle = $DBHandle->prepare($MySQLCmd) } ) {
    print STDERR MySQLMakeID() . ": $@\n-->$MySQLCmd<--\n";
    return undef;

  } elsif ( ! eval { $StmtHandle->execute() } ) {
    print STDERR MySQLMakeID() . ": $StmtHandle->errstr\n-->$MySQLCmd<--\n";
    return undef;

  } else {
    return $StmtHandle;

  }

}

#=====================================================================================
# Return a various statement handle values and conditions
#====================================================================================

sub MySQLNumOfFields ($) { my ($sh) = @_; return $sh->{'NUM_OF_FIELDS'}      }
sub MySQLErrStr      ($) { my ($sh) = @_; return $sh->errstr                 }

sub MySQLGetHashRef  ($) { my ($sh) = @_;
  if ( my $Ref = $sh->fetchrow_hashref() ) { return $Ref }
  else                                     { MySQLCloseHandle($sh); return undef } 
}

sub MySQLGetArray ($) { my ($sh) = @_; 
  if ( my $Ref = $sh->fetchrow_array()   ) { return $Ref }
  else                                     { MySQLCloseHandle($sh); return undef } 
}

#=====================================================================================
# Optionally set but always return various statement handle values and conditions
#=====================================================================================

sub MySQLUseResults ($;$) { 
  my ($sh, $val) = @_;
  if ( defined($val) ) { $sh->{'mysql_use_result'} = $val }
  return $sh->{'mysql_use_result'};
}

#=====================================================================================
# Update a row in a MySQL table
#=====================================================================================

sub MySQLUpdateRows ($$;$) {

  my ($Table, $SetRef, $WhereRef) = @_;

  my $MySQLCmd;
  my $SetList;
  my $WhereList;

  $MySQLCmd = 'UPDATE ' . $Table;

  $SetList = BuildLists ('set', $SetRef);

  $MySQLCmd = $MySQLCmd . $SetList;

  if ( $WhereRef ) {
    $WhereList = BuildLists ('where', $WhereRef);
    $MySQLCmd = $MySQLCmd . $WhereList ;
  }

  # print "MySQLUpdateRows: MySQLCmd '$MySQLCmd'\n";

  return MySQLDoCmd($MySQLCmd);

}



#=====================================================================================
# Truncate a MySQL table
#=====================================================================================

sub MySQLTruncateTable ($) {

  my ($Table) = @_;

  my $MySQLCmd = 'TRUNCATE TABLE ' . $Table;

  return MySQLDoCmd($MySQLCmd);

}




#=====================================================================================
#
# The routines below maintain a cache of MySQL values to allow inserting multiple
# rows at a time to improve efficiency
#
#=====================================================================================

#=====================================================================================
# Add a table to the MySQL Cache
#=====================================================================================

sub MySQLCacheAddTable ($$;$) {

  my ( $TableName, $FieldNameArray, $DupKeyCmds ) = @_;

  my $DupKeyCmd;

  my $FieldName;
  my $FieldNameString = '';

  for $FieldName ( @$FieldNameArray ) {
    if ( $FieldNameString ) { $FieldNameString = $FieldNameString . ',' }
    $FieldNameString = $FieldNameString . $FieldName;    
  }

    $CacheFieldNameStrings{$TableName} = $FieldNameString;
  @{$CacheFieldNameArrays{$TableName}} = @$FieldNameArray;
  $CacheDupKeyCmds{$TableName}         = $DupKeyCmds;
  $CacheSizes{$TableName}              = 0;
  $CacheFieldValues{$TableName}        = '';

  return 0;

}

#=====================================================================================
# Add a buffer to the MySQL cache
#=====================================================================================

sub MySQLCacheAddBuffer ($$) {

  my ( $TableName, $AddValues ) = @_;

  my $FieldName;
  my $FieldValue;
  my $FieldValues;

  my $CacheValues;

  if ( ! defined($CacheFieldNameStrings{$TableName}) ) {
    print STDERR MySQLMakeID() . ": Table '$TableName' has not been initialized with the 'AddTable' command\n"; 
    return 1;
  }

  if ( $CacheSizes{$TableName} >= $MaxCacheSize ) { 
    # if ( $TableName eq 'tbl_xyz' ) {
      # print "Flushing $TableName cache before adding buffer: CacheSize = '$CacheSizes{$TableName}'\n" }
    MySQLCacheFlush ($TableName);
  }

  $FieldValues = '';

  for $FieldName ( @{$CacheFieldNameArrays{$TableName}} ) {

    $FieldValue = $AddValues->{$FieldName};

    if ( ! defined($FieldValue) ) { $FieldValue  =  '' }                 # Make sure value is defined
    else                          { $FieldValue  =~ s/(["',\\])/\\$1/g } # Make sure that MySQL special chars are escaped

    if ( $FieldValues ) { $FieldValues =  $FieldValues . "," }

    $FieldValues =  $FieldValues . "'" . $FieldValue . "'";

  }

  $CacheValues = $CacheFieldValues{$TableName};

  if ( $CacheValues ) { $CacheValues = $CacheValues . ',' }

  $CacheValues = $CacheValues . '(' . $FieldValues . ')';

  $CacheFieldValues{$TableName} = $CacheValues;

  $CacheSizes{$TableName}++;

  # if ( $TableName eq 'tbl_xyz' ) {
    # print "Added buffer to $TableName cache: CacheSizes '$CacheSizes{$TableName}', CacheValues '$CacheValues'\n" }

  return 0;

}

#=====================================================================================
# Flush entries from MySQL cache
#=====================================================================================

sub MySQLCacheFlush ($) {

  my ( $TableName ) = @_;

  my $FlushTable;
  my @FlushTables,

  my $FieldNames;
  my $FieldValues;
  my $DupKeyCmd;

  my $MySQLCmd;

  if ( lc($TableName) eq 'all' ) { 
    for $FlushTable ( keys(%CacheFieldNameStrings) ) { push @FlushTables, $FlushTable }

  } elsif ( ! defined($CacheFieldNameStrings{$TableName}) ) {
    print STDERR MySQLMakeID() . ": Table '$TableName' has not been initialized with the 'AddTable' command\n"; 
    return 1;

  } else {
    push @FlushTables, $TableName;

  }

  FlushTable: for $FlushTable ( @FlushTables ) {

    $FieldValues = $CacheFieldValues{$FlushTable};

    $CacheFieldValues{$FlushTable} = '';
    $CacheSizes{$FlushTable}       = 0;

    if ( ! $FieldValues ) { next FlushTable }

    $FieldNames = $CacheFieldNameStrings{$FlushTable};
    $DupKeyCmd  = $CacheDupKeyCmds{$FlushTable};

#Removed DELAYED after moving to innodb
#$MySQLCmd = "INSERT DELAYED INTO $FlushTable ($FieldNames) VALUES $FieldValues";

 $MySQLCmd = "INSERT INTO $FlushTable ($FieldNames) VALUES $FieldValues";

    if ( $DupKeyCmd ) { $MySQLCmd = $MySQLCmd . ' ON DUPLICATE KEY UPDATE ' . $DupKeyCmd }

    return MySQLDoCmd($MySQLCmd);

  }

}

sub BuildLists ($$;$) {

  my ($Type, $Ref1, $Ref2) = @_;

  my $Ref1Type = ref($Ref1);
  my $Ref2Type = ref($Ref2);

  my $Name;
  my $NameList;
  my $Value;
  my $ValueList;

  my %Fields;

  my $RtnVal;

  $Type = lc($Type);

  my $TypeIndex = index('values set where orderby', $Type);

  # print "Type '$Type', TypeIndex '$TypeIndex', Ref1Type '$Ref1Type', Ref1 '$Ref1'\n";

  if ( $TypeIndex < 0 ) {

    print STDERR MySQLMakeID() . ": $Type is not a a valid type. Use 'values', 'set' or 'where'\n";
    return $RtnVal;

  } elsif ( $Ref1Type eq '' ) {

    if ( $Type eq 'values' ) {
      $RtnVal = '(' . $Ref1 . ') VALUES (' . $Ref2 . ')';
    } elsif ( $Type eq 'set' ) {
      $RtnVal = ' SET ' . $Ref1;
    } elsif ( $Type eq 'where' ) {
      $RtnVal = ' WHERE ' . $Ref1;
    } elsif ( $Type eq 'orderby' ) {
      $RtnVal = ' ORDER BY ' . $Ref1;
    }

  } elsif ( $Ref1Type eq 'SCALAR' ) {

    if ( $Type eq 'values' ) {
      $RtnVal = '(' . $$Ref1 . ') VALUES (' . $$Ref2 . ')';
    } elsif ( $Type eq 'set' ) {
      $RtnVal = ' SET ' . $$Ref1;
    } elsif ( $Type eq 'where' ) {
      $RtnVal = ' WHERE ' . $$Ref1;    
    } elsif ( $Type eq 'orderby' ) {
      $RtnVal = ' ORDER BY ' . $$Ref1;
    }

  } elsif ( $Ref1Type eq 'HASH' ) {

    for $Name ( keys(%$Ref1) )  {

      $Value = $Ref1->{$Name};

      if ( ! defined($Value) ) { $Value  =  '' }                 # Make sure value is defined
      else                     { $Value  =~ s/(["',\\])/\\$1/g } # Make sure that MySQL special chars are escaped

      $Fields{$Name} = $Value;

    }

    if ( $Type eq 'values' ) {

      while (($Name, $Value) = each %Fields ) {
        if ( $NameList ) {
          $NameList  = $NameList  . ','  . $Name;
          $ValueList = $ValueList . ',"' . $Value . '"';
        } else {
          $NameList  = $Name;
          $ValueList = '"' . $Value . '"';    
        }
      }

      $RtnVal = " ($NameList) VALUES ($ValueList)";

    } elsif ( $Type eq 'set' ) {
      $RtnVal = '';
      while (($Name, $Value) = each %Fields ) {
        if ( $RtnVal ) { $RtnVal = $RtnVal . ',' }
        $RtnVal = $RtnVal . $Name . '="' . $Value . '"';
      }
      $RtnVal = ' SET ' . $RtnVal;

    } elsif ( $Type eq 'where' ) {
      $RtnVal = '';
      while (($Name, $Value) = each %Fields ) {
        if ( $RtnVal ) { $RtnVal = $RtnVal . ' AND ' }
        $RtnVal = $RtnVal . '(' . $Name . '="' . $Value . '")';
      }
      $RtnVal = ' WHERE ' . $RtnVal;

    }

  } else {

    print STDERR MySQLMakeID() . ": Parameter two is unsupported reference type '$Ref1Type'\n";
    return $RtnVal;

  }

  return $RtnVal;

}

sub BuildListsInt ($$;$) {

  my ($Type, $Ref1, $Ref2) = @_;
  my $Ref1Type = ref($Ref1);
  my $Ref2Type = ref($Ref2);

  my $Name;
  my $NameList;
  my $Value;
  my $ValueList;

  my %Fields;

  my $RtnVal;

  $Type = lc($Type);

  my $TypeIndex = index('values set where orderby', $Type);

  # print "Type '$Type', TypeIndex '$TypeIndex', Ref1Type '$Ref1Type', Ref1 '$Ref1'\n";

  if ( $TypeIndex < 0 ) {

    print STDERR MySQLMakeID() . ": $Type is not a a valid type. Use 'values', 'set' or 'where'\n";
    return $RtnVal;

  } elsif ( $Ref1Type eq '' ) {

    if ( $Type eq 'values' ) {
      $RtnVal = '(' . $Ref1 . ') VALUES (' . $Ref2 . ')';
    } elsif ( $Type eq 'set' ) {
      $RtnVal = ' SET ' . $Ref1;
    } elsif ( $Type eq 'where' ) {
      $RtnVal = ' WHERE ' . $Ref1;
    } elsif ( $Type eq 'orderby' ) {
      $RtnVal = ' ORDER BY ' . $Ref1;
    }

  } elsif ( $Ref1Type eq 'SCALAR' ) {

    if ( $Type eq 'values' ) {
      $RtnVal = '(' . $$Ref1 . ') VALUES (' . $$Ref2 . ')';
    } elsif ( $Type eq 'set' ) {
      $RtnVal = ' SET ' . $$Ref1;
    } elsif ( $Type eq 'where' ) {
      $RtnVal = ' WHERE ' . $$Ref1;
    } elsif ( $Type eq 'orderby' ) {
      $RtnVal = ' ORDER BY ' . $$Ref1;
    }

  } elsif ( $Ref1Type eq 'HASH' ) {

    for $Name ( keys(%$Ref1) )  {

      $Value = $Ref1->{$Name};

      if ( ! defined($Value) ) { $Value  =  '' }                 # Make sure value is defined
      else                     { $Value  =~ s/(["',\\])/\\$1/g } # Make sure that MySQL special chars are escaped

      $Fields{$Name} = $Value;

    }

    if ( $Type eq 'values' ) {

      while (($Name, $Value) = each %Fields ) {
        if ( $NameList ) {
          $NameList  = $NameList  . ','  . $Name;
          $ValueList = $ValueList . ',"' . $Value . '"';
        } else {
          $NameList  = $Name;
          $ValueList = '"' . $Value . '"';
        }
      }

      $RtnVal = " ($NameList) VALUES ($ValueList)";

    } elsif ( $Type eq 'set' ) {
      $RtnVal = '';
      while (($Name, $Value) = each %Fields ) {
        if ( $RtnVal ) { $RtnVal = $RtnVal . ',' }
        $RtnVal = $RtnVal . $Name . '="' . $Value . '"';
      }
      $RtnVal = ' SET ' . $RtnVal;

    } elsif ( $Type eq 'where' ) {
      $RtnVal = '';
      while (($Name, $Value) = each %Fields ) {
        if ( $RtnVal ) { $RtnVal = $RtnVal . ' AND ' }
        $RtnVal = $RtnVal . '(' . $Name . '=' . $Value . ')';
      }
      $RtnVal = ' WHERE ' . $RtnVal;

    }

  } else {

    print STDERR MySQLMakeID() . ": Parameter two is unsupported reference type '$Ref1Type'\n";
    return $RtnVal;

  }

  return $RtnVal;

}

最佳答案

你问了两个问题。

如何捕获 MySQL 警告？

1)If the statement executes but there is a mysql warning I want to capture that to last_sql_warning.txt

要做到这一点，最简单的方法就是简单地将 MySQL 警告升级为错误，您已经知道可以记录这些错误。这将达到目的:$DBHandle->do(q|SET sql_mode='traditional'|);

更难的方法 是通过SHOW WARNINGS 枚举警告，您可以检查mysql_warning_count 属性是否报告遇到警告。然而，在撰写本文时，DBD::mysql 只为语句(而非数据库)句柄公开了该属性。

更新 DBD::mysql 自 4.025 (2013-11-05) 开始支持数据库句柄上的此属性，因此下面的代码可以简化为$dbh->{mysql_warning_count} 检查。

因此，您可能会这样做:

my $warnings;
my $ok = eval {
           my $sth = $DBHandle->prepare($MySQLCmd);
           $sth->execute();
           $warnings = $sth->{mysql_warning_count};
           1;
         };

unless ($ok) {                   # Some error encountered
  ... # log it
} elsif ($warnings) {            # Some warning(s) encountered
  ... # open log file

  my $warnings = $DBHandle->selectall_arrayref('SHOW WARNINGS');
  for my $row (@$warnings) {
    # @$row is something like ('Warning', 1265, "Data truncated for column 'col' at row 1")
    ... # log it
  }
}

如何重试由于特定错误而失败的语句？

2)If the statement failes do to a lock timeout i would like to re-submit the query up to two times

在您的错误处理分支中，检查 $DBHandle->err 您关心的 MySQL 错误代码(可能编号为 1205，ER_LOCK_WAIT_TIMEOUT )，并根据需要重试。

关于mysql - DBI - Perl - 记录 MySQL 警告，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7946655/

文章推荐： xcode - 通过 Swift 访问 MacOS 中的键盘设置

文章推荐： mysql - 用于替换 MySQL 查询中的 GREATEST() 的标准 SQL

文章推荐： swift - 如何将此函数输出为一个简单的字符串？

文章推荐： ios - 具有可变图像大小和 AutoLayout 的 UICollectionViewCell

dbi - JDBI:dbi.onDemand 是否比使用 dbi.open 更受欢迎？
我在我的应用程序中使用 JDBI 和 Dropwizard。我已经使用 dbi.open 命令获取了一个 DAO 实例，然后使用它来运行各种查询。在“finally” block 中，我使用 dao.
perl - 为什么即使我已将 DBI 调用包装在 eval 中，我仍会在控制台上看到 DBI 错误？
我有一个在 eval 中运行的数据库查询，以捕获错误。问题是错误消息正在输出到控制台，即使它被困住了。如何阻止错误消息执行此操作，因为我想自己解析它并吐回我自己的消息？ my $dbh = DBI->
mysql - DBI::DatabaseError:用户错误访问被拒绝当使用 Ruby DBI 连接远程 Mysql 服务器时
这是我使用 DBI 的示例代码: db = DBI.connect("DBI:Mysql:database=testdatabase;host=testhost;port=30012", "testu
mysql - 在 Windows-7-x64 上使用 DBI Perl 和 MySql 未定义 $DBI::errstr
使用: MySQL 5.5ActivePerl v5.14.1Windows 7 64 位以下脚本在执行调用失败时有一个未定义的 $DBI::errstr: #!c:/perl/bin/perl.e
php - C :\wamp\apps\phpmyadmin3. 5.1\libraries\dbi\mysqli.dbi.lib.php on line 8 中超过最大执行时间 30 秒
这是整个错误 SCREAM: Error suppression ignored for ( ! ) Fatal error: Maximum execution time of 30 seconds
Perl DBI-捕获错误
在Perl中捕获任何DBI错误的最佳方法是什么？例如，如果由于插入的值中包含非法字符而导致插入失败，那么如何使脚本不失败，但是如何捕获错误并进行适当处理。我不想做“死”，因为我不想停止脚本的执行。
Perl DBI 连接和执行超时
在工作中，我们有一个 DBA，他说他的 RAC 工作得很好，但事实并非如此。像 Toad 或 SQL Developer 这样的 SQL IDE 会随机断开它们的连接(我怀疑是因为 RAC 的网络设置
perl-dbi - 检索已执行的语句
所以我有这个非常简化的片段: @cmd_arry = ("Bob Newhart", "54"); $sqlCmd = "UPDATE foobar SET name = ?, age = ?"; $
perl - DBI-> connect上的错误处理
除了使用标准代码模具“无法连接:$ DBI::errstr\n”来处理错误之外，是否可以编写如下的自定义代码？标准: $dbstore = DBI->connect($dsn, $user, $pw
perl - DBI 在获取行时可以推断或获知数字列类型吗？
假设我正在查询一个类似于以下内容的表: $dbh->selectrow_hashref('SELECT id, name FROM foos WHERE name = "bar"'); 当然，id将是
perl DBI 内存泄漏
我在长时间运行的 perl 脚本中遇到了一些内存泄漏问题，其中 perl 占用的内存量继续增长。因此，我尝试使用 Devel::Leak追踪泄漏。我发现每当我调用 DBI的prepare方法，Deve
Perl DBI 插入和选择
我想从表中复制一条记录，修改记录中的一些字段并插入到同一个表中。该表有 90 列。考虑在一个语句中使用 insert..select 但有 90 列，我需要在选择查询中告诉列名。我怎样才能在 per
perl DBI 和占位符
我有这个查询 select * from table where ID in (1,2,3,5...) 如何使用占位符使用 DBI 构建此查询？例如 : my @list = (1, 2, 3, 4
Perl Dbi 和存储过程
如何使用 perl 和 fbi 针对 sql server 检索存储过程的返回值？有人可以举个例子吗？最佳答案 DBD::ODBC t/dir 中有示例(参见 20SqlServer.t)。基本上你
perl dbi : fetchrow_arrayref
实际上我已经执行了postgres查询，假设它返回了10行。现在我有了语句处理程序( $sth )。 print Dumper $sth->fetchrow_arrayref; print Dumpe
dbi - [perl]如何强制perl在我自己的路径中使用模块？
我想让 perl 在我自己的路径中使用 DBI 模块(假设，/home/users/zdd/perl5/lib/DBI)，但是系统也有一个 DBI 模块，它是/usr/lib/perl5/库/DBI。
perl dbi : fetchrow_arrayref
实际上我已经执行了postgres查询，假设它返回了10行。现在我有了语句处理程序( $sth )。 print Dumper $sth->fetchrow_arrayref; print Dumpe
perl - DBI 的正斜杠问题
我不熟悉在 perl 脚本中使用 DBI 进行 SQL 查询。我遇到的问题与具有正斜杠的字段中的数据有关。我想使用变量作为 where 子句的输入，但它正在做 DBI 打算用正斜杠做的事情:停止查询。
Perl DBI 语句句柄和错误处理
假设我有一个连接到数据库的子例程。然后我想进行查询并接收输出并对其进行处理，当然，但是如果查询无效怎么办？所以让我们假设我有类似的东西: $dbh = DBI->connect(, , ); $qu
perl DBI 和准备好的语句名称冲突
我正在使用 Perl 的 DBI 进行 postgreSQL 访问，我注意到当我有多个并发进程准备同一个查询时，它们似乎在服务器上以相同的准备语句名称结束，从而产生了冲突。 2014-02-10 10

可可西里

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城