Hi Sergey!
thanks a lot showing a patch with unit test/sql test and many other things i don't understand how to do :) 

> That was the patch that i sent on MDEV, just a raw idea, but worked, maybe
> a copy and paste hehe =)
Yes, I used some of your patches. Thanks for sharing them.
MariaDB team is very very wellcome =)
 
> there was a warning in gcc about unsigned and signed when i put the patch
> in jira, but i didn't removed
> maybe "if ((longlong) (tmp->query_id)==id)" could remove the warning, but i
> don't know if it's ok
In your patch `id` is ulong and `query_id` is int64. In my patch `id` is longlong,
so it is not affected.
hummm, i used pentium64 (xeon E31240)  linux 3.6.9, gcc 4.7.2 glibc 2.16, mariadb-10.0.3 source code, maybe its different? 
i will test again at weekend with last mariadb source and check if i done something wrong, but if it's ok no problem =)

> > > So, you implemented KILL [ CONNECTION | QUERY ] [ ID ] expr
> > > It allows, in particular
> > >
> > >    KILL CONNECTION ID 10
> >
> I like the QUERY_ID with underscore to know that we are talking about the
> query_id, not the thread_id
> example:
> KILL CONNECTION QUERY_ID 99999
> KILL QUERY QUERY_ID 99999
I'll leave it up to you to convince Serg to use your syntax. :)
i checked that "QUERY ID"  is "more difficult" to implement than "QUERY_ID" at .yy files, i'm wrong?

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 Swanhart
greenlion@gmail.com
Percona, Inc

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.

i didn't checked percona server, but since justin is reported as percona, maybe he's talking about something that exists
i prefer mariadb as main mysql server, maybe doing this new syntax here we will 'export' to all others forks
i know it'is time to think about what's better in parser speed/complexity and sql human readable format, right?

--
other point about KILL CONNECTION QUERY_ID and KILL QUERY QUERY_ID, and why I worked in this MDEV ... i don't have problems about syntax, i have problem about allowing a kill connection based in a query_id. solving this will solve my problem

My today scenario:
I have at least three or four systems running in one server, and when a connection is broken (disconnect) the system (mainly php scripts) will reconnect to mariadb and continue the work, it's like a batch that can't stop, just pause, wait and continue later (10~60 seconds), or when used in web it's stoped and user must retry request
if i turn off background programs, they will continue from last ok query, it's similar to a background mail queue dispatcher, that's why when connection is droped (tcp/ip or network problem) i restart the job, and when killed it wait a bit

there're some queries that have a "/* -- S++ ABORTABLE -- */" or "/* -- S++ SU ABORTABLE -- */" comment, example:
/* -- S++ SU ABORTABLE -- pid:27001 spd_user: rspadim SELF='/rdm-business/app/config.manutencao.spadim' ms=0 ip=186.222.25.xxx session=mt2ite.6b305o19s3o0msb9h54cgw408gkgcc04o4 */SELECT SUM(quant),SUM(pecas),SUM(pbruto),SUM(pliq),SUM(vbruto),SUM(vliq),SUM(custo) FROM est_mov WHERE estoque_entrada_un=1000

in this case "est_mov" table have >10million rows (~14GB) and can cause server to slow down, in other words i need to kill this connection and script can't restart it, killing the query i just make script to restart
the comment "/* ABORTABLE */" allow program to be canceled via "KILL command" at web php interface, but like any web interface i have some times between informations,like: (with heidisql or mysql command line i have some times too)

