Issue with druid version after 1.2.11. The last packet successfully received from the server was 10,036 milliseconds ago. The last packet sent successfully to the server was 10,036 milliseconds ago.

[vuonglam94]

I have an issue when upgrading Druid from version 1.2.11 to 1.2.18. The new version adds socketTimeout in DruidDataSource.java and the default value is 10_000.
I think it is the reason of exception below:

The last packet successfully received from the server was 10,036 milliseconds ago. The last packet sent successfully to the server was 10,036 milliseconds ago.
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:100)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)

But I think the default socketTimeout of MySQL JDBC is 0 (Timeout, specified in milliseconds, on network socket operations. Value "0" means no timeout).
How can I set socketTimeout in Druid 1.2.18 to no timeout? Please help.

[lizongbo]

The first method

you can set connectTimeout and socketTimeout property value with the jdbc url, just like follow :

            String jdbcUrl =  "jdbc:mysql://192.168.1.1:3306/druiddbdemo?characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&connectTimeout=10000&socketTimeout=1800000

see https://dev.mysql.com/doc/connector-j/8.1/en/connector-j-connp-props-networking.html

but the value must be in 0 - 2147483647， otherwise ，you will seesome exception ：

Caused by: com.mysql.cj.exceptions.WrongArgumentException: The connection property 'connectTimeout' only accepts integer values in the range of 0 - 2147483647, the value '-1' exceeds this range.

Caused by: com.mysql.cj.exceptions.WrongArgumentException: The connection property 'socketTimeout' only accepts integer values in the range of 0 - 2147483647, the value '-1' exceeds this range.

The second method:

ou can call setConnectTimeout and setSocketTimeout to -1 ,so no timout forever ,same as before.

the demo code is follow:

    @Test
    public void testCheckDruidSocketTime() throws SQLException, InterruptedException {
        ThreadPoolExecutor tpe = new ThreadPoolExecutor(5, 5, 20, TimeUnit.SECONDS, new ArrayBlockingQueue<>(500));
        ScheduledThreadPoolExecutor druidCPCreate = new ScheduledThreadPoolExecutor(3);
        ScheduledThreadPoolExecutor druidCPDestroy = new ScheduledThreadPoolExecutor(3);
        DruidDataSource dds = newDataSource(druidCPCreate, druidCPDestroy);
        System.out.println("getConnectTimeout==" + dds.getConnectTimeout());
        System.out.println("getSocketTimeout==" + dds.getSocketTimeout());
    }
    
    public static DruidDataSource newDataSource(ScheduledThreadPoolExecutor druidCPCreate, ScheduledThreadPoolExecutor druidCPDestroy) throws SQLException {
        System.setProperty("druid.mysql.usePingMethod", "false");
        System.setProperty("druid.log.conn", "true");
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(Driver.class.getName());
        dataSource.setUrl(
                "jdbc:mysql://192.168.1.1:3306/druiddbdemo?characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=Asia/Shanghai&connectTimeout=10000&socketTimeout=1800000");
        dataSource.setUsername("demouser");
        dataSource.setPassword("demopwd");
        dataSource.setInitialSize(10);
        dataSource.setMinIdle(10);//
        dataSource.setMaxActive(100);
        dataSource.setMaxWait(10000);
        //set -1 to no timout forever
       dataSource.setConnectTimeout(-1);
        dataSource.setSocketTimeout(-1);
        dataSource.setTimeBetweenEvictionRunsMillis(6000);
        dataSource.setMinEvictableIdleTimeMillis(30000);
        dataSource.setMaxEvictableIdleTimeMillis(40000);
        // dataSource.setPhyTimeoutMillis(180000);
        dataSource.setCreateScheduler(druidCPCreate);
        dataSource.setDestroyScheduler(druidCPDestroy);
        dataSource.setKeepAlive(true);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setPoolPreparedStatements(false);
        dataSource.setFilters("slf4j");
        List<Filter> filters = new ArrayList<>();
        Slf4jLogFilter slf4jLogFilter = new Slf4jLogFilter();
        filters.add(slf4jLogFilter);
        filters.add(weupConnAliveCheckFilter);
        dataSource.setProxyFilters(filters);
        dataSource.init();
        System.out.println(slf4jLogFilter);
        return dataSource;
    }

spring bean config just like follow:

 <bean id="dataSource"
    class="com.alibaba.druid.pool.DruidDataSource" init-method="init"
    destroy-method="close">
    <property name="connectTimeout" value="-1" />
    <property name="socketTimeout" value="-1" />

  </bean>

I suggest you use the second method

[vuonglam94]

@lizongbo, thanks for your help.