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
I understand that this is designed for using inside stored code, but using a GET command seems very unwieldy. Why not just set the variables when the error or warning is caused, so one can simply check them and use them afterwards? Or if a command must be used to retrieve them, why not put them into an I_S table so they can be selected, too? Then we could replace GET DIAGNOSTICS with SELECT @@... = ... FROM ...
ReplyDeleteHi Baron.
ReplyDeleteGET DIAGNOSTICS can also be used inside stored procedures,
see for example mysql-test/t/get_diagnostics.test.
In fact it's the most important use case, I should probably have used a
stored procedure in my code example to avoid confusion.
As for the choice of syntax, several points come to mind:
First, there can be several conditions raised by a single statement.
"GET DIAGNOSTICS CONDITION 5 fifth_msg = MESSAGE_TEXT"
is arguably better compared to "select @@MESSAGE_TEXT_5" if using a global
status variable.
Secondly, implementing an INFORMATION_SCHEMA table or similar is not as easy at it seems.
Sure, the data can be exposed in a table, but then, the real question is to
have clear rules describing the table content, and when it is updated.
The GET DIAGNOSTICS statement is used to inspect the internal server
diagnostics area, and execution of this statement is explicitly forbidden to
write to the diagnostics area, precisely because GET DIAGNOSTICS is
classified as a "diagnostics" statement.
With an I_S table, things are not so clear cut,
since a "SELECT row_count from I_S.STATEMENT_DIAGNOSTICS"
would be a regular SELECT statement, and as such would both read from and write to the diagnostics
area at the same time ... possibly returning the wrong value for row_count here.
Now throw in arbitrary where clauses, joins with other diagnostics and non diagnostics
tables, stored functions invocation -- which of course can raise and catch exceptions
of their own --, and so forth, and suddenly defining what should be in this table at
a given time is not so simple any more.
Last, the GET DIAGNOSTICS statement with the syntax implemented is a (subset
of) the ANSI syntax.
While I personally don't know the motivations behind the ANSI spec, I
suspect (and this is an opinion) that people did not get out of their way
to define a dedicated syntax for this statement without good technical reasons.
Having a syntax where arbitrary SQL expressions and arbitrary tables can not
be used seem like a robust way to avoid complexity when dealing with
exception handling, especially with exceptions raised within exception
handling code.
One use case in favor of an I_S table is to "log everything that happened"
instead of inspecting a particular exception property to handle a specific
case in stored procedure code.
For this, my understanding is that the ANSI syntax is GET DIAGNOSTICS ALL,
which is not implemented in MySQL.
What the GET DIAGNOSTICS syntax forces users to to, is to define a local
variable in stored procedures to hold the result of a diagnostics statement
before using it ... is that so unfriendly ?
I hope this clarifies a bit ;)
Also to clarify, I happen to be familiar with GET DIAGNOSTICS because of
work done previously for SIGNAL and RESIGNAL, but I am not the implementor
here. Credit for this work goes to a lot of people, both current and former
employees of the MySQL group at Oracle.
Regards,
-- Marc
Thanks. I was unaware that it is ANSI standard. My mistake.
ReplyDelete