Warning and error information in stored procedures revisited
By Sveta Smirnova-Oracle on Jul 04, 2012
Originally way to handle warnings and errors in MySQL stored routine was designed as follows:
- if warning was generated during stored routine execution which has a handler for such a warning/error, MySQL remembered the handler, ignored the warning and continued execution
- after routine is executed MySQL checked if there is a remembered handler and activated if any
- it was not possible to choose right handler for an instruction which generated several warnings or errors, because only first one was chosen
- handling conditions in current scope messed with conditions in different
- there were no generated warning/errors in Diagnostic Area that is against SQL Standard.
This patch solved issue with order of condition handlers, but lead to new issues. Most popular was that outer stored routine could see warnings which should be already handled by handler inside inner stored routine, although latest has handler. I even had to wrote a blog post about it.
And now I am happy to announce this behaviour changed third time.
Since version 5.6 Diagnostic Area cleared after instruction leaves its handler.
This lead to that only one handler will see condition it is supposed to proceed and in proper order. All past problems are solved.
I am happy that my old blog post describing weird behaviour in version 5.5 is not true any more.