Bug #42016 show procedure code fails with error 1064 (42000)
Submitted: 10 Jan 2009 16:01 Modified: 11 Feb 2009 3:55
Reporter: Andreas Lund Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:MySQL-server-community-5.1.30-0.rhel4 OS:Linux (CentOS4)
Assigned to: CPU Architecture:Any
Tags: FUNCTION, PROCEDURE, show, stored

[10 Jan 2009 16:01] Andreas Lund
Description:
On my server, trying the example code at 
http://dev.mysql.com/doc/refman/5.1/en/show-procedure-code.html
yields ERROR 1064 (42000)

For stored functions, the same problem appears with "SHOW FUNCTION CODE"

How to repeat:
mysql> DELIMITER //
mysql> CREATE PROCEDURE p1 ()
    ->      BEGIN
    ->        DECLARE fanta INT DEFAULT 55;
    ->        DROP TABLE t2;
    ->        LOOP
    ->          INSERT INTO t3 VALUES (fanta);
    ->          END LOOP;
    ->        END//
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROCEDURE CODE p1//
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 1

Suggested fix:
-
[10 Jan 2009 17:59] MySQL Verification Team
As the docs mention, you have to be running a debug version of the server, then it works exactly as described:

mysql> SHOW PROCEDURE CODE p1//
+-----+----------------------------------------+
| Pos | Instruction                            |
+-----+----------------------------------------+
|   0 | set fanta@0 55                         |
|   1 | stmt 9 "DROP TABLE t2"                 |
|   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
|   3 | jump 2                                 |
+-----+----------------------------------------+
4 rows in set (0.00 sec)

mysql> select version()//
+------------------------+
| version()              |
+------------------------+
| 5.1.30-community-debug |
+------------------------+
1 row in set (0.02 sec)

Try building mysqld with --with-debug option, or run mysqld-debug instead of mysqld.
[11 Feb 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".