Rename MySQL Stored Procedures
I’ve previously blogged about the limitations of MySQL Alter Procedure Syntax and I came across a thread on the MySQL forums with a possible solution. I thought it might be handy to wrap this up into a stored procedure akin to SQL Server’s sp_rename.
This procedure will allow you to easily rename MySQL Stored Procedures in any database. Please be aware that this does update the MySQL system tables and has only had minimal testing. As with all tips you find on the Internet please use with caution!
DELIMITER $$
USE `mysql`$$
DROP PROCEDURE IF EXISTS `mysp_rename_proc`$$
CREATE DEFINER=`root`@`%` PROCEDURE `mysp_rename_proc`(IN p_proc_name VARCHAR(64), IN p_new_name VARCHAR(64), IN p_db VARCHAR(64))
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Use to rename stored procedures.'
BEGIN
proc: BEGIN
# A few tests to see if the input is sensible
IF CHARACTER_LENGTH(IFNULL(p_proc_name, '')) = 0 OR CHARACTER_LENGTH(IFNULL(p_new_name, '')) = 0
OR CHARACTER_LENGTH(IFNULL(p_db, '')) = 0 THEN
BEGIN
SELECT 'Error: One of more of the input parameters is zero in length.' AS Error;
LEAVE proc;
END;
ELSEIF (SELECT COUNT(*)
FROM mysql.proc
WHERE `name` = p_proc_name
AND `type` = 'PROCEDURE'
AND db = p_db) = 0 THEN
BEGIN
SELECT 'Error: The procedure specified in p_proc_name does not exist in this database.' AS Error;
LEAVE proc;
END;
ELSEIF (SELECT COUNT(*)
FROM mysql.proc
WHERE `name` = p_new_name
AND `type` = 'PROCEDURE'
AND db = p_db) = 1 THEN
BEGIN
SELECT 'Error: Unable to rename the procedure specified in p_proc_name as it already exists in this database.' AS Error;
LEAVE proc;
END;
END IF;
# Rename the proc
UPDATE `mysql`.`proc`
SET `name` = p_new_name,
specific_name = p_new_name
WHERE db = p_db
AND `name` = p_proc_name
AND `type` = 'PROCEDURE';
# Update any associated privileges
UPDATE `mysql`.`procs_priv`
SET Routine_name = p_new_name
WHERE db = p_db
AND Routine_name = p_proc_name
AND Routine_type = 'PROCEDURE';
# Check update rowcount to see if privileges need to be flushed
IF(SELECT ROW_COUNT()) > 0 THEN
BEGIN
FLUSH PRIVILEGES;
END;
END IF;
END proc;
END$$
DELIMITER ;
Usage is as follows;
CALL mysp_rename_proc('usp_proc', 'usp_new_proc_name', 'database_proc_exists_in');
As the thread poster mentions the Stored Procedure is callable by it’s old name as well as the new one until you reconnect. There doesn’t seem to be any suitable FLUSH command to resolve this.
If you liked this you may like;