maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

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@xxxxxxxxxxxxx> ha scritto:

 Oggetto: [Maria-discuss] More secure Kill command
 A: maria-discuss@xxxxxxxxxxxxxxxxxxx
 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@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp