I'm working on a FreeBSD 5.4 and I'm having problems successfully trapping errors that occur in communication with a mysql server. My perl script is unable to detect an "aborted connection" or "got error reading communication packets" message from the mysql server.

The mysql mysql.err logfile has the following messages when I run my test: 
060727 15:44:16 Aborted connection 109 to db: 'test_0_8' user: 'test' 
+host: `test.com' (Got an error reading communication packets)

[download]
This could indicate faulty hardware on my network. The code I am using error traps ALL mysql functions that I can, however, when this error occurs, my script hangs while ->execute()'ing a statement. I cannot recover it by error trapping or even using an eval{ alarm() } and ALRM signal handler.

What's really weird is that my script generally always hangs at the same spot ( $i = 386 ) when using the table "monitor_list" but doesn't hang when I'm not selecting from this table. My database tables look fine when I check them with "check table ____" or "myisamchk". How can I trap this nasty error from occuring?

Here is the code I am using. 
#!/usr/bin/perl

use strict;
use DBI;
use DBD::mysql;

my $dbh = db_connect();

$|++;

my $i = 0;
while( 1 ) {
    if ($i < 1000) {
        #    Do queries
        if( my $res = db_query("SELECT * FROM monitor_list WHERE id = 
+$i") ) {
            while( my $row = $res->fetchrow_hashref() ) {
                print "Failed to insert this record\n" unless db_query
+("INSERT INTO test VALUES ( $i, " . rand(6000) . ", " . rand(7000) . 
+" )");
                print "+";
            }
            print "$i ";
            $i+=3;
        }
    } else {
        #    Clear work
        if( db_query( "DELETE FROM test" ) ) {
            $i = 0;
            print "\n";
        } else {
            print "Failed to clear table\n";
        }
    }

}

sub db_query() {
    my $sql = $_[0];

    my $sth;
    eval {
        local $SIG{ALRM} = sub {die "timeout"};
        alarm( 4 );

        my($s, $m, $h) = (localtime)[0,1,2];
        print "$h:$m:$s ";
        if( ref $dbh ) {
            print "R";
            if( $dbh->ping() ) {
                print "P";
                if( $sth = $dbh->prepare($sql) or handle() ) {
                    print "S";
                    if( $sth->execute() or handle() ) {
                        print "E\n";
                    }
                }
            } else {
                $dbh = db_connect();
            }
        }
    };
    if( $@ ) {
        print "Timed out\n";
        return 0;
    }
    return $sth;
}

sub handle() {
    print "Got error: " . $dbh->err . " = " . $dbh->errstr . "\n";
    return 0;
}

sub db_connect() {
    my ($database, $hostname, $username, $port, $dsn, $user, $password
+, $driver, $dbh, $sth , $sql);

    $driver = "mysql";

    while( !$dbh ) {

        $dsn = "DBI:$driver:database=$database;host=$hostname;mysql_co
+nnect_timeout=3";
        if( $dbh = DBI->connect($dsn, $username, $password, { PrintErr
+or => 0, RaiseError => 0 }) ) {
            $dbh->{mysql_auto_reconnect} = 1;
            print "DB Connected\n";
            return( $dbh );
        } else {
            warn( $DBI::errstr );
            sleep(2);
        }
    }

    return( undef );

}

[download]

In reply to trapping mysql errors by vancetech

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.