WL#2111: Stored Procedures: Implement GET DIAGNOSTICS
Affects: Server-5.6
—
Status: Complete
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.
WL#751: Error message construction
WL#2110: Stored Procedures: Implement SIGNAL
WL#2265: Stored Procedures: Implement RESIGNAL
WL#2110: Stored Procedures: Implement SIGNAL
WL#2265: Stored Procedures: Implement RESIGNAL
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, 2024, Oracle Corporation and/or its affiliates. All rights reserved.