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

[3 Apr 2007 22:02] Arjen Lentz
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.
[3 Apr 2007 22:02] Arjen Lentz
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.
[4 Apr 2007 4:03] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[25 Jul 2007 21:15] Sveta Smirnova
test case

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

[25 Jul 2007 21:15] Sveta Smirnova
Bug still exists.
[25 Jul 2007 21:17] Sveta Smirnova
Re-tested with current 5.1 BK
[26 Jul 2007 22:55] Arjen Lentz
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?
[27 Jul 2007 6:20] Sveta Smirnova
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.
[27 Jul 2007 9:42] Arjen Lentz
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.
[26 Oct 2007 10:38] Magnus BlÄudd
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"
[26 Oct 2007 22:57] Arjen Lentz
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...
[10 Apr 2008 13:09] Kristofer Pettersson
There are proc-comments in 6.0. Can we close this now?
[10 Apr 2008 23:56] Arjen Lentz
My opinion on this: http://arjen-lentz.livejournal.com/108138.html
[11 Apr 2008 2:25] James Day
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.
[12 Apr 2008 4:02] James Day
Kristofer, please give the 6.0 version in which they were added so docs can add them to the correct release note.
[11 Jul 2008 17:09] Trudy Pelzer
Comments on this report seem to indicate this is
no longer an issue; please re-verify. Thanks.
[15 Dec 2008 1:47] James Day
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.
[16 Jan 2009 0:00] Bugs System
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".