Bug #17424 Unable to edit stored proceedures when connecting from NOT localhost
Submitted: 15 Feb 2006 12:22 Modified: 15 Feb 2006 20:50
Reporter: Charlie Farrow Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:1.1.7 OS:Microsoft Windows (WIN XP SP 2)
Assigned to:

[15 Feb 2006 12:22] Charlie Farrow
Description:
When connecting to a server not on local host, you are not able to view the stored proceedures when you click 'edit stored proceedure'.

How to repeat:
Try it.

Suggested fix:
Let you edit them!
[15 Feb 2006 13:09] Miguel Solorzano
Are the user's access privileges limited to the schema where the
stored procedure was created?. Thanks in advance.
[15 Feb 2006 18:15] Charlie Farrow
Yes; The user only has access priviliges to the schema where the proceedures were created.
[15 Feb 2006 20:50] Miguel Solorzano
Thank you for the feedback and bug report. I don't think this a Administrator's bug instead is the consequence of bug:

http://bugs.mysql.com/bug.php?id=14564

which basically not permits to get the create procedure
statement for to be edited. Example creating a procedure on my
Linux box:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot db55
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> create procedure sp55()
    -> begin
    -> select 1+1;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure sp55\G
*************************** 1. row ***************************
       Procedure: sp55
        sql_mode: 
Create Procedure: CREATE PROCEDURE `sp55`()
begin
select 1+1;
end
1 row in set (0.00 sec)

mysql> grant all on db55.* to 'user55'@'%' identified by 'user55'//
Query OK, 0 rows affected (0.00 sec)

and then from my XP box:

c:\mysql\bin>mysql -h192.168.0.119 -uuser55 -p db55
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> call sp55();
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.08 sec)

Query OK, 0 rows affected (0.08 sec)

mysql> show create procedure sp55;
+-----------+----------+------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+------------------+
| sp55      |          |                  |
+-----------+----------+------------------+
1 row in set (0.08 sec)

mysql>

Then giving the below privileges:

mysql> grant all on mysql.proc to 'user55'@'%' identified by 'user55'//
Query OK, 0 rows affected (0.00 sec)

from the XP:

mysql> show create procedure sp55;
+-----------+----------+-------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                |
+-----------+----------+-------------------------------------------------+
| sp55      |          | CREATE PROCEDURE `sp55`()
begin
select 1+1;
end |
+-----------+----------+-------------------------------------------------+
1 row in set (0.08 sec)

mysql>

and using Administrator connecting from XP I edited the procedure:

mysql> show create procedure sp55\G
*************************** 1. row ***************************
       Procedure: sp55
        sql_mode: 
Create Procedure: CREATE PROCEDURE `sp55`()
begin
select 1+2;
end
1 row in set (0.00 sec)

mysql>