Bug #30960 processlist state '*** DEAD ***' on recent 5.0.48 windows builds
Submitted: 11 Sep 2007 23:59 Modified: 15 Apr 2008 1:35
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.0.48 OS:Microsoft Windows
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: bfsm_2007_10_18, regression
Triage: D2 (Serious)

[11 Sep 2007 23:59] Shane Bester
Description:
I noticed this recently with my own 5.0BK, but thought it was a problem with my builds.  Not sure if it's a bug or not.  Opening this report in case some investigation is needed.

Now I test official 5.0.48-enterprise builds, and see similar issues.

Many queries show entry in processlist with State like this:

     Id: 11
   User: root
   Host:
     db: test
Command: Query
   Time: 301
  State: *** DEAD ***
   Info: check table rainbow_spatial extended

The box is functioning normally, and the queries do change state and complete successfully.   I've never noticed this *** DEAD *** before.

In sql_show.cc, this code exists:

#if !defined(DONT_USE_THR_ALARM) && ! defined(SCO)
        if (pthread_kill(tmp->real_id,0))
          tmp->proc_info="*** DEAD ***";        // This shouldn't happen
#endif

How to repeat:
Run any long queries, and keeping checking processlist.  I can upload some testcase later if needed.

Suggested fix:
I think the following patch introduced the problem, because now pthread_dummy returns 3 (ESRCH) (indicating an error) instead of 0 (success).

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

so now we get false reports of DEAD threads in processlist.
[12 Sep 2007 1:24] Miguel Solorzano
Thank you for the bug report.

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 11
   User: root
   Host: localhost:49373
     db: test
Command: Query
   Time: 9
  State: *** DEAD ***
   Info: select max(updated) from bigtable
*************************** 2. row ***************************
     Id: 12
   User: root
   Host: localhost:49721
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

mysql> show variables like "%version%"
    -> ;
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.50-nt           |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>
[12 Dec 2007 20:56] 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/39824

ChangeSet@1.2578, 2007-12-12 15:53:46-05:00, iggy@amd64.(none) +1 -0
  Bug#30960 processlist state '*** DEAD ***' on recent 5.0.48 windows builds
  - pthread_kill return values should only be checked on platforms that
  implement it.
[12 Dec 2007 21:36] Sergei Golubchik
The code worked before. It was broken recently by the changeset
jani@hynda.mysql.fi|ChangeSet|20070816142548|01030

-#define pthread_kill(A,B) pthread_dummy(0)
+#define pthread_kill(A,B) pthread_dummy(ESRCH)

in my_pthread.h
note that pthread_dummy(X) is defined as 'return X'

Now you need to ask Jani why he changed that. And revert the change if necessary.
[4 Feb 2008 15:48] Magnus BlÄudd
The change Jani did looks fine to me. What was wrong with Iggy's last patch.
[19 Mar 2008 18:02] 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/44250

ChangeSet@1.2599, 2008-03-19 15:01:03-03:00, davi@mysql.com +1 -0
  Bug#30960 processlist state '*** DEAD ***' on recent 5.0.48 windows builds
  
  The problem is that unimplemented WIN32 version of pthread_kill
  is returning ESRCH no matter the arguments, causing calls to
  mysqld_list_processes to set the procinfo to dead because
  pthread_kill returns non zero. The dead procinfo would show
  up on a second invocation of show processlist.
[19 Mar 2008 18:42] Davi Arnaut
Queued in 5.0-runtime
[26 Mar 2008 14:13] Bugs System
Pushed into 5.1.24-rc
[26 Mar 2008 14:13] Bugs System
Pushed into 5.0.60
[27 Mar 2008 17:49] Bugs System
Pushed into 6.0.5-alpha
[15 Apr 2008 1:33] Paul Dubois
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

On WIndows, SHOW PROCESSLIST could display process entries with a
State value of *** DEAD ***.
[24 May 2008 0:10] Andras Gyomrey
Hello, I'm sorry if this bug was corrected on version 5.0.51a on windows XPSP2. I can still reproduce it selecting a large amount of two tables using a left join on one of the key of them. Is that already fixed on a newer version?
[24 May 2008 3:32] Paul Dubois
"
[15 Apr 3:33] Paul DuBois
Noted in 5.0.60, 5.1.24, 6.0.5 changelogs.

On WIndows, SHOW PROCESSLIST could display process entries with a
State value of *** DEAD ***.
"

So it's fixed in 5.0.60, 5.1.24, and 6.0.5.
[4 Jun 2008 15:55] Tonci Grgin
Bug#37194 was marked as duplicate of this one.
[8 Jul 2008 13:00] Jonatas Cruz
Hello
Where can I get the version 5.0.60 ?
The latest avalaible version is the 5.0.51b.

Tks
[11 Dec 2008 11:30] Andrej Pintar
I have 5.0.51b - nt version. Using Windows 2008 x64. And mysql version is also x64. So while using replication and looking at the process list BINLOG Dump goes **DEAD** every 2 times I refresh (Has sent..., refresh, DEAD, refresh, Has sent..) and so on.

Is this normal. Could this slow down replication??
[5 Oct 2010 3:26] Roel Van de Paar
This can apparently also show in SHOW ENGINE INNODB STATUS:

MySQL thread id 329829, query id 3492843 server.somesite.com 10.0.0.1 root *** DEAD ***