content_image/685694.jpg content_image/685695.jpg content_image/685696.jpg
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; 

<img src='content_image/685694.jpg'>

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; 

<img src='content_image/685695.jpg'>

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 Stack Works 

mysql> GET DIAGNOSTICS CONDITION 2 @p = MESSAGE_TEXT; Query OK, 0 rows affected (0.00 sec) 

mysql> SELECT @p; 

<img src='content_image/685696.jpg'>

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: 

•Execution of a stored program 

A push occurs before the program executes and a pop occurs afterward. If the stored program ends while handlers are executing, there can be more than one diagnostics area to pop; this occurs due to an exception for which there are no appropriate handlers or due to RETURN  in the handler. 

Any warning or error conditions in the popped diagnostics areas then are added to the current diagnostics area, except that, for triggers, only errors are added. When the stored program ends, the caller sees these conditions in its current diagonstics area. 

•Execution of a condition handler within a stored program 

When a push occurs as a result of condition handler activation, the stacked diagnostics area is the area that was current within the stored program prior to the push. The new now-current diagnostics 

