This MDEV comes from a sync problem about SHOW PROCESSLIST and KILL command, i will add two parts to this MDEV:
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!? */
|10||SELECT * FROM A BIG TABLE....|
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:
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
|2||10||rspadim||184.108.40.206: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)
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...