[sql-web.git] / cron / checkProcesses.php

<?php

@chdir(dirname(__FILE__).'/../');
require_once('mitsql.cfg.php');
require_once('mitsql.lib.php');
isOffline() && exit;

define('MAX_PROC_TIME', 100);

$baddbs = $badusers = $bad = array();
$result = mysql_query('SHOW FULL PROCESSLIST');
while ($row = mysql_fetch_assoc($result)){
        if ($row['Time']>MAX_PROC_TIME && $row['Command']!='Sleep') {
                $bad[] = $row;
                $baddbs[] = $row['db'];
                $badusers[] = $row['User'];
        }
}
mysql_free_result($result);

foreach($bad as $badproc) {
        mysql_query('KILL '.$badproc['Id']);
    continue;
        $mailtos = $mailnames = array();
        $baddb = mysql_escape_string($badproc['db']);
        if (empty($baddb)) continue;
        $badtime = $badproc['Time'];
        $badquery = $badproc['Info'];
        $sql = "SELECT User.UserId,User.Name,User.Email
                        FROM `User`
                        NATURAL JOIN DBOwner
                        NATURAL JOIN DB
                        WHERE DB.Name = '$baddb'
                          AND User.UL < 10";
        $r = fetchRows(DBSelect($sql),'UserId');
        foreach($r as $addy) {
                $mailtos[] = $addy['Email'];
                $mailnames[] = $addy['Name'];
        }
        if (empty($mailtos)) {
                echo "No email found for $baddb\n";
                continue;
        }
        $mailto = implode(', ',$mailtos);
        $mailname = implode(', ',$mailnames);
        $mailsubj = "[sql.mit.edu] Slow Query on $baddb";
        $mailbody = "Dear $mailname:

A slow query was found on your database: $baddb
It took $badtime seconds and has been aborted.

We do not allow inefficient SQL queries to run this long. Your query has
been appended to this message for your records. Please optimize your
queries to avoid having your queries killed in the future. If you have any
questions, please contact sql@mit.edu.

The SQL Service
Email: <sql@mit.edu>
Web: http://sql.mit.edu/

---------------------------------------------------------------------------

$badquery";

        mysql_query('KILL '.$badproc['Id']);
        #mail($mailto,$mailsubj,$mailbody,"From: SQL Service <sql@mit.edu>\r\nBcc: sql@mit.edu\r\n");
    echo $mailbody;
}

?>