MySQL ALTER PROCEDURE Syntax

I usually use SQLYog to write any stored procedures for MySQL. Whenever you alter a procedure the editor essentially generates SQL to drop and then recreate it.

DELIMITER $$
 
USE `db`$$
 
DROP PROCEDURE IF EXISTS `my_proc`$$
 
CREATE DEFINER=`root`@`%` PROCEDURE `my proc`()
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT 'Just an example proc.'
BEGIN
       # // proc defintition
END$$
 
DELIMITER ;

I’ve always wondered why it did this as opposed to using the ALTER PROCEDURE Syntax.  I attempted to use this syntax but couldn’t get it to parse whatever I did. As usual the manual explains all;

This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE. source

Okay! The alter procedure statement is virtually useless to us then! The current method of dropping and then recreating routines to modify them is not atomic and can cause problems on production systems. The stored procedure is temporarily unavailable after the first drop statement so this might freak out any other thread calling it at that moment.

I admit this isn’t a massive problem, and I assume it wouldn’t be that difficult to implement, but it’s something you’d expect DBMS vendors to put in place for stored procedures. I enjoy working with MySQL but some days you can get irrated by a number of these little issues!


2 Comments

  1. Cade Roux says:

    Another problem with DROP/CREATE is a loss of permissions settings, which will have to be added aftr teh CREATE to restore the permissions as they were before the DROP.

  2. Rhys says:

    Hi Cade,

    Presumably that only happens if you assign permission to indiviual procs rather than

    GRANT EXECUTE ON db.* TO 'user'@'host'

    Still, obviously something to bear in mind when you’re editing procs in this way.

    Cheers,

    Rhys

Leave a Reply