Mysql processlist

Today, when writing a report, we use the Navicat client to run a stored procedure. Due to the statement, the program has been running all the time, and the whole Navicat client is stuck and can not close the Navicat client.

So I thought of kill dropping this thread, logging in to the server mysql, and trying to find out the program ID with the show processlist method. As a result, I found that many users were logging in and executing statements, and show processlist could not add conditional retrieval.

Mysql > show processlist

+- -+

| | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+- -+

| | 2158 | root | 172.158.136 Sleep 50154 | NULL | Sleep | 20755 | | NULL | 219 | 219 |

| | 2159 | root | 172.158.136 Sleep 50157 | ADM | Sleep | 4087 | | NULL | 0 | 0 |

| | 2187 | prouser | 172.158.226 Sleep 49647 | Sleep | 21335 | NULL | 403 | 403 |

| | 2189 | admin | 172.158.226 Sleep 49692 | skw_reportdata | Sleep | 4269 | | NULL | 0 | 0 |

| | 2203 | admin | 172.158.226 Sleep 49716 | skw_reportdata | Sleep | 20874 | | NULL | 1000 | 1000 | |

| | 2207 | admin | 172.158.226 Sleep 49725 | skw_reportdata | Sleep | 20844 | | NULL | 0 | 0 |

| | 2212 | root | 172.158.136 Sleep 50556 | CDM | Sleep | | NULL | 0 | 1 |

| | 2217 | prouser | 172.30.249.28 Sleep 47190 | account | Sleep | 11360 | | NULL | 0 | 0 |

| | 2218 | root | 172.158.136 Sleep 50601 | DW | Sleep | 20095 | | NULL | 0 | 0 |

| | 2220 | admin | 172.158.61 Sleep 49553 | NULL | Sleep | 20247 | | NULL | 19 | 19 |

| | 2221 | admin | 172.158.61 Sleep 49554 | ADM | Sleep | 20246 | | NULL | 7 | 7 |

| | 2233 | prouser | 172.158.12.125 Sleep 63769 | account | Sleep | 19659 | | NULL | 21 | 21 |

| | 2234 | prouser | 172.158.12.125 Sleep 63771 | account | Sleep | 19512 | | NULL |

. Most of the results are omitted here

The result is not ideal, of course we can use other ways, the result of show processlist is from the information_schema.processlist table.

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)

We can use this table to retrieve the results we need.

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. Loankeeper infotitle title recorder 0reitemology. Loanflowers infocycle interestrecalogy a.loanflowers infocycle interestrecalogy 20140808 title recorder 99990101 composure 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.01sec)

The second rule is that as a result, kill will be OK if we lose it.

Mysql > kill 2645

Query OK, 0 rows affected (0.00 sec)

At this time, the Navicat client will return to normal.!