MySQL Bugs: #27177: SHOW PROCESSLIST Syntax - add descriptions for state

Bug #27177	SHOW PROCESSLIST Syntax - add descriptions for state
Submitted:	15 Mar 2007 15:14	Modified:	3 Jul 2007 15:05
Reporter:	Matthew Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	All Versions	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The following states referenced in the Show Processlist Syntax require more detail:

After create, Analyzing, Changing master, Checking master version, Checking table, Connecting to master, Copying to group table, Copying to tmp table, Creating delayed handler, Creating index, Creating sort index, Creating table from master dump, Creating tmp table, Execution of init_command, FULLTEXT initialization, Finished reading one binlog; switching to next binlog, Flushing tables, Killed, Killing slave, Locked, Making temp file , Opening master dump table, Opening table, Opening tables, Processing request, Purging old relay logs, Queueing master event to the relay log, Reading event from the relay log, Reading from net, Reading master dump table data, Rebuilding the index on master dump table, Reconnecting after a failed binlog dump request, Reconnecting after a failed master event read, Registering slave on master, Removing duplicates, Reopen tables, Repair by sorting, Repair done, Repair with keycache, Requesting binlog dump, Rolling back, Saving state, Searching rows for update, Sending binlog event to slave, Sending data, Sorting for group, Sorting for order, Sorting index, Sorting result, System lock, Table lock, Thread initialized, Updating, User lock, Waiting for INSERT, Waiting for master to send event, Waiting for master update, Waiting for slave mutex on exit, Waiting for table, Waiting for tables, Waiting for the next event in relay log, Waiting on cond, Waiting to finalize termination, Waiting to reconnect after a failed binlog dump request, Waiting to reconnect after a failed master event read, Writing to net, allocating local table, cleaning up, closing tables, converting HEAP to MyISAM, copy to tmp table, creating table, deleting from main table, deleting from reference tables, discard_or_import_tablespace, end, freeing items, got handler lock, got old table, info, init, insert, logging slow query, login, preparing, purging old relay logs, query end, removing tmp table, rename, rename result table, reschedule, setup, starting slave, statistics, storing row into queue, unauthenticated user, update, updating, updating main table, updating reference tables, upgrading lock, waiting for delay_list, waiting for handler insert, waiting for handler lock, waiting for handler open, Waiting for event from ndbcluster

How to repeat:
Go to 
http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html 
and read the documentation.

Suggested fix:
Please add more detail to the 'states' section of the Show Processlist list.

Thank you for a reasonable documentation request.

Can you be more specific? Many of the items in the list
seem pretty obvious or self-explanatory (such as Creating index)
and require no more detail. Thanks.

Can you be more specific? Many of the items in the list
seem pretty obvious or self-explanatory (such as Creating index)
and require no more detail. Thanks.

Matthew: Paul asked to be more specific as to which states should be documented in more detail, saying that, for example, "Creating index" was a rather obvious state. In your reply, you've just pasted the full list from the Reference Manual, which isn't exactly an answer to Paul's question. :-) Can you please come up with a more exact (and thus shorter) list? Thanks!

Stefan,

To be more specific, I'd like to get the following states documented in details:

- Analyzing
- Copying to group table
- Copying to tmp table
- Creating delayed handler
- Creating sort index 
- Creating tmp table 
- Execution of init_command
- Killed (including reasons why thread may "hang" in this state for ages)
- Killing slave
- Locked
- Making temp file
- Opening table vs. Opening tables
- Reading from net vs. Writing to net
- Rolling back
- Sending data
- Sorting for group vs. Sorting for order vs. Sorting index vs Sorting result, 
- System lock 
- Waiting for INSERT
- Waiting for table vs. Waiting for tables
- Waiting to finalize termination
- allocating local table
- cleaning up
- converting HEAP to MyISAM
- copy to tmp table (incuding reaosns why thread may hang for ages in this state)
- discard_or_import_tablespace
- info
- removing tmp table
- rename result table
- reschedule
- storing row into queue
- upgrading lock
- Waiting for event from ndbcluster

I hope, this list is better. Most states are included because I saw/remember problems with their interpretation. Others are just never seen, so they are here based on pure interest. It is not always possible/efficient/reasonable to lookup entire sources to understand these states.

I've asked the developers to provide descriptions.
-Stefan

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Thread command and state descriptions now are listed/described beginning here:

http://dev.mysql.com/doc/mysql/en/thread-information.html