maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxxxxxx>wrote:

> 2013/8/17 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).
>>
> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>