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.