WL#2111: Stored Procedures: Implement GET DIAGNOSTICS

MySQL will implement the standard SQL:2008 GET DIAGNOSTICS 
statement, which is used to get error information from a 
diagnostics area after the execution of an SQL statement

GET DIAGNOSTICS provides a mechanism for 
(a) an application to ask questions such as "Did anything 
go wrong when the last SQL statement was executed and, if 
so, what went wrong?" and 
(b) to get a reply from the server in a standardized way. 
That is, GET DIAGNOSTICS is used to obtain information about 
the previously executed SQL statement.

This is a subtask needed for stored procedures, although
GET DIAGNOSTICS is not used solely with stored procedures;
the statement can be used to check the result after
execution of any SQL statement. It was decided at the Malta 
conference that this feature does not need to be implemented 
for the MySQL 5.0 release. It was off the roadmap for several
years but it is now back on.

-- Needed functionality:
Standard SQL (SQL:2008 Core) requires that a GET DIAGNOSTICS 
statement be supported, to provide an application a method of 
getting specific diagnostics information (i.e. information on 
error and completion conditions) about the execution 
of an SQL statement. MySQL 5.1 currently does not support this 
functionality, although SHOW ERRORS and SHOW WARNINGS give
some very basic diagnostics information.

-- Compatibility:
IBM DB2 supports GET DIAGNOSTICS in a mostly standard SQL-compliant
manner, with some extensions and exceptions.
Oracle does not support GET DIAGNOSTICS.
SQL Server does not support GET DIAGNOSTICS, but includes both 
keywords in a list of future keywords, with the explanation that 
the words may be reserved in a future release. The implication
is that Microsoft is planning to add support for GET DIAGNOSTICS
someday.

Contents
--------

Diagnostics Areas
Syntax
[ CURRENT | STACKED ]
Items marked "always 0" or "always a zero-length string"


Determining CLASS_ORIGIN and SUBCLASS_ORIGIN
Comparing GET DIAGNOSTICS with SHOW WARNINGS
Other DBMSs
MySQL can't support a diagnostics stack
Error checks on 
Where can GET DIAGNOSTICS occur?
What if GET DIAGNOSTICS itself causes an error?
Statements that cause EOF
Privileges
Reserved Words
Examples
Example with mysql client
Replication
References

Diagnostics Areas
-----------------

Every statement (except the GET DIAGNOSTICS statement) returns
diagnostics information such as row_count(), error message, and
sqlstate. In standard SQL this information has a structure.

Diagnostics Area Stack occurs Diagnostics-Area-Stack-Size times
  Diagnostics Area
    Statement Information
      Statement Information Items
    Condition Area List Occurs Condition-Area-Limit Times
      Condition Information Items

For an example, suppose we have
SET @@sql_mode=''//
CREATE PROCEDURE p ()
BEGIN
  DECLARE x TINYINT;
  DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN
    SET x = 5555;
    /* GET DIAGNOSTICS can go here. */
    END;
  CREATE TABLE t (s1 TINYINT, s2 TINYINT);
  INSERT INTO t VALUES (1, 1);
  UPDATE t SET s1 = 6666, s2 = 7777;
  END//
CALL p()//

After "SET x = 5555" the diagnostics area stack could have:

Diagnostics Area #1
  Statement Information
    Statement Information Item (COMMAND_FUNCTION_CODE) = 5
    Statement Information Item (TRANSACTION_ACTIVE) = 1
  Condition Area List
    Condition Area #1
      Condition Information Item (COLUMN_NAME) = 'x'
      Condition Information Item (MESSAGE_TEXT) = 'Out of range'

Diagnostics Area #2
  Statement Information
    Statement Information Item (COMMAND_FUNCTION_CODE) = 82
    Statement Information Item (TRANSACTION_ACTIVE) = 1
  Condition Area List
    Condition Area #1
      Condition Information Item (COLUMN_NAME) = 's1'
      Condition Information Item (MESSAGE_TEXT) = 'Out of range'
    Condition Area #2
      Condition Information Item (COLUMN_NAME) = 's2'
      Condition Information Item (MESSAGE_TEXT) = 'Out of range'

