Skip to main content

GET DIAGNOSTICS

The GET DIAGNOSTICS statement allows an application to examine diagnostic information generated by an SQL statement in the diagnostic area.

Syntax and parameter description​

You do not need special privileges to execute the GET DIAGNOSTICS statement. You can also use SHOW WARNINGS or SHOW ERRORS to view conditions or errors.

The syntax of the GET DIAGNOSTICS statement is as follows:

GET [CURRENT | STACKED] DIAGNOSTICS {
    statement_information_item
    [, statement_information_item] ...
  | CONDITION condition_number
    condition_information_item
    [, condition_information_item] ...
}

statement_information_item:
    target = statement_information_item_name

condition_information_item:
    target = condition_information_item_name

statement_information_item_name: {
    NUMBER
  | ROW_COUNT
}

condition_information_item_name: {
    RETURNED_SQLSTATE
  | MESSAGE_TEXT
  | MYSQL_ERRNO
}

The CURRENT keyword specifies that information is retrieved from the current diagnostic area. The STACKED keyword specifies that information is retrieved from the second diagnostic area, which is available only when the current context is a condition handler. If no keyword is specified, the default is the current diagnostic area.

The retrieval list specifies one or more target = item_name assignments, separated by commas. Each assignment names a target variable and a statement_information_item_name or condition_information_item_name indicator, depending on whether statement information or condition information is being retrieved.

The target indicator used to store item information can be a stored procedure or function parameter, a local variable declared with DECLARE, or a user-defined variable.

The condition_number indicator can be a stored procedure or function parameter, a local variable declared with DECLARE, a user-defined variable, a system variable, or a literal. If the condition number is not within the range of 1 to the number of condition areas with information, a warning is issued, and the warning is added to the diagnostic area without being cleared.

The GET DIAGNOSTICS statement is typically used within handlers in stored programs. As an extension, seekdb allows you to use GET [CURRENT] DIAGNOSTICS to check the execution of an SQL statement outside of a handler context. For example, you can enter the following statement at the prompt:

DROP TABLE test.no_table_found;
ERROR 1051 (42S02): Unknown table 'test.no_table_found'

GET DIAGNOSTICS CONDITION 1
         @c1 = RETURNED_SQLSTATE, @c2 = MESSAGE_TEXT;
Query OK, 0 rows affected

SELECT @c1, @c2;
+-------+------------------------------------+
| @c1   | @c2                                |
+-------+------------------------------------+
| 42S02 | Unknown table 'test.no_table_found' |
+-------+------------------------------------+

This extension applies only to the current diagnostic area and not to the second diagnostic area, because GET STACKED DIAGNOSTICS is executed only when the current context is a condition handler. If this is not the case, an error GET STACKED DIAGNOSTICS when handler not active is returned.

Retrieve information from the diagnostic area​

Generally, the diagnostic area contains the following two types of information:

  • Statement information, such as the number of conditions that occurred or the number of affected rows.

  • Condition information, such as error codes and messages. If a statement triggers multiple conditions, the diagnostic area has a corresponding condition area for each condition. If a statement does not trigger any conditions, the condition information part of the diagnostic area is empty.

The following example shows that the diagnostic area contains information for a statement that triggers three conditions:

Statement information:
  row count
  ... other statement information items ...
Condition area list:
  Condition area 1:
    error code for condition 1
    error message for condition 1
    ... other condition information items ...
  Condition area 2:
    error code for condition 2:
    error message for condition 2
    ... other condition information items ...
  Condition area 3:
    error code for condition 3
    error message for condition 3
    ... other condition information items ...

For more information about the diagnostic area, see Diagnostic area.

The GET DIAGNOSTICS statement can retrieve statement or condition information, but not both in the same statement:

  • To retrieve statement information, you need to retrieve the required statement item into the target variable. The following example shows that the GET DIAGNOSTICS instance assigns the number of available conditions and the number of affected rows to the user variables @c1 and @c2:

    GET DIAGNOSTICS @c1 = NUMBER, @c2 = ROW_COUNT;

  • To retrieve condition information, you need to specify the condition number and retrieve the required condition item into the target variable. The following example shows that the GET DIAGNOSTICS instance assigns the SQLSTATE value and the error message to the user variables @c3 and @c4:

    GET DIAGNOSTICS CONDITION 3
    @c3 = RETURNED_SQLSTATE, @c4 = MESSAGE_TEXT;

When a condition occurs, not all condition items recognized by GET DIAGNOSTICS may be filled. The following example illustrates this:

GET DIAGNOSTICS CONDITION 1
         @c5 = SCHEMA_NAME, @c6 = TABLE_NAME;

SELECT @c5, @c6;
+------+------+
| @c5  | @c6  |
+------+------+
| NULL | NULL |
+------+------+

Use GET STACKED DIAGNOSTICS​

When a condition handler is activated, the current diagnostic area is pushed onto the diagnostic area stack, and a new current diagnostic area is created as a copy of the previous one.

The GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS statements can be used in a handler to access the contents of the current and stacked diagnostic areas. Initially, both diagnostic areas return the same results, so as long as you do not execute any statements in the handler that change the current diagnostic area, you can retrieve information about the condition that activated the handler from the current diagnostic area.

However, statements executed in the handler can modify the current diagnostic area, clearing and setting its contents according to common rules. In such cases, a more reliable way to obtain information about the condition that activated the handler is to use the stacked diagnostic area, which cannot be modified by statements executed in the handler, except for RESIGNAL. For information about when the current diagnostic area is set and cleared, see Diagnostic Area.

