April 2, 2014

Improve your Stored Procedure Error Handling with GET DIAGNOSTICS

In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5. However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well. RESIGNAL just outputs the error, as it comes from the server, for instance:

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

You may not want the error just written to the console, or perhaps you want to at least control how it is written. It's common to see exit handler code in the following form:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

Where the SELECT outputs something not very useful in many cases. With GET DIAGNOSTICS, you can get all of the error information, and you should, if not already. If you were going to use GET DIAGNOSTICS from the command line, you could use something like this (immediately following your query):

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SELECT @sqlstate, @errno, @text;

However, since we want the information from within the stored procedure, we must put this within the exit handler code. So the above exit handler code, now becomes (and I added 1 more line for formatting):

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
 @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
END;

Which results in:

mysql> call my_proc('abc');
+--------------------------------------------------+
| @full_error                                      |
+--------------------------------------------------+
| ERROR 1146 (42S02): Table 'db1.t1' doesn't exist |
+--------------------------------------------------+
1 row in set (0.01 sec)

For more details on GET DIAGNOSTICS, I would recommend the following 2 pages: https://mariadb.com/kb/en/get-diagnostics/ http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html Hope this helps.

About Chris Calender

Chris Calender is a Technical Support Manager 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