#!/usr/bin/perl -w

use DBI;
use strict;

$| = 1;

my $VERSION = 1.3;

#------------------------------------------------------------------------------
# $Id: check_mysql.pl,v 1.10 2005/08/09 03:01:17 groovis Exp $
#
# check_mysql.pl
#
# Provide a timeout for SELECT queries, and when the timeout is reached, kill
# the query and send a report to someone. For install/configuration
# instructions or to get the latest version, visit:
#       http://groovis.net/projects/check_mysql.html
#
#------------------------------------------------------------------------------


#------------------------------------------------------------------------------
# !!! Configure check time and timeout. These are both in seconds.

my $check =      5;	# check processes every $check seconds
my $slow_time =  60;	# stop processes that run for >= $slow_time seconds

# !!! Configure log file - All slow queries also get logged to this file

my $logfile =    "./check_mysql_query.log";	# log slow queries to this file

# !!! Configure the database connection parameters

my $db_string = "dbi:mysql:mysql";	# DBI resource to connect to
my $db_user =   "groovis";		# DBI username to connect as
my $db_pass =   "groovis";		# DBI password to connect with
my $report_to =  "groovis";		# email address to send reports to

# !!! Configure path to sendmail program

my $sendmail_bin = "/usr/sbin/sendmail";

#
#------------------------------------------------------------------------------


my ($dbh,$sth,$sth2,$thread,$state,$time,$query,$explain);

print "connecting\n";
my $opt = {
    'RaiseError'=>0,
    'PrintError'=>0
};
$dbh = DBI->connect($db_string,$db_user,$db_pass,$opt);
unless ($dbh) {
    print "Error: Unable to connect to database: $DBI::errstr\n";
    exit 1;
}

$SIG{'TERM'} = sub {
    print "caught sig TERM!\nexiting!\n";
    $dbh->disconnect;
    exit 1;
};

print "preparing\n";
unless ($sth = $dbh->prepare("show full processlist")) {
    print "error preparing query: $DBI::errstr\nexiting!\n";
    $dbh->disconnect;
    exit 1;
}

print "initialized.. starting loop\n";
while(1) {
    unless ($sth->execute) {
        print "statement execute failed: ".$sth->errstr."\nexiting!\n";
        last;
    }
    while(my @tmp = $sth->fetchrow) {
        $thread = $tmp[0];
        $state = $tmp[4];
        $time = $tmp[5];
        $query = $tmp[7];
        if ($state eq "Query" && $query !~ /^(INSERT|UPDATE|LOAD)/ && $query !~ /OUTFILE/ && $time >= $slow_time) {
            print "killing slow query thread=$thread state=$state time=$time\n";
            $dbh->do("kill $thread");
            unless (log_query($logfile,$query)) {
                print "log_query failed! exiting!\n";
                last;
            }
            unless ($explain = explain($dbh,$query)) {
                print "explain failed! exiting!\n";
                last;
            }
            unless (send_notify($sendmail_bin,$report_to,$thread,$time,$query,$explain)) {
                print "send_notify failed! exiting!\n";
                last;
            }
        }
    }
    sleep($check);
}

$sth->finish;
$dbh->disconnect;

exit 1;

sub send_notify {
    my ($sendmail,$report_to,$thread,$time,$query) = @_;
    unless (open(S,"|".$sendmail." -t")) {
        print "error opening sendmail: $!\n";
        return undef;
    }
    print S "To: $report_to\nSubject: MySQL Alert!\n\n";
    print S "Hi, this is the check_mysql script. I just killed a slow query on the server. ";
    print S "It was thread #$thread, and it was running for $time seconds when I killed it.\n\n";
    print S "$query\n\n$explain\n";
    close(S);
    return 1;
}

sub log_query {
    my ($file,$query) = @_;
    unless (open(O,">>".$file)) {
        print "error opening log file '$file': $!\n";
        return undef;
    }
    print O $query."\n-----\n";
    close(O);
    return 1;
}

sub explain {
    my ($dbh,$query) = @_;
    my $sth;
    unless ($sth = $dbh->prepare("EXPLAIN ".$query)) {
        print "explain failed: ".$sth->errstr."\n";
        return undef;
    }
    unless ($sth->execute) {
        print "explain execute failed: ".$sth->errstr."\n";
        return undef;
    }
    my $explain = ""; my $row = 1;
    while(my $r = $sth->fetchrow_hashref) {
        $explain .= "*************************** $row. row ***************************\n";
        foreach('id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra') {
            my $s1 = ""; foreach(1..(13-length($_))) { $s1 .= " "; }
            $explain .= $s1.$_.": ".($r->{$_}||"")."\n";
        }
    }
    $sth->finish;
    return $explain;
}

=pod

=head1 NAME

check_mysql.pl

=head1 DESCRIPTION

Enforce a timeout for SELECT queries on an MySQL server. Queries that reach the timeout are killed, and a report including the complete query and EXPLAIN output are emailed.

=head1 INSTALLATION

=over 4

=item 1

Create a special MySQL user for this script to use to connect to your database. The user should have a strong password and access control, as it is required that you grant the SUPER and PROCESS privileges to the user so that the script can view processes and kill queries.

=item 2

Edit the configurable items at the top of this script - Set the check frequency and timeout, database connection parameters, and the path to your system's sendmail program.

=back

=head1 USAGE

Simply run the program:

  chmod +x check_mysql.pl
  ./check_mysql.pl

The script will connect to MySQL and begin monitoring for slow queries. For running as a daemon, I suggest using daemontools or similar to start the script and keep it running.

=head1 PREREQUISITES

This script requires C<DBI> and C<DBD::mysql>.

=head1 SCRIPT CATEGORIES

UNIX/System_administration

=cut