Submitted: 3 May 2011 14:49 Modified: 4 May 2011 21:07
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version:any OS:Any (any)
Assigned to: CPU Architecture:Any
Tags: KILL, qc

[3 May 2011 14:49] Roberto Spadim
hi guys, i was trying to kill one query but i made some sh*t

kill 9999
this command only kill the connection with id =9999

what i want...

kill id=9999 and sql like "/*ABORTABLE*/%"

why? i tried to kill a big query but when i executed the kill the query ended and the next phpscript query executed should not be killed :(

another idea for the same command...

DELETE FROM information_schema.processlist
WHERE id='aaaaa' and INFO like '%asdfasdf%'

How to repeat:
none it's a feature request

Suggested fix:
none it's a feature request
[3 May 2011 15:07] Valeriy Kravchuk
Thank you for the feature request.
[4 May 2011 0:55] Davi Arnaut
FWIW, perhaps the most appropriate thing would be to kill a query by its ID.
[4 May 2011 1:23] Roberto Spadim
yeah, it´s by ID...
but think with me...
a script or a program run some queries...
in another computer the DBA work with others queries...

the time between DBA queries:
KILL xxxx
are diferrent (maybe 0.000000000000000000000001 seconds, but never 0 seconds)

between this queries the program/script could end query and start another query

kill must include id number (ok!)
but *could* have some checks to don´t kill wrong query
the point is: don´t kill wrong query, since we today kill connection (not query)
[4 May 2011 1:23] Roberto Spadim
maybe a ID (Unique ID) of the query (not connection) could help a lot!
[4 May 2011 5:47] Tonci Grgin
Valery, while we're at it, what do you think of setting query timeout directly in COM_QUERY so that server takes care of killing (too)long running queries?
[4 May 2011 7:31] Roberto Spadim
i don't know what's COM_QUERY
but, for me, a dba/mysql user

1)changing SHOW PROCESSLIST to add a column of QUERY_ID
2)add a function QUERY_ID(), like CONNECTION_ID() function (i can use it with SELECT,INSERT,UPDATE)
3)add a new command KILL QUERY 99999
4)add a 'alias' KILL CONNECTION 8888 = KILL 8888

QUERY_ID() could be: 
1)NOW() + CONNECTION_ID(), in this case query_id is a varchar(255)
2)a counter of queries for each query sent, query_id++ (this is very nice! could we put it at slow query log?) in this case query_id is a bigint unsigned not null

the second have a problem... since server can get down and the query_id can be slow to update (a small database) we can have query_id=1 very often, maybe mix of hhiiss (or yyyymmdd) + query_id, or a query_id counter inside a mysql table (non volatile query_id), is more interesting/secure
[4 May 2011 21:07] Roberto Spadim
another feature....
since mysql is planning to include memcache as a client library
could we add an column at show processlist? CLIENT_PROTOCOL
could be:

tcp/ipv4, tcp/ipv6, socket, pipe, sharedmemory?, memcache/ipv4, memcache/ipv6 memcache/udpv4, memcache/udpv6, memcache/socket, memcache/pipe, memcache/sharedmemory?, others....

with this, i can better organize connections