GET DIAGNOSTICS can select any item from Diagnostics Area #1,
and assign it to a variable. So, after "SET x = 5555;":

If we say GET DIAGNOSTICS @v = COMMAND_FUNCTION_CODE;
The result is: @v contains '5'.

If we say GET CURRENT DIAGNOSTICS CONDITION 1 @v = COLUMN_NAME;
The result is: @v contains 'x'.

Some things in the above description -- getting from Diagnostics Area #2,
getting command_function_code, getting transaction_active -- are not
possible with this worklog task (WL#2111), they will come with WL#5810.

Syntax
------

GET
 [ CURRENT ] DIAGNOSTICS
 {  |
    }

 ::=
 [ { ,  }... ]

 ::=
 = 

 ::=
NUMBER
| ROW_COUNT

 ::=
CONDITION 
 [ { ,  }... ]

 ::=
 = 

 ::=
  CATALOG_NAME
| CLASS_ORIGIN
| COLUMN_NAME
| CONSTRAINT_CATALOG
| CONSTRAINT_NAME
| CONSTRAINT_SCHEMA
| CURSOR_NAME
| MESSAGE_TEXT
| MYSQL_ERRNO
| RETURNED_SQLSTATE
| SCHEMA_NAME
| SUBCLASS_ORIGIN
| TABLE_NAME

 ::= 
|             /* an OUT parameter in an SQL procedure */
|    /* a DECLAREd variable in an SQL routine */
|  /* @variable but not global @@variable */

 ::= 
(that is, a declared variable or @variable or @@variable or literal, not null)

[ CURRENT | STACKED ]
---------------------

We will allow [ CURRENT ], which has no effect.
We will not allow [ STACKED ], support for it is deferred.

Items marked "always 0" or "always a zero-length string"
--------------------------------------------------------

In later sections which describe item values,
frequently the phrase "always 0" or "always a zero-length
string" will appear. These items exist because they're
in standard SQL, and in some future version (after WL#2111)
they may be filled in automatically. But they will not
be filled in as part of this task.

In fact many items can be set by SIGNAL or RESIGNAL,
but after other statements they will be zero or blank.


----------------------------

If the statement is:
GET DIAGNOSTICS  =  [ , ... ]
then each specified target gets the value of each specified
statement information item. For example:
UPDATE t SET s1 = 5;
GET DIAGNOSTICS @x = ROW_COUNT;
Now @x has the value of ROW_COUNT, which was set when
the UPDATE statement was executed.

The statement information is the header of a diagnostics area.

At this time the only statement information items
that we support are NUMBER and ROW_COUNT.

NUMBER                                  number, for example INTEGER
The number of condition areas that have information.
If the last statement ended with 33 warnings, and it was
possible to fill in a condition area for each one, then NUMBER = 33.

ROW_COUNT                               number, for example INTEGER
A number that shows how many rows were directly affected by a
data-change statement (INSERT, UPDATE, DELETE, MERGE, REPLACE, LOAD).
By "directly" affected Peter means "don't count changes that happen
for triggers or foreign-key cascading".
This number will be the same as what one would get with
the ROW_COUNT() function. This is non-standard behaviour.


----------------------------

If the statement is:
GET DIAGNOSTICS
CONDITION 
 =  [, ... ]
then each specified target gets the value of each specified
condition information item. For example:
UPDATE t SET s1 = 5;
GET DIAGNOSTICS CONDITION 1 @x = MYSQL_ERRNO;
Now @x has the value of MYSQL_ERRNO, which was set when
the UPDATE statement was executed.

The rest of this section is a description of each condition information item.

All CHAR/VARCHAR items are UTF8. No items are nullable.

CATALOG_NAME                             string, for example VARCHAR(64)
A string that shows the current catalog.
Always a zero-length string.

SCHEMA_NAME                              string, for example VARCHAR(64)
A string that shows the current schema.
Always a zero-length string.

TABLE_NAME                               string, for example VARCHAR(64)
A string that shows the current table.
Always a zero-length string.

COLUMN_NAME                              string, for example VARCHAR(64)
A string that shows the current column (if applicable).
Always a zero-length string.

RETURNED_SQLSTATE                        string, for example VARCHAR(5)
A string that shows the SQLSTATE value that would 
have been returned if this were the only condition raised during 
the execution of the last SQL statement.
If the last statement caused one warning '01001', then
the statement as a whole returns SQLSTATE = '01001' and
GET DIAGNOSTICS CONDITION 1 @x = RETURNED_SQLSTATE;
puts '01001' into @x.

MYSQL_ERRNO                              number, for example UNSIGNED SMALLINT
A number that shows the error, as in include/mysqld_error.h.
This is the only MySQL-specific statement information item.

CLASS_ORIGIN                             string, for example VARCHAR(64)
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
  CLASS_ORIGIN = '' i.e. a zero-length string (if not explicitly set)
Else:
  If RETURNED_SQLSTATE begins with a class
  value defined in the SQL standard (e.g. '42'):
    CLASS_ORIGIN = 'ISO 9075'
  else:
    CLASS_ORIGIN = 'MySQL'
See also section "Determining CLASS_ORIGIN and SUBCLASS_ORIGIN".

SUBCLASS_ORIGIN                            string, for example VARCHAR(64)
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
  SUBCLASS_ORIGIN = '' (if not explicitly set)
Else:
  If RETURNED_SQLSTATE ends with a class
  value defined in the SQL standard (e.g. '000'):
    SUBCLASS_ORIGIN = 'ISO 9075'
  else:
    SUBCLASS_ORIGIN = 'MySQL'
For algorithm, see section "Determining CLASS_ORIGIN and SUBCLASS_ORIGIN".

CURSOR_NAME                                string, for example VARCHAR(64)
A string that shows the cursor name.
Always a zero-length string.

CONSTRAINT_CATALOG                         string, for example VARCHAR(64)
Always a zero-length string.

CONSTRAINT_NAME                            string, for example VARCHAR(64)
Always a zero-length string.

CONSTRAINT_SCHEMA                          string, for example VARCHAR(64)
Always a zero-length string.
 
MESSAGE_TEXT                               string, for example VARCHAR(128)
Error message.
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
  MESSAGE_TEXT = ''
Else:
  A string that shows (for a routine) the message text item of the SQL-invoked 
  routine that caused the condition. Otherwise, MESSAGE_TEXT is set to an 
  implementation-defined character string. For MySQL, this should be the text 
  message that relates to the RETURNED_SQLSTATE (for example, "Table  doesn't exist" when RETURNED_SQLSTATE is 42S02).

Determining CLASS_ORIGIN and SUBCLASS_ORIGIN
--------------------------------------------

We will assume that all MySQL programmers, when making SQLSTATE values
for new errors, follow the rules that appear in the standard
document ISO 9075-2, section 24.1 SQLSTATE.
We will assume that the only international standard that we follow is 9075.
So set ORIGIN values based on what is a "standard-defined class".

Let CLASS = the first two letters of RETURNED_SQLSTATE.
If CLASS[1] is any of: 0 1 2 3 4 A B C D E F G H
and CLASS[2] is any of: 0-9 A-Z
  then let CLASS_ORIGIN = 'ISO 9075'.
else
  let CLASS_ORIGIN = 'MySQL'.

Let SUBCLASS = the next three letters of RETURNED_SQLSTATE.
If CLASS_ORIGIN = 'ISO 9075' or SUBCLASS = '000'
  then let SUBCLASS_ORIGIN = 'ISO 9075'.
else
  let SUBCLASS_ORIGIN = 'MySQL'.

(The above does not apply for SIGNAL/RESIGNAL.
For SIGNAL/RESIGNAL, CLASS_ORIGIN and SUBCLASS_ORIGIN
are zero-length strings unless they are explicitly set,
as in (for example) SIGNAL ... SET CLASS_ORIGIN='X'.)

Alternatively, we could check whether RETURNED_SQLSTATE is in the
table in ISO 9075-2 Table 33 SQLSTATE class and subclass values,
with additions from other 9075 documents. This would be more
correct but, well, tedious.

Almost no errors will have CLASS_ORIGIN='MySQL' or SUBCLASS_ORIGIN='MySQL'.

Comparing GET DIAGNOSTICS with SHOW WARNINGS
--------------------------------------------

There was discussion of the differences / similarities
of GET DIAGNOSTICS and SHOW WARNINGS in dev-private thread
"Re: SIGNAL / RESIGNAL / GET DIAGNOSTICS". Example email:
[ mysql intranet ] /secure/mailarchive/mail.php?folder=4&mail=23971

SHOW WARNINGS or SHOW ERRORS should not clear the diagnostics area.
(GET DIAGNOSTICS does not clear the diagnostics area.)

Other DBMSs
-----------

Oracle 11g has SQLERRM and SQLCODE, that's about all.

SQL Server has ERROR_LINE(), ERROR_MESSAGE(), ERROR_NUMBER(),
ERROR_PROCEDURE(), ERROR_STATE(), and @@ERROR

DB2, Mimer and Teradata have a GET DIAGNOSTICS statement which
approximately follows the standard. One difference is:
DB2/Mimer/Teradata syntax:  GET DIAGNOSTICS EXCEPTION n ...
Standard syntax:            GET DIAGNOSTICS CONDITION n ...
Trudy proposed that we allow EXCEPTION as a synonym for CONDITION,
but that was not accepted.

MySQL can't support a diagnostics stack
---------------------------------------

Earlier sections mention multiple diagnostics areas.
Support for that would require an assumption that MySQL really
supports "push" and "pop" on a diagnostics area stack.
That is too hard, for now.

With a single diagnostics area, we have to say "GET DIAGNOSTICS always
returns the results from the last statement". This is grossly inferior to
standard SQL, but grossly superior to what we have now, which is nothing.

Error checks on 
----------------------------------

(In this section we talk about the statement "failing",
and a bad condition number really is an "error",
but the practical consequence is what looks like a warning
as described in the later section
"What if GET DIAGNOSTICS itself causes an error?").

A  cannot be any expression that evaluates to an 
integer. It must be a simple value specification.
For us that will mean (just as in the description of WL#2110):
variables (DECLAREd variables, @variables, @@variables), parameters
(i.e. input parameters of functions or procedures), and literals,
and not NULLs.

If  is 
(a) less than 1 (one) or 
(b) greater than the number of occupied condition areas in the 
diagnostics area, 
then GET DIAGNOSTICS fails with SQLSTATE 35000 "invalid condition number".
("Fails" in the previous sentence merely means "doesn't work" rather
than "causes an exception", see description of Alexander Nozdrin's choice
in a later section.)
(Note that the occupied condition areas are the condition areas 
that were populated by the execution of the previous SQL statement. 
If the execution raised two conditions, then two condition areas 
will be occupied; therefore, if  in GET DIAGNOSTICS 
asks for information on three or more conditions, the statement fails.)

If  has the value 1 (one), then in standard SQL the diagnostics 
information retrieved corresponds to the condition indicated by the 
SQLSTATE value actually returned when the previous SQL statement was 
executed. Thus, if the server returns SQLSTATE 00000 "OK" to the client 
and GET DIAGNOSTICS asks for information on condition one, the information
returned will be for SQLSTATE 00000, regardless of any other conditions
(e.g. a warning about string truncation) raised when the previous SQL 
statement was executed).

If  has a value greater than 1 (one), then the 
order in which each condition raised during the execution of the previous 
SQL statement is implementation-dependent. That is, we can choose the 
order in which we wish to return multiple condition information.

Peter suggested that the first condition information returned should always
relate to the SQLSTATE value actually returned when the previous SQL 
statement was executed, as in standard SQL. That did not happen.
Therefore it is not true in MySQL that one can get the main error with
GET DIAGNOSTICS CONDITION 1 @x = mysql_errno;
Instead one must say something like this
GET DIAGNOSTICS CONDITION number = number;
GET DIAGNOSTICS CONDITION @number @x = mysql_errno;
WL#5810 proposes that this should be fixed.

Where can GET DIAGNOSTICS occur?
--------------------------------

The GET DIAGNOSTICS statement can occur in SQL routines
(stored procedures or functions) and in triggers,
provided it is within a BEGIN ... END compound statement.
It may be in a dynamic compound statement (see WL#3696).
It cannot be the object of a PREPARE statement.
It does not cause a transaction to start.

Peter would like to see GET DIAGNOSTICS anywhere, that is, outside
routines or BEGIN ... END compound statements.
That was up to the implementor.
The implementor decided to allow GET DIAGNOSTICS
outside routines or BEGIN ... END compound statements.

What if GET DIAGNOSTICS itself causes an error?
-----------------------------------------------

By restricting so that  must be a simple value
specification, and that the target cannot be a @@variable, we try to make
sure that most errors will happen during syntax checking.
Syntax checks of course cause errors in the usual fashion;
there's nothing special about GET DIAGNOSTICS for syntax.
If the syntax is no good, it's not really a GET DIAGNOSTICS
statement.

Data exceptions should not cause errors. For example, consider
CREATE PROCEDURE p ()
BEGIN
  DECLARE v TINYINT;
  UPDATE t SET s1 = 5;
  GET DIAGNOSTICS v = ROW_COUNT;
  END
If ROW_COUNT is too big for v, that's okay, there's no
strict-mode checking, v gets the maximum TINYINT value,
and there will be no warning. Always suppress warnings.

So execution of GET DIAGNOSTICS will almost never cause
any change of diagnostics areas. But if it does (for
example "invalid condition number"), here's a decision.

[ Note added 2011-03-25: There were nine suggestions,
Alexander Nozdrin picked suggestion (6), the others
are now erased, see "Progress" on this date for details. ]

Add another warning.
For example, after
DROP TABLE nonexistent_table; /* Causing "Unknown table" */
GET DIAGNOSTICS @m = NUMBER;  /* Causing "Collapse of Civilization" */
There will be two condition areas. The first one has
the original error, "Unknown table". The second one has
a warning, "Collapse of Civilization during GET DIAGNOSTICS".
Also the statement information item NUMBER would be incremented.

Here is an example for a scenario where a statement before
GET DIAGNOSTICS has returned one error and three warnings.

DROP TABLE t;
SET @@sql_mode='';
SET @@max_error_count=64;
SET @x='';
CREATE TABLE t (s1 TINYINT, s2 TINYINT, s3 TINYINT UNIQUE);
INSERT INTO t VALUES (127,127,127);
INSERT INTO t VALUES (999,999,999);
SELECT @@warning_count;
SHOW WARNINGS;
The @@warning_count i.e. number of condition areas is 4.
The result of SHOW WARNINGS is:
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 's1' at row 1 |
| Warning | 1264 | Out of range value for column 's2' at row 1 |
| Warning | 1264 | Out of range value for column 's3' at row 1 |
| Error   | 1062 | Duplicate entry '127' for key 's3'          |
+---------+------+---------------------------------------------+
4 rows in set (0.00 sec)

Now, the question is,
what should happen if,
after the final INSERT statement,
we said
GET DIAGNOSTICS CONDITION 5 @x = mysql_errno;
Definitely a diagnostics statement (like GET DIAGNOSTICS)
does not "clear the MySQL message list", that is, the
existing condition areas remain unchanged.

Therefore the user sees this:

mysql> GET DIAGNOSTICS CONDITION 5 @x = returned_sqlstate;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
|      |
+------+
1 row in set (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 's1' at row 1 |
| Warning | 1264 | Out of range value for column 's2' at row 1 |
| Warning | 1264 | Out of range value for column 's3' at row 1 |
| Error   | 1062 | Duplicate entry '127' for key 's3'          |
| Error   | 1739 | Invalid condition number                    |
+---------+------+---------------------------------------------+
5 rows in set (0.00 sec)

Here are some flipped-a-coin specifications re details:
* The initial return says "1 warning" rather than "5 warnings"
* The code might of course be a different number
* The Message does not end with GET DIAGNOSTICS so it might be
  hard to see that the "Invalid condition number" was not for
  the previous statement
* The GET DIAGNOSTICS warning should be listed first when
  possible but that is deferred to WL#5810
* The value of @x is unchanged when possible.

Some further results for this example might seem a bit odd.
The SHOW WARNINGS won't clear the message list, and
NUMBER has gone up, so the user will get a different
result if he/she now enters exactly the same statement, thus:

mysql> GET DIAGNOSTICS CONDITION 5 @x = returned_sqlstate;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @x;
+-------+
| @x    |
+-------+
| 35000 |
+-------+
1 row in set (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 3456 | Invalid condition number in GET DIAGNOSTICS |
| Warning | 1264 | Out of range value for column 's1' at row 1 |
| Warning | 1264 | Out of range value for column 's2' at row 1 |
| Warning | 1264 | Out of range value for column 's3' at row 1 |
| Error   | 1062 | Duplicate entry '127' for key 's3'          |
+---------+------+---------------------------------------------+
5 rows in set (0.00 sec)

In Alexander Nozdrin's words,
"By doing it this way we achieve the following results:
 - if the user doesn't want to care about GET DIAGNOSTICS, 
    he can continue (warnings don't stop execution flow).
    But anyway there will be traces that something went wrong.

  - if the user wants to have the "full control", he can setup
    a CONDITION HANDLER for [SQLWARNING]. The warning thrown
    by GET DIAGNOSTICS will be handled there."

We have to make an exception for "fatal" errors.
Let's enumerate first what applicable error categories are.

There are syntax errors, which are dismissed by saying
bad statements are not really GET DIAGNOSTICS statements.

There are fatal errors which are not due to an action
of GET DIAGNOSTICS itself, and which happen semi-annually.
The categories are memory (out of memory, stack overrun),
network (lost connection to slave, read error from pipe),
interference (signal), disk (error writing to audit log),
future resource limits (too many statements executed).

There are a few errors which are due to illegal
assignment, which we want to avoid by having no strict-mode
checks. The categories are unwritable targets
(server variables, perhaps input parameters in future),
bad inputs (too long, wrong data type).

There are no errors which are due to privileges,
although in future there might be, when we allow
showing the column name, procedure name, etc.

And there are no-such-input errors. This could happen
with STACKED (a deferred item), but currently the only
real possibility is a condition number out of range.

A fatal error, and only a fatal error, stops execution.

Statements that cause EOF
-------------------------

Some MySQL-specific statements, for example SHOW,
end with EOF. They are not to be treated the way
that we treat "no data" for FETCH.

If statement or command returns EOF with no warning,
then NUMBER = 1, ROW_COUNT = don't care (say -1), and
RETURNED_SQLSTATE = '00000'
MYSQL_ERRNO = 0
MESSAGE_TEXT = 'OK'
CLASS_ORIGIN = 'ISO 9075'
SUBCLASS_ORIGIN = 'ISO 9075'
Thus there's no difference from 'OK'.

Privileges
----------

No special privileges are required.

Reserved Words
--------------

GET

In standard SQL, CURRENT is also reserved.
In MySQL, CURRENT will not be reserved.

CONDITION is already a reserved word.

Examples
--------

DELIMITER //
CREATE PROCEDURE p ()
BEGIN
  DECLARE a INT;
  GET DIAGNOSTICS a = NUMBER, @a = NUMBER;
  END//
CALL p()//
It is not an error to use the same statement information item twice.

GET DIAGNOSTICS CONDITION 1 variable4 = SCHEMA_NAME;
GET DIAGNOSTICS CONDITION 3
  variable5 = TABLE_NAME, variable6 = RETURNED_SQLSTATE;
Both of these statements put information about specific errors or
other conditions that occurred when the previous SQL statement was
executed, into SQL variables.

GET DIAGNOSTICS @row_count = ROW_COUNT, CONDITION 1 @returned_sqlstate =
RETURNED_SQLSTATE;
Result: syntax error. GET DIAGNOSTICS has either a comma-delimited
list of statement information items, or a comma-delimited list of condition
information items.
No mixing.

DELIMITER //
USE test
CREATE PROCEDURE p ()
BEGIN
  DECLARE v VARCHAR(64);
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    GET DIAGNOSTICS CONDITION 1 v = TABLE_NAME;
  DROP TABLE no_such_table;
  SELECT v;
  END//
CALL p()//
Result: ideally it would be 'no_such_table', but we're accepting ''.

DELIMITER //
CREATE PROCEDURE p ()
BEGIN
  DECLARE v CHAR(1);
  CREATE TABLE IF NOT EXISTS already_existing_table (s1 INT);
  GET DIAGNOSTICS CONDITION 1 v = MESSAGE_TEXT;
  SELECT v;
  end//
CALL p()//
Result: 'T'. The _CREATE string has been truncated because v's
length is only 1. This does not cause a warning, because GET
DIAGNOSTICS has nowhere to put warnings for itself. 
(Originally the specification was:
"The  for each statement or condition information item can
be either a host parameter, an SQL parameter, or a reference to
a column in the database. Each target must have a data type that
matches the data type of the item for which it is the target (see
the list that follows). That is, the target for the statement
information item ROW_COUNT must have a data type of INTEGER; the 
target for the condition information item PARAMETER_MODE must have 
a data type of VARCHAR(5)."
but Peter fears that is not the usual way MySQLers want things.)

CREATE PROCEDURE p1 (IN col2_param VARCHAR(3))
LANGUAGE SQL
BEGIN 
  DECLARE v INT DEFAULT 0;
  DECLARE rcount_each INT;
  DECLARE rcount_total INT DEFAULT 0;
  WHILE v < 5 DO
    UPDATE t1 SET col1 = col1 * 1.1  WHERE col2 = col2_param; 
    GET DIAGNOSTICS rcount_each = ROW_COUNT;
    SET rcount_total = rcount_total + rcount_each;
    SET v = v + 1;
    END WHILE;
  SELECT rcount_total;
  END//
Result: number of rows updated by all the UPDATE executions.

/* GET DIAGNOSTICS doesn't clear the diagnostics area. */
CREATE PROCEDURE p ()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN
    GET CURRENT DIAGNOSTICS CONDITION 1 @x = RETURNED_SQLSTATE;
    SIGNAL SQLSTATE '01002';
    GET CURRENT DIAGNOSTICS CONDITION 1 @y = RETURNED_SQLSTATE;
    END;
  SIGNAL SQLSTATE '01001';
  SELECT @x, @y;
  END//
CALL p()//
/* Result: @x = '01001', @y = '01002' */

/* These are all the conditions which can be set with SIGNAL. */
SIGNAL SQLSTATE '77777' SET
  CLASS_ORIGIN = 'a',
  SUBCLASS_ORIGIN = 'a',
  CONSTRAINT_CATALOG = 'a',
  CONSTRAINT_SCHEMA = 'a',
  CONSTRAINT_NAME = 'a',
  CATALOG_NAME = 'a',
  SCHEMA_NAME = 'a',
  TABLE_NAME = 'a',
  COLUMN_NAME = 'a',
  CURSOR_NAME = 'a',
  MESSAGE_TEXT = 'a',
  MYSQL_ERRNO = 1000;
GET DIAGNOSTICS CONDITION 1
  @returned_sqlstate = RETURNED_SQLSTATE,
  @class_origin = CLASS_ORIGIN,
  @subclass_origin = SUBCLASS_ORIGIN,
  @constraint_catalog = CONSTRAINT_CATALOG,
  @constraint_schema = CONSTRAINT_SCHEMA,
  @constraint_name = CONSTRAINT_NAME,
  @catalog_name = CATALOG_NAME,
  @schema_name = SCHEMA_NAME,
  @table_name = TABLE_NAME,
  @column_name = COLUMN_NAME,
  @cursor_name = CURSOR_NAME,
  @message_text = MESSAGE_TEXT,
  @mysql_errno = MYSQL_ERRNO;

Example with mysql client
-------------------------

mysql> CREATE TABLE t (s1 INT);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> UPDATE t SET s1 = 5;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5  Changed: 4  Warnings: 0

mysql> GET DIAGNOSTICS @x = ROW_COUNT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 5    |
+------+
1 row in set (0.00 sec)

The noticeable things for this example are:

* After GET DIAGNOSTICS you see "0 rows affected" etc.
That happens because the protocol fields are not what's
in the current diagnostics area (ROW_COUNT, NUMBER, etc.).
What the server sends to the client in an "OK" packet
(affected rows, insert ID, warning count, message) is
the result of the GET DIAGNOSTICS statement itself.

* In the end, @x = 4, not 5. That happens because
ROW_COUNT is non-standard.

Replication
-----------

GET DIAGNOSTICS affects variables. Variables may be used in DML statements.
Can we ensure that a slave and the master see the same error messages?
Here we must worry about the setting of @@lc_messages and condition area limit.

Option 1.
Instead of logging
GET DIAGNOSTICS CONDITION 1 @x = COLUMN_NAME;
we could find out what column_name is, and log
SET @x = 'whatever column_name is';

Option 2.
Save the settings of @@lc_messages and condition area limit.

Option 3.
Do nothing. Just say GET DIAGNOSTICS is safe for replication.

We will decide on option 3 if the GET DIAGNOSTICS statement
appears anywhere, while assuming master-vs-slave data consistency.

See dev-replication emails starting with
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=119&mail=5761

See Andrei's email at
http://vilje01.norway.sun.com/mailarchive/mail.php?folder=119&mail=5937

References
----------

DB2 GET DIAGNOSTICS statement (Linux, Unix, and Windows)
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0005647.htm

DB2 GET DIAGNOSTICS statement (i5/OS)
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fdb2%2Frbafzmstgetdiag2.htm

Jim Melton, "Diagnostics and Error Management"
http://books.google.ca/books?
id=VCsIPDZVQAIC&pg=PA349&vq=diagnostics+and+error+management&source=gbs_search_r
&cad=1_1&sig=7f7O8RNLWALCLvda2n1RSKGYQno

BUG#10797 Allow error code to be picked up in a stored procedure

BUG#11660 Expose either SQLState, mysql_error() or other diagnostics in stored
procedures

BUG#16371 HOW TO GET SQLSTATE

BUG#17034 Error messages from stored routines should name their source

BUG#42135 Identify Trigger Errors DB Interaction

SQLGetDiagRec() ODBC function as described for DB2 v9.1
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?
topic=/com.ibm.db29.doc.odbc/db2z_fngetdiagrec.htm

WL#355 error messages review

WL#4689 Deadlock Monitor (which refers to WL#2111)


######################################################

On September 8 Geir added this to the "FY11" list.
https://inside.mysql.com/wiki/PlanFY11
This appears to be the first time that the task has
been on any sort of 5.x roadmap.

Mark Callaghan says "WL 2111 is the one that I want.
The worklog is marked as low-priority for release 7.0.
Can this be escalated?"
https://support.mysql.com/view_email.php?ema_id=1&id=451197

Marc Alff says "feature was done 3 years ago with prototyping
code that worked ... is actually trivial ... what is needed is
the political will to declare this a priority + prevent continuous
bikeshed from the various review process ... The previous attempt to
implement this just died from the internal review process and
constant internal opposition to any progress made."
http://bugs.mysql.com/bug.php?id=11660

Rob Young says "Please consider how [BUG#11660 "Expose either SQLState,
mysql_error() or other diagnostics in stored procedures"] can be
fixed/implemented in the near term ... providing simple error handling
within stored procs enhances our competitive positioning against MSFT SQL 
Server"
https://inside.mysql.com/wiki/Server_Support_Issues

Guy Harrison says "this is a low (!) priority enhancement probably
not going to be seen before MySQL 7.0.  That's a drag."
http://guyharrison.squarespace.com/blog/2009/7/13/signal-and-resignal-in-mysql-
54-and-60.html

            
                                    

                
Marc Alff wrote some code in 2008 which included some support
for GET DIAGNOSTICS. See for example
http://lists2.mysql.com/commits/50233

That is sufficient for a task of this nature.

            
                    
    
        


    
    

        Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.