Bug #25421 MySQL threads don't respond to the kill command
Submitted: 4 Jan 2007 18:36 Modified: 20 Nov 2010 19:49
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.27, 5.0.44 OS:Linux (Redhat)
Assigned to: Alexey Kopytov CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[4 Jan 2007 18:36] [ name withheld ]
Description:
I've run into several queries running on production systems which won't respond to the "kill" command.  The threads were already "hung" and killing them has no effect (mysqladmin kill ___) ...the thread still exists in the process list, and still blocks additional queries. This is posing a problem in that in order to restore service to one locked db mysqld has to be fully restarted, disrupting service to other dbs on the same machine.

Of additional interest, after the kill command, the process list shows the status as "Killed" (even though it never goes away). If I then run "kill query" the processlist status actually goes *back* to "query".

MySQL 5.0.26, MyISAM storage engine

How to repeat:
Currently I've not found a pattern which reliably reproduces this.
[5 Jan 2007 10:57] Valeriy Kravchuk
Thank you for a problem report. Please, send the resutls of:

uname -a
getconf GNU_LIBPTHREAD_VERSION
cat /etc/issue

Linux commands.
[5 Jan 2007 15:07] [ name withheld ]
uname -a
Linux [hostname] 2.4.22 #13 Fri Apr 29 16:12:22 PDT 2005 i686 i686 i386 GNU/Linux

getconf GNU_LIBPTHREAD_VERSION
linuxthreads-0.10

cat /etc/issue
Red Hat Linux release 9 (Shrike)
[24 Jan 2007 22:46] Chris Walker
This happens for me also on Fedora Core 5, MySQL 5.0.27.

