[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 ___ 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
Re: [Maria-developers] More Secure Kill Command
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 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.ber...@gmail.com http://www.ebergen.net ___ 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
Re: [Maria-developers] More Secure Kill Command
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
Re: [Maria-developers] More Secure Kill Command
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 ___ 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
Re: [Maria-developers] More Secure Kill Command
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 > 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 ___ 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
Re: [Maria-developers] More Secure Kill Command
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 (-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 or KILL QUERY ? thanks guys :) ___ 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
Re: [Maria-developers] More Secure Kill Command
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] " is well know in mysql world, changing it to "KILL [QUERY | CONNETION] " 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 or something compatible with the well know command "KILL [CONNECTION | QUERY] ", maybe KILL [CONNECTION | QUERY] [ | QUERY | COMMAND | COMMAND ] ... well just a point of view ... ___ 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
Re: [Maria-developers] More Secure Kill Command
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 > or > KILL QUERY > ? 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 ___ 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
Re: [Maria-developers] More Secure Kill Command
Hi, Roberto! On Aug 17, Roberto Spadim wrote: > Hi sergei, > What about give two informations, thread id and query id > If thread id and query id is equal, kill it There's no need to. Query id identifies the query and the connection unambiguously. Specifying also a thread id is redundant. Regards, Sergei ___ 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
Re: [Maria-developers] More Secure Kill Command
nice, but... i was locking the mysql protocol... http://dev.mysql.com/doc/internals/en/com-process-kill.html#packet-COM_PROCESS_KILL it say that we kill a process (ok it's not a SQL command (COM_QUERY)) in few less words... we could add a SQL command to KILL ID (nice! we don't need thread id + query id, i like some redundant information to make my self more secure :P, but since query id can be a 64bits value i think i will not have time to overflow the query id and get the same query id in less than 1 second) and we could add a new protocol command (i don't know if it's nice... because add this is incompatible with mysql protocol, and probably will only work with mariadb client library...), but, if we want to add it, could be nice add a new parameter (optional) about the query id, instead of only KILL and thread_id, add a KILL null (or 0 if thread id can't be 0), and a query id, to KILL protocol ___ 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
Re: [Maria-developers] More Secure Kill Command
Hi, Roberto! On Aug 17, Roberto Spadim wrote: > nice, but... i was locking the mysql protocol... > http://dev.mysql.com/doc/internals/en/com-process-kill.html#packet-COM_PROCESS_KILL > it say that we kill a process (ok it's not a SQL command (COM_QUERY)) > in few less words... I don't see any reason why I should care about kill with the query id using the protocol command COM_PROCESS_KILL. SQL statement KILL QUERY ID is enough for most practical purposes. Regards, Sergei ___ 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
Re: [Maria-developers] More Secure Kill Command
hi sergei =D nice! i don't see reasons too :D just to don't forget about it, and don't add more work :) could / should we create a MDEV for it? ___ 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
Re: [Maria-developers] More Secure Kill Command
i write one MDEV (hehe sorry don't wait you reply), check if it's nice, if not just close it, or change description =] https://mariadb.atlassian.net/browse/MDEV-4911 ___ 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
Re: [Maria-developers] More Secure Kill Command
sergei... one more point... i was looking the mk-kill http://linux.die.net/man/1/mk-kill there's some cases where we want to kill a thread that's no running any query and we need to kill it only when it is not running any query, in other words i can't use kill thread id, i must use kill query id the point here is "how query id is increased?" for example.. (query id = 0 thread id = 10) <- kill query id =0 [query received] query id ++ (query id = 1 thread id = 10) SELECT * FROM TABLE <- kill query id =1 [query done] (query id = 1 thread id = 10) <- kill query id =1 [query received] query id ++ (query id = 2 thread id = 10) SELECT * FROM TABLE <- kill query id =2 [query done] (query id = 2 thread id = 10) <- kill query id =2 (query id = 2 thread id = 10) <- kill query id =2 if i send a KILL QUERY ID = 2, i will only kill the last SELECT, or if the select is done, i will kill the thread with the last executed query id = 2 see the problem? the query id can't be reseted, and it MUST be increased only when we START a new query, not at the end of query, example: (query id = 0 thread id = 10) <- kill query id =0 [query received] (query id = 0 thread id = 10) *SELECT * FROM TABLE <- kill query id =0* query id ++ (query id = 1 thread id = 10) [query done] *(query id = 1 thread id = 10) <- **kill query id =1 * [query received] (query id = 1 thread id = 10) *SELECT * FROM TABLE <- kill query id =1 * query id ++ (query id = 2 thread id = 10) [query done] (query id = 2 thread id = 10) <- kill query id =2 --- Is the QUERY ID increased ONLY WHEN A QUERY START, AND the QUERY ID isn't reseted AFTER query execution? If yes, this is ok, we can kill a thread that is not running a query without problems using query id in this case no problems... bye ___ 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
Re: [Maria-developers] More Secure Kill Command
humm... i will think more about this... maybe i'm too crazy (and unlucky) to understand that the order isn't important... i will check again what i write in last email... please check too and tell me if the order of increase isn't a problem 2013/8/17 Roberto Spadim > sergei... one more point... i was looking the mk-kill > http://linux.die.net/man/1/mk-kill > > there's some cases where we want to kill a thread that's no running any > query and we need to kill it only when it is not running any query, in > other words i can't use kill thread id, i must use kill query id > the point here is "how query id is increased?" for example.. > > > (query id = 0 thread id = 10) <- kill query id =0 > > [query received] > query id ++ (query id = 1 thread id = 10) > SELECT * FROM TABLE <- kill query id =1 > [query done] > (query id = 1 thread id = 10) <- kill query id =1 > > > [query received] > query id ++ (query id = 2 thread id = 10) > SELECT * FROM TABLE <- kill query id =2 > [query done] > (query id = 2 thread id = 10) <- kill query id =2 > > > (query id = 2 thread id = 10) <- kill query id =2 > > > if i send a KILL QUERY ID = 2, i will only kill the last SELECT, or if the > select is done, i will kill the thread with the last executed query id = 2 > > see the problem? the query id can't be reseted, and it MUST be increased > only when we START a new query, not at the end of query, example: > > > > > (query id = 0 thread id = 10) <- kill query id =0 > > [query received] (query id = 0 thread id = 10) > *SELECT * FROM TABLE <- kill query id =0* > query id ++ (query id = 1 thread id = 10) > [query done] > *(query id = 1 thread id = 10) <- **kill query id =1 * > > > [query received] (query id = 1 thread id = 10) > *SELECT * FROM TABLE <- kill query id =1 * > query id ++ (query id = 2 thread id = 10) > [query done] > (query id = 2 thread id = 10) <- kill query id =2 > > > --- > Is the QUERY ID increased ONLY WHEN A QUERY START, AND the QUERY ID isn't > reseted AFTER query execution? > > If yes, this is ok, we can kill a thread that is not running a query > without problems using query id > in this case no problems... > > bye > -- Roberto Spadim SPAEmpresarial ___ 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
Re: [Maria-developers] More Secure Kill Command
I end my "mind work" :P YES we need that query id increment ONLY at START of query... We CAN'T kill a FUTURE query, we MUST kill the CURRENT QUERY running, or the CURRENT THREAD Increment at query end will kill future queries, check : 1) id=0 2) select (id=0) 3) (id=1) 4) select ... (id=1) if i send kill query id =1, at third line, i will kill the thread, if i wait more time i will kill the query running (N I CAN'T DO THIS), but in this order: 1) id=0 2) select (id=1) 3) (id=1) 4) select ... (id=2) if i send kill query id=1, at third line, i will kill the thread, if i wait more time i WILL NOT kill query and WILL NOT KILL the thread, NICE! THAT'S THE IDEA! ___ 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
Re: [Maria-developers] More Secure Kill Command
patch sent :D please check it at MDEV-4916 2013/8/17 Roberto Spadim > I end my "mind work" :P > > YES we need that query id increment ONLY at START of query... > > We CAN'T kill a FUTURE query, we MUST kill the CURRENT QUERY running, or > the CURRENT THREAD > Increment at query end will kill future queries, check : > > 1) id=0 > 2) select (id=0) > 3) (id=1) > 4) select ... (id=1) > > if i send kill query id =1, at third line, i will kill the thread, if i > wait more time i will kill the query running (N I CAN'T DO THIS), but > in this order: > > 1) id=0 > 2) select (id=1) > 3) (id=1) > 4) select ... (id=2) > > if i send kill query id=1, at third line, i will kill the thread, if i > wait more time i WILL NOT kill query and WILL NOT KILL the thread, NICE! > THAT'S THE IDEA! > > -- Roberto Spadim SPAEmpresarial ___ 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
Re: [Maria-developers] More Secure Kill Command
some things that must be done: 1) the KILL QUERY_ID 1,2,3,4,5 don't work, that's my first patch with sql_yacc.yy maybe i done something wrong since %expect changed +8 numbers 2) the QUERY_ID isn't what i want but well worked hehehehe, maybe change the syntax and make it more beautiful 3) there's some warnings in gcc that must be checked (signed compare with unsigned), and others case) that don't use the right flag (i add a bit = 16 for KILL_QUERY command) 4) the KILL QUERY_ID , return a error something like "thread id not found", must be "query id not found" 5) i don't remember if have other problem... ___ 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
