Bug #6478 A store procedure causes mysql server to hang out

• Submitted: 6 Nov 2004 18:42
• Modified: 6 Nov 2004 19:36
• Reporter: Nelson
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• OS: Windows (WinXP Pro)
• CPU Architecture: Any

[6 Nov 2004 18:42] Nelson

Description:
The following store procedure causes my MySQL 5.0.0-alpha-nt via TCP/IP server to hang out and stop, so it's needed to restart it again:

How to repeat:
Executes the following sql in mysql console client:

delimiter //;
DROP PROCEDURE IF EXISTS a_procedure//
CREATE PROCEDURE a_procedure ()
BEGIN
DECLARE total int;
DECLARE campo1 int;
DECLARE campo2 varchar(100);
Set total = 2;
IF @total = 1 THEN
SET campo1 = 1;
SET campo2 = '';
ELSE
SET campo1 = 45;
SET campo2 = 'El presupuesto no existe.';
END IF;
SELECT campo1 as col1, campo2 as col2;
END
//
call a_procedure()//

Suggested fix:
Surely the procedure is not legal, but mysqld should report that with an error message instead of go down, because other users using the server dont have to pay for _my_ wrong procedure. I also reproduced it with a non-root user who only has access to one database.

[6 Nov 2004 19:36] MySQL Verification Team

Thank you for the bug report. This issue was already fixed on current
BK source:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-nt-log

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

mysql> delimiter //;
mysql> DROP PROCEDURE IF EXISTS a_procedure//
Query OK, 0 rows affected, 1 warning (0.22 sec)

mysql> CREATE PROCEDURE a_procedure ()
    -> BEGIN
    -> DECLARE total int;
    -> DECLARE campo1 int;
    -> DECLARE campo2 varchar(100);
    -> Set total = 2;
    -> IF @total = 1 THEN
    -> SET campo1 = 1;
    -> SET campo2 = '';
    -> ELSE
    -> SET campo1 = 45;
    -> SET campo2 = 'El presupuesto no existe.';
    -> END IF;
    -> SELECT campo1 as col1, campo2 as col2;
    -> END
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> call a_procedure()//
+--------+---------------------------+
| campo1 | campo2                    |
+--------+---------------------------+
|     45 | El presupuesto no existe. |
+--------+---------------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)