The query that hangs is a result of MySQL bug 20932. It spends a lot of time in the "statistics" state, but has the behavior described by the original reporter.
[28 Jan 2007 13:20] Valeriy Kravchuk
Please, send SHOW PROCESSLIST results with those threads you can not kill. I want to check if they are in "statistics" state.
[31 Jan 2007 19:50] Chris Walker
Here is the requested "SHOW PROCESSLIST" output. You can see that the original query was in the "statistics" state for about 2 minutes (information from bug #20932 was used to create that condition). The first 'kill' statement flags the process for killing, but doesn't perform the kill. The subsequent 'kill query' command causes the query to no longer appear flagged for killing. The whole time, the query appears to be stuck in the 'statistics' state and is not killed.

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1970
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 1978
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 115
  State: statistics
   Info: SELECT COUNT(*)
2 rows in set (0.00 sec)

mysql> kill 1978;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1970
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 1978
   User: root
   Host: localhost
     db: test
Command: Killed
   Time: 127
  State: statistics
   Info: SELECT COUNT(*)
2 rows in set (0.00 sec)

mysql> kill query 1978;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 1970
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 1978
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 135
  State: statistics
   Info: SELECT COUNT(*)
2 rows in set (0.00 sec)
[12 Feb 2007 17:58] Valeriy Kravchuk
List of cases when kill flag is checked for the thread is presented in the manual, http://dev.mysql.com/doc/refman/5.0/en/kill.html. I do not see "statistics" state (when  the server is calculating statistics to develop a query execution plan) there. So, this report looks like a new feature request for me. Do you agree?
[12 Feb 2007 23:42] Chris Walker
If this is treated as a feature request, can bug #20932 be escalated to a higher priority? I was mainly interested in this feature to compensate for that bug.
[13 Feb 2007 13:37] Valeriy Kravchuk
I think, this is a reasonable feature request: optimizer should check kill flag for a thread while it is working on a plan (while thread is in "statistics" state). I'll try to escalate it (as well as fixing original bug 20932) ASAP.
[7 Nov 2007 12:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37250

ChangeSet@1.2588, 2007-11-07 15:08:50+03:00, kaa@polly.(none) +1 -0
  Fix for bug #25421: MySQL threads don't respond to the kill command
  
  Calculating the estimated number of records for a range scan may take a
  significant time, and it was impossible for a user to interrupt that
  process by killing the connection or the query.
  
  Fixed by checking the thread's 'killed' status in check_quick_keys() and
  interrupting the calculation process if it is set to a non-zero value.
[7 Nov 2007 12:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37252

ChangeSet@1.2617, 2007-11-07 15:12:56+03:00, kaa@polly.(none) +2 -0
  6.0 version of the fix for bug #25421: MySQL threads don't respond to 
  the kill command.
  
  Calculating the estimated number of records for a range scan may take a
  significant time, and it was impossible for a user to interrupt that
  process by killing the connection or the query.
  
  Fixed by checking the thread's 'killed' status in 
  handler::multi_range_read_info_const() and interrupting the calculation 
  process if it is set to a non-zero value. The same must also be done in
  every derived class that overrides multi_range_read_info_const().
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:36] Bugs System
Pushed into 6.0.4-alpha
[19 Nov 2007 4:42] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.

Some threads did not respond to the KILL statement.
[19 Nov 2007 10:51] Sergei Golubchik
That's too generic.

Threads that were calculating the estimated number of records for a range scan didn't respond to the KILL statement. That is, if a "range" join type is possible (even if it's not selected by the optimizer as a join type of choice and thus is not shown by EXPLAIN), the query in the "statistics" state (shown by the SHOW PROCESSLIST) did not respond to the KILL statement.
[19 Nov 2007 14:25] Paul Dubois
Replaced original changelog entry with Serg's text.
[15 Jul 2010 12:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113665

2879 Alexey Kopytov	2010-07-15
      Backport of the fix for bug#25421 to 5.0.
      
      Calculating the estimated number of records for a range scan
      may take a significant time, and it was impossible for a user
      to interrupt that process by killing the connection or the
      query.
      
      Fixed by checking the thread's 'killed' status in
      check_quick_keys() and interrupting the calculation process if
      it is set to a non-zero value.
[21 Jul 2010 16:10] Bugs System
Pushed into 5.0.92 (revid:georgi.kodinov@oracle.com-20100721154924-kvviyf6df2vy7oug) (version source revid:georgi.kodinov@oracle.com-20100721154924-kvviyf6df2vy7oug) (merge vers: 5.0.92) (pib:17)
[22 Jul 2010 15:46] Paul Dubois
Noted in 5.0.92 changelog.
[23 Jul 2010 12:26] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:31] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[2 Aug 2010 7:50] Bugs System
Pushed into 5.0.92 (revid:georgi.kodinov@oracle.com-20100802074824-5201e4ppst9t3yqt) (version source revid:georgi.kodinov@oracle.com-20100721154924-kvviyf6df2vy7oug) (merge vers: 5.0.92) (pib:18)
[2 Aug 2010 19:41] Paul Dubois
Noted in 5.5.6 changelog.
[16 Aug 2010 6:37] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[19 Aug 2010 15:40] Bugs System
Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)
[14 Oct 2010 8:33] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:48] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:02] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 13:11] Jon Stephens
Already documented in the 5.1.24 changelog. No additional changelog entries required. Closed.
[13 Nov 2010 16:19] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[26 Jul 2012 20:16] mandm zoom
In my scenario i am using Server version: 5.1.41-3ubuntu12.8-log (Ubuntu)
and today is the second time in 5 days that i have a session hanging in the db…
+——–+————-+———————+——————-+————-+——–+———————————————————-+————-+
| Id | User | Host | db | Command | Time | State | Info |
+——–+————-+———————+——————-+————-+——–+———————————————————-+————+
| 488807 | user | xx.xx.xx.xx | dbname | Killed | 53139 | init | ALTER TABLE xx_ely DROP email |

yes last time i resolved this by restarting a db..and this time after doing some more research i dont think there is any rollback taking place, so am i hitting a bug?  on ubuntu 10.04 LTS i can go unto (5.1.63-0ubuntu0.10.04.1-log)..is the bug fixed in that version