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.