Movatterモバイル変換

• Diagnostics Area Structure

• Diagnostics Area Information Items

• How the Diagnostics Area is Cleared and Populated

• How the Diagnostics Area Stack Works

• Diagnostics Area-Related System Variables

The diagnostics area contains two kinds of information:

For a statement that produces three conditions, the diagnostics area contains statement and condition information like this:

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 ...

The diagnostics area contains statement and condition information items. Numeric items are integers. The character set for character items is UTF-8. No item can beNULL. If a statement or condition item is not set by a statement that populates the diagnostics area, its value is 0 or the empty string, depending on the item data type.

The statement information part of the diagnostics area contains these items:

The condition information part of the diagnostics area contains a condition area for each condition. Condition areas are numbered from 1 to the value of theNUMBER statement condition item. IfNUMBER is 0, there are no condition areas.

Each condition area contains the items in the following list. All items are standard SQL exceptMYSQL_ERRNO, which is a MySQL extension. The definitions apply for conditions generated other than by a signal (that is, by aSIGNAL orRESIGNAL statement). For nonsignal conditions, MySQL populates only those condition items not described as always empty. The effects of signals on the condition area are described later.

For theRETURNED_SQLSTATE,MESSAGE_TEXT, andMYSQL_ERRNO values for particular errors, seeServer Error Message Reference.

If aSIGNAL (orRESIGNAL) statement populates the diagnostics area, itsSET clause can assign to any condition information item exceptRETURNED_SQLSTATE any value that is legal for the item data type.SIGNAL also sets theRETURNED_SQLSTATE value, but not directly in itsSET clause. That value comes from theSIGNAL statementSQLSTATE argument.

SIGNAL also sets statement information items. It setsNUMBER to 1. It setsROW_COUNT to −1 for errors and 0 otherwise.

Nondiagnostic SQL statements populate the diagnostics area automatically, and its contents can be set explicitly with theSIGNAL andRESIGNAL statements. The diagnostics area can be examined withGET DIAGNOSTICS to extract specific items, or withSHOW WARNINGS orSHOW ERRORS to see conditions or errors.

SQL statements clear and set the diagnostics area as follows:

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, usingSHOW WARNINGS to display information about conditions stored there.

ThisDROP TABLE statement 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)

ThisSET 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 previousSET statement produced a single condition, so 1 is the only valid condition number forGET 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 sameGET DIAGNOSTICS statement succeeds:

mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @p;+--------------------------+| @p                       |+--------------------------+| Invalid condition number |+--------------------------+1 row in set (0.01 sec)

When a push to the diagnostics area stack occurs, the first (current) diagnostics area becomes the second (stacked) diagnostics area and a new current diagnostics area is created as a copy of it. Diagnostics areas are pushed to and popped from the stack under the following circumstances:

Certain system variables control or are related to some aspects of the diagnostics area:

Example: Ifmax_error_count is 10, the diagnostics area can contain a maximum of 10 condition areas. Suppose that a statement raises 20 conditions, 12 of which are errors. In that case, the diagnostics area contains the first 10 conditions,NUMBER is 10,warning_count is 20, anderror_count is 12.

Changes to the value ofmax_error_count have no effect until the next attempt to modify the diagnostics area. If the diagnostics area contains 10 condition areas andmax_error_count is set to 5, that has no immediate effect on the size or content of the diagnostics area.