MySQL Bugs: #34304: Missing BEGIN in a stored procedure results in vague error

Bug #34304	Missing BEGIN in a stored procedure results in vague error
Submitted:	5 Feb 2008 11:23	Modified:	12 Feb 2008 0:19
Reporter:	Philip Stoev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	6.0.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a CREATE PROCEDURE is missing a BEGIN, MySQL will report a syntax error starting at the ending semicolon on the first line of the procedure body.

In my humble opinion this message is not indicative as to the cause of the error.

How to repeat:
mysql> delimiter |
mysql> CREATE PROCEDURE p1()
    ->         SET @a = 1;
    ->         SET @a = 2;
    ->         SET @a = 3;
    ->         SET @a = 4;
    ->         SET @a = 5;
    ->         SET @a = 6;
    ->         SET @a = 7;
    ->         SET @a = 8;
    -> 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 ';
        SET @a = 2;
        SET @a = 3;
        SET @a = 4;
        SET @a = 5' at line 2

Suggested fix:
I would humbly suggest that the grammar is made so that the lack of a BEGIN block triggers an immediate error closer to the actual error location.

Thank you for the report.

But:

mysql> delimiter |
mysql> CREATE PROCEDURE p1() SET @a = 1; |
Query OK, 0 rows affected (0.36 sec)

is valid syntax.

So error

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 ';
        SET @a = 2;
        SET @a = 3;
        SET @a = 4;
        SET @a = 5' at line 2

looks correct for me.

So I'd like to close this report as "Not a Bug". If you don't agree, please, provide more detailed explanation.

Oh I see. Distinguishing between the two forms and printing a better error message is still theoretically possible, however the current parser may not have the capacity for that.

Please close with "Not a bug" unless you feel that it is OK to leave it as a feature request.

Set to verified and feature request. As Philip (the reporter) wished.

There's no way a parser can do that. Any parser, not just current.

MySQL supports multiple statements in one packet, you can write

SELECT 1; SELECT 2; SELECT 3;

and send that to MySQL in one mysql_real_query() call - it'll work correctly, this "multistatement" will return three result sets.

That is,

CREATE PROCEDURE p1() SET @a = 1; SET @a = 2;

is a valid and syntactically correct "multistatement", two queries the first creates a procedure, the seconds assigns variable to a value.

You get a syntax error because MySQL command line client tells the server to disable support for "multistatements" for this connection (no CLIENT_MULTI_STATEMENTS in mysql_real_connect).