[Maria-developers] More Secure Kill Command
Hi guys i was mailing at maria-discuss, but i think it a new feature, and want some view point from developers... check this problem: connection 1) big select... connection 2) show processlist connection 1) other query connection 2) kill connection 1 id (i take +- 1 second between show processlist and this command) connection 1) killed the problem? the kill connection 1 (KILL thread_id) killed the wrong query could we implement something like: KILL [CONNECTION | QUERY] thread_id [QUERY "some relative unique query id in this database"] the relative unique query id, could be: substr(sha1 or md5 of (thread_id + query start time),0,6) (6 hex numbers is good?) it's like a git small hash commit id, maybe we could make it more unique... but since i will not use it very often, i think a less intensive work is nice here... a global query id could add more one lock for every query... example: KILL 1 QUERY "abcdef" the "abcdef" = substr(MD5( thread id + query start time ),0,6) we could add client ip or others unique information about this query, but something that don't increase (ok just a little) the load of a show processlist / select * from information_schema thanks -- Roberto Spadim
As part of adding per query table/index statistics I added a per thread query_id. This would serve your purpose too. You could do kill connection_id query_id and be guaranteed of killing the query you want on a specific connection. On Fri, Aug 16, 2013 at 2:31 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
Hi guys i was mailing at maria-discuss, but i think it a new feature, and want some view point from developers... check this problem:
connection 1) big select... connection 2) show processlist connection 1) other query connection 2) kill connection 1 id (i take +- 1 second between show processlist and this command) connection 1) killed
the problem? the kill connection 1 (KILL thread_id) killed the wrong query could we implement something like:
KILL [CONNECTION | QUERY] thread_id [QUERY "some relative unique query id in this database"]
the relative unique query id, could be: substr(sha1 or md5 of (thread_id + query start time),0,6) (6 hex numbers is good?) it's like a git small hash commit id, maybe we could make it more unique... but since i will not use it very often, i think a less intensive work is nice here... a global query id could add more one lock for every query...
example: KILL 1 QUERY "abcdef"
the "abcdef" = substr(MD5( thread id + query start time ),0,6)
we could add client ip or others unique information about this query, but something that don't increase (ok just a little) the load of a show processlist / select * from information_schema
thanks
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Eric Bergen eric.bergen@gmail.com http://www.ebergen.net
Hi, Eric! On Aug 16, Eric Bergen wrote:
As part of adding per query table/index statistics I added a per thread query_id. This would serve your purpose too. You could do kill connection_id query_id and be guaranteed of killing the query you want on a specific connection.
Yes, my thought exactly. It'd be pretty easy to extend KILL to support killing by query_id, not by thread_id. Regards, Sergei
yeah i liked this idea about query_id too... thread_id + query_id is a nice solution (at least i think it will work) i was considering query start time too, but since kill isn't a command with very high frequency (kills/second), the query_id could solve the problem nicely the only problem i can see is: if thread id is low (example connection id = 50), and we have a server restart and at the same thread id (50), runs the same number of queries... well the probability is very very small... at least i think it is, and if we execute the KILL, we will get a connection lost from server... and we should execute the show processlist again... if we don't want the show processlist again, just add the query start time (YYYY-MM-DD HH:MM:SS), but for the first implementation query id is nice... the query_id is currently implemented? or should be implemented? sorry i'm asking without reading the source code ... about syntax, should it be KILL <thread_id> <query_id> or KILL <thread_id> QUERY <query id> ? thanks guys :)
Sorry Sergei i didn't read your email before sending more one... "Yes, my thought exactly. It'd be pretty easy to extend KILL to support killing by query_id, not by thread_id." some points that i think important... the "KILL [QUERY | CONNECTION] <thread_id>" is well know in mysql world, changing it to "KILL [QUERY | CONNETION] <query id>" will make some monitor softwares to not work anymode i think a new information to KILL is better, but since we have KILL [QUERY] option i think that QUERY keyword should be avoid.... KILL thread_id QUERY query_id, is nice since it easily tell what's the thread id, and it add a check before kill using query id, and don't add incompatibilty maybe a second command should be nicer if query_id is a "global" "auto increment" value, for example KILL QUERY_ID <query_id> or something compatible with the well know command "KILL [CONNECTION | QUERY] <thread_id>", maybe KILL [CONNECTION | QUERY] [<thread_id> | <thread_id> QUERY <query_id> | <thread_id> COMMAND <query_id> | COMMAND <query id>] ... well just a point of view ...
Hi, Roberto! On Aug 17, Roberto Spadim wrote:
yeah i liked this idea about query_id too...
the query_id is currently implemented? or should be implemented? sorry i'm asking without reading the source code ...
It is implemented. Currently, KILL works like (kill_one_thread function) while ((tmp=it++)) if (tmp->thread_id == id) break; to kill by query id, it should be simply while ((tmp=it++)) if (tmp->query_id == id) break; And some safety checks, of course. Not really a one-line change, but close.
about syntax, should it be KILL <thread_id> <query_id> or KILL <thread_id> QUERY <query id> ?
I don't have a good one. May be KILL QUERY ID nnn ? So that the full syntax would be KILL [ CONNECTION | QUERY [ ID ] ] nnn; Regards, Sergei
PEBKAC... If you want more safety then make a script like molly-guard that will ask you to confirm the kill command you want to execute to avoid mistakes. Le 16/08/2013 23:31, Roberto Spadim a écrit :
Hi guys i was mailing at maria-discuss, but i think it a new feature, and want some view point from developers... check this problem:
connection 1) big select... connection 2) show processlist connection 1) other query connection 2) kill connection 1 id (i take +- 1 second between show processlist and this command) connection 1) killed
the problem? the kill connection 1 (KILL thread_id) killed the wrong query could we implement something like:
KILL [CONNECTION | QUERY] thread_id [QUERY "some relative unique query id in this database"]
the relative unique query id, could be: substr(sha1 or md5 of (thread_id + query start time),0,6) (6 hex numbers is good?) it's like a git small hash commit id, maybe we could make it more unique... but since i will not use it very often, i think a less intensive work is nice here... a global query id could add more one lock for every query...
example: KILL 1 QUERY "abcdef"
the "abcdef" = substr(MD5( thread id + query start time ),0,6)
we could add client ip or others unique information about this query, but something that don't increase (ok just a little) the load of a show processlist / select * from information_schema
thanks
hi! i think it too complex.. i should send two SQL command!? i already know what thread and what query i want to kill... why should i send it twice? 2013/8/17 Jean Weisbuch <jean@phpnet.org>
PEBKAC...
If you want more safety then make a script like molly-guard that will ask you to confirm the kill command you want to execute to avoid mistakes.
Le 16/08/2013 23:31, Roberto Spadim a écrit :
Hi guys i was mailing at maria-discuss, but i think it a new feature, and want some view point from developers... check this problem:
connection 1) big select... connection 2) show processlist connection 1) other query connection 2) kill connection 1 id (i take +- 1 second between show processlist and this command) connection 1) killed
the problem? the kill connection 1 (KILL thread_id) killed the wrong query could we implement something like:
KILL [CONNECTION | QUERY] thread_id [QUERY "some relative unique query id in this database"]
the relative unique query id, could be: substr(sha1 or md5 of (thread_id + query start time),0,6) (6 hex numbers is good?) it's like a git small hash commit id, maybe we could make it more unique... but since i will not use it very often, i think a less intensive work is nice here... a global query id could add more one lock for every query...
example: KILL 1 QUERY "abcdef"
the "abcdef" = substr(MD5( thread id + query start time ),0,6)
we could add client ip or others unique information about this query, but something that don't increase (ok just a little) the load of a show processlist / select * from information_schema
thanks
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial
participants (4)
-
Eric Bergen -
Jean Weisbuch -
Roberto Spadim -
Sergei Golubchik