The diagnostics area consists of two subareas: the statement information and the condition information.

The statement information contains two values:

The diagnostics area is populated and emptied by following the exact rules. Knowing these rules is very important to debug single statements without falling for some common pitfalls, and it is more important to debug the stored programs.

Whenever a statement generates at least one condition (notes, warnings, or errors), the diagnostics area is populated with such conditions. Any condition present previously in the diagnostics area is deleted. However, there is an exception. If the new statement is RESIGNAL or GET DIAGNOSTICS, the old conditions are not deleted. This is to help the developers. Such statements are used to handle errors and even if they are unsuccessful, the user still needs to read the older conditions. However, if the statement cannot be correctly parsed (because of a syntax error), MariaDB does not know that the statement is RESIGNAL or GET DIAGNOSTICS; thus, the diagnostics area is empty.

If a statement does not produce any conditions and does not access any tables, the old contents of the diagnostics area are preserved. If the statement accesses a table, the old contents are always deleted, even if the statement does not produce any conditions.

The max_error_count system variable represents the maximum number of conditions that can be included in the diagnostics area.

Let's see some examples of how the diagnostics area works. In all the following examples, SHOW WARNINGS is used as a simple way to show the whole diagnostics area. As an easy way to generate an error, we will try to set the SQL_MODE server variable to x, which is not a valid value.

Consider the following example where we will first generate an error and visualize the diagnostics area that contains this error:

MariaDB [(none)]> SET sql_mode = 'x';
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'x'
MariaDB [(none)]> SHOW WARNINGS G
*************************** 1. row ***************************
  Level: Error
   Code: 1231
Message: Variable 'sql_mode' can't be set to the value of 'x'
1 row in set (0.00 sec)
MariaDB [(none)]> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW WARNINGS G
*************************** 1. row ***************************
  Level: Error
   Code: 1231
Message: Variable 'sql_mode' can't be set to the value of 'x'
1 row in set (0.00 sec)

After visualizing the diagnostic area that contains the error, we will execute a correct statement that does not access any table. No error is produced, and the old contents of the diagnostics area remain.

Consider the following example where we will generate an error with the SET statement and then another error with the DROP TABLE statement:

MariaDB [(none)]> SET sql_mode = 'x';
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'x'
MariaDB [(none)]> DROP TABLE information_schema.COLUMNS;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
MariaDB [(none)]> SHOW WARNINGS G
*************************** 1. row ***************************
  Level: Error
   Code: 1044
Message: Access denied for user 'root'@'localhost' to database 'information_schema'
1 row in set (0.00 sec)

In the preceding example, because DROP TABLE accesses the referred table, the second statement clears the diagnostics area. Thus, SHOW WARNINGS visualizes only the second error.

Consider the following example where we will generate an error and then execute a RESIGNAL statement that generates another error:

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
MariaDB [(none)]> SHOW WARNINGS G
*************************** 1. row ***************************
  Level: Error
   Code: 1231
Message: Variable 'sql_mode' can't be set to the value of 'x'
*************************** 2. row ***************************
  Level: Error
   Code: 1645
Message: RESIGNAL when handler not active
2 rows in set (0.00 sec)

In the preceding example, since a correctly parsed RESIGNAL statement never clears the diagnostics area, SHOW WARNINGS returns both the errors. Consider the following example, where we will generate an error, and then we will execute a correct statement that reads a table:

MariaDB [(none)]> SET sql_mode = 'x';
ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'x'
MariaDB [(none)]> SELECT COUNT(*) FROM mysql.user G
*************************** 1. row ***************************
COUNT(*): 11
1 row in set (0.00 sec)
MariaDB [(none)]> SHOW WARNINGS G
Empty set (0.00 sec)

In the preceding example, even if the second statement does not produce any warning, the diagnostics area is emptied.