maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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