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 <[email protected]> ha scritto:
Oggetto: [Maria-discuss] More secure Kill command
A: [email protected]
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 : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
