I know that MySQL does not support atomic updates of stored procedures yet and it is bug which was reported on 2005 but not yet resolved.(http://bugs.mysql.com/bug.php?id=9588).
In our project multiple store procedure call within a second, we provide high availability service to client. but due to this bug are not updating any stored procedure during production hours. If we update any thing in stored procedure MySQL simply drops the entire procedure and recreates it again which creates downtime issue for our client.
It's also possible that a client calls a stored routine in exactly the moment between a DROP and a CREATE which create major issue for us.
Anyone facing this kind of issue? If yes if you have any way to resolve this kind of issue then please share.
Thanks
Best Answer
There is no way to solve this problem. It's one of many issues that arise over MySQL not having real transactions: the transactions do not cover DDL.
It's not just stored procedures either. That's a tremendous understatement. MySQL has fundamental problems with concurrency. It's MVCC model is non-existent with DDL: transactions are a function of Inno not MySQL. And, even at that the transaction model is broken with DML too.
I suggest migrating databases. I know that's steep. But shy of that nothing can done.