3.7 MySQL Router Set Trace

Configuration

To configure ROUTER SET trace you must add the following to your MySQL Router configuration file:

max_idle_server_connections: add to the DEFAULT section. This must be set to at least 1.
The following values can be added to the DEFAULT section and apply to all connections, or you can add them to the individual ROUTING: ... sections of connections you want to examine in detail.
- client_ssl_mode: Set to PREFERRED or REQUIRED.
- server_ssl_mode: Set to PREFERRED,REQUIRED, or DISABLED.
- connection_sharing: Set to 1 to enable connection sharing.

For example:

[DEFAULT]
max_idle_server_connections=64 

[routing:{...}]
client_ssl_mode=PREFERRED 
server_ssl_mode=PREFERRED
connection_sharing=1

Enable ROUTER TRACE

ROUTER TRACE can be enabled per session or per statement on the command line of your MySQL client.

Enable per session:
```
ROUTER SET TRACE = 1;
```
Disable per session:
```
ROUTER SET TRACE = 0;
```
Enable per statement:
```
query_attributes router.trace 1;
```
Disable per statement:
```
query_attributes router.trace 0;
```

Trace Format

The trace is returned in a JSON object with the following properties:

start_time: Date and time string denoting the start of the span.
end_time: Date and time string denoting the end of the span.
elapsed_in_span_us: Microseconds spent in the current span. This value is end_time minus start_time.
status_code: Represents the canonical status code of a finished Span. Default value is empty.
name: Name of the event.
attributes: Attributes of the event.
events: An array of events. These contain the following:
- timestamp: Date and time string.
- name: Name of the event.
- attributes: Attributes of the event.

For example:

> mysql --host=127.0.0.1 --port=6446 --show-warnings
> ROUTER SET trace = 1;
> SELECT @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set, 1 warning (0,02 sec)
Note (code 4600): {
  "start_time": "2023-03-23T15:31:08.052442Z",
  "end_time": "2023-03-23T15:31:08.052653Z",
  "elapsed_in_span_us": 211,
  "name": "mysql/query",
  "attributes": {
    "mysql.sharing_blocked": false
  },
  "events": [
    {
      "timestamp": "2023-03-23T15:31:08.052444Z",
      "name": "mysql/query_classify",
      "attributes": {
        "mysql.query.classification": "change_on_tracker"
      }
    },
    {
      "start_time": "2023-03-23T15:31:08.052455Z",
      "end_time": "2023-03-23T15:31:08.052495Z",
      "elapsed_in_span_us": 39,
      "name": "mysql/connect_and_forward",
      "attributes": {
        "mysql.remote.is_connected": true,
        "mysql.remote.endpoint": "localhost:3306",
        "mysql.remote.connection_id": 17,
        "db.name": ""
      },
      "events": [
        {
          "start_time": "2023-03-23T15:31:08.052458Z",
          "end_time": "2023-03-23T15:31:08.052495Z",
          "elapsed_in_span_us": 36,
          "name": "mysql/forward"
        }
      ]
    },
    {
      "start_time": "2023-03-23T15:31:08.052623Z",
      "end_time": "2023-03-23T15:31:08.052627Z",
      "elapsed_in_span_us": 3,
      "name": "mysql/response",
      "attributes": {
        "mysql.session.@@SESSION.statement_id": "84"
      }
    }
  ]
}

Trace Events

The following trace events and attributes are supported:

mysql/query

MySQL Router receives a query.

Attributes:

mysql.sharing_blocked: Boolean. If connection sharing is blocked, mysql.sharing_blocked_by is displayed along with a reason why sharing is blocked.
mysql.sharing_blocked_by: String. Displays the reason why connection sharing is blocked. This can be one of the following values:
- trx-state: A transaction is active.
- trx-characteristics: Transaction state is set. For example, SET TRANSACTION READ ONLY.
- some-state-changed: The session is in an unrecoverable state.
- session-track-gtids: session_track_gtids does not contain the expected value.
- session-track-state-change: session_track_state_change does not contain the expected value.
- session-track-transaction-info: session_track_state_change does not contain the expected value.

mysql/query_classify

Describes how MySQL Router analyzed the statement in the context of connection-sharing.

Attributes:

