|
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:
KILL [ CONNECTION [ QUERY [ ID ] ] | QUERY [ ID ] ] id
|
or (same thing)
|
KILL [ CONNECTION | CONNECTION QUERY [ ID ] | QUERY | QUERY ID ] id
|
examples:
today KILL commands:
KILL QUERY 1; -> this will kill thread id 1, but don't disconnect client
|
KILL 1; or KILL CONNECTION 1; -> this will kill thread 1 and disconnect client
|
new KILL commands:
KILL QUERY ID 1; -> this will kill query id 1, but don't disconnect client
|
KILL CONNECTION QUERY ID 1; KILL CONNECTION QUERY 1; -> this will kill query id 1, and disconnect client
|
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:
Justin Swanhart
|
greenlion@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.
|
—
from sql_class.h (10.0.4)
~ line 2304 - THD class
|
/*
|
Id of current query. Statement can be reused to execute several queries
|
query_id is global in context of the whole MySQL server.
|
ID is automatically generated from mutex-protected counter.
|
It's used in handler code for various purposes: to check which columns
|
from table are necessary for this select, to check if it's necessary to
|
update auto-updatable fields (like auto_increment and timestamp).
|
*/
|
query_id_t query_id;
|
—
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...
|
diff_10_0_4_mdev4911_lex.h
diff_10_0_4_mdev4911_sql_yacc.yy
just one query id/command
no problem