1) SHOW PROCESSLIST   (~1 ms)
2) send processlist to web client (chrome browser)  (~100ms)
3) user select what query to kill (1 ~10 seconds)
4) chrome browser send the http request to server (~20ms)
5) server execute the show processlist to know if the query can or can't be killed based on /* abortable */ comment (~2ms)
6) server kill the query (~1ms) (KILL CONNECTION xxxx)

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"

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)? if not or yes, no problem, we will have query_id to make it unique now :), but think about killing a connection with threadpool without query_id, maybe i could kill the wrong connection, not?
i don't know if "kill connection query_id xxx" add extra complexity, but in my case it's a "must have" feature to allow a better (more precise) user kill command, the kill query will not do the work, since the query is be reexecuted instead of canceled (when connection is dropped/killed)
well if we could allow a "kill connection query_id xxx", this is nice to me, if not i will use "kill connection xxxx" without query_id and users will kill wrong connections (ok i can patch the server and solve this problem, but a 'native' solution is better =) )

---
thinking about threadpool...
internally i know that killing the thread "isn't nice", maybe a interface to send a request to kill query_id xx to thread yyy is better (more precise)

today we do something like: 
for(first thread  to last thread)
  if(query_id==kill_query_id)
    kill thread (check that we don't send to thread the query_id parameters, since we are at server side, it's too fast to changed query_id in only one instruction but possible, right?)

i think we should do something like:
for(first thread  to last thread)
  if(query_id==kill_query_id)
    request_thread_to_kill_query(thread_id,query_id)
and inside request_thread_to_kill_query( function, check if it have the query_id at threadpool, and kill it (kill connection or kill query)

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

other doubt now... when we have a daemon process (plugin) there's a query id for it? 
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


> another question :) maybe in futures MDEV could be nice something like:
> KILL QUERY_ID IN (1,2,3,4,5) or
> KILL QUERY_ID IN (SELECT QUERY_ID FROM information_schema.PROCESSLIST WHERE
> ..... )
> KILL THREAD_ID IN (1,2,3,4,5) or
> KILL THREAD_ID IN (SELECT ID FROM information_schema.PROCESSLIST WHERE
> ..... )
> KILL CONNECTION QUERY_ID IN (1,2,3,4,5) or
> KILL CONNECTION QUERY_ID IN (SELECT QUERY_ID FROM
> information_schema.PROCESSLIST WHERE ..... )
> KILL CONNECTION THREAD_ID IN (1,2,3,4,5) or
> KILL CONNECTION THREAD_ID IN (SELECT ID FROM information_schema.PROCESSLIST
> WHERE ..... )
>
> it's another mdev, but what you think about it?
Heh, what about DELETE FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ...? But I guess
that'll be quite complex.
 
yes i think it's complex too, and i don't know many things about mariadb source code, but i know the complexity creating a new engine just to information_schema, maybe it's a nice engine, but maybe just a lot of work

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



Anyway we didn't yet implement multi-process kill. Feel free to create another
MDEV.

No problem, i will create it, but...
I put one patch in MDEV-4917 with the IF_IDLE flag, but i think it's not nice :/
This could be done with a newer idea using KILL WHERE, and the KILL 1,2,3,4 could use it too, for example KILL WHERE thread_id IN (1,2,3,4,5)


The WHERE is much more flexible and human readable, and we could kill without seeing what is happening, for example... today i MUST get information_schema.PROCESSLIST to know what is running in mysql/mariadb server
A kill command with WHERE will kill the same queries but with less network traffic and less interaction, example:

KILL WHERE command='sleep'

will kill all queries that are Idle, instead of:

SHOW PROCESSLIST
read processlist with a perl/python/php/or any other program/script/human (dba), and create many queries
KILL IF_IDLE QUERY_ID 1
KILL IF_IDLE QUERY_ID 2
KILL IF_IDLE QUERY_ID 3
...

i'm not using in a high production server with thread pool or many connection, but i'm thinking about it with kill 1000 idle process, you will need 1000 kill command + 1 show processlist, this can be reduced with kill where query_id in (1,2,3,4) or better with only one KILL WHERE status='idle', without a lot of network traffic

that's a point to a new MDEV i know
for now, the query_id could solve my problems of killing a connection/query without killing the wrong client

 

Regards,
Sergey

sorry i wrote in poor english, it's not my main language =]
thanks guys!


--
Roberto Spadim