mysql.query.classification: comma-separated list of none or more of the following:
- accept_session_state_from_session_tracker: The statement resulted in a notification from the session tracker which was accepted as is.
- ignore_session_tracker_some_state_changed: The statement resulted in a notification from the session tracker which was ignored.
- session_not_sharable_on_error: Statements such as SET known_variable = 1, unknown_variable = 2 can cause a session state change, although the statement failed. The server responds with an error, but no session tracker, even though the session state changed.
- session_not_sharable_on_success: Set if a statement modifies the session state, but the session tracker does not report it.
- forbidden_function_with_connection_sharing: The statement contains functions or keywords which are not possible with connection sharing. Such as GET DIAGNOSTICS or LAST_INSERT_ID().
- forbidden_set_with_connection_sharing: The statement attempted to set the session tracker information required for connection sharing.

mysql/connect_and_forward

Attributes:

mysql.remote.is_connected: Boolean. If fale, there is no connection. If true, the following values are returned:
- mysql.remote.endpoint: Name of the server connection endpoint.
- mysql.remote.connection_id: Connection ID of the server connection.
- db.name: Name of the schema.

mysql/from_pool_or_connect

Attributes:

mysql.remote.candidates: Comma-separated list of endpoints.
net.peer.name: Hostname of the endpoint this connection connected to in its previous session.
net.peer.port: Port of the endpoint this connection connected to in its previous session.

mysql/from_pool

Attributes:

mysql.error_message: Displayed if status_code is ERROR.
mysql.remote.connection_id: Connection ID of the server connection.

mysql/connect

Attributes:

net.peer.name: Hostname of the endpoint.
net.peer.port: Port of the endpoint.

mysql/authenticate

Attributes:

mysql.remote.needs_full_authentication: Boolean. If a full handshake is required (true) or if a fast reset-connection is possible (false).
If true, followed by mysql/change_user. If false, followed by mysql/reset_connected.

mysql/server_greeting

Attributes:

mysql.remote.connection_id: Connection ID of the server connection.

mysql/client_greeting

Attributes:

db.name: Name of the schema.

mysql/tls_connect

Attributes:

tls.version: TLS version in use.
tls.cipher: TLS cipher used for the connection.
tls.session_resused: Boolean. True if the TLS session was reused.

mysql/response

Attributes:

mysql.session.@@SESSION.*: Session variables changed according to the server session tracker.
mysql.session.transaction_state: Comma-separated list of transaction states.
mysql.session.transaction_characteristics: Statement required to restore the transaction state.

mysql/set_var

Attributes:

mysql.session.@@SESSION.*: Session variables restored after a reconnect.

The following events have the same attributes as mysql/query:

mysql/ping
mysql/stmt_prepare
mysql/stmt_execute
mysql/kill
mysql/statistics
mysql/set_option
mysql/reload
mysql/list_fields

The following events have no attributes:

mysql/prepare_server_connection:
mysql/reset_connection:
mysql/greeting:
mysql/forward:

Trace Examples

Simple Query Forwarding

The following example shows a trace of a simple forwarding of a query:

MySQL Router receives a query.
MySQL Router forwards the query to the server.
MySQL Router waits for the result.
MySQL Router forwards the result to the client.

$ mysql --host=127.0.0.1 --port=6446 --show-warnings
> ROUTER SET trace = 1;
> SELECT @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set, 1 warning (0,02 sec)
Note (code 4600): {
  "start_time": "2023-03-23T15:31:08.052442Z",
  "end_time": "2023-03-23T15:31:08.052653Z",
  "elapsed_in_span_us": 211,
  "name": "mysql/query",
  "attributes": {
    "mysql.sharing_blocked": false
  },
  "events": [
    {
      "timestamp": "2023-03-23T15:31:08.052444Z",
      "name": "mysql/query_classify",
      "attributes": {
        "mysql.query.classification": "accept_session_state_from_session_tracker"
      }
    },
    {
      "start_time": "2023-03-23T15:31:08.052455Z",
      "end_time": "2023-03-23T15:31:08.052495Z",
      "elapsed_in_span_us": 39,
      "name": "mysql/connect_and_forward",
      "attributes": {
        "mysql.remote.is_connected": true,
        "mysql.remote.endpoint": "localhost:3306",
        "mysql.remote.connection_id": 17,
        "db.name": ""
      },
      "events": [
        {
          "start_time": "2023-03-23T15:31:08.052458Z",
          "end_time": "2023-03-23T15:31:08.052495Z",
          "elapsed_in_span_us": 36,
          "name": "mysql/forward"
        }
      ]
    },
    {
      "start_time": "2023-03-23T15:31:08.052623Z",
      "end_time": "2023-03-23T15:31:08.052627Z",
      "elapsed_in_span_us": 3,
      "name": "mysql/response",
      "attributes": {
        "mysql.session.@@SESSION.statement_id": "84"
      }
    }
  ]
}