MySQL Bugs: #13729: Stored procedures: packet error after exception handled

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

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

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?

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

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>

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.)

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

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.

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

Pushed to 5.0.17 bk.

Noted in 5.0.17 changelog.