Bug #31358 Manual and Community-Server differ in DECLARE-syntax

• Submitted: 2 Oct 2007 23:14
• Modified: 3 Oct 2007 0:11
• Reporter: Christoffer Anselm
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.25-community
• OS: Any
• CPU Architecture: Any
• Tags: DECLARE, documentation

[2 Oct 2007 23:14] Christoffer Anselm

Description:
I try to write a stored procedure but I'm not able to use local variables as the syntax accepted by the mysql-server differs from the syntax described in the manual.

To be more specific the ducumentation entries for DECLARE and example-code of the Cursors entry say the syntax of the following statement is absolutely correct, but the mysql server reports an error.

The SQL-query I use is a cut down version of the example-code provided in the manual in the Cursors section that causes exactly the same errormessage.

The SQL-query is:

CREATE PROCEDURE donations_calculate () MODIFIES SQL DATA
BEGIN
  DECLARE i_done INT DEFAULT 0;
END

The errormessage is:

#1064 - 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 '' at line 3

Links to the ducumentation entries:
- DECLARE:
18.2.7.1. DECLARE Local Variables
http://dev.mysql.com/doc/refman/5.0/en/declare-local-variables.html

- Cursors:
18.2.9. Cursors
http://dev.mysql.com/doc/refman/5.0/en/cursors.html

How to repeat:
execute this SQL-query:

CREATE PROCEDURE donations_calculate () MODIFIES SQL DATA
BEGIN
  DECLARE i_done INT DEFAULT 0;
END

[2 Oct 2007 23:27] Paul DuBois

Did you remember to change the statement delimiter before defiining the procedure? Remember that you are using compound-statement syntax, so the ';' in the middle of the procedure will be misinterpreted by mysql unless you redefine the delimiter:

mysql> CREATE PROCEDURE donations_calculate () MODIFIES SQL DATA
    -> BEGIN
    ->   DECLARE i_done INT DEFAULT 0;
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 '' at line 3
mysql> 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 'END' at line 1
mysql> delimiter //
mysql> CREATE PROCEDURE donations_calculate () MODIFIES SQL DATA
    -> BEGIN
    ->   DECLARE i_done INT DEFAULT 0;
    -> END
    -> //
Query OK, 0 rows affected (0.05 sec)

[3 Oct 2007 0:05] MySQL Verification Team

Thank you for the bug report. Please see Paul's comment.

[3 Oct 2007 0:11] Christoffer Anselm

Thanks for that information! Seems that I didn't read the 'CREATE PROCEDURE' manual page as carefully as I should.

Stored procedures and declare-statements now work on my machine as they should :)