add KILL query id, and add query id information to processlist

Description

This MDEV comes from a sync problem about SHOW PROCESSLIST and KILL command, i will add two parts to this MDEV:


FIRST PART:
let me explain the problem:

there's two connections (1) and (2) and they will execute some commands:

1) SELECT * FROM A_BIG_TABLE_THAT_WILL_EXPEND_MANY_TIME
2) SHOW PROCESSLIST /* WHAT HAPPENED WITH MARIADB? USERS ARE REPORTING SYSTEM LOCKED OR SLOW!? */

THREAD ID

COMMAND

10

SELECT * FROM A BIG TABLE....

11

SHOW PROCESSLIST

1) BEGIN TRANSACTION
2) KILL 10 /* WELL THE SELECT IS KILLING MY SERVER, I WILL KILL IT! */

1) NOOOOOOOOOOO! MY BEGIN TRANSACTION WAS KILLED!
2) NOOOOOOOOOOO! I SENT KILL COMMAND TO WRONG QUERY!

=] removing the last two commands, the problem here is how to kill the right query, and not the right thread...

well contacting Sergei Golubchik and maria-developer / maria-discussiong mail list, i think the best way is:

1) add a new parameter to KILL command, the new syntax is:

examples:
today KILL commands:

new KILL commands:

I was thinking about add a redundant command with thread id + query id, and asked to sergei...
since query id is a big int (or a 32 bit int) we have low probability of kill a wrong query id, since the show processlist and the kill command normally take <1 second for scripts/programs, and take <10 seconds for a dba sql interface, and considering a very busy server (1.000.000 qps) we have many time to the query id be the same, for 32 bit overflow we have more than should have 2^32 (4.294.967.296) queries, more than 10 seconds (10.000.000 queries) with busy server...
resuming... we WILL NOT add the redundant command (thread id + query id) just the query id command

2) we WILL NOT change the protocol kill command
(http://dev.mysql.com/doc/internals/en/com-process-kill.html#packet-COM_PROCESS_KILL), just change the SQL (COM_QUERY) KILL command

that's all! good job =)


SECOND PART: (done with diff_10_0_4_sql_show.cc file)
add the QUERY ID column to processlist

SELECT * FROM information_schema.PROCESSLIST

ID

QUERY_ID

USER

HOST

DB

COMMAND

TIME

STATE

INFO

TIME_MS

STAGE

MAX_STAGE

PROGRESS

MEMORY_USED

EXAMINED_ROWS

2

10

rspadim

179.213.3.11:30165

information_schema

Query

0

executing

select * from processlist

0.240

0

0

0.000

83288

0


important commend from mail list:


from sql_class.h (10.0.4)


TODO:
some things that must be done:​

1) the KILL QUERY_ID 1,2,3,4,5 don't work, that's my first patch with sql_yacc.yy maybe i done something wrong since %expect changed +8 numbers

2) the QUERY_ID isn't what i want but well worked hehehehe, maybe change the syntax and make it more beautiful

3) there's some warnings in gcc that must be checked (signed compare with unsigned), and others case) that don't use the right flag (i add a bit = 16 for KILL_QUERY command)

4) the KILL QUERY_ID <non exists query id>, return a error something like "thread id not found", must be "query id not found"

5) i don't remember if have other problem...

Environment

None

Status

Assignee

Sergey Vojtovich

Reporter

Roberto Spadim

Labels

None

External issue ID

None

External issue ID

None

Time tracking

8h

Fix versions

Priority

Major
Configure