spring-boot web app loses ability to connect to My

Question

I have a normal spring boot 1.2.x web app with an embedded Tomcat 7.x container and connected to an RDS instance (running MySQL 5.6). If the application is idle for a period of time (8 hours?) and then it receives a request it throws the following exception

** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: The last packet successfully received from the server was39320 seconds ago.The last packet sent successfully to the server was 39320 seconds ago, whi
ch  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your ap
plication, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this proble
m.

STACKTRACE:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was39320 seconds ago.The last packet sent succe
ssfully to the server was 39320 seconds ago, which  is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or t
esting connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection pr
operty 'autoReconnect=true' to avoid this problem.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
... trimmed more of the stacktrace ... 
Caused by: java.net.SocketException: Broken pipe
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
	at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
	at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3227)
	... 119 more


** END NESTED EXCEPTION **

In my application.yml (where configurations for datasource, hibernate, etc. can be set) I have the following (this is part of what I get when I call the management API /env

   "applicationConfig: [classpath:/application.yml]#rds-profile":{  
      "spring.profiles":"rds-profile",
      "spring.datasource.driverClassName":"com.mysql.jdbc.Driver",
      "spring.datasource.url":"jdbc:mysql://rds-host:3306/mydb?user=mysqlusername&password=****",
      "spring.datasource.schema":"classpath:/schema.sql",
      "spring.datasource.username":"mysqlusername",
      "spring.datasource.password":"******",
      "spring.datasource.testOnBorrow":true,
      "spring.datasource.validationQuery":"SELECT 1",
      "spring.datasource.continueOnError":true,
      "spring.datasource.timeBetweenEvictionRunsMillis":5000,
      "spring.datasource.minEvictableIdleTimeMillis":5000,
      "spring.datasource.max-active":500,
      "spring.jpa.database-platform":"org.hibernate.dialect.MySQL5InnoDBDialect",
      "spring.jpa.database":"MYSQL",
      "spring.jpa.show-sql":false,
      "spring.jpa.generate-ddl":false,
      "spring.jpa.hibernate.ddl-auto":"none",
      "spring.jpa.hibernate.dialect":"org.hibernate.dialect.MySQL5InnoDBDialect"
   },

Curiously, when I call the management API "/configprops" I get this (I don't know if this is the root of the problem?

  "spring.datasource.CONFIGURATION_PROPERTIES":{  
      "prefix":"spring.datasource",
      "properties":{  
         "platform":"all",
         "data":null,
         "driverClassName":"com.mysql.jdbc.Driver",
         "password":"******",
         "url":"jdbc:mysql://rds-host:3306/mydb?user=mysqlusername&password=****",
         "schema":"classpath:/schema.sql",
         "username":"mysqlusername",
         "jndiName":null,
         "xa":{  
            "dataSourceClassName":null,
            "properties":{  

            }
         },
         "continueOnError":true,
         "sqlScriptEncoding":null,
         "separator":";",
         "initialize":true
      }
   },

The question is: given the above configurations and details, why is it that I am still getting the "wait_timeout" exception? I would expect the connections to be tested when borrowed and I would expect the JDBC connection pool to create valid connections if none are available... so why is my application running out of valid connections after (8 hours?) or inactivity?

Thank you.

Answer 1

Try using this as well

spring.datasource.test-while-idle=true
spring.datasource.validation-interval=5000

Answer 2

If you are using auto-configuration to define RDS connection from the property file like this:

cloud.aws.rds.testdb.password=testdbpwd
cloud.aws.rds.testdb.username=testdbuser
cloud.aws.rds.testdb.databaseName=testdb

spring boot datasource auto-configuration will not work even you put these(or tomcat datasource conf) to your configuration file:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.test-on-borrow: true
spring.datasource.validation-query: SELECT 1 FROM DUAL
spring.datasource.log-validation-errors: true

I think this is the reason why you cannot validate your connections in the pool, before using them.

You need to override postProcessAfterInitialization method to set pool properties of the TomcatJdbcDataSourceFactory bean like this:

@Component
public class PoolConfiguration implements BeanPostProcessor {

@Override
public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
    if (bean instanceof TomcatJdbcDataSourceFactory) {
        TomcatJdbcDataSourceFactory tomcatJdbcDataSourceFactory = (TomcatJdbcDataSourceFactory) bean;
        tomcatJdbcDataSourceFactory.setTestOnBorrow(true);
        tomcatJdbcDataSourceFactory.setTestWhileIdle(true);
        tomcatJdbcDataSourceFactory.setValidationQuery("SELECT 1");
    }
    return bean;
}
}

I could not find any other solution for this.By the way this might be a bug of spring-cloud-aws-autoconfigure packet.

Good Luck!