MySQL Bugs: #3583: query cache doesn't work for stored procedures

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:	Per-Erik Martin	CPU Architecture:	Any

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:

Verified against 5.0.1 with a different test case (attached).  Thank you.

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.

this problem still exists in version 5.0.45

it seems like that is not the problem,sorry

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