From: Joe Presbrey <presbrey@mit.edu>
Date: Tue, 11 Oct 2005 09:34:26 +0000 (+0000)
Subject: admin list shows newest users first
X-Git-Url: http://andersk.mit.edu/gitweb/sql-web.git/commitdiff_plain/6eacda6de3cc184b0a7233cf10f8cdb2ce7a9779

admin list shows newest users first
cron daemon to check slow queries


git-svn-id: svn://presbrey.mit.edu/sql/mitsql@72 a142d4bd-2cfb-0310-9673-cb33a7e74f58
---

diff --git a/admin/main.php b/admin/main.php
index bdd8023..0852432 100755
--- a/admin/main.php
+++ b/admin/main.php
@@ -17,7 +17,8 @@ $sql = sprintf("SELECT User.UserId,Username,Name,dSignup,dLastCheck,nBytes,nByte
 				NATURAL JOIN UserStat
 				NATURAL JOIN UserQuota
 				WHERE bEnabled=1
-				ORDER BY dSignup");
+				ORDER BY dSignup DESC");
+//				ORDER BY nBytes DESC");
 $users = fetchRows(DBSelect($sql),'UserId');
 
 include 'tpl/head.php';
diff --git a/cron/checkProcesses.php b/cron/checkProcesses.php
new file mode 100755
index 0000000..b0ce051
--- /dev/null
+++ b/cron/checkProcesses.php
@@ -0,0 +1,61 @@
+<?php
+
+chdir('../');
+require_once('mitsql.cfg.php');
+require_once('mitsql.lib.php');
+define('MAX_PROC_TIME', 10);
+
+echo '<pre>';
+
+$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) {
+	$mailtos = $mailnames = array();
+	$baddb = mysql_escape_string($badproc['db']);
+	$badtime = $badproc['Time'];
+	$badquery = $badproc['Info'];
+	mysql_query('KILL '.$badproc['Id']);
+	if (empty($baddb)) continue;
+	$sql = "SELECT User.UserId,User.Name,User.Email
+			FROM `User`
+			NATURAL JOIN DBOwner
+			NATURAL JOIN DB
+			WHERE DB.Name = '$baddb'";
+	$r = fetchRows(DBSelect($sql),'UserId');
+	foreach($r as $addy) {
+		$mailtos[] = $addy['Email'];
+		$mailnames[] = $addy['Name'];
+	}
+	$mailto = implode(', ',$mailtos);
+	$mailname = implode(', ',$mailnames);
+	$mailsubj = "[sql] 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 to conserve server
+resources. Your query has been appended to this message for your records.
+Please optimize your queries to avoid having your query killed in the
+future. If you have any questions, please contact sql@mit.edu.
+
+The SQL Service is available at http://sql.mit.edu/
+
+---------------------------------------------------------------------------
+
+$badquery";
+
+	$mailto = 'jwp@mit.edu';
+	mail($mailto,$mailsubj,$mailbody,"From: SQL Service <sql@mit.edu>\r\nBcc: sql@mit.edu\r\n");
+}
+
+?>
diff --git a/global.act.php b/global.act.php
index 94e662d..467282b 100644
--- a/global.act.php
+++ b/global.act.php
@@ -48,4 +48,5 @@ if (isPost() || isset($i_refresh)) {
 	checkQuotas($UserId);
 	isset($i_refresh) && redirect('main');
 }
+
 ?>