The GET DIAGNOSTICS statement is a good way to show the structure of the diagnostics area because it can copy each value into a variable.

It has a verbose and an error-prone syntax, but it is the only way to analyze the diagnostics area within a stored program. In fact, SHOW WARNINGS returns a result set to the client but does not allow the SQL code to access it. The HANDLER block is executed when an exact error or a class of errors occurs, but it does not provide information about the problem. The reader is expected to be familiar with the stored programs. However, some examples of the use of GET DIAGNOSTICS are useful to demonstrate the contents of the diagnostics area.

First, let's populate the diagnostics area with two errors. For this, we will use the same statements of the previous example:

MariaDB [(none)]> SET sql_mode = 'x';
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'x'
MariaDB [(none)]> RESIGNAL;
ERROR 1645 (0K000): RESIGNAL when handler not active

Now, let's use GET DIAGNOSTICS to copy the number of conditions in the diagnostics area into a variable, and SELECT to show these values. Consider the following example:

MariaDB [(none)]> GET DIAGNOSTICS @num_errs = NUMBER;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT @num_errs;
+-----------+
| @num_errs |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

Now, we know that there are two conditions. Let's visualize the SQLSTATE value, the condition number, and the message of the two conditions, as shown in the following code:

MariaDB [(none)]> GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @error = MESSAGE_TEXT;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT @sqlstate, @errno, @error;
+-----------+--------+------------------------------------------------------+
| @sqlstate | @errno | @error                                               |
+-----------+--------+------------------------------------------------------+
| 42000     |   1231 | Variable 'sql_mode' can't be set to the value of 'x' |
+-----------+--------+------------------------------------------------------+
1 row in set (0.00 sec)