From a1617ebaeb256b3d359cc9fe389b8001235100c2 Mon Sep 17 00:00:00 2001 From: Igor Pashev Date: Mon, 27 Jun 2016 03:51:58 +0800 Subject: Allow killing queries --- README.md | 21 ++++++--- mywatch.cabal | 8 +++- sql/mywatch_kill.sql | 26 +++++++++++ src/Application.hs | 26 +++++++++-- static/mywatch.css | 3 ++ static/mywatch.js | 122 ++++++++++++++++++++++++++++++++++++--------------- 6 files changed, 160 insertions(+), 46 deletions(-) create mode 100644 sql/mywatch_kill.sql 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 @@ -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 = $(''); - plCols.map(function(c) { - var 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(''); + } + } else { + plHeader.children('#kill').remove(); + } + procs.map(function(p) { + var tr = $(''); + if (cankill) { + var td; + if (('' != p['host']) && (0 < p['time']) && ('Killed' != p['state'])) { + td = $(' '); + td.on('click', function() { + kill($(this).parent().attr('id')); + }); + } else { + td = $(''); + } + tr.append(td); + } + plCols.map(function(c) { + var 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('
  • ' + s + '
    • ') }); 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 +}); -- cgit v1.2.3