MySQL 5.6 GET DIAGNOSTICS – Getting the MySQL warning, error, or exception that just occurred

I’ve been waiting a while for this feature, and I’m so happy they finally have it. The GET DIAGNOSTICS statement allows you to get an error message in MySQL like you would in Oracle PL/SQL with SQLCODE and SQLERRM. While it would be nice if these were available as built-in variables (like in Oracle PL/SQL)  it is nice that you can call this from the mysql command line in addition to using in a procedure or function. This is new in 5.6 – if you want this in earlier versions the best (that I’ve found) you can do is code in as many errors as you can, but that isn’t good if you’re looking for unexpected errors.

Here is the MySQL 5.6 Documentation: http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

Here is the basic call:

GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;

The following are some simple calls within the mysql command line program that demonstrate the call:

mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-12-16 10:14:55 |
+---------------------+
1 row in set (0.00 sec)
mysql> GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @p1, @p2;
+------+------+
| @p1 | @p2 |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)
mysql> select crap;
ERROR 1054 (42S22): Unknown column 'crap' in 'field list'
mysql> GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.00 sec)
mysql> select @p1, @p2;
+-------+---------------------------------------+
| @p1 | @p2 |
+-------+---------------------------------------+
| 42S22 | Unknown column 'crap' in 'field list' |
+-------+---------------------------------------+
1 row in set (0.00 sec)

Clearly at the command line you see the message anyway, but it is there anyway. I know I’ve had issues before that require “show engine innodb status” and if I can find one of those again I’ll see if GET DIAGNOSTICS makes the error easier to find that scrolling through the show … status output.

What I wanted to use this functionality for was the ability to have a MySQL procedure do work and when an error occurs store the specific error message in a table for us to examine. In other words, I needed a way to trap warnings and errors in a MySQL procedure and store them or report them. This way the message returned to the user could be more generic, but we could see exactly what had happened in our table.

This is the code (slightly modified) that I have at the beginning of applicable procedures:

DECLARE CONTINUE HANDLER FOR SQLWARNING
 begin
 set v_msg = concat('section=',ifnull(v_section,'*NULL*'), ...);
 GET DIAGNOSTICS CONDITION 1 v_error = RETURNED_SQLSTATE, v_error_text = MESSAGE_TEXT;
 call log_our_error('day','database','the_procname',null,p_obj_id,null,null,v_error,v_error_text,v_msg);
 end;
 DECLARE EXIT HANDLER FOR SQLEXCEPTION
 begin
 set v_msg = concat('section=',ifnull(v_section,'*NULL*'), ...);
 GET DIAGNOSTICS CONDITION 1 v_error = RETURNED_SQLSTATE, v_error_text = MESSAGE_TEXT;
 call log_our_error('day','database','the_procname',null,p_obj_id,null,null,v_error,v_error_text,v_msg);
 end;

Then I created  an intentional coding error in the procedure, ran the procedure, and checked the table:

mysql> select * from our_error;
+---------------------+----------+--------------------+--------+--------+--------+------------+------------+-------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+-------------+-----------+------+---------------------+---------+
| ts | app_name | proc_name | a_id | b_id | c_id | d_id | error_code | error_text | long_text | occurrences | processed | ackd | ackd_time | ackd_by |
+---------------------+----------+--------------------+--------+--------+--------+------------+------------+-------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+-------------+-----------+------+---------------------+---------+
| 2013-12-21 00:00:00 | database | ale_test_comp_item | 0 | 50 | 0 | 0 | HY000 | Incorrect integer value: 'hello' for column 'v_event_id' at row 1 | section=First try all elements, yadda yadda yadda | 1 | 0 | 0 | 0000-00-00 00:00:00 | NULL |
+---------------------+----------+--------------------+--------+--------+--------+------------+------------+-------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+-------------+-----------+------+---------------------+---------+
1 row in set (0.00 sec)

So, there we have it. Very useful functionality to have in MySQL.

7 responses to “MySQL 5.6 GET DIAGNOSTICS – Getting the MySQL warning, error, or exception that just occurred

  1. Pingback: MySQL 5.6 GET DIAGNOSTICS – Getting the MySQL warning, error, or exception that just occurred | Jed's

  2. Hi, Jed.

    Your post is very useful, so maybe you can help me with the following problem that I’ve got: apparently I cannot combine the GET DIAGNOSTICS and ROLLBACK statements inside a stored procedure: besides catching the error, I want to be able to reverse all the previous processed data, not just stop the execution. Please find bellow my script:

    1. Create the log table:
    CREATE TABLE tbl_logs (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `txt` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`));
    ———
    2. Create the stored procedure:
    DELIMITER $$

    CREATE PROCEDURE `test`()
    BEGIN
    DECLARE state CHAR(5) DEFAULT ‘00000’;
    DECLARE msg TEXT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    begin
    rollback;

    GET DIAGNOSTICS CONDITION 1 state = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

    insert into tbl_logs (txt)
    select concat(‘Error ‘,state,’: ‘,msg);
    end;

    start transaction;
    truncate table tbl_logs;
    insert into tbl_logs (txt) select state;

    — drop table no_such_table;

    insert into tbl_logs (txt) select ‘commit’;
    commit;
    END
    ———

    3. call the procedure:
    call test();
    select * from tbl_logs;

    => check the table: 2 rows
    00000
    commit
    ———

    4. Alter the procedure:
    Take the comment out, making the drop table visible.

    ———
    5. call the procedure:
    call test();
    select * from tbl_logs;

    => check the table: 2 rows
    00000
    Error 42S02: Unknown table ‘no_such_table’

    => the handler catches the error and stops the execution, is just that doesn’t consider the rollback (no matter what other modifications are previously made, they are commited)…

    Can you help by telling me how can I combine GET DIAGNOSTICS and ROLLBACK statements so both of them to take effect?
    Thanks a lot!

  3. You are right, no truncate statement should be used.
    But even without it, that insert (or any other DML) before the error is always committed in this simple example, as well as in my stored procedures which are more complex… We installed an upper MySQL version especially for being able to take advantage of GET DIAGNOSTICS, and now I have a Rollback issue to consider… probably is something that I’m doing wrong…

    • Maria, Yes, that is interesting. I wonder if an EXIT handler automatically commits – I would not think it would, but maybe the act of going into the EXIT handler block causes that. You could easily write a simple test case to see.

  4. Thank you, Jed, for all your help! I understand that I should be more careful where and when to use truncate / drop statements from now on…

  5. The feature is indeed useful, but I find the syntax too long and error-prone. For debugging purpose, I have written a procedure which logs the whole output of GET DIAGNOSTICS into a table called diagnostics_area. I had to write a procedure because there is no easy way do to that (as you write one row, the whole diagnostics area is cleared, and you won’t be able to log the next rows). Here is the link, in case you are interested.
    https://github.com/santec/sql_diagnostix/blob/master/diagnostics_area.sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s