[Maria-discuss] More secure Kill command
hi guys, i hit a problem in this exact second... some programs are running and i want to kill only one big query well i executed kill 17143 ok.. but when i execute this command, the query was done, and starting a second query... in other words... i killed the wrong query could we add a hash information to kill, to make it more secure and precise? for example... kill 17143 md5 "123456789......." where md5 = id + start time of query + others informations to make it relative unique maybe it exists and i don't know... any other idea how to solve this? thanks -- Roberto Spadim
if you killed the query and was the wrong one you should have to look better next time. I don't see probable that query ids had to be reused (unless we are talking here from a really uncommon scenario where you make queries fast enough to overflow and get old ids again...) I recommend you to use innotop [1] or mtop [2] to monitor your long-running queries and kill the right one. Also, there is other kind of information like user and host that you can use to "identify" the query. Remember, this is not MS Windows, so you won't be asked twice :D [1] http://code.google.com/p/innotop/ [2] http://mtop.sourceforge.net/ On Fri, Aug 16, 2013 at 4:48 PM, Roberto Spadim <roberto@spadim.com.br>wrote:
hi guys, i hit a problem in this exact second... some programs are running and i want to kill only one big query well i executed kill 17143
ok.. but when i execute this command, the query was done, and starting a second query... in other words... i killed the wrong query
could we add a hash information to kill, to make it more secure and precise? for example...
kill 17143 md5 "123456789......." where md5 = id + start time of query + others informations to make it relative unique
maybe it exists and i don't know... any other idea how to solve this?
thanks
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Gabriel Sosa Sometimes the questions are complicated and the answers are simple. -- Dr. Seuss
hi! well, what i'm doing... SELECT * FROM `information_schema`.`PROCESSLIST`; this give me a table with this fields: `ID` BIGINT(4) NOT NULL DEFAULT '0',`USER` VARCHAR(128) NOT NULL DEFAULT '',`HOST` VARCHAR(64) NOT NULL DEFAULT '',`DB` VARCHAR(64) NULL DEFAULT NULL,`COMMAND` VARCHAR(16) NOT NULL DEFAULT '',`TIME` INT(7) NOT NULL DEFAULT '0',`STATE` VARCHAR(64) NULL DEFAULT NULL,`INFO` LONGTEXT NULL,`TIME_MS` DECIMAL(22,3) NOT NULL DEFAULT '0.000',`STAGE` TINYINT(2) NOT NULL DEFAULT '0',`MAX_STAGE` TINYINT(2) NOT NULL DEFAULT '0',`PROGRESS` DECIMAL(7,3) NOT NULL DEFAULT '0.000',`MEMORY_USED` INT(7) NOT NULL DEFAULT '0',`EXAMINED_ROWS` INT(7) NOT NULL DEFAULT '0' I'm using the ID to kill... KILL <id here> the problem is, query is executed (no problem) but the ID don't change... in other words the ID here is the connection id, not the query id... there's a place to get the query id? and just kill it? i didn't read this at mysql docs, i will check again
you can get the query id running: show processlist; show full processlist; you will see the id as the first left column. Again, try installing innotop or mtop, they will make your like a lot easier... Regards On Fri, Aug 16, 2013 at 5:42 PM, Roberto Spadim <roberto@spadim.com.br>wrote:
hi! well, what i'm doing...
SELECT * FROM `information_schema`.`PROCESSLIST`;
this give me a table with this fields:
`ID` BIGINT(4) NOT NULL DEFAULT '0',`USER` VARCHAR(128) NOT NULL DEFAULT '',`HOST` VARCHAR(64) NOT NULL DEFAULT '',`DB` VARCHAR(64) NULL DEFAULT NULL,`COMMAND` VARCHAR(16) NOT NULL DEFAULT '',`TIME` INT(7) NOT NULL DEFAULT '0',`STATE` VARCHAR(64) NULL DEFAULT NULL,`INFO` LONGTEXT NULL,`TIME_MS` DECIMAL(22,3) NOT NULL DEFAULT '0.000',`STAGE` TINYINT(2) NOT NULL DEFAULT '0',`MAX_STAGE` TINYINT(2) NOT NULL DEFAULT '0',`PROGRESS` DECIMAL(7,3) NOT NULL DEFAULT '0.000',`MEMORY_USED` INT(7) NOT NULL DEFAULT '0',`EXAMINED_ROWS` INT(7) NOT NULL DEFAULT '0'
I'm using the ID to kill...
KILL <id here>
the problem is, query is executed (no problem) but the ID don't change... in other words the ID here is the connection id, not the query id...
there's a place to get the query id? and just kill it? i didn't read this at mysql docs, i will check again
-- Gabriel Sosa Sometimes the questions are complicated and the answers are simple. -- Dr. Seuss
hum same problem the ID is the connection id, not the query id... example if i execute: (1 = one connection, 2 = other connection) 1)mysql_connect... (create a connection id) 2)mysql_connect... (create a connection id) 1)show processlist (i will see 1 and 2 connection id) 1) select * from big table 2)show processlist -> let's tell it return connection: 1)id = 10, 2)id = 11 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 2)show processlist -> it return connection: 1)id = 10, 2)id = 11 see? there's no change at id... maybe expose the query id solve this problem.. (in this case it should return ~8 to (1), and ~2 to (2)) i'm using mariadb 10.0.3 here any idea?
did you try what I told you? the *show processlist;* command will show you the query id. On Fri, Aug 16, 2013 at 5:54 PM, Roberto Spadim <roberto@spadim.com.br>wrote:
hum same problem the ID is the connection id, not the query id... example
if i execute: (1 = one connection, 2 = other connection) 1)mysql_connect... (create a connection id) 2)mysql_connect... (create a connection id) 1)show processlist (i will see 1 and 2 connection id) 1) select * from big table 2)show processlist -> let's tell it return connection: 1)id = 10, 2)id = 11 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 2)show processlist -> it return connection: 1)id = 10, 2)id = 11 see? there's no change at id... maybe expose the query id solve this problem.. (in this case it should return ~8 to (1), and ~2 to (2)) i'm using mariadb 10.0.3 here
any idea?
-- Gabriel Sosa Sometimes the questions are complicated and the answers are simple. -- Dr. Seuss
hi gabriel, i think yes, i did what you told, i use show processlist / information_schema.processlist to get id and after kill (i don't know if there's another solution to get query id of other process) what happened? connection 1) big select... connection 2)show processlist connection 2) kill connection 1 id (i take +- 1 second between show processlist and this command) connection 1) other query connection 1) killed in other words, i killed the second query of connection 1, not the first one
ops sorry wrong order: 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
well, like i told... the manual (5.7) only say about thread id, not query id maybe it's time to implement KILL [CONNECTION | QUERY] thread_id [QUERY "some relative unique query id in this database"] the unique query id, could be: substr(sha1 or md5 of (thread_id + query start time),6) 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 a lock for nothing... ... KILL [CONNECTION | QUERY] *thread_id* * - KILL CONNECTION <http://dev.mysql.com/doc/refman/5.7/en/kill.html> is the same as KILL <http://dev.mysql.com/doc/refman/5.7/en/kill.html> with no modifier: It terminates the connection associated with the given thread_id. - KILL QUERY <http://dev.mysql.com/doc/refman/5.7/en/kill.html> terminates the statement that the connection is currently executing, but leaves the connection itself intact. * 2013/8/16 Roberto Spadim <roberto@spadim.com.br>
hum same problem the ID is the connection id, not the query id... example
if i execute: (1 = one connection, 2 = other connection) 1)mysql_connect... (create a connection id) 2)mysql_connect... (create a connection id) 1)show processlist (i will see 1 and 2 connection id) 1) select * from big table 2)show processlist -> let's tell it return connection: 1)id = 10, 2)id = 11 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 2)show processlist -> it return connection: 1)id = 10, 2)id = 11 see? there's no change at id... maybe expose the query id solve this problem.. (in this case it should return ~8 to (1), and ~2 to (2)) i'm using mariadb 10.0.3 here
any idea?
-- Roberto Spadim SPAEmpresarial
I'm not sure that would be useful (particularly if your transactions always leave the database in a consinstent state... which should always be the case, unless you use non-transactional engines, but then data integrity is not supposed to be vital). However, there is a little trick I use when debugging stored programs. It is not intended to solve the "problem" you are reporting, but probably it does... see below. If a stored programs has a loop, it could be an infinite loop because of a bug. To quicly kill them, I have a simple procedure called kill_like(). I think that an example is the better way to explain it: CALL my_proc(); CALL _.kill_like('%my_proc%'); kill_like() returns an error if 2 or more queries were found, because in that case the pattern wasn't restrictive enough. Only the query is killed, not the connection. I think this solves your problem because if the query finishes while you type the command, a NOT FOUND condition is reported and nothing happens. Here's the (trivial!) code DELIMITER || CREATE DATABASE IF NOT EXISTS `_`; DROP PROCEDURE IF EXISTS `_`.`kill_like`; CREATE PROCEDURE `_`.`kill_like`(IN `sql_pattern` TEXT) `whole_proc`: BEGIN SELECT `ID` FROM `information_schema`.`PROCESSLIST` WHERE `INFO` LIKE `sql_pattern` INTO @query_id; IF FOUND_ROWS() = 0 THEN SET @message_text = 'Query not found'; /*!50500 SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; ELSEIF FOUND_ROWS() > 1 THEN SET @message_text = 'More than 1 query match the pattern'; /*!50500 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; END IF; KILL QUERY @query_id; END; || DELIMITER ; I hope this helps. Federico -------------------------------------------- Ven 16/8/13, Roberto Spadim <roberto@spadim.com.br> ha scritto: Oggetto: [Maria-discuss] More secure Kill command A: maria-discuss@lists.launchpad.net Data: Venerdì 16 agosto 2013, 21:48 hi guys, i hit a problem in this exact second...some programs are running and i want to kill only one big querywell i executed kill 17143 ok.. but when i execute this command, the query was done, and starting a second query... in other words... i killed the wrong query could we add a hash information to kill, to make it more secure and precise?for example... kill 17143 md5 "123456789......."where md5 = id + start time of query + others informations to make it relative unique maybe it exists and i don't know... any other idea how to solve this? thanks -- Roberto Spadim -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
2013/8/17 Federico Razzoli <federico_raz@yahoo.it>
I'm not sure that would be useful (particularly if your transactions always leave the database in a consinstent state... which should always be the case, unless you use non-transactional engines, but then data integrity is not supposed to be vital).
hi, the problem is not transactional or not transactional... some time you are running a SELECT without transaction involved, and next query you start a transaction (BEGIN TRANSACTION for example) the problem happen when you try to KILL the SELECT, and instead of killing it, you kill the BEGIN TRANSACTION it's not a engine problem, i don't know how to call, but it's something like "a problem of sync between what you want to do, and what really happen", because when you get the thread id of a connection, you don't get the "query id" from that connection, you kill the thread, not the query... the point here is not kill the thread, is kill the thread only if it is running that query id
However, there is a little trick I use when debugging stored programs. It is not intended to solve the "problem" you are reporting, but probably it does... see below.
If a stored programs has a loop, it could be an infinite loop because of a bug. To quicly kill them, I have a simple procedure called kill_like(). I think that an example is the better way to explain it:
CALL my_proc(); CALL _.kill_like('%my_proc%');
kill_like() returns an error if 2 or more queries were found, because in that case the pattern wasn't restrictive enough. Only the query is killed, not the connection.
I think this solves your problem because if the query finishes while you type the command, a NOT FOUND condition is reported and nothing happens.
Here's the (trivial!) code
DELIMITER ||
CREATE DATABASE IF NOT EXISTS `_`;
DROP PROCEDURE IF EXISTS `_`.`kill_like`; CREATE PROCEDURE `_`.`kill_like`(IN `sql_pattern` TEXT) `whole_proc`: BEGIN SELECT `ID` FROM `information_schema`.`PROCESSLIST` WHERE `INFO` LIKE `sql_pattern` INTO @query_id;
IF FOUND_ROWS() = 0 THEN SET @message_text = 'Query not found'; /*!50500 SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; ELSEIF FOUND_ROWS() > 1 THEN SET @message_text = 'More than 1 query match the pattern'; /*!50500 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; END IF;
KILL QUERY @query_id; END;
|| DELIMITER ;
I hope this helps.
well this may work, and may not work, you don't set a lock between information_schema.PROCESS_LIST and KILL command, the query can change between the time of process_list return and the kill command, and yes, i have a very high lucky and i killed a query that was not the right query because i used the thread id, instead of the query id i think the solution is a new parameter to KILL command, the query_id information...
Federico
Thanks federico! :) i think i will do a patch, just talking with sergei at developers to know what more i'm missing
Hi, KILL THREAD THREAD_ID WITH QUERY_ID QUERY_ID and KILL QUERY THREAD_ID WITH QUERY_ID QUERY_ID and possibly KILL QUERY WITH QUERY_ID should be supported. This is a very important and missing feature which is included in other forks. I_S.PROCESS_LIST should be extended to include QUERY_ID (you can get query_id from SHOW commands but not I_S). The above KILL commands if the QUERY_ID no longer exists on the system, thus you can KILL a SELECT without worrying that it has moved on to creating a new transaction or statement. --Justin On Sat, Aug 17, 2013 at 9:37 AM, Roberto Spadim <roberto@spadim.com.br>wrote:
2013/8/17 Federico Razzoli <federico_raz@yahoo.it>
I'm not sure that would be useful (particularly if your transactions always leave the database in a consinstent state... which should always be the case, unless you use non-transactional engines, but then data integrity is not supposed to be vital).
hi, the problem is not transactional or not transactional... some time you are running a SELECT without transaction involved, and next query you start a transaction (BEGIN TRANSACTION for example) the problem happen when you try to KILL the SELECT, and instead of killing it, you kill the BEGIN TRANSACTION it's not a engine problem, i don't know how to call, but it's something like "a problem of sync between what you want to do, and what really happen", because when you get the thread id of a connection, you don't get the "query id" from that connection, you kill the thread, not the query... the point here is not kill the thread, is kill the thread only if it is running that query id
However, there is a little trick I use when debugging stored programs. It is not intended to solve the "problem" you are reporting, but probably it does... see below.
If a stored programs has a loop, it could be an infinite loop because of a bug. To quicly kill them, I have a simple procedure called kill_like(). I think that an example is the better way to explain it:
CALL my_proc(); CALL _.kill_like('%my_proc%');
kill_like() returns an error if 2 or more queries were found, because in that case the pattern wasn't restrictive enough. Only the query is killed, not the connection.
I think this solves your problem because if the query finishes while you type the command, a NOT FOUND condition is reported and nothing happens.
Here's the (trivial!) code
DELIMITER ||
CREATE DATABASE IF NOT EXISTS `_`;
DROP PROCEDURE IF EXISTS `_`.`kill_like`; CREATE PROCEDURE `_`.`kill_like`(IN `sql_pattern` TEXT) `whole_proc`: BEGIN SELECT `ID` FROM `information_schema`.`PROCESSLIST` WHERE `INFO` LIKE `sql_pattern` INTO @query_id;
IF FOUND_ROWS() = 0 THEN SET @message_text = 'Query not found'; /*!50500 SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; ELSEIF FOUND_ROWS() > 1 THEN SET @message_text = 'More than 1 query match the pattern'; /*!50500 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; END IF;
KILL QUERY @query_id; END;
|| DELIMITER ;
I hope this helps.
well this may work, and may not work, you don't set a lock between information_schema.PROCESS_LIST and KILL command, the query can change between the time of process_list return and the kill command, and yes, i have a very high lucky and i killed a query that was not the right query because i used the thread id, instead of the query id i think the solution is a new parameter to KILL command, the query_id information...
Federico
Thanks federico! :) i think i will do a patch, just talking with sergei at developers to know what more i'm missing
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
nice i open the MDEV, and put this email there, do you have a link about percona server including this feature? https://mariadb.atlassian.net/browse/MDEV-4911 2013/8/17 Justin Swanhart <greenlion@gmail.com>
Hi,
KILL THREAD THREAD_ID WITH QUERY_ID QUERY_ID and KILL QUERY THREAD_ID WITH QUERY_ID QUERY_ID and possibly KILL QUERY WITH QUERY_ID
should be supported. This is a very important and missing feature which is included in other forks.
I_S.PROCESS_LIST should be extended to include QUERY_ID (you can get query_id from SHOW commands but not I_S).
The above KILL commands if the QUERY_ID no longer exists on the system, thus you can KILL a SELECT without worrying that it has moved on to creating a new transaction or statement.
--Justin
On Sat, Aug 17, 2013 at 9:37 AM, Roberto Spadim <roberto@spadim.com.br>wrote:
2013/8/17 Federico Razzoli <federico_raz@yahoo.it>
I'm not sure that would be useful (particularly if your transactions always leave the database in a consinstent state... which should always be the case, unless you use non-transactional engines, but then data integrity is not supposed to be vital).
hi, the problem is not transactional or not transactional... some time you are running a SELECT without transaction involved, and next query you start a transaction (BEGIN TRANSACTION for example) the problem happen when you try to KILL the SELECT, and instead of killing it, you kill the BEGIN TRANSACTION it's not a engine problem, i don't know how to call, but it's something like "a problem of sync between what you want to do, and what really happen", because when you get the thread id of a connection, you don't get the "query id" from that connection, you kill the thread, not the query... the point here is not kill the thread, is kill the thread only if it is running that query id
However, there is a little trick I use when debugging stored programs. It is not intended to solve the "problem" you are reporting, but probably it does... see below.
If a stored programs has a loop, it could be an infinite loop because of a bug. To quicly kill them, I have a simple procedure called kill_like(). I think that an example is the better way to explain it:
CALL my_proc(); CALL _.kill_like('%my_proc%');
kill_like() returns an error if 2 or more queries were found, because in that case the pattern wasn't restrictive enough. Only the query is killed, not the connection.
I think this solves your problem because if the query finishes while you type the command, a NOT FOUND condition is reported and nothing happens.
Here's the (trivial!) code
DELIMITER ||
CREATE DATABASE IF NOT EXISTS `_`;
DROP PROCEDURE IF EXISTS `_`.`kill_like`; CREATE PROCEDURE `_`.`kill_like`(IN `sql_pattern` TEXT) `whole_proc`: BEGIN SELECT `ID` FROM `information_schema`.`PROCESSLIST` WHERE `INFO` LIKE `sql_pattern` INTO @query_id;
IF FOUND_ROWS() = 0 THEN SET @message_text = 'Query not found'; /*!50500 SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; ELSEIF FOUND_ROWS() > 1 THEN SET @message_text = 'More than 1 query match the pattern'; /*!50500 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text; */ SELECT @message_text AS `error`; LEAVE `whole_proc`; END IF;
KILL QUERY @query_id; END;
|| DELIMITER ;
I hope this helps.
well this may work, and may not work, you don't set a lock between information_schema.PROCESS_LIST and KILL command, the query can change between the time of process_list return and the kill command, and yes, i have a very high lucky and i killed a query that was not the right query because i used the thread id, instead of the query id i think the solution is a new parameter to KILL command, the query_id information...
Federico
Thanks federico! :) i think i will do a patch, just talking with sergei at developers to know what more i'm missing
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial
well this may work, and may not work, you don't set a lock between information_schema.PROCESS_LIST and KILL command, the query can change between the time of process_list return and the kill command, and yes, i have a very high lucky and i killed a query that was not the right query because i used the thread id, instead of the query id i think the solution is a new parameter to KILL command, the query_id information...
Of course this MAY happen even using that procedure, but you should be very very very unlucky :) Federico
For the record, I found this patch: https://bugs.launchpad.net/percona-server/+bug/725436 I'm not asking for it, but probably people who wants the feature proposed by Roberto may also want this patch applied? It solves a similar problem. Bye Federico
humm, interesting but there's a "wait_timeout" variable to avoid many idle threads... but thinking about a feature of kill command, this is nice, and 'easy' to implement too 2013/8/17 Federico Razzoli <federico_raz@yahoo.it>
For the record, I found this patch: https://bugs.launchpad.net/percona-server/+bug/725436
I'm not asking for it, but probably people who wants the feature proposed by Roberto may also want this patch applied? It solves a similar problem.
Bye Federico
-- Roberto Spadim SPAEmpresarial
patch sent :D please check it at MDEV-4916 2013/8/17 Roberto Spadim <roberto@spadim.com.br>
humm, interesting but there's a "wait_timeout" variable to avoid many idle threads... but thinking about a feature of kill command, this is nice, and 'easy' to implement too
2013/8/17 Federico Razzoli <federico_raz@yahoo.it>
For the record, I found this patch: https://bugs.launchpad.net/percona-server/+bug/725436
I'm not asking for it, but probably people who wants the feature proposed by Roberto may also want this patch applied? It solves a similar problem.
Bye Federico
-- Roberto Spadim SPAEmpresarial
-- Roberto Spadim SPAEmpresarial
Is shutdown statement safe in all cases, or could it sometimes lead to half executed transactions/table crashes? I didn't experience any trouble, I'm asking just to be sure. Thanks for Maria10 features, Federico Razzoli
With any non-transactional storage engines, shutdown could lead to half-executed transactions/statements. It should not lead to any crashed tables. Regards, Jeremy On Wed, Aug 21, 2013 at 9:26 AM, Federico Razzoli <federico_raz@yahoo.it>wrote:
Is shutdown statement safe in all cases, or could it sometimes lead to half executed transactions/table crashes? I didn't experience any trouble, I'm asking just to be sure.
Thanks for Maria10 features, Federico Razzoli
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (5)
-
Federico Razzoli -
Gabriel Sosa -
Jeremy Cole -
Justin Swanhart -
Roberto Spadim