#!/usr/bin/perl

#
# MySQL Process Check and Limitation 
# http://www.cphelp.gr
#
use DBI;
use strict;
use POSIX qw(setsid);

$| = 1;
# daemonize the program
&daemonize;

my $check = 5;		
my $slow_time =  60;	
my $max_concurrent_connections = '5';	
my $warning_email = 'admin';	
my $check_interval = '60';
my $logfile = "/var/log/mycheck.log";

my $db_string = "dbi:mysql:mysql";	
my $db_user =   "";
my $db_pass =   "";

my $report_to =  "root";
my $sendmail_bin = "/usr/sbin/sendmail";
my %counter = ('aUser' => 0);
my $key = "";
my $abusers = "";
my $abuser_user = "";

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

print "Connecting\n";
my $opt = {
    'RaiseError'=>0,
    'PrintError'=>0
};

if ((!$db_user) or (!$db_pass)) {
        open(MYCNF,"/root/.my.cnf");
        while(<MYCNF>) {
                chomp;
                s/"//g;
                if(/user/is) { (undef,$db_user) = split('=', $_, 2); }
                if(/pass/is) { (undef,$db_pass) = split('=', $_, 2); }
        }
        close(MYCNF);
}
$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;
    }
	
	#
	# Check For Queries running for too long
	#	
    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;
            }
        }
	}
    
	#
	# Check For Excessive Conections
	#
	while (my $ref = $sth->fetchrow_hashref()) {
		$counter{$ref->{'User'}} ++;
    }
    
	foreach $key (keys %counter) {
		if($counter{$key} > $max_concurrent_connections) {
			$abusers .= "$key:$counter{$key}\n";
		}
	}
   
	foreach ($abusers) {
		chomp;
		($abuser_user,undef) = split(':', $_ , 2);
		my $watch = $dbh->prepare("SHOW PROCESSLIST");
        	$watch->execute();
        
		while (my $ref = $watch->fetchrow_hashref()) {
			if($ref->{'User'} eq $abuser_user) {
				print "Killed $abuser_user\n";
                $dbh->do("kill $ref->{'Id'}");
            }
        }
    }

	
	
	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 "Slow query killed 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;
}

sub daemonize {
    chdir '/'or die "Can't chdir to /: $!";
    open STDIN, '/dev/null' or die "Can't read /dev/null: $!";
    open STDOUT, '>>/dev/null' or die "Can't write to /dev/null: $!";
    open STDERR, '>>/dev/null' or die "Can't write to /dev/null: $!";
    defined(my $pid = fork) or die "Can't fork: $!";
    exit if $pid;
    setsid or die "Can't start a new session: $!";
    umask 0;
}