Bug #61043 doubt query cache information at show processlist

• Submitted: 3 May 2011 17:44
• Modified: 7 Jul 2017 9:45
• Reporter: Roberto Spadim (Basic Quality Contributor)
• Status: Won't fix
• Category: MySQL Server: Query Cache
• Severity: S3 (Non-critical)
• Version: 5.5.6, 5.1.59
• OS: Any
• CPU Architecture: Any
• Tags: qc

[3 May 2011 17:44] Roberto Spadim

Description:
hi guys
why i'm seeing "checking query cache for query"
in this query:
"/* pid:4185 spd_user: rspadim */SHOW PROCESSLIST"

should it be checked? 
show processlist should never being checked
or i'm having problem with comments at start of query? "/* */"

How to repeat:
execute
/* pid:4185 spd_user: rspadim */SHOW PROCESSLIST

in a script many times (php for example)

query about show processlist at another client (mysql-cli for exemple)

Suggested fix:
i don't know what checking do, but i think it's should never be cached, could anyone check if it return always 'false' for show processlist and information_schema queries?

[3 May 2011 17:46] Roberto Spadim

without /* */ 
i don't have "checking query cache for query" in state

just execute 
"/* comment*/show processlist"

and this:
"show processlist"

to check it! :) don't need a php script like first post...

[10 May 2011 8:57] Valeriy Kravchuk

What exact version of server, x.y.z, are you working with?

[10 May 2011 17:15] Roberto Spadim

tested with 
mysql 5.1.49
mariadb 5.2.5 i don´t know what mysql compatible version it´s

[20 Jun 2011 11:40] Valeriy Kravchuk

Please, check with a newer version, 5.1.57, and inform about the results. I do not see this problem with current mysql-5.1 tree on Mac.

[20 Jun 2011 11:47] Valeriy Kravchuk

Sorry, I had just used wrong test case (mysql client removes comments by default). This is the correct one:

macbook-pro:5.1 openxs$ while true; do bin/mysql -c -uroot test -e "/* comment */show processlist"; done
+------+------+-----------+------+---------+------+--------------------------------+--------------------------------+
| Id   | User | Host      | db   | Command | Time | State                          | Info                           |
+------+------+-----------+------+---------+------+--------------------------------+--------------------------------+
| 4045 | root | localhost | test | Sleep   |    5 |                                | NULL                           |
| 4046 | root | localhost | test | Query   |    0 | checking query cache for query | /* comment */ show processlist |
+------+------+-----------+------+---------+------+--------------------------------+--------------------------------+
...

Verified with 5.1.59 on Mac OS X.

[20 Jun 2011 22:37] Roberto Spadim

at 5.5.6 have the problem too
/* test */  show processlist

Id;User;Host;db;Command;Time;State;Info
3;root;localhost:22860;NULL;Query;0;checking query cache for query;/* test */  show processlist

[20 Jun 2011 22:38] Roberto Spadim

5.5.6 and 5.1.59 (windows versions) too
mac 5.1.59 too
linux - maria 5.2.5 too

[7 Jul 2017 9:45] Erlend Dahl

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/