I was recently debugging a stored procedure and could not easily identify the underlying reason for why it was failing.
It had a standard exit handler catch-all for SQLEXCEPTION, which was:
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SELECT ...; END;
When there was an error, it didn’t really output anything useful.
As of MySQL 5.5, there is RESIGNAL:
“RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event.”
There is also some good information about it here as well:
It is very simple to use, just add it (though it is much more robust that just this – see above link):
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SELECT ...; RESIGNAL; END;
By just adding RESIGNAL, in addition to what was output by the SELECT, we now see the actual error printed immediately after, for instance:
mysql> call my_proc('abc'); +---------+---------+-------+ | STATUS | Records | Query | +---------+---------+-------+ | ....... | NULL | NULL | +---------+---------+-------+ 1 row in set (0.01 sec) ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
Notice the last line is what you are looking for (when troubleshooting).
As I mentioned, this is available as of MySQL and MariaDB 5.5.
If you are using MySQL 5.6 or MariaDB 10.0, I would recommend skipping RESIGNAL and using GET DIAGNOSTICS within your exit handler to obtain the exact error code, SQL state, and error message *all* of the time. I cover using GET DIAGNOSTICS here, if interested.
Hope this helps.
Tags: Debug Stored Procedures, Debugging Stored Procedures, DECLARE EXIT HANDLER FOR SQLEXCEPTION, error in stored procedure, exit handler, exit handler error, GET DIAGNOSTICS, get error code from stored procedure, mysql stored procedure, RESIGNAL, SQLEXCEPTION