3

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

2
  • Just an update: It's still not possible in 5.7. It says so in the manual.
    – tombom
    Nov 23, 2016 at 8:08
  • 1
    MySQL 8 will have this feature. But the question is, why stored procedures need to be updated so frequently? Once in full blue moon if a SP needs to be updated, it shouldn't take more than a second.
    – Allen King
    Jul 14, 2017 at 5:52

2 Answers 2

2

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.

1

An approach... When installing an upgrade to a Stored Routine, change the name and install it. Then upgrade the caller(s) to call the new Routine.

This approach avoids the problem you mention, plus it may give you a way to revert if something goes 'wrong'.

Eventually, drop the old Routines.

3
  • But i can not change the name of procedure, this procedure call internally into php code, if i change the name then also it will create an issue.
    – sam
    Mar 4, 2015 at 6:27
  • 2
    You have a choice of issues to face.
    – Rick James
    Mar 4, 2015 at 6:30
  • As a third option, consider migrating your application to PostgreSQL. I agree with the OP though, this doesn't solve the problem, at all. =( Oct 21, 2017 at 23:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.