GET DIAGNOSTICS

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

Syntax and parameter description

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

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 you do not specify a keyword, 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 the retrieval statement information or condition information.

The target indicator used to store item 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.

The condition_number indicator 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 in the range from 1 to the number of condition areas that contain information, a warning is issued, and the warning is added to the diagnostic area without being cleared.

The GET DIAGNOSTICS statement is typically used in handlers in stored programs. As an extended feature, OceanBase Database allows you to use the GET [CURRENT] DIAGNOSTICS statement to check the execution status of an SQL statement outside of a handler context. For example, if you call the OBClient client 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 diagnostic area and not to the second diagnostic area. This is because the GET STACKED DIAGNOSTICS statement is executed only when the current context is a condition handler. If the current context is not a condition handler, an error GET STACKED DIAGNOSTICS when handler not active is returned.

Retrieve diagnostic area information

In general, 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 contains a corresponding condition area for each condition. If a statement does not trigger any conditions, the condition information section 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 to the target variable. The following example shows that the GET DIAGNOSTICS statement assigns the number of available conditions and the number of affected rows to the user variables @c1 and @c2, respectively:

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

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

  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 shows this:

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, 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 you can retrieve information about the activation condition of the handler from the current diagnostic area as long as no statements in the handler modify it.

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

The following example shows how to use GET STACKED DIAGNOSTICS in a handler to retrieve information about an exception that has been handled after the current diagnostic area has been modified by statements in the handler.

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 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 in the handler have not 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;

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

    -- The current DA should be empty (if the INSERT succeeds),
    -- 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 in the following example:

+---------------------------------+--------+------------------------------+
| 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 result. The new insertion succeeds and clears the current diagnostic area, but the stacked diagnostic area remains unchanged, still containing information about the activation condition of the 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 handler completes, the diagnostic area it was in is popped from 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, including an empty row caused by the attempt to insert NULL into an empty string, as shown in the following example:

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

In the previous example, the first two GET DIAGNOSTICS statements in the handler that retrieve information from the current and stacked diagnostic areas return the same values. If a statement that resets the current diagnostic area is executed earlier in the handler, this is not the case. Assume that the proc() procedure is rewritten to place the DECLARE statements inside the handler definition rather than before it. The results would be different, as shown in the following example:

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