You are here
Wed, 2014-04-02 23:38
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." http://dev.mysql.com/doc/refman/5.5/en/resignal.html There is also some good information about it here as well: https://mariadb.com/kb/en/resignal/ 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 existNotice 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.