PlanetMysql.ru — информация о СУБД MySQL

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)

Exception handling code can then inspect attributes of the exception raised, to find out which error in particular occurred. Most critical items are fully implemented.

mysql> select @p1, @p2, @p3;
+------------------------------------+-------+------+
| @p1                                | @p2   | @p3  |
+------------------------------------+-------+------+
| Unknown table 'test.no_such_table' | 42S02 | 1051 |
+------------------------------------+-------+------+
1 row in set (0.00 sec)

Unfortunately, others are not fully supported yet.

mysql> select @p4, @p5;
+------+------+
| @p4  | @p5  |
+------+------+
|      |      |
+------+------+
1 row in set (0.00 sec)

The SCHEMA_NAME and TABLE_NAME condition items should be 'test' and 'no_such_table' respectively, indicating which table caused the DROP TABLE statement to fail.

The GET DIAGNOSTICS statement is implemented, and returns the values found in the server diagnostics area, but what is missing now is to revise the implementation of every statement (such as DROP TABLE) to populate every attribute of the diagnostics area when raising an error.

While it may not look so impressive at first glance, implementing the GET DIAGNOSTICS statement is actually a huge step forward: now with SIGNAL and RESIGNAL, GET DIAGNOSTICS allows an application to implement proper SQL exception handling.

-- Marc Alff




PlanetMySQL Voting: Vote UP / Vote DOWN

To whom it may concern -

Today, I received an email from a user of the udf_init_error UDF (which resides in the lib_mysqludf_udf library). The purpose of this UDF is to generate an error condition, which can be used to abruptly terminate a trigger or stored procedure. As such it is a workaround for bug #11661. This is all described extensively in my now ancient article here.

The user wrote me because of a problem experienced in MySQL 5.5:

...calling

select udf_init_error('Transaction Cannot Be Done Because....');

will return user friendly error message:

Transaction Cannot Be Done Because....

. But in MySQL 5.5, it returns

Can't initialize function 'udf_init_error; Transaction Cannot Be Done Because....

The Can't initialize function 'udf_init_error; bit is so annoying! How can I get rid of that?

I explained that the UDF still works like it should; it's just that at some point during the 5.0 lifecycle, the format of the error message was changed. (I can't recall exactly which version that was, but I did file bug #38452 that describes this issue).

Anyway, I suggested to move away from using the udf_init_error() UDF, and port all dependent code to use the SIGNAL syntax instead, which was introduced in MySQL 5.5. (For a friendly introduction to using the SIGNAL syntax, please check out one of my prior articles).

Unfortunately, for this particular user this would not be an easy task:

The use of SIGNAL did come to my mind, but the implementation is not easy. I have thousands of stored routines to modify. Besides, I'm already satisfied with what the UDF does.

On the one hand, It makes me happy to hear the udf_init_error() UDF served him so well that he wrote so many routines that rely on it; on the other hand, I feel bad that this is holding him back from upgrading to MySQL 5.5.

For everybody that is in this same position, I'd like to suggest the following solution: simply re-implement udf_init_error() as a stored SQL function that uses the SIGNAL functionality instead. The error message returned to the client will not be exactly the same as in the olden MySQL 5.0 days, but at least there will not be an annoying complaint about a UDF that cannot be initialized.

Here's a very simple example that illustrates how to do it:

CREATE FUNCTION udf_init_error(
   p_message VARCHAR(80)
)
RETURNS INTEGER
DETERMINISTIC
NO SQL
BEGIN
   DECLARE err CONDITION FOR SQLSTATE '45000';
   SIGNAL err SET MESSAGE_TEXT = p_message;
   RETURN 1;
END;

I hope this helps.
PlanetMySQL Voting: Vote UP / Vote DOWN