packages feed

mywatch 0.1.2 → 0.2.0

raw patch · 7 files changed

+183/−48 lines, 7 files

Files

ChangeLog.md view
@@ -1,9 +1,20 @@+0.2.0+=====++  * Allow killing queries via `CALL mysql.mywatch_kill(id)`. You should have+    this routine on your database server and grant MyWatch privilege to+    execute it. Provided a safe example of this function.++  * Minor improvements in UI++ 0.1.2 =====    * Use location hash for server name   * Compact server list (`display: inline-block`) + 0.1.1 ===== @@ -19,7 +30,8 @@     processes SSL options. SSL now works with MariaDB's     libmysqlclient. -  * Fixed parsing of `GRANT` queries (they have `NULL` states).+  * Fixed parsing of `GRANT` queries (they have `NULL` states)+  0.1.0 =====
README.md view
@@ -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@@ -65,11 +65,30 @@ !include /run/keys/my.secret.cnf ``` +Sproxy Configuration+==================== -Database Privileges-===================+* To access the service allow `GET` for `/`, `/static/%` and `/severlist.json`.+* To see processes on a particular MySQL server allow `GET` and `HEAD` for+  `/server/:server/processlist.json`.+* To kill processes allow `DELETE` for `/server/:server/process/%` -MyWatch needs the `PROCESS` privilege.++Database Configuration+======================++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
mywatch.cabal view
@@ -1,8 +1,9 @@ name: mywatch-version: 0.1.2-synopsis: View MySQL processes+version: 0.2.0+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>@@ -19,6 +20,9 @@   static/external/jquery-2.2.4.min.js   static/mywatch.css   static/mywatch.js++extra-source-files:+  sql/*.sql  source-repository head   type: git
+ sql/mywatch_kill.sql view
@@ -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 ;+
src/Application.hs view
@@ -16,9 +16,9 @@ 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.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 @@   -- 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@@ -70,6 +72,24 @@   case HM.lookup server ps of     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
static/mywatch.css view
@@ -1,4 +1,8 @@ #serverList li { display: inline-block; }+table td { text-align:center; }+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-query { white-space: pre-wrap; }+table#processList td.mywatch-number { text-align: right; }+table#processList td.mywatch-query { text-align: left; white-space: pre-wrap; }
static/mywatch.js view
@@ -4,14 +4,16 @@   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 @@     info.show();   } -  function switchServer(server) {+  function switchServer() {+    cankill = undefined;     clearInterval(interval);     if ('' !== server) {       document.title = server + ' — ' + 'MyWatch';@@ -31,8 +34,8 @@       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 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 @@                 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();