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 Swanhartgreenlion@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.x<186.222.25.204>xx 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
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
another question :) maybe in futures MDEV could be nice something like: 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