Topics in this section:
Notices are a way to send axillary data from the server to the client that can be:
global
local to the current message sequence
Notices don't affect the state of the current message sequence, that means: the client is may ignore notices and still be able to properly understand the message sequence.
Global Notices are sent by the server in case of events happen that are unrelated to the currently active message sequence:
server is shutting down
node disconnected from group
schema or table dropped
binlog events
Server Shutting Down
The server indicates that it shuts down in a clean way.
Node Disconnected from Group
The slave stopped to replicate changes from the master/group and may contain stale information.
Checking for SHOW SLAVE STATUS
and
io_thread
and sql_thread
leads to a race condition for the following query. One would
have to check after query of the slave is still running to see
if it didn't stop in between.
Schema or Table Dropped/Altered/...
If a client maintains a cache of recent queries + resultsets it would improve the caching behavior if the client would be notified if a underlying table or schema was dropped, changed, and so on.
Local Notices are related to the currently active Message Sequence like:
Committed Transaction IDs
Transaction State Changes
SQL warnings
Warning
Session Variable Changed
It allows intermediates to track state changes on the clients session that may be otherwise unnoticeable like:
CREATE PROCEDURE init() BEGIN
SET @@sql_mode=ANSI;
END
Session variable changes are usually done from the client via
SET @@...
or SELECT @@... :=
1
, but can also be done via:
stored procedures
triggers
-
connection setup
@@character_set_server
Part of this functionality is provided in the MySQL C/S
Protocol via WL#4797
SESSION_SYSVAR_TRACKER
and the initial
handshake packet.
Session State Changed
Via
SessionStateChanged
.
Account Expired while Authentication
current schema changes:
USE ...
sever-side generated primary keys (like
AUTO_INCREMENT
)rows-found, rows-matched, rows-affected
- CURRENT_SCHEMA
Sent after statement that changes the current schema like
USE ...
- GENERATED_INSERT_ID
-
Sent after an ID was created by an INSERT-operation.
NoteMultiple
GENERATED_INSERT_ID
notices may be sent per message sequence. Stored Procedures, Multi-Row INSERTs, and so on. - ROWS_FOUND
Rows that would be found if
LIMIT
wasn't applied (seeSQL_CALC_FOUND_ROWS
)- ROWS_AFFECTED
Rows affected by a modifying statement
- ROWS_MATCHED
-
Rows matched by the criteria of a modifying statement (
UPDATE
,INSERT
,DELETE
, ...)NoteROWS_AFFECTED
andROWS_MATCHED
where sent in the MySQL C/S Protocol as plain textinfo
for aOK
packet after anUPDATE
:Rows matched: 0 Changed: 0 Warnings: 0
- ACCOUNT_EXPIRED
Sent after a successful authentication before
AuthenticateOk
- TRX_COMMITTED
-
Sent after a transaction was committed. .value may contain a transaction identifier.
NoteUsed to track implicit, explicit and auto commits.
- TRX_ROLLEDBACK
-
Sent after a transaction was rolled back.
NoteUsed to track implicit and explicit rollback operations.
- SESSION_ID
Sent after a session ID is assigned by the server.
The MySQL C/S provided some of this information via functions:
Parameter | Information Functions |
---|---|
CURRENT_SCHEMA | DATABASE() |
GENERATED_INSERT_ID | LAST_INSERT_ID() |
ROWS_FOUND | FOUND_ROWS() |
ROWS_AFFECTED | ROW_COUNT() |
SESSION_ID | CONNECTION_ID() |