April 2, 2014

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." 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 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.

About Chris Calender

Chris Calender is a Principal Support Engineer with MariaDB. Prior to joining SkySQL (which later merged with MariaDB) he was a Principal Support Engineer at MySQL/Sun/Oracle, as well as Support Manager of Americas East. Before that, he worked as a MySQL DBA and developer for numerous Fortune 500 Companies, including Clear Channel, Western & Southern, and Cincinnati Bell. Chris has both Bachelor's and Master's degrees in Computer Science, from Miami University and the University of Cincinnati, respectively. He has been using MySQL since 3.23 days. Having deployed well into the tens of thousands of database servers, he is an expert at deployment, managing such instances, server tuning and query tuning. In the past, he devised a revolutionary technique for recovering corrupted, and what was then unrecoverable data, thus saving some companies completely, as well as setting himself apart in the MySQL/MariaDB ecosystem as such as expert. He has also been very willing to share his knowledge as he has been one of the most prolific bloggers in the MySQL Ecosystem (see planet.mysql.com), ranking on the "Top 20 Authors" list for much of the past 10 years, and even holding the #1 spot numerous times.

Read all posts by Chris Calender