MySQL Bugs: #25421: MySQL threads don't respond to the kill command

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

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.

Thank you for a problem report. Please, send the resutls of:

uname -a
getconf GNU_LIBPTHREAD_VERSION
cat /etc/issue

Linux commands.

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)

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.

Please, send SHOW PROCESSLIST results with those threads you can not kill. I want to check if they are in "statistics" state.

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)

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?

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.

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.

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.

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().

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Noted in 5.1.23, 6.0.4 changelogs.

Some threads did not respond to the KILL statement.

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.

Replaced original changelog entry with Serg's text.

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.

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)

Noted in 5.0.92 changelog.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

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)

Noted in 5.5.6 changelog.

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)

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)

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)

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)

Already documented in the 5.1.24 changelog. No additional changelog entries required. Closed.

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)

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