SQL statements can generate diagnostic information for the diagnostic area. Standard SQL includes a stack for the diagnostic area, encompassing the diagnostic areas of each nested execution context. Standard SQL also supports the GET STACKED DIAGNOSTICS syntax, which is used to reference a second diagnostic area during the execution of a conditional handler. This topic describes the structure of the diagnostic area, the items of information it identifies, how to clear and set the diagnostic area, and how it is pushed onto and popped off the stack.
Structure of the diagnostic area
The diagnostics section contains the following two types of information:
Statement information. For example, 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, this section of the diagnostic area provides a condition area for each condition. If the statement does not trigger any conditions, this section of the diagnostic area is empty.
The following example shows the statements and condition information contained in the diagnostic area for a statement that triggers all three conditions.
Statement information: -- Statement information
row count
... other statement information items ... -- Other statement information list
Condition area list: -- Condition area list
Condition area 1: -- Condition area 1
error code for condition 1 -- Error code for condition 1
error message for condition 1 -- Error message for condition 1
... other condition information items ... -- Other condition information list
Condition area 2: -- Condition area 2
error code for condition 2: -- Error code for condition 2
error message for condition 2 -- Error message for condition 2
... other condition information items ... -- Other condition information list
Condition area 3: -- Condition area 3
error code for condition 3 -- Error code for condition 3
error message for condition 3 -- Error message for condition 3
... other condition information items ... -- Other condition information list
Information Items in the Diagnosis Section
The diagnostic area contains statement and condition items. The numeric items are integers, and the character items use the UTF-8 character set. No item can be NULL. If a statement filling the diagnostic area does not have statement or condition information items set, its value is 0 or an empty string, depending on the item's data type.
The diagnostics section contains the following statement information:
NUMBER: an integer indicating the number of conditional areas with information.ROW_COUNT: an integer indicating the number of rows affected by the statement.ROW_COUNThas the same value as theROW_COUNT()function.
The Condition Information section of the diagnostics area contains a condition area for each condition. The condition areas are numbered from 1 to the value of the NUMBER clause. If NUMBER is 0, there is no condition area.
Each condition section of standard SQL contains the items listed below (MYSQL_ERRNO is an extension):
RETURNED_SQLSTATE: A string indicating theSQLSTATEvalue of the condition.MESSAGE_TEXT: The string indicating the conditional error message.MYSQL_ERRNO: an integer representing the error code of the condition.
The preceding definition applies to conditions that are not generated by signals (that is, by SIGNAL or RESIGNAL statements).
If a SIGNAL (or RESIGNAL) statement populates the diagnostic area, its SET clause can assign valid data type values to any condition item except for RETURNED_SQLSTATE. The SIGNAL also sets the RETURNED_SQLSTATE value, which comes from the SQLSTATE parameter of the SIGNAL statement, rather than being set directly in its SET clause.
The SIGNAL also sets the statement information item NUMBER to 1 and sets ROW_COUNT to -1 to indicate an error; otherwise, it is set to 0.
Clear and populate the diagnostic area
Non-diagnostic SQL statements automatically populate the diagnostic area, and you can use the SIGNAL and RESIGNAL statements to explicitly set the content of the diagnostic area. You can use GET DIAGNOSTICS to check the diagnostic area for extracting specific information, or use SHOW WARNINGS or SHOW ERRORS to view warnings or errors.
You can execute the following SQL statement to clear and set the diagnostic area:
When the server starts executing a statement after parsing, it clears non-diagnostic statements from the diagnostic area. Diagnostic statements are not cleared from the diagnostic area. The following statements are diagnostic:
GET DIAGNOSTICSSHOW ERRORSSHOW WARNINGS
If a statement triggers a condition, the diagnostic area clears conditions belonging to earlier statements. The exception is that conditions triggered by
GET DIAGNOSTICSandRESIGNALare added to the diagnostic area instead of being cleared.
Therefore, statements in the diagnostic area will be cleared when the conditions they trigger are met, even if they are not cleared immediately upon execution.
The following example shows the impact of various statements on the diagnostic area and uses SHOW WARNINGS to display information about storage conditions.
Example 1: The DROP TABLE statement clears the diagnostic area and populates it when a condition is met.
obclient> DROP TABLE IF EXISTS test.no_table_found;
Query OK, 0 rows affected, 1 warning
obclient> SHOW WARNINGS;
+-------+------+-------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------+
| Note | 1051 | Unknown table 'test.no_table_found' |
+-------+------+-------------------------------------+
1 row in set
Example 2: The SET statement generates an error, so it clears and populates the diagnostic area.
obclient> SET @var1 = @@var;
ERROR 1193 (HY000): Unknown system variable 'var'
obclient> SHOW WARNINGS;
+-------+------+-------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------+
| Error | 1193 | Unknown system variable 'var' |
+-------+------+-------------------------------+
1 row in set
Example 3: The SET statement in the previous example generates a condition, so 1 is the only valid condition number for GET DIAGNOSTICS at this time. The following statement generates an alert using condition number 2, which will be added to the diagnostic area and will not be cleared.
obclient> GET DIAGNOSTICS CONDITION 2 @var2 = MESSAGE_TEXT;
Query OK, 0 rows affected, 1 warning
obclient> SHOW WARNINGS;
+-------+------+------------------------------+
| Level | Code | Message |
+-------+------+------------------------------+
| Error | 1193 | Unknown system variable 'var'|
| Error | 1753 | Invalid condition number |
+-------+------+------------------------------+
2 rows in set
Example 4: Based on the previous example, there are now two conditions in the diagnostic area, so the same GET DIAGNOSTICS statement will execute successfully.
obclient> GET DIAGNOSTICS CONDITION 2 @var2 = MESSAGE_TEXT;
Query OK, 0 rows affected
obclient> SELECT @var2;
+---------------------------+
| @var2 |
+---------------------------+
| Invalid condition number |
+---------------------------+
1 row in set
How the diagnostic heap stack works
When a diagnostic area is pushed onto the stack, the first (current) diagnostic area becomes the second (stack's) diagnostic area, and a new current diagnostic area is created as its replica. Diagnostic areas are pushed onto the stack and popped off the stack in the following situations:
Execution of stored programs
The diagnostic area is pushed before program execution and then pops up afterward. If a stored program terminates during execution, multiple diagnostic areas may pop up. This can happen either because no appropriate handler raises an exception or because a
RETURNstatement within the handler causes the termination.Then, add any alerts or error conditions from the pop-up diagnostic area to the current diagnostic area. For triggers, only add error conditions. When the stored program ends, the caller will see these conditions in their current diagnostic area.
Execute conditional handlers in stored programs
When a push occurs due to the activation of a condition handler, the diagnostic area of the stack is the current area before the push (in the stored program). The new current diagnostic area is the current diagnostic area of the handler.
GET [CURRENT] DIAGNOSTICSandGET STACKED DIAGNOSTICScan be used in handlers to access the contents of the current (handler) and stack (stored program) diagnostic areas. Initially, they return the same result, but statements executed in the handler modify the current diagnostic area, setting its content according to general rules. Except forRESIGNAL, statements executed in a handler cannot modify the stack's diagnostic area.If the handler executes successfully, the current (handler) diagnostic area pops up, and the stack (storager) diagnostic area becomes the current diagnostic area again. Conditions added to the handler diagnostic area during the execution of the handler are added to the current diagnostic area.
Execute
RESIGNALDuring the execution of a conditional handler within a compound statement in a stored program, the
RESIGNALstatement is used to pass available error condition information.RESIGNALmay modify part or all of the information before passing it and can alter the stack diagnostic area. For more details, see RESIGNAL.