Light-weight 'ping' to Mysql Server

Posted on | Last modified: | In |
Words count: 396

The MySQL JDBC driver (Connector/J) provides a ping mechanism.

If you do a SQL query prepended with / ping / such as:

1
/* ping */ SELECT 1

This will actually cause the driver send a ping to the server and return a fake, light-weight, result set.

摘自https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html#idm139975764832048

MySQL Connector/J can validate the connection by executing a lightweight ping against a server. In the case of load-balanced connections, this is performed against all active pooled internal connections that are retained. This is beneficial to Java applications using connection pools, as the pool can use this feature to validate connections. Depending on your connection pool and configuration, this validation can be carried out at different times:

  1. Before the pool returns a connection to the application.
  2. When the application returns a connection to the pool.
  3. During periodic checks of idle connections.

To use this feature, specify a validation query in your connection pool that starts with /* ping */. Note that the syntax must be exactly as specified. This will cause the driver send a ping to the server and return a dummy lightweight result set. When using a ReplicationConnection or LoadBalancedConnection, the ping will be sent across all active connections.

1
2
3
4
5
6
protected static final String PING_MARKER = "/* ping */";
... 
if (sql.charAt(0) == '/') { 
	if (sql.startsWith(PING_MARKER)) { 
		doPingInstead(); 
		...

All of the previous statements will issue a normal SELECT statement and will not be transformed into the lightweight ping. Further, for load-balanced connections, the statement will be executed against one connection in the internal pool, rather than validating each underlying physical connection. This results in the non-active physical connections assuming a stale state, and they may die. If Connector/J then re-balances, it might select a dead connection, resulting in an exception being passed to the application. To help prevent this, you can use loadBalanceValidateConnectionOnSwapServer to validate the connection before use.

If your Connector/J deployment uses a connection pool that allows you to specify a validation query, take advantage of it, but ensure that the query starts exactly with /* ping */. This is particularly important if you are using the load-balancing or replication-aware features of Connector/J, as it will help keep alive connections which otherwise will go stale and die, causing problems later.