WL#5928: Most statements should clear the diagnostic area
Affects: Server-5.7
—
Status: Complete
According to the SQL standard, the first diagnostics area should be emptied at the beginning of the execution of anythat is not an . Currently, MySQL clears the diagnostic area as follows (start quote): * When the server starts executing a statement after parsing it, it clears the diagnostics area for nondiagnostic statements that use tables. Diagnostic statements are SHOW WARNINGS, SHOW ERRORS, and GET DIAGNOSTICS. * If a statement raises a condition, the diagnostics area is cleared of conditions that belong to earlier statements. The exception is that conditions raised by GET DIAGNOSTICS and RESIGNAL are added to the diagnostics area without clearing it. Thus, even a statement that does not normally clear the diagnostics area when it begins executing clears it if the statement raises a condition. Quoted from http://dev.mysql.com/doc/refman/5.6/en/diagnostics-area.html#diagnostics-area- populating This worklog is about changing MySQL to follow the SQL standard with respect to clearing the diagnostic area. This means that: 1) We will also clear the diagnostic area for non-diagnostic statments that does not use tables. 2) Clearing the diagnostic area should be done at one well-defined point during statement execution. User Documentation ================== http://dev.mysql.com/doc/refman/5.7/en/show-warnings.html http://dev.mysql.com/doc/refman/5.7/en/diagnostics-area.html#diagnostics-area- populating http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html
REQUIREMENTS
Func-Req (1.1)
If an exception condition is raised during parsing, it must become the sole
contents of the diagnostics area, and an error status must be sent.
Func-Req (1.2)
If parsing completes successfully and a diagnostics statement is detected, it
shall return data of the diagnostics area describing the command executed in the
previous cycle.
Func-Req (1.3)
In all other cases, the diagnostics area shall be cleared, and the command
executed.
Func-Req (1.4)
During TRIGGER excution, Warnings shall be suppressed, whereas exceptions are
thrown (SQL 2008, Foundation, 15.19 k).
Func-Req (1.5)
After the execution of a STORED PROCEDURE, the diagnostics area shall reflect
the execution of the last (non-diagnostics) procedure statement (e.g. the sole
statement in the routine body, the last statement before the END of the outmost
compound statement, etc.).
Func-Req (1.6)
After the execution of a STORED FUNCTION, the diagnostics area shall reflect the
execution of the last (non-diagnostics) procedure statement (the RETURN
statement).
Func-Req (1.6.1)
The caller (e.g. SELECT ...) of a STORED FUNCTION may however aggregate the
conditions from all stored functions it calls: if f1() throws a warning, SELECT
f1(),f1(),f1(); SHOW WARNINGS; will show a list of three warnings. This is
implementation-defined.
Func-Req (1.7)
If the result set for a SELECT can be returned from the QUERY CACHE, it must
still clear the diagnostics area. The diagnostics area will not be populated
with any conditions generated in the original execution.
DISCUSSION
[See below in Low level design for relevant excerpts from the SQL standard
and nomenclature.]
The MySQL Reference Manual says for SHOW WARNINGS:
"Statements that do not use tables and do not generate
messages have no effect on the message list."
http://dev.mysql.com/doc/refman/5.6/en/show-warnings.html
And it says for the error_count system variable:
"The number of errors that resulted from the last statement that generated
messages."
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
And it says for the warning_count system variable:
"The number of errors, warnings, and notes that resulted from the last statement
that generated messages."
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
Example:
mysql> DROP TABLE no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> CREATE EVENT e ON SCHEDULE EVERY 1 SECOND DO DELETE FROM t;
Query OK, 0 rows affected (0.35 sec)
mysql> SHOW ERRORS;
+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Error | 1051 | Unknown table 'test.no_such_table' |
+-------+------+------------------------------------+
1 row in set (0.00 sec)
In this example SHOW ERRORS is showing the results of
the DROP TABLE statement, not the CREATE EVENT statement,
because the CREATE EVENT statement was successful and
did not "use" a table.
Terminology
-----------
The word "used" in the quote from the manual means "actually
used" not "effectively used" There's effective use of tables
when one needs a privilege, or drops a database, or repeats a
cached query, or refers to a table in CREATE EVENT, etc. That
doesn't matter -- it's not actual use.)
The "message list" is sometimes called the "warning stack".
It is analogous to one diagnostics stack in standard SQL
but its content and behaviour are not as in standard SQL.
What the change would be
------------------------
Generally speaking, after the change, all statements that
can say "okay" will say "okay". This means the message
list will be cleared, SHOW ERRORS and SHOW WARNINGS would
return an empty set, and GET DIAGNOSTICS would show a
completion condition.
Actually, not quite "all" statements. We exclude:
* Statements that aren't executed. Our current examples are
DECLARE condition
DECLARE handler
(Not DECLARE variable, which is capable of causing errors)
* Diagnostics statements. Our current examples are
SHOW ERRORS
SHOW WARNINGS
GET DIAGNOSTICS
SHOW COUNT(*) ERRORS
SHOW COUNT(*) WARNINGS
* SELECT @@error_count | @@warning_count will be supported for
the time being in that SELECT @@warning_count will return the
same result as SHOW COUNT(*) WARNINGS, and SELECT @@error_count
will reflect SHOW COUNT(*) ERRORS. However, as these are, in a
manner of speaking, "diagnostics variables" but SELECT etc. are
not diagnostics statements, SHOW COUNT(*) ... are repeatable,
whereas SELECT @@warning_count | @@error_count are NOT repeatable --
they will give correct results when issued as the first non-diagnostics
statement after the statement whose diagnostics we're interested in;
it will however then clear the diagnostics area (and populate it with
any conditions that may arise during its own execution). At this point,
any diagnostics statements or non-diagnostics statements querying these
"diagnostics variables" will pertain to the results of the SELECT:
DROP TABLE non_existent;
SHOW WARNINGS;
# will give warnings
SHOW WARNINGS;
# will still give the warnings about non-existent table
GET DIAGNOSTICS @n = number;
GET DIAGNOSTICS ...
SELECT @@warning_count;
# this will still render "1" ("no such table"), but the DA will be
# cleared due to SELECT not being a diagnostics statement. If any
# conditions are raised during this SELECT, we will now see THOSE
# conditions instead of "no such table", otherwise, an empty list:
SHOW WARNINGS;
@@error_count and @@warning_count are MySQL specific extensions
support for which may be dropped at a later date.
* As a special case, if f() throws one warning, we now support that
SELECT f(), f(), f(); will result in a condition list containing
three warnings: As all these warnings are raised within the
scope of the same statement (e.g. SELECT), they are accumulated.
* The standard requires that diagnostics statements are not preparable
(Foundation 2007, 4.33.7). Trying to prepare GET DIAGNOSTICS ... will
fail (as it already did) with ER_UNSUPPORTED_PS; additionally,
SHOW [COUNT(*)] ERRORS|WARNINGS and use of @@error_count | @@warning_count
are now unsupported in prepared statements.
* As a general rule, if one is interested in the conditions raised by a
statement, one is required to query them right away (preferably using
the GET DIAGNOSTICS interface), as any later statements will clear the
diagnostics area (except in the few cases enumerated above, as per the
standard -- essentially, diagnostics statements, and some statements
that are not executed (i.e. used at define-time of a stored program,
not at runtime)). These rules also hold within compound statements
(whether in handlers or otherwise); a compound statement is not "one
statement" in the sense that any enclosing block would see the
accumulated warnings raised by all statements in the enclosed compound
statement. If a warning is not handled, it will be cleared by the next
non-diagnostics statement in the compound statement; only warnings raised
by the last statement in the block will be seen by the next statement
outside the block. If an error is not handled, execution of the compound
statement will be aborted, and the next statement outside the block will
see the error. Any unhandled error/warning conditions "seen outside the
block" shall fire any appropriate handlers there. Understanding this
behavior is key to correct use of DECLARE HANDLER.
Why keep the behaviour?
-----------------------
The only known reason for keeping the behaviour is the fear
that we'd break an existing application that wants to know
the result from a significant earlier statement.
Why change the behaviour?
-------------------------
Three bug reports have comments mentioning the "use tables
and generate messages" rule:
BUG#35296 CREATE EVENT does not clear warnings.
Reporter: Paul DuBois.
BUG#43012 Bad error message handling in "drop database".
Reporter: Jorgen Loland.
BUG#49634 Cached queries do not clear warnings.
Reporter: Daniel Nichter.
Although they were declared "not a bug" or else not really fixed,
their existence shows that even MySQL employees expect to see
cleared message lists after successful non-message-generating statements.
Also, clearing message lists would be closer to standard SQL.
mysql_error() and mysql_errno()
-------------------------------
The MySQL Reference Manual says for mysql_error() in the API:
"If a function didn't fail, the return value of mysql_error()
may be the previous error or an empty string to indicate no error.
A rule of thumb is that all functions that have to ask the server
for information reset mysql_error() if they succeed."
http://dev.mysql.com/doc/refman/5.6/en/mysql-error.html
In 2003 Georg Richter posted a bug report about this behaviour:
BUG#706 mysql_fetch_row doesn't reset errorcode.
But Monty Widenius declared it "not a bug" and explained
"This is not a bug. The intention is that mysql_error() and
mysql_errno() are only well defined if you get an error for
the previous statement. (A bit like 'errno' in C)."
Peter thought this is a similar case. Davi didn't.
We won't directly do anything about it.
1 DIRECT INVOCATION
1.01 - At the start of interactive processing ("the cycle"), a temporary
diagnostics area is pushed to the stack.
1.02 - Then, the parser is run.
1.03 - Based on whether a diagnostics statement is detected, the parser will
flag for preservation of the previous cycle's diagnostics area, so when a
diagnostics statement is actually executed later, it will have the necessary
data to describe the previous command.
1.04 - After parsing, the parsing-DA is popped.
1.05 - If any conditions are raised during parsing, the main DA shall be cleared
and populated with the conditions raised during parsing. (SQL 2008 Foundation
22.1 5)
1.06 - If any exception conditions were raised during parsing, the cycle ends here.
1.07 - If no conditions were raised during parsing, and the detected statement
is not diagnost, the DA shall be cleared. (SQL 2008 Foundation 22.1 6b v)
1.08 - If no conditions were raised during parsing, and the detected statement
is a diagnostics statement, the DA remains unmodified at this stage, so the
statement has the necessary data to return diagnostics pertaining to the
statement executed in the previous cycle. (SQL 2008 Foundation 23.1 2 a)
1.09 - The statement will be executed, possibly adding new conditions to the
diagnostics area. This specifically means that a non diagnostics statement will
start populating the DA (SQL 2008 Foundation 22.1 10), whereas a diagnostics
statement will not modify the DA describing the previous statement's execution
unless an exception condition is raised while executing the diagnostics
statement. (SQL 2008 Foundation 23.1 3)
1.10 - OK/EOF/Error is sent to the client.
1.11 - The cycle ends.
2 STORED PROGRAMS
2.01 Instructions used in stored programs may fall into one of three classes each:
2.01.1 SP-statements that preserve the DA (requiring for it not be cleared
pre-execution)
2.01.2 SP-statements that require the DA to be cleared pre-execution
2.01.3 SQL-statements that require the DA to be cleared pre-execution, but that
are executed through stages 1.07 - 1.10, so any clearing of the DA may be
deferred to the mechanisms already in place there.
3 QUERY CACHE
3.01 As the query cache bypasses normal parsing/execution (and DA clearing), the
DA needs to be cleared explicitly on cache hits. Once the query cache identifies
the current statement as potentially cacheable (QC is activated, query is a
SELECT, doesn't have SQL_NO_CACHE, etc.), the diagnostics area will therefore be
cleared. This is safe as at this point the statement is known not to be a
diagnostics statement. As the QC has three callers (SP, PS, interactive), the
clearing of the diagnostics area happens in send_result_to_client().
ad 1.03 - flagging diagnostics statements
Parser shall set lex->keep_diagnostics, which shall default to false,
to true for statements which must not clear the DA.
sql/sql_lex.cc: lex_start() shall set the default, lex->keep_diagnostics= false;
sql/sql_lex.h: add bool keep_diagnostics; to
struct LEX: public Query_tables_list, somewhat similar
to bool contains_plaintext_password
sql/sql_yacc.yy: eligible commands set lex->keep_diagnostics= true;
at this point, that's
DECLARE COND, DECLARE HANDLER, GET DIAGNOSTICS, COUNT (*) WARNINGS,
COUNT (*) ERRORS, SHOW WARNINGS, SHOW ERRORS, and CALL.
The inclusion of CALL in this list is pending further updates.
BEGIN...END is part of the sp_block_content rule, which itself gets
wrapped in sp_[un]labeled_block.
TABLE OF CHANGES TO NON-INTERACTIVE STATEMENTS, AND THEIR JUSTIFICATION IN THE
SQL STANDARD
Compound: new behavior: does NOT count as "one statement" in that
the warnings of the individual statements
are not accumulated; each sub statement
of the compound clears the DA in the regular
way; if an error occurs, we either error out
of the compound, in which case the DA is there
for the caller, or we ignore the error and the
DA will be cleared when the next stmt is
executed.
Standard: SQL 2008, Foundation, 4.30
SF: new behavior: after SF, DA will be set with results of last
non diag stmt (as per the standard), i.e. RETURN.
merge conditions from all called SF.
old behavior: suppression not requested by caller, warnings
may or may not survive depending on the vagaries
of the current DA clearing.
where: sp_head.cc: execute_function->execute(thd, TRUE);
Standard: Foundation, 4.30 (DA reflects last stmt)
Standard doesn't seem to forbid adding conditions
of ALL called SF to current caller (e.g. SELECT)
SP: new behavior: DA reflects last (non-diag) stmt only
old behavior: suppression not requested by caller, warnings
may or may not survive depending on the vagaries
of the current DA clearing.
where: sp_head.cc: execute_procedure->execute(thd, TRUE)
Standard: Foundation, 4.30 (DA reflects last stmt)
Trigger: new behavior: warnings are suppressed
old behavior: warnings are suppressed
where: sp_head.cc: execute_trigger->execute(thd, FALSE)
Standard: SQL 2008, Foundation, 15.19 k) - failing
exceptions bubble up
NOMENCLATURE AND REQUIREMENT IN THE SQL STANDARD OF 2008, AND ASSUMPTIONS
PERTAINING THERETO
from SQL Standard, "Foundation"
4.29.2 Status parameters
[ERR]
"If a condition is raised that causes a statement to have no effect other than
that associated with raising the condition (that is, not a completion
condition), then the condition is said to be an 'exception condition' or
'exception.'"
[WARN/OK]
"If a condition is raised that permits a statement to have an effect other than
that associated with raising the condition (corresponding to an SQLSTATE class
value of successful completion, warning, or no data), then the condition is said
to be a 'completion condition.'
[...]
The completion condition 'no data' has special significance and is used to
indicate an empty result."
[NOTE / INFORMATION]
"The completion condition 'successful completion' is defined to indicate a
completion condition that does not correspond to warning or no data. This
includes conditions in which the SQLSTATE subclass provides
implementation-defined information of a non-cautionary nature."
"For the purpose of choosing status parameter values to be returned, exception
conditions for transaction rollback have precedence over exception conditions
for statement failure. Similarly, the completion condition no data has
precedence over the completion condition warning, which has precedence over the
completion condition successful completion. All exception conditions have
precedence over all completion conditions. The values assigned to SQLSTATE shall
obey these precedence requirements."
4.30 Diagnostics area
"The ordering of the information about conditions placed into a diagnostics area
is implementationdependent, except that the first condition area in a
diagnostics area always corresponds to the condition specified by the SQLSTATE
value."
[We violate that last bit, but that's a documented issue and not something
we'll have to resolve here.]
"A statement information item gives information about the *innermost*
SQL-statement that is being executed when a condition is raised. A condition
information item gives information about the condition itself."
[If warnings are generated at various points of stored program execution, the
caller will NOT see them, as each new non-diagnostics statement will clear the
diagnostics area. After stored program execution, the DA will therefore not
contain the sum of all warnings thrown; the stored program call is not treated
as one (compound) statement in that respect. The diagnostics area found after
execution is that of the last non-diagnostics statement executed, with any non
exceptional conditions, i.e. warnings, thrown by any diagnostics statements
following said last non-diagnostics statement appended to the diagnostics area.]
"At the beginning of the execution of any that is not
an , the first diagnostics area is emptied. An
implementation places information about a completion condition or an exception
condition reported by SQLSTATE into a vacant condition area in this diagnostics
area. If other conditions are raised, the extent to which these cause further
condition areas to become occupied is implementation-defined."
13.5
Format
- OPEN, FETCH, CLOSE are SQL procedure statements
- PREPARE, EXECUTE, DEALLOCATE are SQL dynamic statements, and therefore SQL
procedure statements
- CALL and RETURN are SQL control statements, and therefore SQL procedure statements
General rules, 11
"If S is not an , then diagnostics information
resulting from the execution of S is placed into the first diagnostics area,
causing the first condition area in the first diagnostics area to become
occupied. Whether any other condition areas become occupied is
implementation-defined."
15.19 Execution of triggers
General rules, 5
k) If the execution of TSS is not successful, then an exception condition is
raised: triggered action exception. The exception condition that caused TSS to
fail is raised.
NOTE 430 Raising the exception condition that caused TSS to fail enters the
exception information into the diagnostics area that was pushed prior to the
execution of TSS.
20.13
General rules 3,
"Exception condition or completion condition information resulting from the
PREPARE or EXECUTE is reflected in the diagnostics area."
22.1
General rules
5) If S does not conform to the Format, Syntax Rules, and Access Rules for a
, then an exception condition is raised: syntax error or
access rule violation.
6b v) "The first diagnostics area is emptied." (before execution)
10) Diagnostics information resulting from the execution of S is placed into the
first diagnostics area, causing the first condition area in the first
diagnostics area to become occupied.
NOTE 473 The method of accessing the diagnostics information is
implementation-defined, but does not alter the contents of the diagnostics area.
23.1
23.1
2 a) NOTE 474 The itself may return information
via the SQLSTATE parameter, but does not modify the previous contents of DA.
3) If is specified, then let N be the value of
. If N is less than 1 (one) or greater than the number of
occupied condition areas in DA, then an exception condition is raised: invalid
condition number.
24.1 SQLSTATE
"If multiple completion conditions: warning or multiple exception conditions,
including implementation-defined exception conditions, are raised, then it is
implementation-dependent which of the corresponding SQLSTATE values is returned
in the SQLSTATE status parameter, provided that the precedence rules in
Subclause 4.29.2, "Status parameters", are obeyed. Any number of applicable
conditions values in addition to the one returned in the SQLSTATE status
parameter, may be returned in the diagnostics area."
PSM 2007
4.10.9 Compound statements
A compound statement allows a sequence of SQL-statements to be considered as a
single SQL-statement. A compound statement also defines a local scope in which
SQL-variables, condition handlers, and standing SQLserver cursors can be declared.
PSM 14.1
::=
[ ] BEGIN [ [ NOT ] ATOMIC ]
[ ] [ ]
[ ]
[ ]
END [ ]
General Rules
2) The SQL variables, standing SQL-server cursors, and handlers specified in the
, , and the of CS are created in an implementation-dependent order.
3) c) If the execution of [a statement] Si [within the current compound
statement] terminates with exception conditions or completion conditions other
than successful completion, then:
i) The following is effectively executed without further
Syntax Rule checking: RESIGNAL
ii) If there are unhandled exception conditions at the completion of the
execution of a handler (if any), then the execution of CS is terminated immediately.
PSM 14.4
"The following SQL-statement is effectively executed:
SET = ; NULL if not otherwise stated"
[This implies clearing the DA.]
PSM 14.2
2) Let CS be the simply containing HD.
3) When H is *activated*:
a) If H is activated in an atomic execution context and the condition raised is
a transaction rollback with any subcondition, then the following is effectively executed: RESIGNAL
NOTE 20 If a condition results in an implicit rollback (See Subclause 4.35.5,
"Implicit rollbacks", in [ISO9075-2]) in an atomic execution context, the
transaction has been effectively rolled back by the time the Handler body is
executed.
If any transaction initiating statement is executed following this, it would
require a transaction to be initiated in an atomic execution context, which is
not valid. Therefore, the condition is effectively resignaled to an outer
non-atomic execution context.
b) Case:
i) If HD specifies CONTINUE, then:
1) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with PUSH as OPERATION and the
diagnostics area stack as STACK.
2) HA is executed.
3) Case:
A) If there is an unhandled condition other than successful completion at the
completion of HA, then:
I) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with PUSH as OPERATION and the
diagnostics area stack as STACK.
II) The following is effectively executed: RESIGNAL
B) Otherwise:
I) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with POP as OPERATION and the
diagnostics area stack as STACK.
II) HA completes with completion condition successful completion and the
SQLsession continues as it would have done if execution of the innermost
executing statement that raised the condition had completed.
ii) If HD specifies EXIT, then:
1) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with PUSH as OPERATION and the
diagnostics area stack as STACK.
2) HA is executed.
3) For every open standing SQL-server cursor CR declared in the of CS, the General Rules of Subclause 15.4, "Effect of closing
a cursor", in [ISO9075-2], are applied with CR as CURSOR and SAVE as DISPOSITION.
4) Case:
A) If there is an unhandled condition other than successful completion at the
completion of HA, then:
I) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with PUSH as OPERATION and the
diagnostics area stack as STACK.
II) The following is effectively executed: RESIGNAL
B) Otherwise:
I) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with POP as OPERATION and the
diagnostics area stack as STACK.
II) HA completes with completion condition successful completion and the
SQLsession continues as it would have done if execution of CS had completed.
iii) If HD specifies UNDO, then:
1) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with PUSH as OPERATION and the
diagnostics area stack as STACK.
2) All changes made to SQL-data or schemas by the execution of SQL-statements
contained in the of CS and any s
triggered by the execution of any such statements are canceled.
3) For every open standing SQL-server cursor CR declared in the of CS, the General Rules of Subclause 15.4, "Effect of closing
a cursor", in [ISO9075-2], are applied with CR as CURSOR and SAVE as DISPOSITION.
4) HA is executed.
5) Case:
A) If there is an unhandled condition other than successful completion at the
completion of HA, then:
I) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with PUSH as OPERATION and the
diagnostics area stack as STACK.
II) The following is effectively executed: RESIGNAL
B) Otherwise:
I) The General Rules of Subclause 23.2, "Pushing and popping the diagnostics
area stack", in [ISO9075-2], are applied with POP as OPERATION and the
diagnostics area stack as STACK.
II) HA completes with completion condition successful completion and the
SQLsession continues as it would have done if execution of CS had completed.
Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.