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

[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] MySQL Verification Team
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.