15.5 Notices

Topics in this section:

• Global Notices

• Local Notices

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

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.

Note

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

Local Notices are related to the currently active Message Sequence like:

• Committed Transaction IDs

• Transaction State Changes

• SQL warnings Warning

• session variable changed and session state changed

Session Variable Changed

Via SessionVariableChanged

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

Note

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.

Note

Multiple 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 (see SQL_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, ...)

Note

ROWS_AFFECTED and ROWS_MATCHED where sent in the MySQL C/S Protocol as plain text info for a OK packet after an UPDATE:

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.

Note

Used to track implicit, explicit and auto commits.

Tip

http://dev.mysql.com/doc/en/innodb-implicit-commit.html

TRX_ROLLEDBACK

Sent after a transaction was rolled back.

Note

Used to track implicit and explicit rollback operations.

Tip

http://dev.mysql.com/doc/en/innodb-implicit-commit.html

SESSION_ID

Sent after a session ID is assigned by the server.

Note

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()