hi! well, what i'm doing...

SELECT * FROM `information_schema`.`PROCESSLIST`;

this give me a table with this fields:

`ID` BIGINT(4) NOT NULL DEFAULT '0',`USER` VARCHAR(128) NOT NULL DEFAULT '',`HOST` VARCHAR(64) NOT NULL DEFAULT '',`DB` VARCHAR(64) NULL DEFAULT NULL,`COMMAND` VARCHAR(16) NOT NULL DEFAULT '',`TIME` INT(7) NOT NULL DEFAULT '0',`STATE` VARCHAR(64) NULL DEFAULT NULL,`INFO` LONGTEXT NULL,`TIME_MS` DECIMAL(22,3) NOT NULL DEFAULT '0.000',`STAGE` TINYINT(2) NOT NULL DEFAULT '0',`MAX_STAGE` TINYINT(2) NOT NULL DEFAULT '0',`PROGRESS` DECIMAL(7,3) NOT NULL DEFAULT '0.000',`MEMORY_USED` INT(7) NOT NULL DEFAULT '0',`EXAMINED_ROWS` INT(7) NOT NULL DEFAULT '0'

I'm using the ID to kill...

KILL <id here>

the problem is, query is executed (no problem) but the ID don't change... in other words the ID here is the connection id, not the query id...

there's a place to get the query id? and just kill it? i didn't read this at mysql docs, i will check again

hum same problem the ID is the connection id, not the query id... example

if i execute: (1 = one connection, 2 = other connection) 1)mysql_connect... (create a connection id) 2)mysql_connect... (create a connection id) 1)show processlist (i will see 1 and 2 connection id) 1) select * from big table 2)show processlist -> let's tell it return connection: 1)id = 10, 2)id = 11 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 2)show processlist -> it return connection: 1)id = 10, 2)id = 11 see? there's no change at id... maybe expose the query id solve this problem.. (in this case it should return ~8 to (1), and ~2 to (2)) i'm using mariadb 10.0.3 here

any idea?

hi gabriel, i think yes, i did what you told, i use show processlist / information_schema.processlist to get id and after kill (i don't know if there's another solution to get query id of other process)

what happened?

connection 1) big select... connection 2)show processlist connection 2) kill connection 1 id (i take +- 1 second between show processlist and this command) connection 1) other query connection 1) killed

in other words, i killed the second query of connection 1, not the first one

well, like i told... the manual (5.7) only say about thread id, not query id maybe it's time to implement KILL [CONNECTION | QUERY] thread_id [QUERY "some relative unique query id in this database"] the unique query id, could be: substr(sha1 or md5 of (thread_id + query start time),6) like a git small hash commit id, maybe we could make it more unique... but since i will not use it very often, i think a less intensive work is nice here... a global query id could add a lock for nothing...

...

KILL [CONNECTION | QUERY] *thread_id*

*

-

KILL CONNECTION http://dev.mysql.com/doc/refman/5.7/en/kill.html is the same as KILL http://dev.mysql.com/doc/refman/5.7/en/kill.html with no modifier: It terminates the connection associated with the given thread_id. -

KILL QUERY http://dev.mysql.com/doc/refman/5.7/en/kill.html terminates the statement that the connection is currently executing, but leaves the connection itself intact.

*

2013/8/16 Roberto Spadim roberto@spadim.com.br

hum same problem the ID is the connection id, not the query id... example

if i execute: (1 = one connection, 2 = other connection) 1)mysql_connect... (create a connection id) 2)mysql_connect... (create a connection id) 1)show processlist (i will see 1 and 2 connection id) 1) select * from big table 2)show processlist -> let's tell it return connection: 1)id = 10, 2)id = 11 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 1) select * from another big table 2)show processlist -> it return connection: 1)id = 10, 2)id = 11 see? there's no change at id... maybe expose the query id solve this problem.. (in this case it should return ~8 to (1), and ~2 to (2)) i'm using mariadb 10.0.3 here

any idea?

2013/8/17 Federico Razzoli federico_raz@yahoo.it

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

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@gmail.com

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@spadim.com.brwrote:

...

2013/8/17 Federico Razzoli federico_raz@yahoo.it

...

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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

yes, i'm unlucky kkkkkkk =P

humm, interesting but there's a "wait_timeout" variable to avoid many idle threads... but thinking about a feature of kill command, this is nice, and 'easy' to implement too

2013/8/17 Federico Razzoli federico_raz@yahoo.it

For the record, I found this patch: https://bugs.launchpad.net/percona-server/+bug/725436

I'm not asking for it, but probably people who wants the feature proposed by Roberto may also want this patch applied? It solves a similar problem.

Bye Federico

Is shutdown statement safe in all cases, or could it sometimes lead to half executed transactions/table crashes? I didn't experience any trouble, I'm asking just to be sure.

Thanks for Maria10 features, Federico Razzoli