MySQL will implement the standard SQL:2003 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 is now off the roadmap.
Rationale
---------
-- Needed functionality:
Standard SQL (SQL:2003 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.0 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.
Note added by Marc Alff, April 10, 2007:
* SIGNAL and RESIGNAL need to implement the SET <signal information> *syntax*,
but do not need (yet) to implement storing signal information in the
diagnostic storage area in the server, since the only way to retrieve that
data is with GET DIAGNOSTICS
Full implementation of the diagnostics storage area is required for
GET DIAGNOSTICS only.
As a result, changing the WL dependencies as follows:
- SIGNAL can be implemented by itself,
- RESIGNAL depends on SIGNAL
- GET DIAGNOSTICS depends on both SIGNAL and RESIGNAL
* During the Customer Advisory Board in Cupertino, in Dec 2006,
customers reported that SIGNAL alone, even without RESIGNAL, could be
useful in some cases.
* About the parser: I don't understand what in the grammar or in the spec
could cause a problem, SIGNAL / RESIGNAL / GET DIAGNOSTICS is no more
complicated than the existing statements currently supported.
Contents
--------
Diagnostics Areas
Syntax
Syntax: [ CURRENT | STACKED ]
<statement information list>
Function and Function_Code Values
<condition information list>
Determining CLASS_ORIGIN and SUBCLASS_ORIGIN
Other statement information items or condition information items
Comparing GET DIAGNOSTICS with SELECT FROM DIAGNOSTICS_AREAS
Other DBMSs
What if MySQL can't support a diagnostics stack?
Error checks on <condition number>
Where can GET DIAGNOSTICS occur?
Privileges
Reserved Words
Examples
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
CREATE PROCEDURE p ()
BEGIN
DECLARE x TINYINT;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET x = 5555;
CREATE TABLE t (s1 TINYINT, s2 TINYINT);
INSERT INTO t VALUES (1, 1);
UPDATE t SET s1 = 6666, s2 = 7777;
END//
CALL p()//
During "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 either diagnostics area
and assign it to a variable.
For an example, suppose we have
GET STACKED DIAGNOSTICS CONDITION 2 @v = COLUMN_NAME;
The result is: @v contains 's2'.
Syntax
------
GET
[ CURRENT | STACKED ] DIAGNOSTICS
{ <statement information list> |
<condition information list> }
<statement information list> ::=
<statement information item> [ { , <statement information item> }... ]
<statement information item> ::=
<target> = <statement information item name>
<statement information item name> ::=
NUMBER
| MORE
| COMMAND_FUNCTION
| COMMAND_FUNCTION_CODE
| DYNAMIC_FUNCTION
| DYNAMIC_FUNCTION_CODE
| ROW_COUNT
| TRANSACTIONS_COMMITTED
| TRANSACTIONS_ROLLED_BACK
| TRANSACTION_ACTIVE
<condition information list> ::=
CONDITION <condition number>
<condition information item> [ { , <condition information item> }... ]
<condition information item> ::=
<target> = <condition information item name>
<condition information item name> ::=
CATALOG_NAME
| CLASS_ORIGIN
| COLUMN_NAME
| CONDITION_IDENTIFIER
| CONDITION_NUMBER
| CONNECTION_NAME
| CONSTRAINT_CATALOG
| CONSTRAINT_NAME
| CONSTRAINT_SCHEMA
| CURSOR_NAME
| MESSAGE_LENGTH
| MESSAGE_OCTET_LENGTH
| MESSAGE_TEXT
| MYSQL_ERRNO
| PARAMETER_MODE
| PARAMETER_NAME
| PARAMETER_ORDINAL_POSITION
| RETURNED_SQLSTATE
| ROUTINE_CATALOG
| ROUTINE_NAME
| ROUTINE_SCHEMA
| SCHEMA_NAME
| SERVER_NAME
| SPECIFIC_NAME
| SUBCLASS_ORIGIN
| TABLE_NAME
| TRIGGER_CATALOG
| TRIGGER_NAME
| TRIGGER_SCHEMA
<target> ::=
| <SQL parameter> /* an OUT parameter in an SQL procedure */
| <SQL variable reference> /* a DECLAREd variable in an SQL routine */
| <MySQL variable reference> /* @variable but not global @@variable */
<condition number> ::= <simple value specification>
(that is, a numeric positive number, not null)
Syntax: [ CURRENT | STACKED ]
-----------------------------
The [ CURRENT | STACKED ] optional clause specifies:
(if CURRENT) diagnostics area #1, i.e. top of stack
(if STACKED) diagnostics area #2, i.e. second from top of stack
The default is CURRENT. See later section "Diagnostics Areas".
If the statement is:
GET STACKED DIAGNOSTICS ...
and no condition handler is activated, then GET DIAGNOSTICS fails
with SQLSTATE 0Z002 "diagnostics exception stacked diagnostics
accessed without active handler".
That's harsh. In standard SQL the diagnostics area "stack" also
exists for a trigger, and it might be useful to get diagnostics
for either the within-the-trigger statement, or the caused-the-trigger
statement. But that requires changes to trigger execution, so it's
outside the scope of this task.
<statement information list>
----------------------------
If the statement is:
GET DIAGNOSTICS <target> = <statement information item name> [ , ... ]
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.
The rest of this section is a description of each statement information item.
NUMBER 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.
MORE CHAR(1)
Whether there were more conditions than condition areas.
'Y' means yes and 'N' means no.
If the last statement ended with 34 warnings, and it was not
possible to fit in every warning because the maximum number of
conditions (the "condition area limit") was 33, then NUMBER = 33
and MORE = 'Y'.
COMMAND_FUNCTION VARCHAR(implementation-defined)
A string that shows the SQL statement type, often the first word
of the statement.
If the last statement was COMMIT, then COMMAND_FUNCTION = 'COMMIT WORK'.
The full list of statements is in section "Function and Function_Code Values".
COMMAND_FUNCTION_CODE INTEGER
A number that shows the SQL statement, associated with COMMAND_FUNCTION.
If the last statement was COMMIT, then COMMAND_FUNCTION_CODE = 11.
If COMMAND_FUNCTION_CODE < 0, the statement is a MySQL extension.
If COMMAND_FUNCTION_CODE = 0, the statement is unrecognized (an error).
If COMMAND_FUNCTION_CODE > 0, the statement is defined in standard SQL.
The full list of statements is in section "Function and Function_Code Values".
DYNAMIC_FUNCTION VARCHAR(implementation-defined)
A string that shows the SQL statement type, often the first word
of the statement -- for a statement that's done dynamically.
Otherwise DYNAMIC_FUNCTION is a zero-length string.
If the last statement was PREPARE stmt1 FROM 'UPDATE t SET s1 = 5',
then COMMAND_FUNCTION = 'PREPARE', COMMAND_FUNCTION_CODE = 56,
DYNAMIC_FUNCTION = 'UPDATE WHERE', DYNAMIC_FUNCTION_CODE = 82.
The full list of statements is in section "Function and Function_Code Values".
DYNAMIC_FUNCTION_CODE INTEGER
A number that shows the SQL statement, associated with DYNAMIC_FUNCTION
-- for a statement that's done dynamically.
Otherwise DYNAMIC_FUNCTION_CODE is 0.
If the last statement was EXECUTE stmt1 and the previous PREPARE
was of a REPLACE statement, then COMMAND_FUNCTION = 'EXECUTE',
COMMAND_FUNCTION_CODE = 44, DYNAMIC_FUNCTION = '_REPLACE',
DYNAMIC_FUNCTION_CODE = -38.
The full list of statements is in section "Function and Function_Code Values".
ROW_COUNT 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 I mean "don't count changes that happen
for triggers or foreign-key cascading".
This number is often the same as what one would get with
the ROW_COUNT() function, but the ROW_COUNT() function does not
include rows that were updated to the same value as before.
For SELECT I recommend that ROW_COUNT be the number of rows
retrieved, but if the implementor says it will be -1, okay.
TRANSACTIONS_COMMITTED INTEGER
A number that shows how many transactions were committed since the
last time the diagnostics area was cleared.
(The standard document says: "TRANSACTIONS_COMMITTED indicates the
number of SQL-transactions that were committed during the invocation
of an external routine.")
The value is always 0.
TRANSACTIONS_ROLLED_BACK INTEGER
A number that shows how many transactions were rolled back since the
last time the diagnostics area was cleared.
(The Standard says: "TRANSACTIONS_ROLLED_BACK indicates the number of
SQL-transactions that were rolled back during the invocation of an
external routine.")
The value is always 0.
TRANSACTION_ACTIVE INTEGER
A number that equals 1 if a transaction is currently active, or equals
0 if a transaction is not currently active.
We will leave it up to the implementor -- if it's easiest to return
0 every time, then do so.
Function and Function_Code values
---------------------------------
This table shows the possible values for the statement information items
COMMAND_FUNCTION, COMMAND_FUNCTION_CODE, DYNAMIC_FUNCTION, and
DYNAMIC_FUNCTION_CODE.
The "Type" column has "Standard" if the statement is in the Standard but
not in MySQL, "Both" if it's in the standard and MySQL supports it,
"MySQL" for a statement that is MySQL only.
The "Code" column is COMMAND_FUNCTION_CODE / DYNAMIC_FUNCTION_CODE
from the SQL:2008 table (in which case it's always a positive integer),
or is '?'. The value '?' appears when Type = MySQL, and always
represents the negation of a constant taken from sql/sql_lex.h
For standard statements, values of the other columns come from the
SQL:2008 table "SQL-statement codes".
For MySQL statements, values of the other columns are arbitrary, or
are the same as values for other DBMSs (in cases where MySQL's statement
is very similar to some other DBMS's non-standard statement).
The table is in alphabetic order by COMMAND_FUNCTION / DYNAMIC_FUNCTION.
Type CODE COMMAND_FUNCTION/ SQL STATEMENT EXECUTED/
DYNAMIC_FUNCTION SQL STATEMENT DYNAMICALLY
PREPARED/EXECUTED
-------- ---- ----------------- -------------------------
Both 0 A zero-length string Any unrecognized statement
Standard 1 ALLOCATE CURSOR <allocate cursor statement>
Standard 2 ALLOCATE DESCRIPTOR <allocate descriptor statement>
Standard 3 ALTER DOMAIN <alter domain statement>
Both 17 ALTER ROUTINE <alter routine statement>
Standard 134 ALTER SEQUENCE <alter sequence generator statement>
Both 4 ALTER TABLE <alter table statement>
Standard 127 ALTER TRANSFORM <alter transform statement>
Standard 60 ALTER TYPE <alter type statement>
Both 5 ASSIGNMENT <assignment statement>
Both 12 BEGIN END <compound statement>
Both 7 CALL <call statement>
Standard 86 CASE <case statement>
Both 9 CLOSE CURSOR <close statement>
Both 11 COMMIT WORK <commit statement>
Standard 13 CONNECT <connect statement>
Standard 6 CREATE ASSERTION <assertion definition>
Standard 52 CREATE CAST <user-defined cast definition>
Standard 8 CREATE CHARACTER SET <character set definition>
Standard 10 CREATE COLLATION <collation definition>
Standard 23 CREATE DOMAIN <domain definition>
Standard 51 CREATE MODULE <SQL-server module definition>
Standard 114 CREATE ORDERING <user-defined ordering definition>
Standard 61 CREATE ROLE <role definition>
Both 14 CREATE ROUTINE <schema routine>
Both 64 CREATE SCHEMA <schema definition>
Standard 133 CREATE SEQUENCE <sequence generator definition>
Both 77 CREATE TABLE <table definition>
Standard 117 CREATE TRANSFORM <transform definition>
Standard 79 CREATE TRANSLATION <transliteration definition>
Both 80 CREATE TRIGGER <trigger definition>
Standard 83 CREATE TYPE <user-defined type definition>
Both 84 CREATE VIEW <view definition>
Standard 15 DEALLOCATE DESCRIPTOR <deallocate descriptor statement>
Both 16 DEALLOCATE PREPARE <deallocate prepared statement>
Standard 18 DELETE CURSOR <delete statement: positioned>
Both 19 DELETE WHERE <delete statement: searched>
Standard 20 DESCRIBE <describe statement>
Standard 22 DISCONNECT <disconnect statement>
Standard 24 DROP ASSERTION <drop assertion statement>
Standard 78 DROP CAST <drop user-defined cast statement>
Standard 25 DROP CHARACTER SET <drop character set statement>
Standard 26 DROP COLLATION <drop collation statement>
Standard 27 DROP DOMAIN <drop domain statement>
Standard 28 DROP MODULE <drop module statement>
Standard 115 DROP ORDERING <drop user-defined ordering statement>
Standard 29 DROP ROLE <drop role statement>
Both 30 DROP ROUTINE <drop routine statement>
Both 31 DROP SCHEMA <drop schema statement>
Standard 135 DROP SEQUENCE <drop sequence generator statement>
Both 32 DROP TABLE <drop table statement>
Standard 116 DROP TRANSFORM <drop transform statement>
Standard 33 DROP TRANSLATION <drop transliteration statement>
Both 34 DROP TRIGGER <drop trigger statement>
Standard 35 DROP TYPE <drop data type statement>
Both 36 DROP VIEW <drop view statement>
Standard 37 DYNAMIC CLOSE <dynamic close statement>
Standard 38 DYNAMIC DELETE CURSOR <dynamic delete statement: positioned>
Both 39 DYNAMIC FETCH <dynamic fetch statement>
Both 40 DYNAMIC OPEN <dynamic open statement>
Standard 42 DYNAMIC UPDATE CURSOR <dynamic update statement: positioned>
Both 43 EXECUTE IMMEDIATE <execute immediate statement>
Both 44 EXECUTE <execute statement>
Both 45 FETCH <fetch statement>
Standard 46 FOR <for statement>
Standard 98 FREE LOCATOR <free locator statement>
Standard 47 GET DESCRIPTOR <get descriptor statement>
Standard 99 HOLD LOCATOR <hold locator statement>
Both 48 GRANT <grant privilege statement>
Standard 49 GRANT ROLE <grant role statement>
Both 88 IF <if statement>
Both 50 INSERT <insert statement>
Both 102 ITERATE <iterate statement>
Both 89 LEAVE <leave statement>
Both 90 LOOP <loop statement>
Standard 128 MERGE <merge statement>
Both 53 OPEN <open statement>
Standard 54 PREPARABLE DYNAMIC DELETE CURSOR <preparable dynamic delete
statement: positioned>
Standard 55 PREPARABLE DYNAMIC UPDATE CURSOR <preparable dynamic update
statement: positioned>
Both 56 PREPARE <prepare statement>
Both 57 RELEASE SAVEPOINT <release savepoint statement>
Both 95 REPEAT <repeat statement>
Both 91 RESIGNAL <resignal statement>
Both 58 RETURN <return statement>
Both 59 REVOKE <revoke privilege statement>
Standard 129 REVOKE ROLE <revoke role statement>
Both 62 ROLLBACK WORK <rollback statement>
Both 63 SAVEPOINT <savepoint statement>
Both 21 SELECT <direct select statement: multiple rows>
Both 41 SELECT <dynamic single row select statement>
Both 65 SELECT <select statement: single row>
Both 85 SELECT CURSOR <dynamic select statement>
Both 66 SET CATALOG <set catalog statement>
Standard 136 SET COLLATION <set session collation statement>
Standard 67 SET CONNECTION <set connection statement>
Standard 68 SET CONSTRAINT <set constraints mode statement>
Standard 70 SET DESCRIPTOR <set descriptor statement>
Both 72 SET NAMES <set names statement>
Standard 69 SET PATH <set path statement>
Standard 73 SET ROLE <set role statement>
Both 74 SET SCHEMA <set schema statement>
Both 76 SET SESSION AUTHORIZATION <set session user identifier statement>
Standard 109 SET SESSION CHARACTERISTICS <set session characteristics statement>
Standard 71 SET TIME ZONE <set local time zone statement>
Both 75 SET TRANSACTION <set transaction statement>
Standard 118 SET TRANSFORM GROUP <set transform group statement>
Both 92 SIGNAL <signal statement>
Standard 111 START TRANSACTION <start transaction statement>
Both 139 TRUNCATE TABLE <truncate table statement>
Standard 81 UPDATE CURSOR <update statement: positioned>
Both 82 UPDATE WHERE <update statement: searched>
Both 97 WHILE <while statement>
MySQL ? _SQLCOM_ALTER_DB
MySQL ? _SQLCOM_ALTER_DB_UPGRADE
MySQL ? _SQLCOM_ALTER_EVENT
MySQL ? _SQLCOM_ALTER_FUNCTION
MySQL ? _SQLCOM_ALTER_PROCEDURE
MySQL ? _SQLCOM_ALTER_SERVER
MySQL ? _SQLCOM_ALTER_TABLE
MySQL ? _SQLCOM_ALTER_TABLESPACE
MySQL ? _SQLCOM_ANALYZE
MySQL ? _SQLCOM_ASSIGN_TO_KEYCACHE
MySQL ? _SQLCOM_BACKUP
MySQL ? _SQLCOM_BACKUP_TEST
MySQL ? _SQLCOM_BEGIN
MySQL ? _SQLCOM_BINLOG_BASE64_EVENT
MySQL ? _SQLCOM_CALL
MySQL ? _SQLCOM_CHANGE_MASTER
MySQL ? _SQLCOM_CHANGE_DB
MySQL ? _SQLCOM_CHECK
MySQL ? _SQLCOM_CHECKSUM
MySQL ? _SQLCOM_COMMIT
MySQL ? _SQLCOM_CREATE_DB
MySQL ? _SQLCOM_CREATE_EVENT
MySQL ? _SQLCOM_CREATE_FUNCTION
MySQL ? _SQLCOM_CREATE_INDEX
MySQL ? _SQLCOM_CREATE_PROCEDURE
MySQL ? _SQLCOM_CREATE_SERVER
MySQL ? _SQLCOM_CREATE_SPFUNCTION
MySQL ? _SQLCOM_CREATE_TABLE
MySQL ? _SQLCOM_CREATE_TRIGGER
MySQL ? _SQLCOM_CREATE_VIEW
MySQL ? _SQLCOM_CREATE_USER
MySQL ? _SQLCOM_DEALLOCATE_PREPARE
MySQL ? _SQLCOM_DELETE
MySQL ? _SQLCOM_DELETE_MULTI
MySQL ? _SQLCOM_DO
MySQL ? _SQLCOM_DROP_DB
MySQL ? _SQLCOM_DROP_EVENT
MySQL ? _SQLCOM_DROP_FUNCTION
MySQL ? _SQLCOM_DROP_INDEX
MySQL ? _SQLCOM_DROP_PROCEDURE
MySQL ? _SQLCOM_DROP_SERVER
MySQL ? _SQLCOM_DROP_TABLE
MySQL ? _SQLCOM_DROP_TRIGGER
MySQL ? _SQLCOM_DROP_USER
MySQL ? _SQLCOM_DROP_VIEW
MySQL ? _SQLCOM_EMPTY_QUERY
MySQL ? _SQLCOM_EXECUTE
MySQL ? _SQLCOM_FLUSH
MySQL ? _SQLCOM_GET_DIAGNOSTICS
MySQL ? _SQLCOM_GRANT
MySQL ? _SQLCOM_HA_OPEN
MySQL ? _SQLCOM_HA_READ
MySQL ? _SQLCOM_HA_CLOSE
MySQL ? _SQLCOM_HELP
MySQL ? _SQLCOM_INSERT
MySQL ? _SQLCOM_INSERT_SELECT
MySQL ? _SQLCOM_INSTALL_PLUGIN
MySQL ? _SQLCOM_KILL
MySQL ? _SQLCOM_LOAD
MySQL ? _SQLCOM_LOCK_TABLES
MySQL ? _SQLCOM_OPTIMIZE
MySQL ? _SQLCOM_PRELOAD_KEYS
MySQL ? _SQLCOM_PREPARE
MySQL ? _SQLCOM_PURGE
MySQL ? _SQLCOM_PURGE_BEFORE
MySQL ? _SQLCOM_RELEASE_SAVEPOINT
MySQL ? _SQLCOM_RENAME_TABLE
MySQL ? _SQLCOM_RENAME_USER
MySQL ? _SQLCOM_REPAIR
MySQL ? _SQLCOM_REPLACE
MySQL ? _SQLCOM_REPLACE_SELECT
MySQL ? _SQLCOM_RESET
MySQL ? _SQLCOM_RESIGNAL
MySQL ? _SQLCOM_RESTORE
MySQL ? _SQLCOM_REVOKE
MySQL ? _SQLCOM_REVOKE_ALL
MySQL ? _SQLCOM_ROLLBACK
MySQL ? _SQLCOM_ROLLBACK_TO_SAVEPOINT
MySQL ? _SQLCOM_SAVEPOINT
MySQL ? _SQLCOM_SELECT
MySQL ? _SQLCOM_SET_OPTION
MySQL ? _SQLCOM_SLAVE_START
MySQL ? _SQLCOM_SLAVE_STOP
MySQL ? _SQLCOM_SHOW_ARCHIVE
MySQL ? _SQLCOM_SHOW_AUTHORS
MySQL ? _SQLCOM_SHOW_BINLOGS
MySQL ? _SQLCOM_SHOW_BINLOG_EVENTS
MySQL ? _SQLCOM_SHOW_CHARSETS
MySQL ? _SQLCOM_SHOW_COLLATIONS
MySQL ? _SQLCOM_SHOW_COLUMN_TYPES
MySQL ? _SQLCOM_SHOW_CONTRIBUTORS
MySQL ? _SQLCOM_SHOW_CREATE
MySQL ? _SQLCOM_SHOW_CREATE_DB
MySQL ? _SQLCOM_SHOW_CREATE_EVENT
MySQL ? _SQLCOM_SHOW_CREATE_FUNC
MySQL ? _SQLCOM_SHOW_CREATE_PROC
MySQL ? _SQLCOM_SHOW_CREATE_TRIGGER
MySQL ? _SQLCOM_SHOW_DATABASES
MySQL ? _SQLCOM_SHOW_ENGINE_LOGS
MySQL ? _SQLCOM_SHOW_ENGINE_MUTEX
MySQL ? _SQLCOM_SHOW_ENGINE_STATUS
MySQL ? _SQLCOM_SHOW_ERRORS
MySQL ? _SQLCOM_SHOW_EVENTS
MySQL ? _SQLCOM_SHOW_FIELDS
MySQL ? _SQLCOM_SHOW_FUNC_CODE
MySQL ? _SQLCOM_SHOW_GRANTS
MySQL ? _SQLCOM_SHOW_KEYS
MySQL ? _SQLCOM_SHOW_MASTER_STAT
MySQL ? _SQLCOM_SHOW_NEW_MASTER
MySQL ? _SQLCOM_SHOW_OPEN_TABLES
MySQL ? _SQLCOM_SHOW_PLUGINS
MySQL ? _SQLCOM_SHOW_PRIVILEGES
MySQL ? _SQLCOM_SHOW_PROCESSLIST
MySQL ? _SQLCOM_SHOW_PROC_CODE
MySQL ? _SQLCOM_SHOW_PROFILE
MySQL ? _SQLCOM_SHOW_PROFILES
MySQL ? _SQLCOM_SHOW_SLAVE_HOSTS
MySQL ? _SQLCOM_SHOW_SLAVE_STAT
MySQL ? _SQLCOM_SHOW_STATUS
MySQL ? _SQLCOM_SHOW_STATUS_FUNC
MySQL ? _SQLCOM_SHOW_STATUS_PROC
MySQL ? _SQLCOM_SHOW_STORAGE_ENGINES
MySQL ? _SQLCOM_SHOW_TABLES
MySQL ? _SQLCOM_SHOW_TABLE_STATUS
MySQL ? _SQLCOM_SHOW_TRIGGERS
MySQL ? _SQLCOM_SHOW_VARIABLES
MySQL ? _SQLCOM_SHOW_WARNS
MySQL ? _SQLCOM_SIGNAL
MySQL ? _SQLCOM_TRUNCATE
MySQL ? _SQLCOM_UNINSTALL_PLUGIN
MySQL ? _SQLCOM_UNLOCK_TABLES
MySQL ? _SQLCOM_UPDATE
MySQL ? _SQLCOM_UPDATE_MULTI
MySQL ? _SQLCOM_XA_COMMIT
MySQL ? _SQLCOM_XA_END
MySQL ? _SQLCOM_XA_PREPARE
MySQL ? _SQLCOM_XA_RECOVER
MySQL ? _SQLCOM_XA_ROLLBACK
MySQL ? _SQLCOM_XA_START
The implementor has a choice for entries like TRUNCATE,
which appear under 'Both' and under 'MySQL' in the table.
Either:
(choice 1) Set COMMAND_FUNCTION_CODE = SQLCOM_TRUNCATE * -1
Set DYNAMIC_FUNCTION_CODE = SQLCOM_TRUNCATE * -1
Set COMMAND_FUNCTION = '_SQLCOM_TRUNCATE'
Set DYNAMIC_FUNCTION = '_SQLCOM_TRUNCATE'
Or:
(choice 2) Set COMMAND_FUNCTION_CODE = 139
Set DYNAMIC_FUNCTION_CODE = 139
Set COMMAND_FUNCTION = 'TRUNCATE TABLE'
Set DYNAMIC_FUNCTION = 'TRUNCATE TABLE'
The implementor may choose differently for different item.
We recognize that values for 'MySQL' items may change.
<condition information list>
-----------------------------
If the statement is:
GET DIAGNOSTICS <target> = <statement information item name> [ , ... ]
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 rest of this section is a description of each condition information item.
CATALOG_NAME VARCHAR(64)
A string that shows the current catalog.
Always a zero-length string.
(It could be NULL, but GET DIAGNOSTICS usually expects no nulls.)
SCHEMA_NAME VARCHAR(64)
A string that shows the current schema.
Always a zero-length string.
CONDITION_NUMBER INTEGER
A number that is the same as <condition number>.
If the GET DIAGNOSTICS statement is "GET DIAGNOSTICS
CONDITION 44 ...", then CONDITION_NUMBER = 44.
RETURNED_SQLSTATE 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 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 VARCHAR(64)
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
CLASS_ORIGIN = '' i.e. a zero-length string
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 VARCHAR(64)
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
SUBCLASS_ORIGIN = ''
Else:
If RETURENED_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 VARCHAR(64)
A string that shows the cursor name.
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
CURSOR_NAME = ''
Else:
CURSOR_NAME = ''.
Alternatively, if the implementor can figure out the cursor name ...
If RETURNED_SQLSTATE='01001' (cursor operation conflict)
Or RETURNED_SQLSTATE='24000' (invalid cursor state)
CURSOR_NAME = name of cursor that's causing the problem
else
CURSOR_NAME = ''.
CONDITION_IDENTIFIER VARCHAR(64)
A string that shows the condition in a DECLARE ... CONDITION.
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
If statement was SIGNAL | RESIGNAL <condition name>:
CONDITION_IDENTIFIER = <condition name> from SIGNAL|RESIGNAL
Else:
CONDITION_IDENTIFIER = ''
Else:
CONDITION_IDENTIFIER = ''
For example:
CREATE PROCEDURE p ()
BEGIN
DECLARE x CONDITION FOR SQLEXCEPTION
GET DIAGNOSTCS CONDIION 1 @x=CONDITION_IDENTIFIER;
SIGNAL SQLSTATE '45000' /* unhandled user-defined exception */;
END
then @x will be 'x'.
CONSTRAINT_CATALOG + CONSTRAINT_SCHEMA + CONSTRAINT_NAME (all VARCHAR(64))
+ CATALOG_NAME + SCHEMA_NAME + TABLE_NAME + COLUMN_NAME (all VARCHAR(64))
If COMMAND_FUNCTION or DYNAMIC_FUNCTION = 'SIGNAL' or 'RESIGNAL':
CONSTRAINT_NAME etc. = ''
Else:
If RETURNED_SQLSTATE='23000' (integrity constraint violation)
Or RETURNED_SQLSTATE='40002' (transaction rollback - integrity constraint
violation)
Or RETURNED_SQLSTATE='27000' (triggered data change violation -- caused by a
violation of a primary/foreign key constraint)
then
CONSTRAINT_NAME etc.= name of violated constraint.
TABLE_NAME etc. = name of table that constraint is defined on.
/* There are further rules for standard temporary tables, but
MySQL doesn't support standard temporary tables. */
If RETURNED_SQLSTATE='09000' (triggered action exception)
Or RETURNED_SQLSTATE='40004' (transaction rollback - triggered action exception)
Or RETURNED_SQLSTATE='27000' (triggered dta change violation -- caused by an
activation of a trigger)
then
TRIGGER_NAME etc. = name of activated trigger
TABLE_NAME etc. = name of table on which activated trigger is defined
If RETURNED_SQLSTATE='44000' (with check option violation)
then
TABLE_NAME etc. = view that causes the check option violation
If RETURNED_SQLSTATE='42000' (syntax error or access rule violation -- caused by
reference to a specific table)
then
TABLE_NAME etc. = name of table that is inaccessible
COLUMN_NAME = name of column that is inaccessible, or ''
If RETURNED_SQLSTATE<>'42000' (not syntax error or access rule violation)
If CATALOG_NAME + SCHEMA_NAME + TABLE_NAME + COLUMN_NAME = a column for which
user has no privilege
then
COLUMN_NAME = ''
(that is, whatever value was assigned to COLUMN_NAME by the
previous rules, is now changed to a zero-length string; this ensures that
the user does not get to see information on an object for which he/she
has no privileges).
If RETURNED_SQLSTATE='42000' (syntax error or access rule violation)
If CATALOG_NAME + SCHEMA_NAME + TABLE_NAME = a table for which the user has no
privilege
then
TABLE_NAME etc. = ''
(that is, whatever values were assigned to these three
variables by the previous rules, are now changed to zero-length strings).
ROUTINE_CATALOG + ROUTINE_SCHEMA + ROUTINE_NAME (all VARCHAR(64))
If RETURNED_SQLSTATE='22003' (data exception - numeric value out of range)
Or RETURNED_SQLSTATE='22001' (data exception - string data, right truncation)
Or RETURNED_SQLSTATE='22018' (data exception - invalid character value for cast)
Or RETURNED_SQLSTATE='22015' (data exception - interval field overflow)
Or RETURNED_SQLSTATE='23000' (integrity constraint violation)
Or RETURNED_SQLSTATE='01004' (string data, right truncation)
then
if (condition was raised during an attempt by a stored procedure to assign a
value to an SQL parameter)
then
ROUTINE_CATALOG + ROUTINE_SCHEMA + ROUTINE_NAME + SPECIFIC_NAME = routine that
caused the
condition
PARAMETER_MODE = <parameter mode> value (IN, OUT, INOUT) of the parameter
PARAMATER_ORDINAL_POSITION = the parameter's ordinal position e.g. 1 for
parameter#1
PARAMETER_NAME = name of parameter (for us, this is always a zero-length string)
If RETURNED_SQLSTATE='39000' (external routine invocation exception)
Or RETURNED_SQLSTATE='38000' (external routine exception)
Or RETURNED_SQLSTATE='01000' (warning)
then
ROUTINE_CATALOG + ROUTINE_SCHEMA + ROUTINE_NAME + SPECIFIC_NAME = routine that
caused the
condition
PARAMETER_MODE = <parameter mode> value (IN, OUT, INOUT) of the parameter
PARAMATER_ORDINAL_POSITION = the parameter's ordinal position e.g. 1 for
parameter#1
PARAMETER_NAME = name of parameter (for us, this is always '')
MESSAGE_TEXT 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 <table
name> doesn't exist" when RETURNED_SQLSTATE is 42S02).
MESSAGE_LENGTH INTEGER
A number that shows the length in characters of the MESSAGE_TEXT value.
Always MESSAGE_LENGTH = CHAR_LENGTH(MESSAGE_TEXT).
MESSAGE_OCTET_LENGTH INTEGER
A number that shows the length in bytes of the MESSAGE_TEXT value.
Always MESSAGE_OCTET_LENGTH = OCTET_LENGTH(MESSAGE_TEXT).
CONNECTION_NAME + SERVER_NAME all VARCHAR(64)
If (statement is an <SQL connection statement> i.e. CONNECT TO, SET CONNECTION,
DISCONNECT)
then
/* Well, this can't happen, since MySQL doesn't support such statements */
else
CONNECTION_NAME = connection_name /* no such thing. maybe connection number? */
SERVER_NAME = server name /* no such thing. maybe server id? */
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, 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'.
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.
Other statement information items or condition information items
----------------------------------------------------------------
There has been discussion about adding more items, e.g.
last_insert_id(). Some decisions are unmade.
There could be a field for "variable name". But we'll decide
(as a MySQL extension) that if there's no column involved but
there's a variable name, then COLUMN_NAME = variable name.
GET DIAGNOSTICS ALL
-------------------
This is non-core SQL:2008 feature F123 "All diagnostics".
GET DIAGNOSTICS <target> = ALL;
or
GET DIAGNOSTICS <target> = ALL STATEMENT;
or
GET DIAGNOSTICS <target> = ALL CONDITION [ <condition number> ];
The <target> is a character string.
It gets a concatenation of all the items, separated by semicolons.
We will not support this feature.
Comparing GET DIAGNOSTICS with SELECT FROM DIAGNOSTICS_AREAS
------------------------------------------------------------
Suppose there was a table named DIAGNOSTICS_AREAS:
CREATE TABLE DIAGNOSTICS_AREAS
(DIAGNOSTICS_AREA_NUMBER INT,
CONDITION_NUMBER INT,
RETURNED_SQLSTATE VARCHAR(5),
DYNAMIC_FUNCION_CODE INT,
etc.);
Suppose this table was filled with condition information at the
end of each statement.
Then a person who wanted to get the value of RETURNED_SQLSTATE
for condition #3 in diagnostics area #2 would be able to say:
SELECT RETURNED_SQLSTATE INTO @x
FROM DIAGNOSTICS_AREAS
WHERE DIAGNOSTICS_AREA_NUMBER = 2 AND CONDITION_NUMBER = 3;
instead of
GET STACKED DIAGNOSTICS CONDITION 3 @x = RETURNED_SQLSTATE;
The SELECT would be better, because there would be no special
syntax and less to explain. Ignore the fact that the SELECT
itself clears the diagnostics area, or pretend it's pushed.
Peter wants to see something like this in a table which is
visible to all, that is, connection #2 should be able to
select connection #1's diagnostics, if privileges are okay.
Peter thinks this means that the diagnostics must be in a
publicly-readable section of a THD which can be read with
SELECT as if it's an INFORMATION_SCHEMA or PERFORMANCE_SCHEMA view.
Then we'd tell everyone: here's GET DIAGNOSTICS as in the
standard, but you don't really need it, you can use SELECT.
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
Other DBMSs
-----------
Oracle has SQLERRM and SQLCODE, that's about it.
SQL Server is similarly impoverished.
DB2 has a GET DIAGNOSTICS statement which approximately follows
the standard. One difference is:
DB2 syntax: GET DIAGNOSTICS EXCEPTION n ...
Standard syntax: GET DIAGNOSTICS CONDITION n ...
Trudy proposed that we allow EXCEPTION as a synonym for CONDITION.
Peter doesn't agree.
What if MySQL can't support a diagnostics stack?
------------------------------------------------
I've assumed that there can be multiple diagnostics areas.
That is, MySQL really supports "push" and "pop" on a diagnostics area stack.
If that proves to be too hard, well, the implementor had better say so.
With a single diagnostics area, we'd 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 <condition number>
----------------------------------
A <condition number> cannot be any expression that evaluates to an
integer. It must be a literal.
If <condition number> 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".
(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 <condition number> in GET DIAGNOSTICS
asks for information on three or more conditions, the statement fails.)
If <condition number> has the value 1 (one), then 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 one condition, 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 <condition number> 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. I
suggest that the first condition information returned should always
relate to the SQLSTATE value actually returned when the previous SQL
statement was executed.
Where can GET DIAGNOSTICS occur?
--------------------------------
GET DIAGNOSTICS can be embedded in an SQL routine or dynamic
compound statement. It is not an executable statement and cannot be
dynamically prepared.
Peter would like to see GET DIAGNOSTICS anywhere, that is, outside
BEGIN ... END compound statements. That's up to the implementor.
What if GET DIAGNOSTICS itself causes an error?
-----------------------------------------------
By restricting so that <condition number> must be a literal,
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, well,
too bad. SQLSTATE will change, but nothing else, so the
diagnostics area remains intact.
Privileges
----------
No special privileges are required.
We do not allow assignment to @@variables, and so we do not need
to worry about the SUPER privilege for @@global.variables.
If we implement SELECT FROM DIAGNOSTICS_AREAS, then a SUPER
privilege is necessary for seeing areas of other connections.
Sometimes users lack a privilege which would allow them to know
of an object's existence. If that happens with (for example)
SHOW CREATE VIEW or EXPLAIN or SELECT ... FROM INFORMATION_SCHEMA.VIEWS,
we can cause an error or change the information to NULL.
We'll have to consider that here, because some of the diagnostics
area fields may contain object names.
Reserved Words
--------------
GET
In standard SQL, CURRENT is also reserved.
In MySQL, CURRENT will not be reserved.
CONDITION is already a reserved word.
Examples
--------
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 @more = MORE, 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: 'test'.
DELIMITER //
CREATE PROCEDURE p ()
BEGIN
DECLARE v CHAR(1);
CREATE TABLE IF NOT EXISTS already_existing_table (s1 INT);
GET DIAGNOSTICS CONDITION 1 v = DYNAMIC_FUNCTION;
SELECT v;
end//
CALL p()//
Result: '_'. The _CREATE string has been truncated because V's
length is only 1. This does not cause a warning, because GET
DIAGNOSTICS itself has nowhere to put warnings for itself.
(Originally the specification was:
"The <target> 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 SQLSTATE CHAR(5);
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.
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, 4 rows affected (0.00 sec)
Rows matched: 5 Changed: 4 Warnings: 0
mysql> SELECT @x;
+------+
| @x |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
The noticeable things for this example are:
* Ater GET DIAGNOSTICS you see "4 rows affected" etc.
That happens because the protocol fields are what's
in the current diagnostics area.
* In the end, @x = 5, not 4. That happens because
ROW_COUNT is 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.
Possibly statement replication is difficult and we should log
"SET @x='column1'" statement rather than a 'GET DIAGNOSTICS ... @x=COLUMN_NAME".
References
----------
DB2 GET DIAGNOSTICS statement
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0005647.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