Bug #14857 Reading dump files with single statement stored routines fails.
Submitted: 11 Nov 2005 11:10 Modified: 27 Feb 2006 21:13
Reporter: Per-Erik Martin
Status: Closed
Category:Server: SP Severity:S2 (Serious)
Version:5.0.17 OS:Any (Any)
Assigned to: Magnus Svensson Target Version:

[11 Nov 2005 11: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 11: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 18: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 13: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 17:06] Per-Erik Martin 
Ok to push with patch for BUG#16878.
[24 Feb 2006 9:16] Magnus Svensson 
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 21: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>