Bug #30960 processlist state '*** DEAD ***' on recent 5.0.48 windows builds

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.

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>

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.

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.

The change Jani did looks fine to me. What was wrong with Iggy's last patch.

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.

Queued in 5.0-runtime

Pushed into 5.1.24-rc

Pushed into 5.0.60

Pushed into 6.0.5-alpha

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 ***.

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?

"
[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.

Bug#37194 was marked as duplicate of this one.

Hello
Where can I get the version 5.0.60 ?
The latest avalaible version is the 5.0.51b.

Tks

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??

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 ***