andersk / sql-web.git / blame
| Commit | Line | Data |
|---|---|---|
| 6eacda6d JP |
1 | <?php |
| 2 | ||
| aaaa7a8f | 3 | @chdir(dirname(__FILE__).'/../'); |
| 6eacda6d JP |
4 | require_once('mitsql.cfg.php'); |
| 5 | require_once('mitsql.lib.php'); | |
| 6 | define('MAX_PROC_TIME', 10); | |
| 7 | ||
| 6eacda6d JP |
8 | $baddbs = $badusers = $bad = array(); |
| 9 | $result = mysql_query('SHOW FULL PROCESSLIST'); | |
| 10 | while ($row = mysql_fetch_assoc($result)){ | |
| 11 | if ($row['Time']>MAX_PROC_TIME && $row['Command']!='Sleep') { | |
| 12 | $bad[] = $row; | |
| 13 | $baddbs[] = $row['db']; | |
| 14 | $badusers[] = $row['User']; | |
| 15 | } | |
| 16 | } | |
| 17 | mysql_free_result($result); | |
| 18 | ||
| 19 | foreach($bad as $badproc) { | |
| 20 | $mailtos = $mailnames = array(); | |
| 21 | $baddb = mysql_escape_string($badproc['db']); | |
| 22 | $badtime = $badproc['Time']; | |
| 23 | $badquery = $badproc['Info']; | |
| 24 | mysql_query('KILL '.$badproc['Id']); | |
| 25 | if (empty($baddb)) continue; | |
| 26 | $sql = "SELECT User.UserId,User.Name,User.Email | |
| 27 | FROM `User` | |
| 28 | NATURAL JOIN DBOwner | |
| 29 | NATURAL JOIN DB | |
| 30 | WHERE DB.Name = '$baddb'"; | |
| 31 | $r = fetchRows(DBSelect($sql),'UserId'); | |
| 32 | foreach($r as $addy) { | |
| 33 | $mailtos[] = $addy['Email']; | |
| 34 | $mailnames[] = $addy['Name']; | |
| 35 | } | |
| 36 | $mailto = implode(', ',$mailtos); | |
| 37 | $mailname = implode(', ',$mailnames); | |
| 38 | $mailsubj = "[sql] Slow Query on $baddb"; | |
| 39 | $mailbody = "Dear $mailname: | |
| 40 | ||
| 41 | A slow query was found on your database: $baddb | |
| 42 | It took $badtime seconds and has been aborted. | |
| 43 | ||
| 44 | We do not allow inefficient SQL queries to run this long to conserve server | |
| 45 | resources. Your query has been appended to this message for your records. | |
| 46 | Please optimize your queries to avoid having your query killed in the | |
| 47 | future. If you have any questions, please contact sql@mit.edu. | |
| 48 | ||
| 49 | The SQL Service is available at http://sql.mit.edu/ | |
| 50 | ||
| 51 | --------------------------------------------------------------------------- | |
| 52 | ||
| 53 | $badquery"; | |
| 54 | ||
| 55 | $mailto = 'jwp@mit.edu'; | |
| 56 | mail($mailto,$mailsubj,$mailbody,"From: SQL Service <sql@mit.edu>\r\nBcc: sql@mit.edu\r\n"); | |
| 57 | } | |
| 58 | ||
| 59 | ?> |