MySQL Bugs: #44997: The mysql compound-statement signal parses with errors.

Bug #44997	The mysql compound-statement signal parses with errors.
Submitted:	20 May 2009 22:22	Modified:	26 May 2009 17:17
Reporter:	Alex Katebi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	6.0.10	OS:	Linux
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The signal staement does not parse without errors.

How to repeat:
This example from the reference manual does not parse.

CREATE PROCEDURE p (divisor INT)
BEGIN
DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
IF divisor = 0 THEN
SIGNAL divide_by_zero;
END IF;
END;

Suggested fix:
none

Thank you for the bug report. Please use DELIMITER:

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

c:\dbs>60C

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.12-alpha-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 6.0 > USE test
Database changed
mysql 6.0 > DELIMITER $$
mysql 6.0 > CREATE PROCEDURE p (divisor INT)
    -> BEGIN
    -> DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012';
    -> IF divisor = 0 THEN
    -> SIGNAL divide_by_zero;
    -> END IF;
    -> END$$
Query OK, 0 rows affected (0.07 sec)

mysql 6.0 > DELIMITER ;
mysql 6.0 >

I did use the delimiter. The problem is not the delimiter. Can you try it yourself? It will not work.

OK. I did it with test.p rather than use test.

use test
delimiter $$
mysql> CREATE PROCEDURE P(divisor int) BEGIN DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; IF divisor = 0 THEN  SIGNAL divide_by_zero; END IF; END$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'divide_by_zero; END IF; END' at line 1

The SIGNAL / RESIGNAL statement is available starting with 6.0.11, not 6.0.10:

http://dev.mysql.com/doc/refman/6.0/en/news-6-0-11.html

So, a 6.0.10 server will not know about SIGNAL and generate a syntax error,
as expected.

The original report is correct that the examples from the documentation do not
work when pasted verbatim, because of the delimiter.

Re-opening this bug, assigning to the doc team.

Is 6.0.11 available? Where can I get it from?
Thanks so much!

http://dev.mysql.com/downloads/mysql/6.0.html

With 6.0.11 or higher and use of the delimiter, the example parses correctly.