Bug #13729 Stored procedures: packet error after exception handled
Submitted: 4 Oct 2005 0:49 Modified: 30 Nov 2005 17:58
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.15-rc-debug OS:Linux (SUSE 9.2)
Assigned to: Bugs System CPU Architecture:Any

[4 Oct 2005 0:49] Peter Gulutzan
Description:
I have a stored procedure which contains an UPDATE statement which causes an exception which is handled by a "CONTINUE" handler. After I call the procedure, the error "Packets out of order ..." soon follows.

How to repeat:
(after "delimiter //")

mysql> create table t32 (s1 int, primary key (s1))//
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t32 values (1),(2)//
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create procedure p32 () begin declare continue handler for sqlexception select 55; update t32 set s1 = 1; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p32()//
+----+
| 55 |
+----+
| 55 |
+----+
1 row in set (0.01 sec)

mysql> drop procedure p32//
Packets out of order (Found: 6, expected 1)
ERROR 2013 (HY000): Lost connection to MySQL server during query
[4 Oct 2005 15:18] Valeriy Kravchuk
Can't repeat on today's 5.0.15-BK build (ChangeSet@1.2009, 2005-10-04 04:03:48+02:00, kent@mysql.com) on Fedora Core 1 as described:

mysql> delimiter //
mysql> create table t32 (s1 int, primary key (s1))//
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t32 values (1),(2)//
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create procedure p32 () begin declare continue handler for sqlexception
    -> select 55; update t32 set s1 = 1; end//
Query OK, 0 rows affected (0.03 sec)

mysql> call p32()//
+----+
| 55 |
+----+
| 55 |
+----+
1 row in set (0.00 sec)

mysql> drop procedure p32//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t32//
+----+
| s1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> create procedure p32 () begin declare continue handler for sqlexception
    -> select 55; update t32 set s1 = 1; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p32()//
+----+
| 55 |
+----+
| 55 |
+----+
1 row in set (0.00 sec)

mysql> drop procedure p32//
Query OK, 0 rows affected (0.00 sec)

mysql> select version();
    -> //
+-----------+
| version() |
+-----------+
| 5.0.15-rc |
+-----------+
1 row in set (0.00 sec)

What I am missing? --with-debug option for build?
[6 Oct 2005 21:26] Peter Gulutzan
I can repeat the error, using mysqld built today (2005-10-06) with
BUILD/compile-pentium-debug
and starting mysqld with
mysqld --user=root --skip-networking
[7 Oct 2005 2:41] Miguel Solorzano
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create table t32 (s1 int, primary key (s1))//
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t32 values (1),(2)//
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create procedure p32 () begin declare continue handler for sqlexception
    -> select 55; update t32 set s1 = 1; end//
Query OK, 0 rows affected (0.00 sec)

mysql> call p32()//
+----+
| 55 |
+----+
| 55 |
+----+
1 row in set (0.01 sec)

mysql> drop procedure p32//
Packets out of order (Found: 6, expected 1)
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[7 Oct 2005 16:00] Per-Erik Martin
The handler/SP things are working ok, but something goes wrong in the update statement when an error occurs - it seems packages are sent even if it should have been aborted...
(Using an "insert" that generates the same exception works for instance.)
[21 Oct 2005 11:00] 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/31285
[18 Nov 2005 14:31] Konstantin Osipov
As discussed on IRC, we need a patch that makes sure that thd->is_fatal_error is not set on DUPLICATE KEY. This bug should be fixed in a manner that terminates SP execution if thd->is_fatal_error is set.
[23 Nov 2005 11:02] 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/32589
[24 Nov 2005 19:32] Per-Erik Martin
Pushed to 5.0.17 bk.
[30 Nov 2005 17:58] Paul Dubois
Noted in 5.0.17 changelog.