The Spring Boot integration Druid is abnormal

In the Spring Boot integration Druid project, error logs frequently contain the following error messages:

discard long time none received connection. , jdbcUrl : jdbc:mysql://******?useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8, version : 1.2.3, lastPacketReceivedIdleMillis : 172675
Copy the code

Druid version 1.2.2 and earlier versions of Druid did not cause this exception. This problem exists in the previous versions. The following is to analyze the cause and solution of the exception.

Abnormal analysis

First of all, the above exception does not affect the normal operation of the program, but as a programmer to see the program constantly exception is unbearable. So we have to get to the bottom of it.

Trace stack information will find corresponding anomaly from the alibaba. Druid. Pool. DruidAbstractDataSource# testConnectionInternal method from them, the corresponding code is as follows:

if (valid && isMySql) { // unexcepted branch long lastPacketReceivedTimeMs = MySqlUtils.getLastPacketReceivedTimeMs(conn); if (lastPacketReceivedTimeMs > 0) { long mysqlIdleMillis = currentTimeMillis - lastPacketReceivedTimeMs; if (lastPacketReceivedTimeMs > 0 // && mysqlIdleMillis >= timeBetweenEvictionRunsMillis) { discardConnection(holder); String errorMsg = "discard long time none received connection. " + ", jdbcUrl : " + jdbcUrl + ", jdbcUrl : " + jdbcUrl + ", lastPacketReceivedIdleMillis : " + mysqlIdleMillis; LOG.error(errorMsg); return false; }}}Copy the code

The above code, MySqlUtils. GetLastPacketReceivedTimeMs (conn) is to obtain the last time, use mysqlIdleMillis is calculated free time, TimeBetweenEvictionRunsMillis is constant in 60 seconds. If the connection is idle for more than 60 seconds, discardConnection(holder) discards the old connection and prints a LOG log.warn (errorMsg) along the way.

The principle of track

In the code above, we see that there is a prerequisite for entering the business logic, namely that both the valid and isMySql variables are true. IsMySql is required to be true; we are using the Mysql database itself. So can I make valid false? So won’t you enter the business processing?

Let’s look at the source for valid, again above this method:

boolean valid = validConnectionChecker.isValidConnection(conn, validationQuery, validationQueryTimeout);

Copy the code

We find the Mysql validConnectionChecker subclasses MySqlValidConnectionChecker, achieve the class implementation to isValidConnection are as follows:

public boolean isValidConnection(Connection conn, String validateQuery, int validationQueryTimeout) throws Exception { if (conn.isClosed()) { return false; } if (usePingMethod) { if (conn instanceof DruidPooledConnection) { conn = ((DruidPooledConnection) conn).getConnection(); } if (conn instanceof ConnectionProxy) { conn = ((ConnectionProxy) conn).getRawObject(); } if (clazz.isAssignableFrom(conn.getClass())) { if (validationQueryTimeout <= 0) { validationQueryTimeout = DEFAULT_VALIDATION_QUERY_TIMEOUT; } try { ping.invoke(conn, true, validationQueryTimeout * 1000); } catch (InvocationTargetException e) { Throwable cause = e.getCause(); if (cause instanceof SQLException) { throw (SQLException) cause; } throw e; } return true; } } String query = validateQuery; if (validateQuery == null || validateQuery.isEmpty()) { query = DEFAULT_VALIDATION_QUERY; } Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); if (validationQueryTimeout > 0) { stmt.setQueryTimeout(validationQueryTimeout); } rs = stmt.executeQuery(query); return true; } finally { JdbcUtils.close(rs); JdbcUtils.close(stmt); }}Copy the code

We can see three returns from the above method: the first connection is closed; The second uses the ping form to check; Third, use select 1 to check. Ping returns true regardless of whether an exception is thrown. We can disable this mode here.

The business logic that goes into ping is determined by the variable usePingMethod. Tracing the code will find the setting here:

public void configFromProperties(Properties properties) { String property = properties.getProperty("druid.mysql.usePingMethod"); if ("true".equals(property)) { setUsePingMethod(true); } else if ("false".equals(property)) { setUsePingMethod(false); }}Copy the code

So, that is to say, when we put the system properties druid. Mysql. UsePingMethod can be set to false to disable this feature.

Disable Ping Method

Once the root of the problem is found, all that remains is how to disable it, usually in one of three forms.

First, the increase in operating parameters when start the program: – Ddruid. Mysql. UsePingMethod = false.

Second, in a Spring Boot project, you can add the following static code to the Boot class:

static {
    System.setProperty("druid.mysql.usePingMethod","false");
}
Copy the code

Third, class file configuration. Add the following to the project’s DruidConfig class:

/* * Druid log error: discard long time none received connection:xxx * */ @PostConstruct public void setProperties(){ System.setProperty("druid.mysql.usePingMethod","false"); }Copy the code

At this point, the feature is successfully turned off and the exception message is no longer displayed.

Why clear connections that have been idle for more than 60 seconds

Ali set the idle waiting time for the database to be 60 seconds. When the idle waiting time is reached, the mysql database will close the idle connections to improve the processing capacity of the database server.

MySQL’s default idle wait time is 8 hours, which is the configured value of wait_timeout. An exception occurs if the database actively closes a free connection and the pool is unaware that the connection is still in use.