MariaDB/MySQL: Procedures to easily work with the Diagnostics Area

Versione italiana

UPDATE 2013-08-30: I fixed 2 bugs and create a GitHub repo called sql_diagnostix. Sorry for not doing this before. If I (or someone else) find new bugs, I’ll update the repo.

The problem

To quickly see information about the errors and warnings generated by the last executed statement, we can use SHOW WARNINGS. However this statement’s results cannot be accessed via SQL, so they cannot be used for error handling within a stored program.

For that purpose we can use GET DIAGNOSTICS, which has two problems:

  • It requires a lot of code
  • There is not a trivial way to quicly show all info from Diagnostics Area.

The first problem may discourage developers to use GET DIAGNOSTICS at all.

The second problem is not so important in most cases, because the missing info are SQLSTATE and the information which can only be set via SIGNAL and RESIGNAL, and cannot be read by an external program (SCHEMA_NAME, CURSOR_NAME, etc). However SQLSTATE can be important for you and setting/reading all SIGNAL‘s clauses could help debugging and problem solving… that’s why they exist :-)

Oracle MySQL 5.7 has two Diagnostics Areas, and supports the STACKED keyword to access the second DA from an error HANDLER. However, this doesn’t help much: it just allows to execute another table-based statement before reading information about the error conditions which occurred. So, the STACKED DA doesn’t seem to solve any real life problem.

The solution

I created three Stored Procedures to address these problems:

  • _.materialize_diagnostics_area()
  • _.show_diagnostics_area()
  • _.show_full_diagnostics_area()

All the Procedures create a MEMORY temporary table called DIAGNOSTICS_AREA in a database called _. If the table already exists, it is DROPped and reCREATEd. Incidentally they also empty server’s Diagnostics Area, but the information will be avaible in that table until you call one of those routines again.

_.DIAGNOSTICS_AREA has a column called ID, which indicates each condition’s position in the Diagnostics Area. Other columns names and values are equal to the conditions properties in the diagnostics area. The only exception is the SQLSTATE column, which has the name that can be used with SIGNAL and RESIGNAL, not the name used in the diagnostics area (RETURNED_SQLSTATE).

Since both Stored Programs and external programs will be able to access _.DIAGNOSTICS_AREA, this technique exposes all the information that you can set with SIGNAL to external programs (MYSQL_ERRNO, etc).

Directly call materialize_diagnostics_area() if you want to easily copy the DA into a table and later run queries on it.

show_full_diagnostics_area() shows all the information, after populating the table. It’s a sort of SHOW WARNINGS with a lot of columns.

show_diagnostics_area() only shows the ID, SQLSTATE, MYSQL_ERRNO, MESSAGE_TEXT columns.

Example:

MariaDB [test]> INSERT INTO `t` VALUES (1/0), (1/0), (1/0), (-1);
Query OK, 4 rows affected, 4 warnings (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 4
 
MariaDB [test]> CALL _.show_diagnostics_area();
+----+----------+-------------+--------------------------------------------+
| ID | SQLSTATE | MYSQL_ERRNO | MESSAGE_TEXT                               |
+----+----------+-------------+--------------------------------------------+
|  1 | 22012    |        1365 | Division by 0                              |
|  2 | 22012    |        1365 | Division by 0                              |
|  3 | 22012    |        1365 | Division by 0                              |
|  4 | 22003    |        1264 | Out of range value for column 'c' at row 4 |
+----+----------+-------------+--------------------------------------------+
4 rows in set (0.09 sec)

Limitations

If your Stored Program needs to be fast, use these Procedures only for debug.

As mentioned above, each call will erase old data from _.DIAGNOSTICS_AREA. This is by design.

An information is present in SHOW WARNING‘s output but missing from _.DIAGNOSTICS_AREA: the Level column. The reason is that I wasn’t able to find in MySQL documentation how to distinguish a Warning from a Note, except using SHOW WARNINGS. If there is no way, or a way exists but is not documented, I can’t add that information.

5.7’s STACKED diagnostics area is not used.

The code

Okay, I wrote enough. Now, if you are interested:

Download the SQL code

I won’t explain the code, because it seems to me very simple and it’s commented. Just take a look to materialize_diagnostics_area() and you’ll understand what I did.

Enjoy!

About these ads

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s