1

I am using this thread -

Rename a mysql procedure

to rename a stored procedure

Here upon trying the command as shown in the answer -

UPDATE `mysql`.`proc`
SET name = '<new_proc_name>',
specific_name = '<new_proc_name>'
WHERE db = '<database>' AND
  name = '<old_proc_name>';

I get the error -

ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist while calling a stored procedure

Here regarding the other questions regarding mysql.proc does not exit, none address the specific problem of calling a stored procedure.

Payel Senapati
  • 1,134
  • 1
  • 11
  • 27
  • 1
    That table was removed in MySQL 8.0. – Barmar Oct 19 '21 at 23:06
  • 1
    I [can't find even a feature request](https://bugs.mysql.com/search.php?search_for=rename+procedure&status%5B%5D=Open&severity=-4&limit=10&order_by=&cmd=display&phpver=&os=0&os_details=&bug_age=0&cpu_arch=0&cpu_arch_details=&last_updated=0&tags=&similar=) for this. – danblack Oct 20 '21 at 00:01

2 Answers2

4

The mysql.proc table was removed in MySQL 8.0. See No more mysql.proc in MySQL 8.0

You can use information_schema.routines to get information about stored procedures. But this is a read-only view, you can't update it. So I don't think there's any simple way to rename procedures any more. You may be able to use dynamic SQL to define the procedure with the new name using this information.

EDIT:

Unfortunately, the above is not possible just in MySQL, because CREATE PROCEDURE can't be executed using PREPARE, and information_schema.routines doesn't contain all the information needed to recreate the procedure. You could do it in an external language by performing a SHOW CREATE PROCEDURE query and then replacing the name to form a new query.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Please show me how to use dynamic SQL to define the procedure with new name – Payel Senapati Oct 19 '21 at 23:32
  • 1
    I [got this far](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5f56fb2e96b920c6d277285e1deaf0e4) before giving up. – danblack Oct 19 '21 at 23:55
  • You're pretty close, but you're missing the parameter list in the dynamic SQL. – Barmar Oct 20 '21 at 01:09
  • Of course, which isn't in the IS view. And take all the other definer/sql mode args to recreate it exactly. [apparently](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d411c56783cd331f724562c5640b0ccd) "This command is not supported in the prepared statement protocol yet". So grabbing the sql from mysqldump --routines, editing the SQL for the routine name and re-importing is maybe less burdensome. I agree with you @PayelSenapati this is pretty horrible. – danblack Oct 20 '21 at 01:35
0

Recommend avoid fiddling with any mysql table directly.

Use show create procedure old_proc_name

And then create procedure new_proc_name ....

And drop the old drop procedure old_proc_name

danblack
  • 12,130
  • 2
  • 22
  • 41
  • It has the main problem of writing the entire procedure again, for example if the procedure is thousand line one, it will be hugely frustrating to write it again to just change the name – Payel Senapati Oct 19 '21 at 23:31