maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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 <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
>>
>>
>


-- 
Roberto Spadim
SPAEmpresarial