In the following example, after the current diagnostic area has been modified by a handler statement, you can use GET STACKED DIAGNOSTICS in the handler to retrieve information about the handled exception.

In the stored procedure proc(), we attempt to insert two values into a table that contains a TEXT NOT NULL column. The first value is a non-NULL string, and the second value is NULL. The column does not allow NULL values, so the first insertion succeeds, but the second causes an exception. The procedure includes an exception handler that attempts to insert NULL into an empty string:

DROP TABLE IF EXISTS tbl1;

CREATE TABLE tbl1 (col1 TEXT NOT NULL);
DROP PROCEDURE IF EXISTS proc;

delimiter //

CREATE PROCEDURE proc ()
BEGIN
  -- Declare variables to store diagnostic area information
  DECLARE err_count INT;
  DECLARE err_no INT;
  DECLARE err_msg TEXT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- The current DA is non-empty here because the preceding statements executed in the handler did not clear it.
    GET CURRENT DIAGNOSTICS CONDITION 1
      err_no = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
    SELECT 'current DA before mapped insert' AS op, err_no, err_msg;
    GET STACKED DIAGNOSTICS CONDITION 1
      err_no = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
    SELECT 'stacked DA before mapped insert' AS op, err_no, err_msg;

-- Attempt to insert NULL into an empty string
    INSERT INTO tbl1 (col1) VALUES('');

-- The current DA should be empty (if the INSERT succeeded),
    -- So check for a condition before attempting to retrieve condition information
    GET CURRENT DIAGNOSTICS err_count = NUMBER;
    IF err_count = 0
    THEN
      SELECT 'mapped insert succeeded, current DA is empty' AS op;
    ELSE
      GET CURRENT DIAGNOSTICS CONDITION 1
        err_no = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
      SELECT 'current DA after mapped insert' AS op, err_no, err_msg;
    END IF ;
    GET STACKED DIAGNOSTICS CONDITION 1
      err_no = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
    SELECT 'stacked DA after mapped insert' AS op, err_no, err_msg;
  END;
  INSERT INTO tbl1 (col1) VALUES('string 1');
  INSERT INTO tbl1 (col1) VALUES(NULL);
END;
//

delimiter ;
CALL proc();

When the handler is activated, a copy of the current diagnostic area is pushed onto the diagnostic area stack. The handler first displays the contents of the current and stacked diagnostic areas, which are initially the same, as shown:

+---------------------------------+--------+------------------------------+
| op                              | err_no | err_msg                      |
+---------------------------------+--------+------------------------------+
| current DA before mapped insert |  1048  | Column 'col1' cannot be null |
+---------------------------------+--------+------------------------------+
1 row in set

+---------------------------------+--------+------------------------------+
| op                              | err_no | err_msg                      |
+---------------------------------+--------+------------------------------+
| stacked DA before mapped insert |  1048  | Column 'col1' cannot be null |
+---------------------------------+--------+------------------------------+
1 row in set

Statements executed after the GET DIAGNOSTICS statement may reset the current diagnostic area. For example, the handler inserts NULL into an empty string and displays the results. The new insertion succeeds and clears the current diagnostic area, but the stacked diagnostic area remains unchanged, still containing information about the condition that activated the handler, as shown:

+----------------------------------------------+
| op                                           |
+----------------------------------------------+
| mapped insert succeeded, current DA is empty |
+----------------------------------------------+
1 row in set

+--------------------------------+--------+------------------------------+
| op                             | err_no | err_msg                      |
+--------------------------------+--------+------------------------------+
| stacked DA after mapped insert |  1048  | Column 'col1' cannot be null |
+--------------------------------+--------+------------------------------+
1 row in set

When the condition handler ends, the diagnostic area it was in is popped off the stack, and the stacked diagnostic area becomes the current diagnostic area for the stored procedure. After the procedure returns, the table contains two rows, with the empty row resulting from the attempt to insert NULL into an empty string, as shown:

+----------+
| col1     |
+----------+
| string 1 |
|          |
+----------+

In the preceding example, the first two GET DIAGNOSTICS statements in the condition handler that retrieve information from the current and stacked diagnostic areas return the same values. This is not the case if a statement that resets the current diagnostic area is executed earlier in the handler. Assume that the DECLARE statements in the rewritten proc() procedure are placed inside the handler definition instead of before it, as shown:

CREATE PROCEDURE proc()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Declare variables to store diagnostic area information
    DECLARE err_count INT;
    DECLARE err_no INT;
    DECLARE err_msg TEXT;
    GET CURRENT DIAGNOSTICS CONDITION 1
      err_no = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
    SELECT 'current DA before mapped insert' AS op, err_no, err_msg;
    GET STACKED DIAGNOSTICS CONDITION 1
      err_no = MYSQL_ERRNO, err_msg = MESSAGE_TEXT;
    SELECT 'stacked DA before mapped insert' AS op, err_no, err_msg;
    END;
  INSERT INTO tbl1 (col1) VALUES('string 1');
  INSERT INTO tbl1 (col1) VALUES(NULL);
END;
//
...

+---------------------------------+--------+---------+
| op                              | err_no | err_msg |
+---------------------------------+--------+---------+
| current DA before mapped insert |  NULL  | NULL    |
+---------------------------------+--------+---------+
1 row in set

+---------------------------------+--------+------------------------------+
| op                              | err_no | err_msg                      |
+---------------------------------+--------+------------------------------+
| stacked DA before mapped insert |  1048  | Column 'col1' cannot be null |
+---------------------------------+--------+------------------------------+
1 row in set

Contents

Edit this page