Allow killing queries

6 files changed, 160 insertions, 46 deletions

diff --git a/README.md b/README.md
index b5b2359..1397b99 100644
--- a/README.md
+++ b/README.md
@@ -1,8 +1,8 @@
 My Watch
 ========
 
-HTTP server for viewing MySQL queries on multiple servers. Designed to work
-behind [Sproxy](https://github.com/zalora/sproxy).
+Web application for viewing and killing MySQL queries on multiple
+servers. Designed to work behind [Sproxy](https://github.com/zalora/sproxy).
 
 
 Requirements
@@ -66,10 +66,21 @@ user = user2
 ```
 
 
-Database Privileges
-===================
+Database Configuration
+======================
 
-MyWatch needs the `PROCESS` privilege.
+MyWatch needs the [PROCESS](http://dev.mysql.com/doc/refman/en/privileges-provided.html#priv_process)
+privilege.
+
+To be able to kill queries a procedure named `mywatch_kill` must exist
+in the `mysql` database.  MyWatch invokes `CALL mysql.mywatch_kill(id)`
+for killing queries.  It's up to you how to implement this routine, for a
+safe example see [sql/mywatch_kill.sql](sql/mywatch_kill.sql). Of cource,
+MyWatch should be granted to execute this procedure.  If this procedure
+does not exist, MyWatch will not show this possibility in user interface,
+API will work, but result in Internal Server Error (HTTP 500). There is no
+filtering at application level, though the "kill" button may not be shown
+in some circumstances.
 
 
 Screenshots
diff --git a/mywatch.cabal b/mywatch.cabal
index c6c895f..48220d5 100644
--- a/mywatch.cabal
+++ b/mywatch.cabal
@@ -1,8 +1,9 @@
 name: mywatch
 version: 0.1.2
-synopsis: View MySQL processes
+synopsis: Web application to view and kill MySQL queries
 description:
-  View queries on multiple MySQL servers. Designed to work behind Sproxy.
+  View and kill queries on multiple MySQL servers.
+  Designed to work behind Sproxy.
 license: MIT
 license-file: LICENSE
 author: Igor Pashev <pashev.igor@gmail.com>
@@ -20,6 +21,9 @@ data-files:
   static/mywatch.css
   static/mywatch.js
 
+extra-source-files:
+  sql/*.sql
+
 source-repository head
   type: git
   location: https://github.com/zalora/mywatch.git
diff --git a/sql/mywatch_kill.sql b/sql/mywatch_kill.sql
new file mode 100644
index 0000000..0701467
--- /dev/null
+++ b/sql/mywatch_kill.sql
@@ -0,0 +1,26 @@
+DELIMITER $$
+
+DROP PROCEDURE IF EXISTS mysql.mywatch_kill $$
+CREATE PROCEDURE mysql.mywatch_kill (IN i BIGINT)
+  COMMENT 'Kill a query found in information_schema.processlist by ID'
+-- It seems reasonable that this procedure kills the connection, not just
+-- the query, because of the `DELETE` HTTP method on the process id. If the
+-- connection is not killed, the process id remains.
+BEGIN
+
+  DECLARE n BIGINT;
+
+  SELECT id INTO n
+    FROM information_schema.processlist
+    WHERE info IS NOT NULL
+    AND host <> '' -- means non-system user
+    AND id = i;
+
+  IF (n IS NOT NULL) THEN
+    KILL n; -- Use `CALL mysql.rds_kill(n);` on RDS
+  END IF;
+
+END $$
+
+DELIMITER ;
+
diff --git a/src/Application.hs b/src/Application.hs
index 899708e..64f8dab 100644
--- a/src/Application.hs
+++ b/src/Application.hs
@@ -16,9 +16,9 @@ import Data.Default.Class (def)
 import Data.List (sort)
 import Data.Pool (Pool, withResource)
 import Data.Text.Lazy (Text)
-import Database.MySQL.Simple (Connection, query_)
+import Database.MySQL.Simple (Connection, Only(..), query_, execute)
 import GHC.Generics (Generic)
-import Network.HTTP.Types (ok200, notFound404, StdMethod(HEAD))
+import Network.HTTP.Types (ok200, notFound404, notImplemented501, StdMethod(HEAD))
 import Network.Wai (Application, Middleware)
 import Network.Wai.Middleware.RequestLogger (Destination(Handle),
   mkRequestLogger, RequestLoggerSettings(destination, outputFormat),
@@ -26,7 +26,7 @@ import Network.Wai.Middleware.RequestLogger (Destination(Handle),
 import Network.Wai.Middleware.Static (addBase, hasPrefix, staticPolicy, (>->))
 import System.IO (stderr)
 import Web.Scotty (ScottyM, ActionM, middleware, json, file, addroute, get,
-  status, text, param, scottyApp)
+  delete, status, text, param, scottyApp)
 import qualified Data.HashMap.Lazy as HM
 
 import LogFormat (logFormat)
@@ -52,6 +52,8 @@ myProcess ps logger dataDir = do
   -- Used by client to see which servers are really allowed by Sproxy
   addroute HEAD "/server/:server/processlist.json" $ apiCanProcessList ps
 
+  delete "/server/:server/process/:id" $ apiKill ps
+
 data Process = Process {
     id      :: Int
   , user    :: Text
@@ -71,6 +73,24 @@ apiCanProcessList ps = do
     Nothing -> status notFound404 >> text server
     Just _  -> status ok200
 
+apiKill :: Pools -> ActionM ()
+apiKill ps = do
+  server <- param "server"
+  case HM.lookup server ps of
+    Nothing -> status notFound404 >> text server
+    Just p  -> do
+      id <- param "id"
+      if (id :: Int) == 0 then do
+        [ Only f ] <- withDB p $ \c ->
+                query_ c "SELECT COUNT(*) FROM information_schema.routines \
+                \WHERE routine_type = 'PROCEDURE' AND routine_schema = 'mysql' \
+                \AND routine_name = 'mywatch_kill'"
+        if (f::Int) > 0 then status ok200
+        else status notImplemented501 >> text "mywatch_kill"
+      else do
+        _ <- withDB p $ \c -> execute c "CALL mysql.mywatch_kill(?)" [ id ]
+        status ok200
+
 apiGetProcessList :: Pools -> ActionM ()
 apiGetProcessList ps = do
   server <- param "server"
diff --git a/static/mywatch.css b/static/mywatch.css
index 354c03a..aa0a761 100644
--- a/static/mywatch.css
+++ b/static/mywatch.css
@@ -1,4 +1,7 @@
 #serverList li { display: inline-block; }
+table td.btn { opacity:0; max-width:1em; }
 table th { text-align: center; }
+table tr:hover td.btn { opacity:1; }
 table#processList td { white-space: nowrap; }
+table#processList td.mywatch-number { text-align: right; }
 table#processList td.mywatch-query { white-space: pre-wrap; }
diff --git a/static/mywatch.js b/static/mywatch.js
index ec9400f..a575301 100644
--- a/static/mywatch.js
+++ b/static/mywatch.js
@@ -4,14 +4,16 @@ $(function() {
   var infoHead = $('#info>h1');
   var main = $('#main');
   var plBody = $('#processList>tbody');
+  var plHeader = $('#processList>thead>tr');
   var serverList = $('#serverList>ul');
 
-  var interval = null;
+  var cankill;
+  var interval;
+  var server;
 
-  var plCols = $('#processList>thead>tr>th')
-    .map(function() {
-      return $(this).text();
-    }).get();
+  var plCols = plHeader.children().map(function() {
+    return $(this).text();
+  }).get();
 
 
   function commonError(jqXHR, textStatus, errorThrown) {
@@ -23,7 +25,8 @@ $(function() {
     info.show();
   }
 
-  function switchServer(server) {
+  function switchServer() {
+    cankill = undefined;
     clearInterval(interval);
     if ('' !== server) {
       document.title = server + ' — ' + 'MyWatch';
@@ -31,8 +34,8 @@ $(function() {
       var s = $('a[href="#' + server + '"]');
       if (s) {
         s.parent().addClass('active');
-        getProcessList(server);
-        interval = setInterval(getProcessList, 60 * 1000, server);
+        getProcessList();
+        interval = setInterval(getProcessList, 60 * 1000);
       }
     } else {
       document.title = 'MyWatch';
@@ -40,37 +43,85 @@ $(function() {
   }
 
   function onHash() {
-    switchServer(location.hash.substring(1));
+    server = location.hash.substring(1);
+    switchServer();
   };
   window.onhashchange = onHash;
 
-  function getProcessList(server) {
+  function kill(id) {
     $.ajax({
-      url: "server/" + server + "/processlist.json",
-      method: "GET",
-      error: commonError,
-      success: function(procs) {
-        plBody.empty();
-        procs.map(function(p) {
-          var tr = $('<tr>');
-          plCols.map(function(c) {
-            var td = $('<td>');
-            td.text(p[c]);
-            if ('info' === c) {
-              td.addClass('mywatch-query');
-            } else if ('time' === c) {
-              td.css('text-align', 'right');
-            } else if ('id' === c) {
-              td.css('text-align', 'right');
-            }
-            tr.append(td);
-          });
-          plBody.append(tr);
+      url: 'server/' + server + '/process/' + id,
+      method: 'DELETE',
+      success: function() {
+        $('#' + id).fadeOut(300, function() {
+          $(this).remove();
         });
-        info.hide();
-        main.show();
       }
     });
+  }
+
+  function showProcessList(procs) {
+    plBody.empty();
+    if (cankill) {
+      if (!plHeader.children('#kill').length) {
+        plHeader.prepend('<th id="kill">');
+      }
+    } else {
+      plHeader.children('#kill').remove();
+    }
+    procs.map(function(p) {
+      var tr = $('<tr id="' + p['id'] + '">');
+      if (cankill) {
+        var td;
+        if (('' != p['host']) && (0 < p['time']) && ('Killed' != p['state'])) {
+          td = $('<td role="button" title="KILL" class="btn btn-danger btn-xs">&nbsp;</td>');
+          td.on('click', function() {
+            kill($(this).parent().attr('id'));
+          });
+        } else {
+          td = $('<td>');
+        }
+        tr.append(td);
+      }
+      plCols.map(function(c) {
+        var td = $('<td>');
+        if ('id' === c) {
+          td.addClass('mywatch-number');
+        } else if ('info' === c) {
+          td.addClass('mywatch-query');
+        } else if ('time' === c) {
+          td.addClass('mywatch-number');
+        }
+        td.text(p[c]);
+        tr.append(td);
+      });
+      plBody.append(tr);
+    });
+    info.hide();
+    main.show();
+  }
+
+  function getProcessList() {
+    function get() {
+      $.ajax({
+        url: 'server/' + server + '/processlist.json',
+        method: 'GET',
+        error: commonError,
+        success: showProcessList
+      });
+    }
+    if (typeof cankill === 'undefined') {
+      $.ajax({
+        url: 'server/' + server + '/process/0',
+        method: 'DELETE',
+        complete: function(jqXHR) {
+          cankill = (200 === jqXHR.status);
+          get();
+        }
+      });
+    } else {
+      get();
+    }
   };
 
   $.ajax({
@@ -95,9 +146,8 @@ $(function() {
                 serverList.append('<li><a href="#' + s + '">' + s + '</a></li>')
               });
               serverList.find('a').on('click', function() {
-                var s = $(this).text();
-                if ('#' + s === location.hash) {
-                  getProcessList(s);
+                if ($(this).text() === server) {
+                  getProcessList();
                 }
               });
               info.hide();
@@ -108,4 +158,4 @@ $(function() {
       });
     }
   });
-});
\ No newline at end of file
+});