Bug #1655 Stored Procedure causes packets out of order

• Submitted: 24 Oct 2003 20:00
• Modified: 27 Oct 2003 3:05
• Reporter: Peter Gulutzan
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Linux (SuSE 8.2)
• CPU Architecture: Any

[24 Oct 2003 20:00] Peter Gulutzan

Description:
A stored procedure causes packets out of order if it contains an EXPLAIN statement. 
 
Possibly related: a stored procedure causes packets out of order if it contains a SELECT. 

How to repeat:
mysql> create procedure p1 () explain t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p1(); 
+-------+---------+------+-----+---------+-------+ 
| Field | Type    | Null | Key | Default | Extra | 
+-------+---------+------+-----+---------+-------+ 
| s1    | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+ 
1 row in set (0.76 sec) 
 
mysql> drop procedure p1; 
Packets out of order (Found: 11, expected 1) 
ERROR 2013 (HY000): Lost connection to MySQL server during query 
 
.... alternatively, to cause packets out of order with SELECT instead of with EXPLAIN: 
 
mysql> create table t (s1 int); 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create procedure p1 () select s1 from t; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p1(); 
Empty set (0.00 sec) 
 
mysql> drop procedure p1; 
Packets out of order (Found: 5, expected 1) 
ERROR 2013 (HY000): Lost connection to MySQL server during query

[27 Oct 2003 3:05] Indrek Siitan

Cannot repeat with the latest (2003-10-27) BK tree:

mysql> create procedure p1 () explain t1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p1(); 
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| E1    | int(10) unsigned |      | PRI | 0       |       |
| E2    | int(10) unsigned |      |     | 0       |       |
| E3    | int(10) unsigned |      |     | 0       |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> drop procedure p1; 
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (s1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> create procedure p1 () select s1 from t; 
Query OK, 0 rows affected (0.00 sec)

mysql> call p1(); 
ERROR 1289: SELECT in a stored procedure must have INTO

mysql> drop procedure p1; 
Query OK, 0 rows affected (0.01 sec)

[3 Jan 2004 9:59] Peter Gulutzan

The bug is still there. I will try to say "how to repeat" starting from the beginning of the 
session, with a new database, with a new table. The following is an unedited 
cut-and-paste from my screen. I did a 'bk pull' today. 
 
How to repeat: 
 
pgulutzan@aijj42nay42q9:~> /usr/local/mysql/bin/mysql --user=root 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 29 to server version: 5.0.0-alpha-debug 
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 
 
mysql> create database db100; 
Query OK, 1 row affected (0.00 sec) 
 
mysql> use db100; 
Database changed 
mysql> create table t100 (s1 int); 
Query OK, 0 rows affected (0.41 sec) 
 
mysql> create procedure p100 () explain t100; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p100(); 
+-------+---------+------+-----+---------+-------+ 
| Field | Type    | Null | Key | Default | Extra | 
+-------+---------+------+-----+---------+-------+ 
| s1    | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+ 
1 row in set (0.00 sec) 
 
mysql> drop procedure p100; 
Packets out of order (Found: 11, expected 1) 
ERROR 2013 (HY000): Lost connection to MySQL server during query

[9 Feb 2004 16:52] Peter Gulutzan

It is still returning 'packets out of order'. It no longer happens with SELECT. But it does still 
happen if the procedure contains any of: 
EXPLAIN 
SHOW PRIVILEGES 
SHOW TABLE STATUS 
SHOW PROCESS LIST 
 
Perhaps it's only repeatable with version 5.0.0-alpha-debug.

[15 Feb 2004 9:03] Peter Gulutzan

I can also get 'packets out of order' with HANDLER. For example: 
 
(Table t was defined earlier with "create table t (s1 int)".) 
 
mysql> create procedure p1 () begin handler t open; handler t read first; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p1()// 
+------+ 
| s1   | 
+------+ 
|    1 | 
+------+ 
1 row in set (0.00 sec) 
 
mysql> call p1()// 
Packets out of order (Found: 6, expected 1) 
ERROR 2013 (HY000): Lost connection to MySQL server during query