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 <[email protected]> > 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 >> >> > -- Roberto Spadim SPAEmpresarial
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
