How to properly configure Spring Datasource for an Elastic Beanstalk app?

Question

I'm running into an issue integrating Spring Security with my Elastic Beanstalk app backed by a MySql database. If I deploy my app I'm able to login in correctly for some time but eventually I'll start to receive login errors without an exception being thrown so I'm unable to get any useful information about the issue. I've downloaded the logs as well and can't see anything of value. I can see where the logs show accessing the public page, attempting to access the private section, returning the login page, and then the loginError page; however, nothing about any issue.

Even though I'm unable to login through a browser I am able to login if I run the app from an IDE as well as view the db in MySQL Workbench. This suggests to me the problem is due to some persistent state on the server.

I've had a similar problem before with another Beanstalk app using Spring Security and was able to resolve it by setting application properties as follows:

spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1

I'm using a more recent version of Spring than that app and the properties have been changed to specific datasources so I tried adding the following properties:

spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.validation-query=SELECT 1

When that didn't work I added another based on an answer to a similar question here; now the properties are:

spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.test-while-idle=true
spring.datasource.tomcat.validation-query=SELECT 1

That seemed to work (possibly due to less login activity) but eventually resulted in the same behavior .

I've looked into the various properties available but before I spend a lot of time randomly setting and/or overriding default settings I wanted to see if there's a reliable way to deal with this.

How can I configure my datasource to avoid login errors after long periods of time?

ChiefTwoPencils · Accepted Answer · 2017-02-28T04:39:53.030

This isn't a problem of specific configuration values but with where those configurations reside. The default location for the application.properties (/resources; Intellij) is fine for deploying as a jar with an embedded Tomcat server but not as a war with a provided server. The file isn't found/used so no changes to the file affect the one given by AWS.

There are a number of ways to handle this; I chose to add an RDS configuration bean in my SpringBootServletInitializer:

@Bean
public RdsInstanceConfigurer instanceConfigurer() {
    return () -> {
        TomcatJdbcDataSourceFactory dataSourceFactory =
                new TomcatJdbcDataSourceFactory();
        // Abondoned connections...
        dataSourceFactory.setRemoveAbandonedTimeout(60);
        dataSourceFactory.setRemoveAbandoned(true);
        dataSourceFactory.setLogAbandoned(true);
        // Tests
        dataSourceFactory.setTestOnBorrow(true);
        dataSourceFactory.setTestOnReturn(false);
        dataSourceFactory.setTestWhileIdle(false);
        // Validations
        dataSourceFactory.setValidationInterval(30000);
        dataSourceFactory.setTimeBetweenEvictionRunsMillis(30000);
        dataSourceFactory.setValidationQuery("SELECT 1");
        return dataSourceFactory;
    };
}

score 0 · Answer 2 · edited Aug 24 '17 at 02:03

Below are the settings that worked for me. From Connection to Db dies after >4<24 in spring-boot jpa hibernate

dataSourceFactory.setMaxActive(10);
dataSourceFactory.setInitialSize(10);
dataSourceFactory.setMaxIdle(10);
dataSourceFactory.setMinIdle(1);
dataSourceFactory.setTestWhileIdle(true);
dataSourceFactory.setTestOnBorrow(true);
dataSourceFactory.setValidationQuery("SELECT 1 FROM DUAL");
dataSourceFactory.setValidationInterval(10000);
dataSourceFactory.setTimeBetweenEvictionRunsMillis(20000); 
dataSourceFactory.setMinEvictableIdleTimeMillis(60000);

How to properly configure Spring Datasource for an Elastic Beanstalk app?

2 Answers2