chris.calender

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.

 

Tags: 

About the Author

chris.calender's picture

Chris Calender is a Principal Support Engineer with MariaDB. Earlier he was a Principal Support Engineer at MySQL/Sun/Oracle. And 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.

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: