Bug #14857 Reading dump files with single statement stored routines fails.
Submitted: 11 Nov 2005 10:10 Modified: 27 Feb 2006 20:13
Reporter: Per-Erik Martin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.17 OS:Any (Any)
Assigned to: Magnus Blåudd CPU Architecture:Any

[11 Nov 2005 10:10] Per-Erik Martin 
Description:
Reading a dump file with stored routines containing just single statement as body (no begin-end) fails with a syntax error. E.g.

/*!50003 CREATE FUNCTION `f`() RETURNS bigint(20)
return 42 */;;

Changing the body to "begin return 42; end" will make it work.

This appears to be a lexer/parser problem, debugging shows that the parser sees the token '*' at the end and thus fails at that point. (Unlike the similar BUG#14723, where it's the extraction of the routine body that's failing.)

How to repeat:
DELIMITER ;;
/*!50003 CREATE FUNCTION `f`() RETURNS bigint(20)
return 42 */;;
/*!50003 CREATE PROCEDURE `p`()
select 42 */;;
DELIMITER ;
[11 Nov 2005 10:38] Valeriy Kravchuk 
Verified just as described on 5.0.17-BK (ChangeSet@1.1957, 2005-11-09 20:31:01+03:00, ...)
[16 Nov 2005 17:06] Per-Erik Martin 
If anyone run into this bug (befor it's fixed), the workaround is to wrap the single statement in a BEGIN-END, e.g.:

/*!50003 CREATE PROCEDURE `p`()
begin select 42; end */;;
[21 Feb 2006 12:43] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2970
[21 Feb 2006 16:06] Per-Erik Martin 
Ok to push with patch for BUG#16878.
[24 Feb 2006 8:16] Magnus Blåudd 
Pushed a fix for bug#16878 to 5.0.19 and 5.1.8 that makes the parser handle "single statement"  functions or procedures with embedded "not version" comments like /*!50001 <feature> */.

Ex of a procedure that couldn't be loaded without this fix.
/*!50003 CREATE PROCEDURE `p`()
select 42 */;;

Added tests for this bug.
[27 Feb 2006 20:13] Mike Hillyer 
Documented in 5.0.19 and 5.1.8:

      <listitem>
        <para>
          Stored routines that contained only a single statement were
          not written properly to the dumpfile when using <filename>mysqldump</filename>.
          (Bug #14857)
        </para>
      </listitem>