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