MySQL Bugs: #60980: SHOW PROCEDURE CODE example returns an error

Bug #60980	SHOW PROCEDURE CODE example returns an error
Submitted:	26 Apr 2011 14:54	Modified:	26 Apr 2011 15:38
Reporter:	Daniël van Eeden	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.47	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	SP

Description:
The example on this page does not work.
http://dev.mysql.com/doc/refman/5.1/en/show-procedure-code.html

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
mysql> SELECT VERSION()//
+-----------+
| VERSION() |
+-----------+
| 5.1.47    |
+-----------+
1 row in set (0.00 sec)

How to repeat:
Try example

Suggested fix:
Fix SHOW PROCEDURE CODE. (Or fix documentation if that's not possible).

It works with debug built. Maybe a documentation or server error messages bu?.

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.57-Win X64-debug-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >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 5.1 >

Behavior on 5.5:

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >SHOW PROCEDURE CODE p1;
ERROR 1289 (HY000): The 'SHOW PROCEDURE|FUNCTION CODE' feature is disabled; you need MySQL built with '--with-debug' to have it working
mysql 5.5 >

I tried it on a server w/o debug. And it's in the docs that this only works on debug enabled servers. So it's not a bug.

Changed status to the correct one.