Spring boot integration Druid exception

In the spring boot integrated Druid project, the following error messages are frequently found in the error log:

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

After troubleshooting, it was found that the exception was caused by Druid version, which did not appear in version 1.2.2 or earlier. In the above versions, there is this problem, the following is to analyze the causes of the exception and solutions.

Anomaly 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 abnormal or intolerable. So we still need to get to the bottom of it.

Trace the stack information and find that the corresponding exception is thrown from the com.alibaba.druid.pool.druidabstractdatasource # testconnectioninternal method. 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;
        }
    }
}

In the above code, mysqlutils.getlastpacketreceivedtimems (conn) is to get the last used time, mysqlidle millis is to calculate the idle time, and timebetweenevecitionrunsmillis is a constant of 60 seconds. If the connection is idle for more than 60 seconds, the discard connection (holder) discards the old connection and prints a log. Warn (errormsg) along with it.

Principle tracing

In the above code, we can see that there is a prerequisite for entering the business logic, that is, the variables valid and ismysql are true at the same time. It is necessary for ismysql to be true. What we use is the MySQL database. Can I make valid false?In this way, it will not enter the business processing?

Let’s take a look at the source of the valid method

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

We find the MySQL implementation subclass of validconnectionchecker, MySQL validconnectionchecker. The implementation of isvalidconnection in this class is 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);
    }

}

We can see that there are three return places in the above methods: the first connection is closed; The second uses Ping to check; Third, use select 1 to check. When Ping is used, it will return true no matter whether the exception is thrown or not. Here we can disable this mode.

The business logic of Ping mainly depends on the variable usepingmethod. Tracing code will find the settings 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);
    }
}

In other words, when we set the system property Druid. Mysql. Usepingmethod to false, we can disable this function.

Disable ping method

After finding the root of the problem, the rest is how to disable it. There are usually three forms.

First, when starting the program, add: – Druid. Mysql. Usepingmethod = false in the running parameters.

Second, in the spring boot project, you can add the following static code to the startup class:

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

Third, class file configuration. In the druidconfig class of the project, add:

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

So far, the function has been successfully turned off, and the exception information will never appear again.

Why clear connections that are idle for more than 60 seconds

It is speculated that the idle waiting time of the database set by Alibaba is 60 seconds. When the MySQL database reaches the idle waiting time, the idle connection will be closed to improve the processing capacity of the database server.

The default idle waiting time of MySQL is 8 hours, which is “wait”_ “Timeout”. If the database actively closes the idle connection, but the connection pool does not know that it is still using the connection, an exception will be generated.