GET DIAGNOSTICS

You can use the GET DIAGNOSTICS statement to enable an application to check the diagnostic information generated by SQL statements in the diagnostics area.

Syntax and parameters

You can execute the GET DIAGNOSTICS statement without special privileges. You can also execute the SHOW WARNINGS or SHOW ERRORS statement to query exception conditions or errors.

Syntax of the GET DIAGNOSTICS statement:

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 to retrieve information from the current diagnostics area. The STACKED keyword specifies to retrieve information from the second diagnostics area, which is available only when the current context is a condition handler. If no keyword is specified, the current diagnostics area is used by default.

The retrieval list specifies one or more target = item_name assignments that are separated with commas. A target variable and a statement_information_item_name or condition_information_item_name designator (depending on whether statement information or condition information is retrieved) are named in each assignment.

A target designator used to store project information can be a stored procedure or function parameter, a stored program local variable declared by using the DECLARE statement, or a user-defined variable.

A condition_number designator can be a stored procedure or function parameter, a stored program local variable declared by using the DECLARE statement, a user-defined variable, a system variable, or a literal. If the condition number is not within the range from 1 to the number of condition areas with information, an alert will occur, and the alert will be added to the diagnostics area without being cleared.

The GET DIAGNOSTICS statement is usually used in a handler of a stored program. As an extended feature, you can use the GET [CURRENT] DIAGNOSTICS statement to check the execution of SQL statements beyond the handler context. For example, when you call the OBClient program, you can enter the following statement at the prompt:

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

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

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

This extended feature applies only to the current diagnostics area, but not to the second diagnostics area, because the GET STACKED DIAGNOSTICS statement is executed only when the current context is a condition handler. If this condition is not met, the following error occurs: GET STACKED DIAGNOSTICS when handler not active.

Query information about the diagnostics area

Generally, a diagnostics area contains the following two types of information:

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

• Condition information, such as the error code and message. If a statement raises multiple conditions, the diagnostics area contains a condition area for each condition. If a statement does not raise any condition, the condition information part in the diagnostics area is empty.

The following example shows the diagnostics area for a statement that raises 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 diagnostics areas, see Diagnostics area.

You can use the GET DIAGNOSTICS statement to query either statement or condition information. However, you cannot obtain both information by using a single GET DIAGNOSTICS statement.

• To obtain statement information, retrieve the required statement items into the target variables. In the following example, the GET DIAGNOSTICS statement assigns the number of available conditions and the number of affected rows to user variables @c1 and @c2:

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

• To obtain condition information, specify a condition number and retrieve the required condition items into the target variables. In the following example, the GET DIAGNOSTICS statement assigns the SQLSTATE value and error messages to user variables @c3 and @c4:

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

When a condition occurs, condition items identified by the GET DIAGNOSTICS statement may not all be filled. Example:

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

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

Use GET STACKED DIAGNOSTICS

When a condition handler is activated, it is pushed to the diagnostics area stack, the first (current) diagnostics area becomes the second (stacked) diagnostics area, and a new current diagnostics area is created as a copy of it.

You can use the GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS statements in the handler to access the content in the current and stacked diagnostics areas. Initially, both diagnostics areas return the same results. Therefore, you can query information about the activation condition of the handler from the current diagnostics area, provided that you do not execute any statements in the handler to modify the current diagnostics area.

However, you can execute a statement in the handler to modify the current diagnostics area to clear and set its content based on common rules. In this case, a more reliable way to query information about the activation condition of the handler is to use the stacked diagnostics area. This area cannot be modified by statements executed in the handler, except for the RESIGNAL statement. For more information about when to set and clear the current diagnostics area, see Diagnostics area.

The following example shows how the GET STACKED DIAGNOSTICS statement can be used in a handler to query information about the handled exception after the current diagnostics area is modified by using handler statements.

In a 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 support NULL values. Therefore, the first insert succeeds but the second insert causes an exception. The procedure includes an exception handler that maps attempts to insert NULL into inserts of the 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 diagnostics area information.
  DECLARE err_count INT;
  DECLARE err_no INT;
  DECLARE err_msg TEXT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- The current diagnostics area here is not empty because no prior statements executed in the handler have cleared 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;

    -- Map attempted NULL insert to empty string insert.
    INSERT INTO tbl1 (col1) VALUES('');

    -- The current diagnostics area here is empty if the INSERT operation succeeds.
    -- Therefore, check whether conditions exist before you query 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 diagnostics area is pushed to the diagnostics area stack. The handler displays the content of the current and stacked diagnostic areas. Initially, the content of both diagnostic areas is the same.

+---------------------------------+--------+------------------------------+
| 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 diagnostics area. For example, the handler maps the NULL insert to an empty-string insert and displays the result. The new insert succeeds, and the current diagnostics area is cleared. However, the stacked diagnostics area remains unchanged and still contains condition information about the activated handler, as shown in the following example:

+----------------------------------------------+
| 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, its current diagnostics area is popped from the stack, and the stacked diagnostics area becomes the current diagnostics area in the stored procedure. The table returned by the stored procedure contains two rows. The empty row results from an attempt to insert NULL that was mapped to an empty-string insert, as shown in the following example:

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

In the preceding example, the first two GET DIAGNOSTICS statements used in the condition handler to retrieve information from the current and stacked diagnostics areas return the same values. However, this is not the case if the statement used to reset the current diagnostics area is executed earlier in the handler. Assume that you rewrite the stored procedure proc() to place the DECLARE statement in the handler definition instead of before it, a different result occurs:

CREATE PROCEDURE proc()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Declare variables to store diagnostics 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