MySQL Bugs: #27628: Processlist shows status NULL when server is purging/managing query cache

Bug #27628	Processlist shows status NULL when server is purging/managing query cache
Submitted:	3 Apr 2007 22:02	Modified:	16 Jan 2009 15:33
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any (any)
Assigned to:	Timothy Smith	CPU Architecture:	Any
Tags:	bfsm_2007_07_19, bfsm_2007_08_02, bfsm_2007_10_18, qcache, query cache, show processlist

Description:
SHOW PROCESSLIST shows status NULL when the server is busy purging or otherwise managing the query cache in a particular connection. This makes tracking down problems more difficult as it's not obvious where the server is "stuck".

Issues affected by http://bugs.mysql.com/21074 and http://bugs.mysql.com/26460 took much longer to track down because of this.

How to repeat:
This is easy to show/repeat while bug 21074 still exists.

 - Create a very big query cache
 - Issue lots of different select queries on one underlying table.
 - Issue one update statement on underlying table.

The last step will put that connection into the NULL state for considerable time while the query cache code does its thing.

Suggested fix:
Change to status = "Query Cache Purge" or something similar and relevant.
Docs (SHOW PROCESSLIST section) will need to be updated for this also.

This is set as a bug not feature request since
 - it hinders tracking down existing bugs/problems
 - it'll be peanuts to fix
 - could possibly be fixed as part of the http://bugs.mysql.com/21074 work.

Thank you for a bug report. Verified just as described.

test case

Attachment: bug27628.test (application/octet-stream, text), 10.29 KiB.

Bug still exists.

Re-tested with current 5.1 BK

As commented at the start, this is bug not feature request since
 - it hinders tracking down existing bugs/problems (customers?)
 - it should be peanuts to fix (don't make me embarass you with a patch on my blog!)

I don't see in the (public) comments whether the link with http://bugs.mysql.com/21074 has been checked?

Arjen, thank you for the feedback.

Link to bug #21074 has been checked. Bug #21074  has been fixed, but fix for it does not solve this one.

Hi Sveta - so how does the change in status (from verified bug to feature request) affect the scheduling of a fix? Bugs tend to get fixed, feature requests tend to hang around for a loooong time. Can't be good for those users tripping over this, or for support engineers trying to track down the related problems and being unable to see the proper status.

The idea of the suggested patch looks fine, of course it shall be possible to see what the server's thread are doing - and especially in longer running operations.

But please make sure it really reflects _what_ it does. So for example when flushing tables, use something like:

 thd->proc_info="Flushing table from cache";

and the other places should surely have other texts associated with them, thus there is no need for the global variable static "const char* proc_str"

Euh, so why is this issue scheduled for 6.0 ?
As Magnus wrote, it's a tiny patch.
Has no operational impact, but it would greatly help MySQL support engineers and others do their work, and of course it also assists MySQL users/customers identify what's going on in the server.

I see from the tags that this item has been discussed in 3 bug priority meetings over the course of 4 months... that seems like a lot of effort, compared to the size of the actual issue?
Just wondering...

There are proc-comments in 6.0. Can we close this now?

My opinion on this: http://arjen-lentz.livejournal.com/108138.html

The underlying bugs in the query cache that could cause it to take a long time seem to have been fixed, so this currently doesn't seem to have substantial impact. Now it's more to help identify whether there are any other issues with it. Doesn't seem worth risking breaking 5.0; would be useful in 5.1 in case there are outstanding query cache issues there.

Kristofer, please give the 6.0 version in which they were added so docs can add them to the correct release note.

Comments on this report seem to indicate this is
no longer an issue; please re-verify. Thanks.

Last call: anyone seeing any issues in production systems with current versions that make this of great interest in 5.0 or 5.1? If not I'll close as fixed in 6.0 already and wontfix for earlier versions.

The other bug fixes to the query cache have eliminated the issues that made this a pressing request and the impact on users is currently minimal. The Support team no longer encounters issues where this is significant.

James Day, MySQL Senior Support Engineer, Sun Microsystems.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".