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.
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 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.
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.
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. Regards, Sergei