How can I stop a running MySQL query

Question

I connect to mysql from my Linux shell  Every now and then I run a SELECT query that is too big  It prints and prints and I already know this is not what I meant  I would like to stop the query   Hitting Ctrl C  a couple of times  kills mysql completely and takes me back to shell  so I have to reconnect     Is it possible to stop a query without killing mysql itself

User · Answer

Just to add   KILL QUERY   Id   where Id is connection id from show processlist  is more preferable if you are do not want to kill the connection usually when running from some application   For more details you can read mysql doc here

User · Answer

Connect to mysql  mysql -uusername -p  -hhostname   show full processlist   mysql gt  show full processlist   --------- -------- ------------------- --------- --------- ------ ------- ------------------    Id        User     Host                db        Command   Time   State   Info                --------- -------- ------------------- --------- --------- ------ ------- ------------------    9255451   logreg   dmin001 ops 37651   logdata   Query        0   NULL    show processlist    --------- -------- ------------------- --------- --------- ------ ------- ------------------    Kill the specific query  Here id 9255451  mysql gt  kill 9255451    If you get permission denied  try this SQL   CALL mysql rds kill 9255451

User · Answer

mysql gt show processlist   mysql gt  kill  number from first col

User · Answer

If you have mysqladmin available  you may get the list of queries with    gt  mysqladmin -uUSERNAME -pPASSWORD pr   ----- ------ ----------------- -------- --------- ------ -------------- ------------------    Id    User   Host              db       Command   Time   State          Info                ----- ------ ----------------- -------- --------- ------ -------------- ------------------    137   beet   localhost 53535   people   Query     292    Sending data   DELETE FROM          145   root   localhost 55745            Query     0                     show processlist    ----- ------ ----------------- -------- --------- ------ -------------- ------------------    Then you may stop the mysql process that is hosting the long running query    gt  mysqladmin -uUSERNAME -pPASSWORD kill 137

User · Answer

Use mysqladmin to kill the runaway query   Run the following commands   mysqladmin -uusername -ppassword pr   Then note down the process id   mysqladmin -uusername -ppassword kill pid   The runaway query should no longer be consuming resources

User · Answer

The author of this question mentions that it   s usually only after MySQL prints its output that he realises that the wrong query was executed  As noted  in this case  Ctrl-C doesn   t help  However  I   ve noticed that it will abort the current query     if you catch it before any output is printed  For example  mysql gt  select   from jos users  jos comprofiler   MySQL gets busy generating the Cartesian Product of the above two tables and you soon notice that MySQL hasn t printed any output to screen  the process state is Sending data  so you type Ctrl-C  Ctrl-C -- sending  quot KILL QUERY 113240 quot  to server     Ctrl-C -- query aborted  ERROR 1317  70100   Query execution was interrupted  Ctrl-C can similarly be used to stop an UPDATE query

User · Answer

You need to run following command to kill the process  Find out the id of the process which you wanted to kill by  gt  show processlist   Take the value from id column and fire below command kill query  lt processId gt    Query parameter specifies that we need to kill query command process  The syntax for kill process as follows  KILL  CONNECTION   QUERY  processlist id  Please refer this link for more information

[mysql] How can I stop a running MySQL query?

Examples related to mysql

Examples related to kill

Examples related to processlist