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 <[email protected]>wrote: > 2013/8/17 Federico Razzoli <[email protected]> > >> 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 : [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
