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

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 …