Warning and error information in stored procedures revisited
- by user13334359
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
This logic was not ideal and causes several problems, particularly:
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.
First try to fix this was done in version 5.5. Patch left Diagnostic Area intact after stored routine execution, but cleared it in the beginning of each statement which can generate warnings or to work with tables. Diagnostic Area checked after stored routine execution.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.
