CODEWITHSUNDEEP
mysqlstored-procedures
Payel Senapati
Payel Senapati

Reputation: 1356

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

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.

Upvotes: 1

Views: 3949

Answers (2)

Barmar
Barmar

Reputation: 780798

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.

Upvotes: 6

danblack
danblack

Reputation: 14666

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

Upvotes: 0

Related Questions