How the Diagnostics Area is Populated

Most SQL statements populate the diagnostics area automatically, and its contents can be set explicitly with the SIGNAL and RESIGNAL statements. The diagnostics area can be examined with GET DIAGNOSTICS to extract specific items, or with SHOW WARNINGS or SHOW ERRORS to see all conditions or all errors.

SQL statements clear and set the diagnostics area as follows:

When the server starts executing a statement after parsing it, it clears the diagnostics area for nondiagnostic statements that use tables. Diagnostic statements are SHOW WARNINGS, SHOW ERRORS, and GET DIAGNOSTICS.
If a statement raises a condition, the diagnostics area is cleared of conditions that belong to earlier statements. The exception is that conditions raised by GET DIAGNOSTICS and RESIGNAL are added to the diagnostics area without clearing it.

Thus, even a statement that does not normally clear the diagnostics area when it begins executing clears it if the statement raises a condition.

The following example shows the effect of various statements on the diagnostics area, using SHOW WARNINGS to display information about all conditions stored there.

This DROP TABLE statement uses a table, so it clears the diagnostics area and populates it when the condition occurs:

mysql> DROP TABLE IF EXISTS test.no_such_table;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Note | 1051 | Unknown table 'test.no_such_table' |
+-------+------+------------------------------------+
1 row in set (0.00 sec)

This SET statement does not use tables, and SET does not generate warnings, so it leaves the diagnostics area unchanged:

mysql> SET @x = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Note | 1051 | Unknown table 'test.no_such_table' |
+-------+------+------------------------------------+
1 row in set (0.00 sec)

This SET statement generates an error, so it clears and populates the diagnostics area:

mysql> SET @x = @@x;
ERROR 1193 (HY000): Unknown system variable 'x'
mysql> SHOW WARNINGS;
+-------+------+-----------------------------+
| Level | Code | Message |
+-------+------+-----------------------------+
| Error | 1193 | Unknown system variable 'x' |
+-------+------+-----------------------------+
1 row in set (0.00 sec)

The previous SET statement produced a single condition, so 1 is the only valid condition number for GET DIAGNOSTICS at this point. The following statement uses a condition number of 2, which produces a warning that is added to the diagnostics area without clearing it:

mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------+
| Level | Code | Message |
+-------+------+------------------------------+
| Error | 1193 | Unknown system variable 'xx' |
| Error | 1753 | Invalid condition number |
+-------+------+------------------------------+
2 rows in set (0.00 sec)

Now there are two conditions in the diagnostics area, so the same GET DIAGNOSTICS statement succeeds:

How the Diagnostics Area is Populated

Retornar