MySQL: How to modify stored procedures atomically?

Question

I have searched through the internet, and understand that the only way to change the body of a store procedure is by dropping and creating it again. There seems nothing wrong with the mechanism but if I have a client application (or thousands of distributed clients) that keeps invoking the store procedure to update some data on the server database, dropping the procedure would result in data lost and/or corruption.

I'm thinking if there is a syntax like "CREATE PROCEDURE IF EXIST..." or something functions similarly so the update operation would be carried out smoothly. Yet I didn't find such thing being available in MySQL.

So how do you guys think this issue can be addressed? Awesome thoughts?

Answer 1

You cannot modify a stored procedure (though you can change its characteristics) in MySQL. From the ALTER PROCEDURE page.

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.

While it is possible to lose data while performing this update (though it should be a relatively small window), it's unlikely that your data will be corrupted. I'd take a look at message queuing technologies if your system needs to be guarded against data loss from database downtime.