In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5.

However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well.

RESIGNAL just outputs the error, as it comes from the server, for instance:

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

read more

I was recently debugging a stored procedure and could not easily identify the underlying reason for why it was failing.

It had a standard exit handler catch-all for SQLEXCEPTION, which was:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

When there was an error, it didn't really output anything useful.

As of MySQL 5.5, there is RESIGNAL:

"RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event."

read more

In a previous post, I discussed debugging stored procedures with RESIGNAL, which is of great value when troubleshooting errors raised by your stored procedures, functions, triggers, and events as of MySQL/MariaDB 5.5.

However, as of MySQL 5.6 and MariaDB 10.0, there is GET DIAGNOSTICS, which can be used to get the exact error details as well.

RESIGNAL just outputs the error, as it comes from the server, for instance:

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

You may not want the error just written to the console, or perhaps you want to at least control how it is written.

It’s common to see exit handler code in the following form:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

Where the SELECT outputs something not very useful in many cases.

With GET DIAGNOSTICS, you can get all of the …

I was recently debugging a stored procedure and could not easily identify the underlying reason for why it was failing.

It had a standard exit handler catch-all for SQLEXCEPTION, which was:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT ...;
END;

When there was an error, it didn’t really output anything useful.

As of MySQL 5.5, there is RESIGNAL:

“RESIGNAL passes on the error condition information that is available during execution of a condition handler within a compound statement inside a stored procedure or function, trigger, or event.”

http://dev.mysql.com/doc/refman/5.5/en/resignal.html

There is also some good information about it here as well:

https://mariadb.com/kb/en/resignal/

It is very simple to use, …

A new feature just got merged into mysql-trunk, the GET DIAGNOSTICS statement.

Many people have been asking for this for a very long time, so it is worth mentioning it.

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.6.4-m6-log |
+--------------+
1 row in set (0.00 sec)

mysql> drop table test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'

Why is it important ? In short, it allows to programmatically (i.e., in SQL) inspect what happened in a SQL exception.

mysql> get diagnostics condition 1
  @p1 = MESSAGE_TEXT,
  @p2 = RETURNED_SQLSTATE,
  @p3 = MYSQL_ERRNO,
  @p4 = SCHEMA_NAME,
  @p5 = TABLE_NAME;

Query OK, 0 rows affected (0.00 sec)

…