Bug #6897 Stored procedure packet error with sql_cache
Submitted: 30 Nov 2004 19:44 Modified: 3 Aug 2005 21:01
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.5-beta-debug OS:Linux (SuSE 9.2)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[30 Nov 2004 19:44] Peter Gulutzan
Description:
If the query cache size is non-zero, and I use "SELECT SQL_CACHE ..." inside a 
stored procedure, twice, I get "Packets out of order ...". If I try to continue, the 
client hangs. 
 

How to repeat:
/* Assume mysqld server has just started, with default query_cache_size = 0 */ 
 
mysql> delimiter // 
mysql> set global query_cache_size = 100000// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table tg (s1 int)// 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create procedure fg () begin select sql_cache * from tg; select sql_cache * from tg; 
end;// 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> call fg()// 
Empty set (0.00 sec) 
 
Packets out of order (Found: 1, expected 5) 
ERROR 2013 (HY000): Lost connection to MySQL server during query 
mysql> select sql_cache * from tg// 
ERROR 2006 (HY000): MySQL server has gone away 
No connection. Trying to reconnect... 
Connection id:    2 
Current database: db5 
 
Empty set (0.01 sec) 
 
/* from this point, no further action is possible with this client */
[30 Nov 2004 23:14] Miguel Solorzano
Thank you for the bug report.
[12 Apr 2005 13:17] Per-Erik Martin
This is no longer repeatable.
[12 Apr 2005 20:52] Peter Gulutzan
I was able to repeat the same error with 5.0.5-beta-debug pulled today.
[13 Apr 2005 9:24] Per-Erik Martin
Now it's repeatable again. Strange...
[12 May 2005 17:21] Per-Erik Martin
Now I can't repeat it again.
valgrind-max build, tried with valgrind and in a debugger. Working fine...
[31 May 2005 1:34] Peter Gulutzan
I'm still getting problems. Try this, immediately after starting server and client:

set global query_cache_size = 100000;
select @@global.query_cache_size;
create table tg (s1 int);//
create procedure fg () begin select sql_cache * from tg; select sql_cache
     * from tg;
     end;//
call fg()//
call fg()//
call fg()//
select sql_cache * from tg//
[27 Jun 2005 20:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26460
[23 Jul 2005 4:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27514
[24 Jul 2005 19:28] Oleksandr Byelkin
Thank you for bugreport!

Bugfix pushed to 5.0.11 source repository
[3 Aug 2005 21:01] Mike Hillyer
Documented in 5.0.11 changelog:

<listitem><para>Multiple <literal>SELECT SQL_CACHE</literal> queries  in a stored procedure causes error and client hang. (Bug #6897)</para></listitem>
[21 Sep 2005 13:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30151