--- title: "Quickly Debugging Stored Procedures, Functions, Triggers, and Events with RESIGNAL" publish_date: 2014-04-02 author: "MariaDB" --- # Quickly Debugging Stored Procedures, Functions, Triggers, and Events with RESIGNAL 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](http://www.chriscalender.com/?p=1482), if interested. Hope this helps.