handling mysql errors
A test script I'm using to write better error handling routines for mysql work is having problems with one of my test data tables. My script hangs upon "executing" a simple SQL statement "SELECT * FROM monitor_list WHERE id = $id" where $id = 387 or 402 or 553 or 571 or 623.
Is my table corrupt? I've run myiasmchk on it and don't see any problems:
Checking MyISAM file: test_0_8/monitor_list.MYI
Data records: 311 Deleted blocks: 6
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 61% Packed: 0% Max levels: 2
Total: Keyblocks used: 61% Packed: 0%
- check records and index references
Records: 311 M.recordlength: 126 Packed: 80%
Recordspace used: 99% Empty space: 1% Blocks/Record: 1.03
Record blocks: 321 Delete blocks: 6
Record data: 39214 Deleted data: 228
Lost space: 374 Linkdata: 1280
User time 0.00, System time 0.00
Maximum resident set size 1296, Integral resident set size 700
Non-physical pagefaults 117, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 0, Involuntary context switches 3
I've tried to install a SIG{ALRM} handler to break out of this "hang" but control is never returned to my script. Why wouldn't I be able to restore control to my script? Here is the code in question.
#!/usr/bin/perl
use strict;
use DBI;
use DBD::mysql;
my $dbh = db_connect();
$|++;
my $i = 0;
while( 1 ) {
if ($i < 1000) {
# Do queries
print "$i ";
if( my $res = db_query("SELECT * FROM monitor_list WHERE id = " . int($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 "+";
}
$i++;
}
} 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_connect_timeout=3";
if( $dbh = DBI->connect($dsn, $username, $password, { PrintError => 0, RaiseError => 0 }) ) {
$dbh->{mysql_auto_reconnect} = 0;
print "DB Connected\n";
return( $dbh );
} else {
warn( $DBI::errstr );
sleep(2);
}
}
return( undef );
}