KILL [CONNECTION | QUERY] [<thread_id> | <query_id case with QUERY ID, or CONNECTION QUERY ID syntax> | (SELECT)]
or
KILL [CONNECTION | QUERY] [ID | <query_id case with QUERY ID, or CONNECTION QUERY ID syntax> | WHERE where_parameters]

KILL CONNECTION (SELECT thread_id FROM information_schema.PROCESSLIST WHERE status LIKE '%idle%')
or
KILL CONNECTION WHERE status LIKE '%idle%'

DELETE FROM information_schema.PROCESSLIST WHERE status LIKE '%IDLE%'
or
UPDATE information_schema.PROCESSLIST SET status='close' WHERE status LIKE '%some status%'

UPDATE information_schema.PROCESSLIST SET status='idle' WHERE status LIKE '%some status%'

Hi sergey :) well 10.1 with kill and subquery :) nice feature :) maybe too much, but very nice 

Em 14/09/2013 13:58, "Sergei Golubchik" <serg@mariadb.org> escreveu:
Hi, Roberto!
On Sep 13, Roberto Spadim wrote:
>
> > My preference is to kill connection by thread_id and query by
> > query_id, because I normally either want to stop a particular query,
> > or stop all activity in particular connection. But it is
> > incompatible change.
> >
> there's mysql work with this kind of syntax?  i didn't found it at bug.mysql
> at mail list, Justin at Percona, talked about patchs in others forks maybe
> a single unique syntax is better than maridb only syntax, check message
> from MDEV description (copied from mail list):
>
> Justin Swanhartgreenlion@gmail.com Percona, Inc
>
> 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.

It's not in MySQL 5.6 and not in Percona Server 5.5 or 5.6.
I did not check Google patches, Facebook patches and other sets of MySQL
patches, though.
i didn`t found too :/ 

> my problem isn't the program allowing a kill command since it can restart
> the work or stop, it's not a problem
> the "problem" is the boring time lost at a wrong kill command, since i use
> persistent connections at php, and a thread running a script can be used in
> another script without changing it thread_id (can be confirmed at show
> processlist)
> my problem is sending a kill command to the wrong thread since i'm using
> the thread_id to kill the connection and not the query_id, check i use
> "kill connection xxx" not "kill query xxx"

Okay... This makes sense. If you use a connection pool that, indeed,
connection id does not correspond to a logical connection.

Still, while KILL CONNECTION QUERY_ID is kind of ok, KILL QUERY QUERY_ID
is very silly. I'd rather allow subqueries in KILL, to support
kill query query_id is very ugly hehehe, kill query id is ok, and kill connection query_id or query id with space is ok too 
 

  KILL CONNECTION (select thread_id from information_schema.processlist
                   where ...)

then you won't need to kill by query id or state or if_idle - you can
have everything in the where clause.

but this is a larger change that we cannot do 10.0, we simply don't have
time for it. We could try to do it in 10.1 though, properly and
generally, so that you can kill using as complex conditions as you like.
Instead of creating many limited shortcut syntax variants for special
cases.
yeah =) in future we can go back and do some job here
 

> i'm not using the threadpool yet and i don't know how processlist is
> reported with thread pool, is the id isn't unique in this case (using
> threadpool)?

Unique. Every connection has its own connection id, with or without
thread pool. Internal scheduling implementation doesn't affect
user-visible connection ids.
nice i will test thread pool and check how it works
 

> i don't know if it's what mariadb/mysql should do inside code when using
> threadpool, i'm using only one process per connection, and don't have this
> kind of problem

threadpool doesn't have this problem either.

> other doubt now... when we have a daemon process (plugin) there's a
> query id for it?

No.

> query_id=0? in this case we only have an "unique query id" with
> thread_id+query_id? maybe we should avoid the KILL QUERY_ID = 0

Right, thanks.

> again about sintax... maybe a WHERE could be added to KILL instead of a
> DELETE FROM INFORMATION_SCHEMA...
>
> KILL [CONNECTION | QUERY] [WHERE some_fields some_operators some_values
> and_no_subquery | <thread_id> | QUERY_ID <query_id>]
>
> about WHERE, we could use the same fields of show processlist:
>
> ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO, TIME_MS, STAGE,
> MAX_STAGE, PROGRESS, MEMORY_USED, EXAMINED_ROWS, QUERY_ID

I'd rather allow subqueries there, it'll be much more natural.
yes maybe information schema engine, or kill subquery?
 

Regards,
Sergei

bye :)