aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--README.md21
-rw-r--r--mywatch.cabal8
-rw-r--r--sql/mywatch_kill.sql26
-rw-r--r--src/Application.hs26
-rw-r--r--static/mywatch.css3
-rw-r--r--static/mywatch.js122
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
+});
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-22 00:38:07 +0000