MySQL Bugs: #61041: KILL COMMAND FEATURES

Bug #61041	KILL COMMAND FEATURES
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:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	any	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	KILL, qc

Description:
hi guys, i was trying to kill one query but i made some sh*t

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

Thank you for the feature request.

FWIW, perhaps the most appropriate thing would be to kill a query by its ID.

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:
SHOW PROCESSLIST
and 
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)

maybe a ID (Unique ID) of the query (not connection) could help a lot!

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?

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)
  NOW = YYYYMMDDHHIISS
or
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

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