问题

线上在查比较复杂的SQL是,呈现以下报错:

Error querying database.  Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure\n\nThe last packet successfully received from the server was 10,181 milliseconds ago. The last packet sent successfully to the server was 10,239 milliseconds ago.

从报错意思上看,是数据库连贯曾经被敞开了。通过屡次试验确认,只有SQL执行超过10s,就会呈现以上谬误。

起因

我的项目是springboot,数据库连接池用的是阿里的druid-spring-boot-starter-1.2.15


如上图所示:JDBC通过socket对字节流进行解决,因而也会有一些根本网络操作,相似于HttpClient这种用于网络操作的代码库;同样的也会受到ConnectTimeout/SocketTime的影响。
DruidDataSource有设置以上超时的办法:

DruidDataSource dataSource = new DruidDataSource();dataSource.setConnectTimeout(100_000);dataSource.setSocketTimeout(100_000);

然而以后零碎并没有设置以上参数,那为什么申请超时10s就会报错?并且以后druid-spring-boot-starter-1.2.15版本并没有socketTime&connetctTimeout配置参数(无奈通过spring.datasource.druid前缀来配置)。
通过源码(DruidDatasource类)能够看到以下内容:

public class DruidDataSource extends DruidAbstractDataSource implements DruidDataSourceMBean, ManagedDataSource, Referenceable, Closeable, Cloneable, ConnectionPoolDataSource, MBeanRegistration {    public void init() throws SQLException {        …… // 省略        try {            …… // 省略            if (this.jdbcUrl != null) {                this.jdbcUrl = this.jdbcUrl.trim();                …… // 省略                initFromUrlOrProperties(); // 从url里解析参数            }            if (connectTimeout == 0) {                socketTimeout = DEFAULT_TIME_CONNECT_TIMEOUT_MILLIS; // 10_000            }            if (socketTimeout == 0) {                socketTimeout = DEFAULT_TIME_SOCKET_TIMEOUT_MILLIS; // 10_000            }            …… // 省略        } catch (SQLException e) {            …… // 省略        } finally {            …… // 省略        }    }}

通过以上源码能够看到,如果没有设置参数值,则会默认设置为10s
持续看initFromUrlOrProperties办法:

public class DruidDataSource extends DruidAbstractDataSource implements DruidDataSourceMBean, ManagedDataSource, Referenceable, Closeable, Cloneable, ConnectionPoolDataSource, MBeanRegistration {    private void initFromUrlOrProperties() {        // 从jdbc的url里解析           if (jdbcUrl.startsWith("jdbc:mysql://")) {            if (jdbcUrl.indexOf("connectTimeout=") != -1 || jdbcUrl.indexOf("socketTimeout=") != -1) {                String[] items = jdbcUrl.split("(\\?|&)");                for (int i = 0; i < items.length; i++) {                    String item = items[i];                    if (item.startsWith("connectTimeout=")) {                        String strVal = item.substring("connectTimeout=".length());                        setConnectTimeout(strVal);                    } else if (item.startsWith("socketTimeout=")) {                        String strVal = item.substring("socketTimeout=".length());                        setSocketTimeout(strVal);                    }                }            }            // 从配置的connectProperties里解析:spring.datasource.druid.connectionProperties=connectTimeout=50000;socketTimeout=50000;            Object propertyConnectTimeout = connectProperties.get("connectTimeout");            if (propertyConnectTimeout instanceof String) {                setConnectTimeout((String) propertyConnectTimeout);            } else if (propertyConnectTimeout instanceof Number) {                setConnectTimeout(((Number) propertyConnectTimeout).intValue());            }            Object propertySocketTimeout = connectProperties.get("socketTimeout");            if (propertySocketTimeout instanceof String) {                setSocketTimeout((String) propertySocketTimeout);            } else if (propertySocketTimeout instanceof Number) {                setSocketTimeout(((Number) propertySocketTimeout).intValue());            }        }    }    }

能够看到有以下两种形式来配置:

  • JDBC的URL:jdbc:mysql://xxxx?connectTimeout=50000&socketTimeout=50000
  • 通过配置spring.datasource.druid.connectionProperties来设置:spring.datasource.druid.connectionProperties=connectTimeout=50000;socketTimeout=50000;

并且因为initFromUrlOrProperties办法是在init办法内,以及propertyConnectTimeout是在URL前面解析,能够得出优先级:connectProperties 大于 jdbcUrl 大于 set办法