maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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@xxxxxxxxx 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