MySQL Bugs: #42597: Misleading error when starting a transaction with BEGIN in a Stored Procedure

Bug #42597	Misleading error when starting a transaction with BEGIN in a Stored Procedure
Submitted:	4 Feb 2009 15:27	Modified:	19 May 2009 20:35
Reporter:	Thomas Keller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.0.67/5.1/6.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
One cannot start a transaction with the BEGIN keyword in a stored procedure, but has to use START TRANSACTION instead. MySQL obviously has a problem with the BEGIN ... END wrapper of an SP, it would be nonetheless nice to have a better error message for this.

How to repeat:
This does not work:

> delimiter $
> create procedure foo() begin  begin; rollback; 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 '; rollback; end' at line 1

This works:

> delimiter $
> create procedure foo() begin  start transaction; rollback; end$
Query OK, 0 rows affected (0.08 sec)

Thank you for the bug report.

BEGIN and BEGIN WORK are currently not working as aliases for START TRANSACTION inside stored procedures. The parser assumes that BEGIN inside a stored procedure is the beginning of a BEGIN..END block and therefore gives syntax error for BEGIN (transaction).

Changing this is difficult for a number of reasons:
- Stored procedures can contain a single statement, so BEGIN..END is optional.
- Nested BEGIN..END blocks are supported.
- Match each END to a BEGIN would require the parser to look potentially quite far ahead.

Giving a more detailed error message is pretty much equal to supporting BEGIN inside stored procedures, so the same issues apply there.

Potential fixes all have drawbacks:
- Using semicolon do separate BEGIN/BEGIN WORK (has semicolon) from BEGIN..END (no semicolon), requires extensive changes to both the lexer and the parser. Also, a semicolon after BEGIN could just as well mean empty statement, so there's ambiguity.
- Removing BEGIN/BEGIN WORK as an alias is not backwards compatible.
- Assuming implicit start of transaction (inside stored procedures) is a change of behavior.

Recommendation: Update the documentation for START TRANSACTION to say that
BEGIN and BEGIN WORK are not supported aliases inside stored procedures.
This should also be included in "D.1. Restrictions on Stored Routines, Triggers, and Events".

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added:

Within stored programs (procedures, functions, triggers, events), the
parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block.
Begin a transaction in this context with START TRANSACTION instead.

To:

http://dev.mysql.com/doc/refman/5.1/en/commit.html
http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html