trapping mysql errors

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

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]

Comment on trapping mysql errors Select or Download Code

Replies are listed 'Best First'.
Re: trapping mysql errors by Khen1950fx (Canon) on Jul 28, 2006 at 04:17 UTC
It seems like a memory problem to me. Either the max_allowed_packet variable value is to small, or your queries require more memory than has been allocated for mysqld. Also, it could be that someone is trying to get on without authorization or with a bad password. See the MySQL Reference Manual for aborted connections: Aborted Connections	[reply]
Re: trapping mysql errors by jdtoronto (Prior) on Jul 28, 2006 at 04:32 UTC
Built in to DBI there is a fairly extensive tracing mechanism. You may find you can watch exactly what is going on from the Perl side using that facility. However, as Khen1950fx has suggested it does have the sound of server trouble written all over it! In cases where I have actually had server trouble it has been possible to evoke a similar, if not identical repsonse, when doing the same thing from the MySQL monitor. jdtoronto	[reply]
Re^2: trapping mysql errors by vancetech (Beadle) on Jul 28, 2006 at 16:43 UTC
I left the process over night and observed the problem where it halts at id #387... after a length of time (hours?) a SIG{ALRM} catches the execute statement. The script loops and $dbh->ping() determines the database is disconnected so it reconnects and hangs again with the same id #387. `61 362 +363 +364 +365 +366 367 368 369 370 371 372 373 374 375 376 377 + 378 379 380 381 382 383 384 385 386 387 Timed out 387 Timed out 387 DB Connected 387 Timed out 387 DB Connected 387 Timed out 387 DB Connected 387 Timed out 387 DB Connected` [download] My main question is: "Why isn't the $SIG{ALRM} catching this hang in a timely manner?" Ok I'm finding that it's possibly a networking issue that is only local to the machine I'm running on. When I use my FQDN as the database host ( which traverses the router ) I get this problem. When I use localhost as the database host, I do not get this problem. If I start the count at 387 it will hang right away including starting at the other numbers I have problems with ( $i == 402 \|\| $i == 553 \|\| $i == 571 \|\| $i == 623 ). From another PC on the same network, I am unable to replicate this error both using the FQDN and the internal IP address of the database server. My DBI trace doesn't show me anything other than that is is hanging on the execute statement: 387 -> ping for DBD::mysql::db (DBI::db=HASH(0x81a75f0)~0x81a6964) <- ping= 1 at ./test.pl line 65 via ./test.pl line 59 -> prepare for DBD::mysql::db (DBI::db=HASH(0x81a75f0)~0x81a6964 ' +SELECT * FROM monitor_list WHERE id = 387 LIMIT 1') dbih_setup_handle(DBI::st=HASH(0x81cd488)=>DBI::st=HASH(0x81cd428) +, DBD::mysql::st, 81cd0c8, Null!) dbih_make_com(DBI::db=HASH(0x81a6964), 81a6c04, DBD::mysql::st, 22 +4, 0) thr#0 dbih_setup_attrib(DBI::st=HASH(0x81cd428), Err, DBI::db=HASH(0x81a +6964)) SCALAR(0x815cdd8) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81cd428), State, DBI::db=HASH(0x8 +1a6964)) SCALAR(0x815ce38) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81cd428), Errstr, DBI::db=HASH(0x +81a6964)) SCALAR(0x815ce08) (already defined) dbih_setup_attrib(DBI::st=HASH(0x81cd428), TraceLevel, DBI::db=HAS +H(0x81a6964)) 15 (already defined) dbih_setup_attrib(DBI::st=HASH(0x81cd428), FetchHashKeyName, DBI:: +db=HASH(0x81a6964)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x81cd428), HandleSetErr, DBI::db=H +ASH(0x81a6964)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x81cd428), HandleError, DBI::db=HA +SH(0x81a6964)) undef (not defined) Setting mysql_use_result to 0 <- prepare= DBI::st=HASH(0x81cd488) at ./test.pl line 65 via ./tes +t.pl line 59 -> execute for DBD::mysql::st (DBI::st=HASH(0x81cd488)~0x81cd428) -> dbd_st_execute for 081a6bc8 -> mysql_st_interal_execute [download] Thanks for your responses, Monks. I couldn't do it without you.	[reply] [d/l] [select]
Re^2: trapping mysql errors by vancetech (Beadle) on Jul 28, 2006 at 17:33 UTC
I am unable to replicate the problem in the mysql monitor. In diagnosing the problem I duplicated the table into a new table and re-ran the program. It now hangs on different ID's. If I delete that ID (and one more it had a problem with) the program runs fine! I have found that the script is actually having problems reading the last column in my table (only when using the FQDN as the database host, and not when using localhost)! If I select EVERY column by name instead of '*' and omit the last column in the table, the script runs normally. The column is a varchar(22) and doesn't matter what I alter it to be. What could possibly be happening here? I'd think it points to table corruption, but any checks I run on the table come up fine.	[reply]