Bug #6897 Stored procedure packet error with sql_cache

• Submitted: 30 Nov 2004 19:44
• Modified: 3 Aug 2005 21:01
• Reporter: Peter Gulutzan
• Status: Closed
• 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] MySQL Verification Team

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