The GET DIAGNOSTICS statement enables applications to check the diagnostic information generated by SQL statements in the diagnostic area.
Syntax and parameters
Executing GET DIAGNOSTICS does not require special privileges. You can also use SHOW WARNINGS or SHOW ERRORS to view exception conditions or errors.
The syntax for 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 indicates retrieving information from the current diagnostic area. The STACKED keyword indicates retrieving information from the second diagnostic area, which is available only when the current context is a conditional handler. If no keyword is specified, the current diagnostic area is used by default.
In the retrieval list, specify one or more target = item_name assignment equations, separated by commas. Each assignment equation names a target variable and an indicator of either statement_information_item_name or condition_information_item_name (depending on whether you are retrieving statement information or condition information).
The target indicator for storing project information can be a stored procedure or function parameter, a local variable declared with DECLARE, or a user-defined variable.
The condition_number identifier can be a stored procedure or function parameter, a local program 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 informative condition areas, an alert is triggered and the alert is added to the diagnostic area without being cleared.
The GET DIAGNOSTICS statement is typically used within stored program handlers. As an extended feature, OceanBase AI Database allows GET [CURRENT] DIAGNOSTICS to check the execution of SQL statements outside the 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 extension is only applicable 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 handler. If this condition is not met, an error GET STACKED DIAGNOSTICS when handler not active occurs.
Get diagnostic area information
Generally speaking, the diagnostic area contains the following two types of information:
Statement information, such as the number of conditions met or the number of rows affected.
Condition information, such as error codes and messages. If a statement triggers multiple conditions, the diagnostic area has a corresponding condition section for each. If the statement does not trigger any conditions, the condition information section in the diagnostic area is empty.
In the following example, the diagnostic area contains information for the statements that generate the 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 section.
The GET DIAGNOSTICS statement can be used to retrieve either statement or condition information, but not both in the same statement:
To obtain statement information, you need to retrieve the required statement items into target variables. In the following example, the
GET DIAGNOSTICSinstance assigns the number of available conditions and the number of affected rows to user variables@c1and@c2:GET DIAGNOSTICS @c1 = NUMBER, @c2 = ROW_COUNT;To obtain condition information, you need to specify the condition number and retrieve the required condition items into target variables. In the following example, the
GET DIAGNOSTICSstatement assigns theSQLSTATEvalue and error message to user variables@c3and@c4:GET DIAGNOSTICS CONDITION 3 @c3 = RETURNED_SQLSTATE, @c4 = MESSAGE_TEXT;
When the conditions occur, not all condition items identified by GET DIAGNOSTICS may be populated. As shown in the following 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 onto the diagnostic area stack. The first (current) diagnostic area becomes the second (stack) diagnostic area, and a new current diagnostic area is created as its replica.
The GET [CURRENT] DIAGNOSTICS and GET STACKED DIAGNOSTICS statements can be used in handlers to access the contents of the current and stack diagnostic areas. Initially, both diagnostic areas return the same results. Therefore, as long as you do not execute any statements in the handler that change its current diagnostic area, you can obtain information about the activation context of the handler from the current diagnostic area.
However, statements executed in handlers can modify the current diagnostic area and clear or set its contents according to common rules. In such cases, a more reliable way to obtain information about the activation conditions of handlers is to use the stack diagnostic area, which cannot be modified by statements executed in handlers, except for RESIGNAL. For information about when to set and clear the current diagnostic area, see Diagnostic section.
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 obtain information about the exception that has been handled.
In the stored procedure proc(), we attempt to insert two values into a table with a TEXT NOT NULL column. The first value is a non-NULL string, and the second is NULL. Since this column prohibits NULL values, the first insertion succeeds, but the second causes an exception. The procedure includes an exception handler that attempts to insert NULL as 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 region information
DECLARE err_count INT;
DECLARE err_no INT;
DECLARE err_msg TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- The current DA is non-null 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 here (if the INSERT was successful),
-- Therefore, check whether a condition exists before attempting to obtain its 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, the replicas in the current diagnostic area are pushed to the diagnostic area for the stack. The handler first displays the contents of the current and stack diagnostic areas, which are initially the same, as shown below:
+---------------------------------+--------+------------------------------+
| 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
A statement executed after a GET DIAGNOSTICS statement may reset the current diagnostic area. For example, a handler that inserts NULL into a map will insert an empty string and display the result. The new insertion succeeds and clears the current diagnostic area, but the diagnostic areas on the stack remain unchanged, still containing information about the conditions of the activated handlers, as shown below:
+----------------------------------------------+
| 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 conditional handler exits, the diagnostic area where it currently resides is popped from the stack. The diagnostic area at the top of the stack becomes the current diagnostic area for the stored procedure. After the procedure returns, the table contains two rows. The empty row is caused by the attempt to insert a value that maps to an empty string, which results in the insertion of a NULL, as shown below:
+----------+
| col1 |
+----------+
| string 1 |
| |
+----------+
In the previous example, the first two GET DIAGNOSTICS statements in the condition handler that retrieves information from the current and stack diagnostic areas return the same value. This is not the case if the statement that resets the current diagnostic area is executed earlier in the handler. Assuming that rewriting proc() places the DECLARE statement inside the handler definition rather than before it, a different result occurs, as shown below:
CREATE PROCEDURE proc()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Declare variables to store diagnostic region 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