MySQL Bugs: #71276: Manual does not explain PROCESSLIST_STATE in P

Bug #71276	Manual does not explain PROCESSLIST_STATE in P_S.THREADS properly
Submitted:	2 Jan 2014 15:01	Modified:	11 Mar 2015 23:36
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	missing manual, performance_schema, threads

Description:
This manual page, http://dev.mysql.com/doc/refman/5.6/en/threads-table.html, says the following about PROCESSLIST_STATE column:

"An action, event, or state that indicates what the thread is doing. For descriptions of PROCESSLIST_STATE values, see Section 8.12.5, “Examining Thread Information”."

It is not clear what should be in this column for background threads. I'd assume NULL (nothings) based on column name related to SHOW PROCESSLIST (that does NOT show any background threads). Initial example on the manual page also shows NULL for thread/sql/main, but in reality I see "System lock" there on Windows. 

As a side note, description of "System lock" at http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html does not help at all to understand what this state may mean in this specific case.

How to repeat:
mysql> select thread_id, processlist_state, processlist_command, processlist_id,
 name, instrumented from performance_schema.threads where type='BACKGROUND' and
processlist_state is not null\G
*************************** 1. row ***************************
          thread_id: 1
  processlist_state: System lock
processlist_command: NULL
     processlist_id: NULL
               name: thread/sql/main
       instrumented: YES
1 row in set (0.00 sec)

mysql> show full processlist\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost:60315
     db: test
Command: Query
   Time: 0
  State: init
   Info: show full processlist
1 row in set (0.00 sec)

mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.14-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.18 sec)

Suggested fix:
Make sure all possible values in the PROCESSLIST_STATE column are properly explained for background threads. 

Correct example to reflect output in recent minor versions.

Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

Regarding PROCESSLIST_STATE:

"For background threads (threads not associated with a user connection), PROCESSLIST_ID is NULL, so the values are not unique. (Before MySQL 5.6.9, the value is 0 for background threads.)"

(http://dev.mysql.com/doc/refman/5.6/en/threads-table.html, see Bug#71275)

"As a side note, description of "System lock" at http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html does not help at all to understand what this state may mean in this specific case."

Looks pretty detailed to me.