Bug #3583 query cache doesn't work for stored procedures
Submitted: 28 Apr 2004 10:01 Modified: 17 Sep 2004 13:50
Reporter: Sergey Kostyliov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 (bk snapshot from 20040426 OS:Linux (Gentoo 2004.1)
Assigned to: Bugs System CPU Architecture:Any

[28 Apr 2004 10:01] Sergey Kostyliov
Description:
Query cache doesn't work for a simple procedure.  

How to repeat:
mysql> SHOW STATUS; 
... 
| Questions                | 1       | 
| Qcache_queries_in_cache  | 0       | 
| Qcache_inserts           | 0       | 
| Qcache_hits              | 0       | 
... 
 
mysql> SELECT COUNT(*) FROM user; 
+----------+ 
| COUNT(*) | 
+----------+ 
|   473233 | 
+----------+ 
1 row in set (14.13 sec) 
 
mysql> SELECT COUNT(*) FROM user; 
+----------+ 
| COUNT(*) | 
+----------+ 
|   473233 | 
+----------+ 
1 row in set (0.00 sec) 
 
mysql> SHOW STATUS; 
... 
| Qcache_queries_in_cache  | 1       | 
| Qcache_inserts           | 1       | 
| Qcache_hits              | 1       | 
... 
 
mysql> delimiter // 
mysql> CREATE PROCEDURE ucount (OUT uc INT) 
    -> BEGIN 
    ->   SELECT COUNT(*) INTO uc FROM user; 
    -> END 
    -> // 
Query OK, 0 rows affected (0.11 sec) 
 
mysql> delimiter ; 
mysql> CALL ucount(@a); 
Query OK, 0 rows affected (9.00 sec) 
 
mysql> SELECT @a; 
+--------+ 
| @a     | 
+--------+ 
| 473233 | 
+--------+ 
1 row in set (0.01 sec) 
 
mysql> CALL ucount(@a); 
Query OK, 0 rows affected (7.15 sec) 
 
mysql> SELECT @a; 
+--------+ 
| @a     | 
+--------+ 
| 473233 | 
+--------+ 
1 row in set (0.00 sec) 
 
mysql> SHOW STATUS; 
... 
| Qcache_queries_in_cache  | 1       | 
| Qcache_inserts           | 1       | 
| Qcache_hits              | 1       | 
... 
 

Suggested fix:
[5 May 2004 19:09] Dean Ellis
Verified against 5.0.1 with a different test case (attached).  Thank you.
[17 Sep 2004 13:50] Per-Erik Martin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Note that the particular example given, SELECT COUNT(*) INTO uc FROM user,
is still not cached. (The same thing for "INTO @x" at the top-level.)
This is because the query cache works on the network layer, and only handles
results sent back to the client.
[18 Jul 2007 9:10] JinRong Ye
this problem still exists in version 5.0.45
[19 Jul 2007 2:03] JinRong Ye
it seems like that is not the problem,sorry

http://forums.mysql.com/read.php?98,162888,162932#msg-162932