今天在寫報表時，用Navicat客戶端執行一個儲存過程，由於語句的原因導致程式一直執行中，整個Navicat客戶端都卡住了，無法關閉Navicat客戶端。
於是就想到了kill掉這個執行緒，登入伺服器mysql，想用show processlist方法找出程式ID，結果發現有很多使用者在登入並且在執行語句，而且show processlist沒辦法加條件檢索
mysql> show processlist;
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| 2158 | root | 172.158.8.136:50154 | NULL | Sleep | 20755 | | NULL | 219 | 219 |
| 2159 | root | 172.158.8.136:50157 | ADM | Sleep | 4087 | | NULL | 0 | 0 |
| 2187 | prouser | 172.158.8.226:49647 | NULL | Sleep | 21335 | | NULL | 403 | 403 |
| 2189 | admin | 172.158.8.226:49692 | skw_reportdata | Sleep | 4269 | | NULL | 0 | 0 |
| 2203 | admin | 172.158.8.226:49716 | skw_reportdata | Sleep | 20874 | | NULL | 1000 | 1000 |
| 2207 | admin | 172.158.8.226:49725 | skw_reportdata | Sleep | 20844 | | NULL | 0 | 0 |
| 2212 | root | 172.158.8.136:50556 | CDM | Sleep | 930 | | NULL | 0 | 1 |
| 2217 | prouser | 172.30.249.28:47190 |account | Sleep | 11360 | | NULL | 0 | 0 |
| 2218 | root | 172.158.8.136:50601 | DW | Sleep | 20095 | | NULL | 0 | 0 |
| 2220 | admin | 172.158.8.61:49553 | NULL | Sleep | 20247 | | NULL | 19 | 19 |
| 2221 | admin | 172.158.8.61:49554 | ADM | Sleep | 20246 | | NULL | 7 | 7 |
| 2233 | prouser | 172.158.8.125:63769 | account | Sleep | 19659 | | NULL | 21 | 21 |
| 2234 | prouser | 172.158.8.125:63771 | account | Sleep | 19512 | | NULL
.......此處省略大部分結果
結果很不理想，當然我們可以用其他辦法，show processlist的結果是來自information_schema.processlist表中
mysql> desc information_schema.processlist;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| ID | bigint(21) unsigned | NO | | 0 | |
| USER | varchar(16) | NO | | | |
| HOST | varchar(64) | NO | | | |
| DB | varchar(64) | YES | | NULL | |
| COMMAND | varchar(16) | NO | | | |
| TIME | int(7) | NO | | 0 | |
| STATE | varchar(64) | YES | | NULL | |
| INFO | longtext | YES | | NULL | |
| TIME_MS | bigint(21) | NO | | 0 | |
| ROWS_SENT | bigint(21) unsigned | NO | | 0 | |
| ROWS_EXAMINED | bigint(21) unsigned | NO | | 0 | |
| TID | bigint(21) unsigned | YES | | NULL | |
+---------------+---------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
我們可以透過這張表進行檢索出我們需要的結果
mysql> SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'\G;
*************************** 1. row ***************************
id: 2695
user: root
host: localhost
time: 0
command: Query
info: SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'
*************************** 2. row ***************************
id: 2645
user: root
host: 172.158.8.136:53258
time: 1522
command: Query
info: insert into `CDM`.cdm_product(product_id,product_type,product_name,add_rate,base_rate,year_rate,startdate,enddate,is_current)
SELECT a.id,CASE
WHEN IFNULL(i.enlending_type, '9') = '0' THEN '1'
WHEN IFNULL(i.enlending_type, '9') = '1' THEN '2'
WHEN IFNULL(i.enlending_type, '9') = '4' THEN '3'
WHEN IFNULL(i.enlending_type, '9') = '5' THEN '4'
WHEN IFNULL(i.enlending_type, '9') = '6' THEN '5'
WHEN IFNULL(i.enlending_type, '9') = '7' THEN '6'
WHEN IFNULL(i.enlending_type, '9') = '9' THEN '7' ELSE IFNULL(i.enlending_type, '9')END,
a.loan_info_title,0,a.loan_info_interest,a.loan_info_interest,'20140808','99990101',1
FROM DW.dw_biz_invest_statements a
left JOIN DW.dw_biz_loan_info i
ON a.loan_info_id=i.id
GROUP BY loan_info_title
2 rows in set (0.01 sec)
第二條就是我們要的結果kill掉就OK了
mysql>kill 2645;
Query OK, 0 rows affected (0.00 sec)
這個時候Navicat客戶端就恢復正常了。！

mysql processlist

相關文章