MySQL Bugs: #9588: Support for ALTER (or CREATE OR REPLACE) PROCEDURE/FUNCTION <BODY>

Bug #9588	Support for ALTER (or CREATE OR REPLACE) PROCEDURE/FUNCTION <BODY>
Submitted:	2 Apr 2005 20:15	Modified:	30 Nov 2005 20:09
Reporter:	Niels G	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.3	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
When building a MySQL client GUI to edit Procedures and Functions, the developer must provide an editor that provides the saving of the changes to the routine in an atomic operation.  There are 2 ways of doing this.  One is to have the database support rollback of DDL operations.  The second is to have the database provide an ALTER (CREATE OR REPLACE) PROCEDURE/FUNCTION AS <NEW BODY TEXT>. If the database doesn't support either then an editor must DROP the procedure then execute a CREATE.  But if the DROP is sucessfull and the CREATE fails, then the procedure has been destroyed and there is no way for the GUI to recover.  As far as I know MySQL doesn't support DDL in transactions, so I would like to have MySQL support a ALTER PROCEDURE/FUNCTION <NEW BODY TEXT> command (i.e. SQL Server), or a CREATE OR REPLACE PROCEDURE/FUNCTION command (i.e. Oracle and PostgreSQL).  Although if DDL in transaction support would be added, then this would be enough (i.e. DB2).

How to repeat:
same as above

Suggested fix:
same as above

This doesn't apply only to GUIs but also to the command line. And we don't only need atomicity for the reason that something could go wrong: It's also possible that a client calls a stored routine in exactly the moment between a DROP and a CREATE. So this feature is really most wanted, as now I have to be afraid everytime I try to upgrade a routine in a production environment.

Do you have any plans about this features?
Thanks in advance

I was just in the middle of creating a feature request for this after failing to find it on search the first time. 

My developers and I find this to be one of the single most difficult missing features in trying to use MySQL with stored procedures.  If we could add RENAME PROCEDURE, then we could add a syntax of ALTER PROCEDURE that simply created a temporary procedure with the SQL provided, and if that SQL passed syntax checking in the CREATE PROCEDURE, then an atomic rename could be performed similar to what RENAME TABLE allows, moving the temporary procedure in place of the production procedure.

The current syntax with DROP PROCEDURE IF EXISTS followed by CREATE PROCEDURE has caused our company downtime and quality of service issues more than once, and resulted in stored procedures being lost.  We now take much better precautions when it comes to editing stored procedures so that minor syntax problems don't cause a stored procedure to be dropped and not recreated.  We still have the race condition of the procedure not actually existing for a brief moment too.

I'm quite certain we're not the only people who have suffered under the DROP/CREATE system of stored procedures in MySQL.  We do not have the same issue using another RDBMS system we also employ which supports ALTER PROCEDURE.  Please consider implementing this feature for 5.1.  Thanks!

Jeremiah,

No, you aren't alone. My co-workers and I too.

This feature should be added in MySQL v.5.1 because it's very important!

Regards, Santo.

Jeremiah and Santa,

Myself and my company have been bit in the bum by the same issue here.

I agree that this would be very useful, however, since we can...

DROP PROCEDURE IF EXISTS procname;

... we have no excuse.  The extra SQL is annoying, but it's probably better(tm) if we have to think about dropping the procedure before replacing it with something new.  There are times when I want a CREATE PROCEDURE to fail if the procedure is already defined.  There are other times when I don't want that.  I am in the camp of it'd be nice to have, but there are other things I consider higher priority right now.

Kevin,

You're not thinking of atomicity.  It's not just a matter of "extra SQL".  It's a matter of the fact that for a short time, that stored procedure does not exist.  During that window of time, all of our clients get SQL exceptions due to the stored procedure not existing.  We are a telephony services company and have anywhere between hundreds and thousands of stored procedure calls per second, thus the window in which the stored procedure does not exist is bigger than you might think.  An ALTER operation is atomic and seamless, so the procedure is there in one state, and then there in another state, but it's always there.  Even if there's a very short locking period, that's a split second blocking rather than an exception.

This is a production impacting issue, not a "nice to have" issue.  Your solution may work for you, but it doesn't work for the rest of us and isn't the same as actually supporting atomicity in the modification of stored procedures.  This would be much the same as if you had to DROP a MERGE table in order to change what its member tables are.  The whole point is seamless operation.

Bug #30165 was marked as duplicate of this one.

Can you advise on status for this feature-request?
I agree that it is actullay one level above just "nice to have".
David.

After so many years (submitted in 2005, my comment was in 2007) and so many releases, it doesn't appear that this is high on their priority list.  Unfortunate since the addition of stored procedures was a major advancement for MySQL, but the lack of atomicity in updating them makes any changes to the stored procedures a potential downtime event.

I just want to add my two cents that this feature is critical and above a mere convenience.  Is there any way of knowing the priority of this feature on the list of requested features