It looks like mysql doesn't have the ability to set a query timeout. Is
this true? So in the mean time I created the following perl DBI script
to do it for me.
#!perl
# DBI service name
use constant DSN => "dbi:mysql:ABC:localhost";
# database username
use constant DBUSER => "root";
# database password
use constant DBPASS => "XYZ";
# seconds application should wait before checking again
use constant SLEEP => 5;
# maximum seconds before script kills a query
use constant TIMEOUT => 10;
# hash of usernames that will be checked
my %USERS = ( foo => 1 );
# END OF CONFIG
use strict;
use DBI;
my ($dbh, $read_sth, $kill_sth);
$dbh = DBI->connect(DSN,DBUSER,DBPASS,
{ PrintError => 1, RaiseError => 1, AutoCommit => 1 }) or die
$DBI::errstr;
$read_sth = $dbh->prepare("SHOW FULL PROCESSLIST");
$kill_sth = $dbh->prepare("KILL ?");
print STDERR "Killed Statements:\n";
while (1) {
$read_sth->execute();
while (my ($id, $user, $host, $db, $cmd, $time, $state, $info)
= $read_sth->fetchrow_array()) {
if (exists $USERS{$user} && $time > TIMEOUT &&
$cmd eq 'Query' && $state eq 'Sending data') {
print STDERR "Id: $id\nUser: $user\nHost: $host\nDB:
$db\nCommand: $cmd\nTime: $time\nState:
$state\nInfo:\n$info\n---------------------------------------------------------------------\n";
$kill_sth->execute($id);
}
}
sleep SLEEP;
}
